phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sergey Belousov <sergey.belou...@gmail.com>
Subject Re: Creating multiple views off existent HBase table.
Date Mon, 09 Mar 2015 04:11:34 GMT
done
https://issues.apache.org/jira/browse/PHOENIX-1714
let me know if it unclear and more clarification needed .
On Mar 8, 2015 11:47 PM, "James Taylor" <jamestaylor@apache.org> wrote:

> If you could break it down a bit, that'd be helpful, Sergey. It's a known
> limitation that we don't support aggregation in views. The other stuff
> should work, though. File one JIRA per issue you find.
> Thanks,
> James
>
> On Sun, Mar 8, 2015 at 6:58 PM, Sergey Belousov <sergey.belousov@gmail.com
> > wrote:
>
>> Hi James
>>
>> Would you be able to shred some light on issues with WHERE part for
>> CREATE VIEW AS I am having as described in prier message?
>> I am kind of stuck here :(.
>>
>> Thank you.
>>
>> On Fri, Mar 6, 2015 at 7:09 PM, Sergey Belousov <
>> sergey.belousov@gmail.com> wrote:
>>
>>> And in addition for CREATE VIEW ... AS SELECT ...
>>>
>>> your example
>>> create view v2(a VARCHAR PRIMARY KEY, "f1".c INTEGER) as select * from
>>> "t1";
>>>
>>> it seems that AS SELECT part is very limited. It looks like I can not
>>> specify columns or any where clause there what kind of very
>>> limits purpose of the VIEW. For example
>>> I want to create VIEW for each CQ from existent HBase table column
>>>
>>> In HBase shell:
>>> create 't1'
>>> put 't1','r1','f1:b','r1f1b'
>>> put 't1','r1','f1:c','r1f1c'
>>> put 't1','r2','f1:c','r2f1c'
>>> put 't1','r3','f1:d','r3f1d'
>>>
>>> hbase(main):041:0> scan 't1'
>>> ROW                                            COLUMN+CELL
>>>
>>>
>>>  r1                                            column=f1:b,
>>> timestamp=1425686025521, value=r1f1b
>>>
>>>  r1                                            column=f1:c,
>>> timestamp=1425686025545, value=r1f1c
>>>
>>>  r2                                            column=f1:c,
>>> timestamp=1425686025565, value=r2f1c
>>>
>>>  r3                                            column=f1:d,
>>> timestamp=1425686025581, value=r3f1d
>>>
>>> In Phoenix sqlline:
>>> create view vB(a VARCHAR PRIMARY KEY, "f1"."b" VARCHAR) as select * from
>>> "t1";
>>> create view vC(a VARCHAR PRIMARY KEY, "f1"."c" VARCHAR) as select * from
>>> "t1";
>>> create view vD(a VARCHAR PRIMARY KEY, "f1"."d" VARCHAR) as select * from
>>> "t1";
>>>
>>> so basically when I do
>>> select * from vB;
>>>
>>> I do not want to see null
>>>
>>> but I can only do it using when SELECT * FROM vB where b is not null;
>>> Even thou this does not error
>>> create view vB(a VARCHAR PRIMARY KEY, "f1"."b" VARCHAR) as select * from
>>> "t1" where "f1"."b" is not null;
>>>
>>> here is result I am getting
>>>
>>> select * from vB;
>>> Error: ERROR 1001 (42I01): Undefined column family. familyName=F1.null
>>> (state=42I01,code=1001)
>>>
>>> select a,b from vB;
>>> Error: ERROR 1001 (42I01): Undefined column family. familyName=F1.null
>>> (state=42I01,code=1001)
>>>
>>> Any ideas how to do it or why AS SELECT part is not same select you can
>>> do when you query VIEW?
>>>
>>> Thank you
>>>
>>> On Thu, Mar 5, 2015 at 10:36 PM, Sergey Belousov <
>>> sergey.belousov@gmail.com> wrote:
>>>
>>>> Thank you James
>>>>
>>>> this one works for me too but my case little bit more complex
>>>> my key is
>>>> <4byte><4byte><4byte><8byte><array[0..N of 8bytes
members]>
>>>> I manage to project properly <4byte><4byte><4byte><8byte>
but
>>>> <array[0..N of 8bytes members]> part keep figting me :)
>>>>
>>>> Is it possible to have array of \x00\x00\x00\x01\x00\x00\x00\x01
>>>> elements and what is proper way to surface it in the view and be able to
>>>> have WHERE on it with regex on bytes? [?]
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, Mar 5, 2015 at 3:44 PM, James Taylor <jamestaylor@apache.org>
>>>> wrote:
>>>>
>>>>> This worked fine for me.
>>>>> In HBase shell:
>>>>> create 't1', {NAME => 'f1'}
>>>>>
>>>>> In Phoenix sqlline:
>>>>> create view v1(a VARCHAR PRIMARY KEY, "f1".b INTEGER) as select * from
>>>>> "t1";
>>>>> create view v2(a VARCHAR PRIMARY KEY, "f1".c INTEGER) as select * from
>>>>> "t1";
>>>>>
>>>>> If your table name and column family name are not upper case, make
>>>>> sure you surround them by double quotes when referencing in your
>>>>> statement. You can also declare a default column family like this
>>>>> instead:
>>>>>
>>>>> create view v3(a VARCHAR PRIMARY KEY, d INTEGER) as select * from "t1"
>>>>> default_column_family='f1';
>>>>>
>>>>> Thanks,
>>>>> James
>>>>>
>>>>> On Thu, Mar 5, 2015 at 8:19 AM, Sergey Belousov
>>>>> <sergey.belousov@gmail.com> wrote:
>>>>> > Hi All
>>>>> >
>>>>> > It seems like impossible now to create more than one view (with
>>>>> different
>>>>> > names and columns of course) on top of existent HBase table.
>>>>> >
>>>>> > It also seems impossible to provide view name other than original
>>>>> HBase
>>>>> > table name you base you view of.
>>>>> >
>>>>> > Are thouse statements correct or I just missing something? and if
>>>>> they are
>>>>> > is it something that can be implemented in historicity short time
to
>>>>> make it
>>>>> > possible? Is it even possible or there is some big underlying issues
>>>>> that
>>>>> > will stop it from happening?
>>>>> >
>>>>> >
>>>>> > Thank you
>>>>> >
>>>>>
>>>>
>>>>
>>>
>>
>

Mime
View raw message