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 >>>> > >>>> >>> >>> >> >
