How to Map HBase Table to Phoenix ("Table undefined" error)

[#1]

What is the proper way to map an existing HBase table to a new Phoenix table? Phoenix documentation gives a light example on how to do this. When we try this on existing HBase table the Phoenix "create table" command is accepted, however it fails with "table undefined" error when we try to query the new Phoenix table.

How could the create table syntax succeeed, yet the "table undefined" error occur when we query it?

Hbase table definition

tablename = weblog

Columnfamily = clicks

column=clicks:Compression_ratio

column=clicks:Cookie1

column=clicks:Cookie2

column=clicks:Data_center

column=clicks:Host

column=clicks:Incoming_client_protocol

column=clicks:Rqst_status

column=clicks:Tran_dt

column=clicks:Xforwarder

column=clicks:fdx_cbid

column=clicks:header_size

column=clicks:input_bytes

column=clicks:millisecs_to_serv_rqst

column=clicks:output_bytes

column=clicks:referring_ip_addr

column=clicks:rqst_first_ln

column=clicks:unknown

column=clicks:user_agent

column=clicks:web_user

column=clicks:web_user2

View DDL used in phoenix

CREATE VIEW "weblog" ( pk VARCHAR PRIMARY KEY,

"clicks".Compression_ratio VARCHAR,

"clicks".Cookie1 VARCHAR,

"clicks".Cookie2 VARCHAR,

"clicks".Data_center VARCHAR,

"clicks".Host VARCHAR,

"clicks".Incoming_client_protocol VARCHAR,

"clicks".Rqst_status VARCHAR,

"clicks".Tran_dt VARCHAR,

"clicks".Xforwarder VARCHAR,

"clicks".fdx_cbid VARCHAR,

"clicks".header_size VARCHAR,

"clicks".input_bytes VARCHAR,

"clicks".millisecs_to_serv_rqst VARCHAR,

"clicks".output_bytes VARCHAR,

"clicks".referring_ip_addr VARCHAR,

"clicks".rqst_first_ln VARCHAR,

"clicks".unknown VARCHAR,

"clicks".user_agent VARCHAR,

"clicks".web_user VARCHAR,

"clicks".web_user2 VARCHAR)

List of tables from Phoenix

0: jdbc:phoenix:drh70018.bigdata.fedex.com:21> !tables

+------------------------------------------+------------------------------------------+-----------------------------------------+

| TABLE_CAT | TABLE_SCHEM | TABLE_NAME |

+------------------------------------------+------------------------------------------+-----------------------------------------+

| null | SYSTEM | CATALOG |

| null | SYSTEM | SEQUENCE |

| null | SYSTEM | STATS |

| null | null | CUSTOMERS |

| null | null | EXAMPLE |

| null | null | WEB_STAT |

| null | null | weblog

Error from Phoenix:

0: jdbc:phoenix:drh70018.bigdata.fedex.com:21> select * from weblog;

Error: ERROR 1012 (42M03): Table undefined. tableName=WEBLOG (state=42M03,code=1012)

0: jdbc:phoenix:drh70018.bigdata.fedex.com:21>

[#2]

@Wes Floyd isn't case-sensitive issue?

[#3 · Accepted solution]

@Wes Floyd from phoenix docs (https://phoenix.apache.org/ - home page):

"Note that the table and column family names are case sensitive, with Phoenix upper-casing all names. To make a name case sensitive in the DDL statement, surround it with double quotes as shown below:"

When I did it, I used all UPPER-CASE for both hbase and phoenix, without any quotes.

[#4]

@Guilherme Braccialli Good point. I'm surprised the error shows WEBLOG in all caps, because our HBase and Phoenix table definition is in all lower case. Do you suggest we make all our HBase and Phoenix tables in all Upper case to get past the issue? What is your rule of thumb generally for Phoenix/HBase table names?

[#5]

@Wes Floyd If your hbase columns are already created. You can just use double quotes around the column name (e.g. "clicks"."millisecs_to_serv_rqst")

[#6]

Lets say you have an existing HBase table called "transactions", column family called "transactions" that has a column called transactionId. You can map that table to a Phoenix view with: CREATE VIEW "TransactionHistory" (k VARCHAR primary key, "Transactions"."transactionId" VARCHAR);. Now you should be able to Select "transactionId" from "Transactions". You can use ALTER VIEW to add additional columns from he HBASE table after that.

[#7]

Sir i don't think you have made any mistake while creating the view, just put a double cote (") around the view name while query

select * from "weblog";

@Wes Floyd