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 <[email protected]> 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 <[email protected]> > 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 >> <[email protected]> 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 >> > >> > >
