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