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 Sat, 07 Mar 2015 00:09:15 GMT
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