Hi Sergey,
I was following your post as I wanted to do multiple views, and the following is my experience: *Does not work **(Undefined column family. familyName=ONBOARDING.null)**:* create view Persistance_v8 ( rowid VARCHAR PRIMARY KEY, "onboarding"."dealer_landscape_viewed" boolean, "preferences"."dealer_ids_order" varchar ) AS SELECT * FROM "persistance" where *"dealer_landscape_viewed"* is null *Does work **(returns expected results when queried):* create view Persistance_v8 ( rowid VARCHAR PRIMARY KEY, "onboarding"."dealer_landscape_viewed" boolean, "preferences"."dealer_ids_order" varchar ) AS SELECT * FROM "persistance" where *"onboarding"."dealer_landscape_viewed"* is null So it seems that for me, quoting and including the column family in the where clause of the view select is what fixes it (without quoting and including the CF, Phoenix uppercases the CF and then complains about CF.null). However, I see in your Jira that you already have the CF quoted and included, so I’m not sure why it works for me but not for you. Cheers, Matt *From:* Sergey Belousov [mailto:[email protected]] *Sent:* 09 March 2015 04:12 *To:* [email protected] *Subject:* Re: Creating multiple views off existent HBase table. 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 >
