5

I have a Hbase table "http_access_log", now i want to use Apache phoenix for SQL on it.

Should I create phoenix view or table to map hbase table? And if the hbase table is updated by the hbase api, will the phoenix view or table be updated?

Community
  • 1
  • 1
robertc
  • 51
  • 1
  • 2

2 Answers2

3

If you have a pre-existing table you'll have to create a view to access it:

create view "http_access_log_v" (pk VARCHAR PRIMARY KEY, "colfam1"."colum1" VARCHAR, "colfam1"."colum2" VARCHAR) as select * from "http_access_log";

With the above view in place you can then do selects against it like so:

select * from http_access_log_v;

Example

Say I had a HBase table 'config'. I cannot do selects directly against this table via Phoenix.

sqlline> select * from "config"; 
Error: ERROR 1012 (42M03): Table undefined. tableName=config (state=42M03,code=1012)

However if I create a view against a select * from "config" of this HBase table: 

sqlline> create view "config-data" (pk VARCHAR PRIMARY KEY, "data"."id" VARCHAR, "data"."categoryName" VARCHAR) as select * from "config";
No rows affected (1.588 seconds)

I can then query against a subset of the columns available that have been configured within a Phoenix SQL view:

sqlline> select * from "config-data";
+------------------------------------------+------------------------------------------+------------------------------------------+
|                    PK                    |                    id                    |               categoryName               |
+------------------------------------------+------------------------------------------+------------------------------------------+
| QA-AA00|D|MC|MSG|C10|M3               | null                                     | null                                     |
| QA-AA00|D|MC|MSG|C2|M1                | null                                     | null                                     |
...

And I still cannot query the HBase table directly:

sqlline> select * from "config"; Error: ERROR 1012 (42M03): Table undefined. tableName=config (state=42M03,code=1012)

References

slm
  • 15,396
  • 12
  • 109
  • 124
  • Does it work actually? If the table doesnt exist on Phoenix, how can you refer it ("http_access_log") ? – Thomas Decaux Dec 22 '15 at 23:18
  • @ThomasDecaux it definitely works, you have to create a view and query against that is it's a preexisting hbase table. – slm Dec 22 '15 at 23:22
  • But "http_access_log" is not found for Phoenix, I believe you have to create a view with the same name as the pure HBase table, isnit? – Thomas Decaux Dec 23 '15 at 13:13
  • @ThomasDecaux - no you do not have to create the view w/ the same name. A view is essentially like a table, except it has it's own subset of columns that map against either another table or a select. The tables in HBase are not directly accessible via SQL in Phoenix. You have to create a view if they already exist and map the columns in the HBase talble to columns in a view. – slm Dec 23 '15 at 14:26
  • Here, "http_access_log" is not a Phoenix table, so you cannot use it in Phoenix. – Thomas Decaux Dec 23 '15 at 15:29
  • @ThomasDecaux - I'm not following what you're saying. The example I showed above creates a view (http_access_log_v) which I can then perform queries against. This view pulls columns out from an HBase table, http_access_log via the select * from http_access_log that the view was constructed against. – slm Dec 23 '15 at 16:08
  • Where did you see that in the doc? (the as select * from ...) Here what I got in the Phoenix "CLI" http://b3.ms/Oq5nR46G90wB You can see the pure HBase "toto" is undefined for Phoenix. Maybe it's a bug? – Thomas Decaux Dec 23 '15 at 21:13
-1

According documentation:

https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table

You must create a view with the same name, and the schema you need.

Thomas Decaux
  • 21,738
  • 2
  • 113
  • 124