Hbase table with namespace mapping with Apache Phoenix table with schema

[#1]

Hi All,

We are facing an issue to map existing Base table with apache phoenix table.

We have table called PROD:MYTAB1 in HBase and have some records in it.

[hbase(main):008:0> scan 'PROD:MYTAB1'

ROW COLUMN+CELL

1 column=CF1:DEPT, timestamp=1516286699016, value=Information Technology

1 column=CF1:NAME, timestamp=1516286698951, value=Bhavesh Vadaliya

1 column=CF1:_0, timestamp=1516286700481, value=

1 column=CF2:DESIGNATION, timestamp=1516286700481, value=System Admin

1 column=CF2:SALARY, timestamp=1516286699070, value=1000

We mapped the HBase PROD:MYTAB1 with Apache Phoenix table using below command on Apache Phoenix Prompt

CREATE TABLE 'PROD'.'MYTAB1' (ID VARCHAR PRIMARY KEY, CF1.NAME VARCHAR,CF1.DEPT VARCHAR, CF2.SALARY VARCHAR, CF2.DESIGNATION VARCHAR) ;

Table got created successfully but when we ran select query, it just displayed ID field only, remaining all columns are empty.

Here is the screenshot:

Could you please help me to identify the issue, am I missing something to map table with namespace and schema.

Regards,

Bhavesh

[#2]

Can you check whether phoenix.schema.isNamespaceMappingEnabled is true?

If it is false in your environment, then phoenix query creating new table under default namespace of Hbase instead of mapping it.

If it is already "true" then execute following to map table-->

CREATE TABLE PROD."MYTAB1" (ID VARCHAR PRIMARY KEY, "CF1"."NAME" VARCHAR,"CF1."DEPT" VARCHAR, "CF2".SALARY VARCHAR, "CF2".DESIGNATION VARCHAR) ;

If it is false, you have to set it to true and then try mapping table.

[#3]

@schhabra

Hi Schhabra,

Thanks for your quick response.

Yes, the schema mapping property is enabled at both side client and server side and I have executed the create table statement as you have provided in your answer but it didn't resolved my issue, Phoenix table is still not able to fetch the columns except ID.

Thanks,

Bhavesh

[#4]

@Bhavesh Were you able to resolve the issue?

Even after setting the property phoenix.schema.isNamespaceMappingEnabled on all nodes, i am not able to read the data from phoenix command line

hbase(main):002:0> scan 'DEFAULT:TEST'
ROW                            COLUMN+CELL
 1                             column=B:MESSAGE, timestamp=1533062047409, value=Hello
 2                             column=B:MESSAGE, timestamp=1533062316879, value=World
2 row(s) in 0.0860 seconds
--phoenix
0: jdbc:phoenix:thin:url=http://localhost:876> select * from "DEFAULT".TEST;
+-----+----------+
| ID  | MESSAGE  |
+-----+----------+
+-----+----------+
No rows selected (0.031 seconds)

But count works

0: jdbc:phoenix:thin:url=http://localhost:876> select count(*) from "DEFAULT".TEST;
+-----------+
| COUNT(1)  |
+-----------+
| 2         |
+-----------+
1 row selected (0.013 seconds)

[#5]

Hi Bhavesh,

The issue was not about the setting the properties. Phoenix was unable to recognise the column names which are declared under columnar family. Hence while creating a table in phoenix, you should define the column names which are defined under columnar family in inverted commas. Otherwise it will show null values while executing the query.

Please find the details below.

CREATE TABLE PROD."MYTAB1" (ID VARCHAR PRIMARY KEY, "CF1"."NAME" VARCHAR,"CF1."DEPT" VARCHAR, "CF2"."SALARY" VARCHAR, "CF2"."DESIGNATION" VARCHAR) ;

Regards,

Suresh

[#6]

Hi Suresh Babu

I have tried with your create table statement but it did't resolved my issue. The result were same, only ID column got populated.

Thanks,

Bhavesh

[#7]

Hi Bhavesh,

I meet same issue , the link could help you:

https://phoenix.apache.org/columnencoding.html

two way can solve the issue:

First way, you can create table add the var "COLUMN_ENCODED_BYTES = 0;",for example:

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Helvetica}

CREATE TABLE 'PROD'.'MYTAB1'

(ID VARCHAR PRIMARY KEY, CF1.NAME VARCHAR,CF1.DEPT VARCHAR, CF2.SALARY VARCHAR, CF2.DESIGNATION VARCHAR)

COLUMN_ENCODED_BYTES = 0;

Second way , you can set phoenix.default.column.encoded.bytes.attrib to 0 in your client hbase-site.xml,for example:

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Helvetica}

<property>

<name>phoenix.default.column.encoded.bytes.attrib</name>

<value>0</value>

</property>

B.R.

Tracy

[#8]

Hello Tracy,

How did you handle the data types in Phoenix after the setting the parameter ?

Are all columns mapped as VARCHAR ? or Were there any scenarios to use the datatypes ( INTEGER,TIMESTAMP's etc)

Thank you

[#9]

All columns mapped as VARCHAR.Thanks