done https://issues.apache.org/jira/browse/PHOENIX-1714 let me know if it unclear and more clarification needed . On Mar 8, 2015 11:47 PM, "James Taylor" <[email protected]> wrote:
> 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 <[email protected] > > 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 < >> [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 >>>>> > >>>>> >>>> >>>> >>> >> >
