phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: Creating multiple views off existent HBase table.
Date Mon, 09 Mar 2015 03:47:42 GMT
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