Support Questions
Find answers, ask questions, and share your expertise

mapping hbase to phoenix tables going wrong

Master Collaborator

i have read all the posts regarding this but still I am not able to get it right ๐Ÿ˜ž

see blow the first query is showing a new column with name "PK" and ACCT_NUM empty .

the second query showing ACCT_NUM empty and PUR_ID populated.

how can I map all the columns of a column family to the phoenix table columns where the hbase row key becomes the primary key column of the phoenix table ?

hbase(main):001:0> scan 'PUR_ACCT_PHX2',{FILTER=>"(PrefixFilter('1001181'))"}
ROW                                    COLUMN+CELL
 1001181                               column=cf1:ACCTSTAT_ACCT_STATUS_CODE, timestamp=1523892576289, value=01
 1001181                               column=cf1:ACCTTYPE_ACCT_TYPE_CODE, timestamp=1523892576289, value=01
 1001181                               column=cf1:EMP_EMP_CODE, timestamp=1523892576289, value=9999
 1001181                               column=cf1:PLAZA_PLAZA_ID, timestamp=1523892576289, value=009500
 1001181                               column=cf1:PRODUCT_PUR_PRODUCT_CODE, timestamp=1523892576289, value=31
 1001181                               column=cf1:PROD_AMT, timestamp=1523892576289, value=10
 1001181                               column=cf1:PURSTAT_PUR_STATUS_CODE, timestamp=1523892576289, value=10
 1001181                               column=cf1:PUR_DET_ID, timestamp=1523892576289, value=661592624
 1001181                               column=cf1:PUR_ID, timestamp=1523892576289, value=752545412
 1001181                               column=cf1:PUR_TRANS_DATE, timestamp=1523892576289, value=2015-01-20 06:36:10.0
 1001181                               column=cf1:_0, timestamp=1523892576289, value=
1 row(s) in 0.2230 seconds

0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> CREATE table "PUR_ACCT_PHX2" (pk VARCHAR PRIMARY KEY, "cf1"."ACCT_NUM" varchar);
0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select * from PUR_ACCT_PHX2 LIMIT 10;
|    PK    | ACCT_NUM  |
| 1001181  |           |
| 1002226  |           |
| 1002491  |           |

CREATE table "PUR_ACCT_PHX2" (pk VARCHAR PRIMARY KEY, "cf1"."ACCT_NUM" varchar, "cf1"."PUR_ID" varchar);
0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select acct_num,pur_id from PUR_ACCT_PHX2 LIMIT 10;
| ACCT_NUM  |   PUR_ID   |
|           | 752545412  |
|           | 752541376  |
|           | 752539214  |


Accepted Solutions


Master Collaborator

hi Josh I did follow that link but its showing one column table example and I have multiple columns , I tried various options to make multiple columns to work but I am not able to , kindly advise

another post I found on this forum is suggesting exactly the same way I am trying to create the table so what I am doing wrong?


The commands you shared in your initial question are not what that post says to do for a Phoenix table mapped to an HBase table. You said you ran `CREATE TABLE`, but that FAQ instructs you to use `CREATE VIEW`.

Master Collaborator

ok thanks Josh I figured out my mistake . I didn't realize that phoenix automatically find the primary key and I don't have to specify the primary key column name explicitly .

thanks for the guidance .