mapping hbase to phoenix tables going wrong

[#1]

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  |

[#2 · Accepted solution]

https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table

[#3]

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?

[#4]

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`.

[#5]

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 .