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 01:58:35 GMT
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