phoenix map existing HBase table

Question

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?

Welcome to Stackoverflow. You might want to refer this link stackoverflow.com/help/how-to-ask — Nagama Inamdar

slm slm · Accepted Answer · 2015-12-17T18:38:03

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

How I map Phoenix table to an existing HBase table?

phoenix map existing HBase table

2 Answers

Example

References