Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hbase table with namespace mapping with Apache Phoenix table with schema

avatar
Contributor

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

78498-screen-shot-2018-06-22-at-22023-pm.png

8 REPLIES 8

avatar
Expert Contributor

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.

avatar
Contributor

@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

avatar
Contributor

@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)

avatar
New Contributor

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

avatar
Contributor

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

avatar
New Contributor

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

avatar
Contributor

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

avatar
New Contributor

All columns mapped as VARCHAR.Thanks