Created 04-05-2018 07:22 PM
I created a salted hbase table via phoenix and then loaded data into it using sqoop . I see all the rows in the table from both phoenix and hbase but when I try to query the data I get strange results , like its adding "-" and extra numbers which I understand for the primary key column but why its changing the other column values ?
basically how can I select any of this data in phoenix ?
8527342 column=0:PROD_AMT, timestamp=1522953014328, value=35 8527342 column=0:PURSTAT_PUR_STATUS_CODE, timestamp=1522953014328, value=10 8527342 column=0:PUR_DET_ID, timestamp=1522953014328, value=661579130 8527342 column=0:PUR_ID, timestamp=1522953014328, value=752530930 8527342 column=0:PUR_TRANS_DATE, timestamp=1522953014328, value=2015-01-20 05:03:23.0 8527784 column=0:ACCTSTAT_ACCT_STATUS_CODE, timestamp=1522953013791, value=01 8527784 column=0:ACCTTYPE_ACCT_TYPE_CODE, timestamp=1522953013791, value=01 8527784 column=0:EMP_EMP_CODE, timestamp=1522953013791, value=9999 8527784 column=0:PLAZA_PLAZA_ID, timestamp=1522953013791, value=009500 8527784 column=0:PRODUCT_PUR_PRODUCT_CODE, timestamp=1522953013791, value=31 8527784 column=0:PROD_AMT, timestamp=1522953013791, value=20 8527784 column=0:PURSTAT_PUR_STATUS_CODE, timestamp=1522953013791, value=10 8527784 column=0:PUR_DET_ID, timestamp=1522953013791, value=661579920 8527784 column=0:PUR_ID, timestamp=1522953013791, value=752531746 8527784 column=0:PUR_TRANS_DATE, timestamp=1522953013791, value=2015-01-20 05:07:23.0 8528228 column=0:ACCTSTAT_ACCT_STATUS_CODE, timestamp=1522953014328, value=01 8528228 column=0:ACCTTYPE_ACCT_TYPE_CODE, timestamp=1522953014328, value=01 8528228 column=0:EMP_EMP_CODE, timestamp=1522953014328, value=9999 8528228 column=0:PLAZA_PLAZA_ID, timestamp=1522953014328, value=009500 8528228 column=0:PRODUCT_PUR_PRODUCT_CODE, timestamp=1522953014328, value=31 8528228 column=0:PROD_AMT, timestamp=1522953014328, value=10 8528228 column=0:PURSTAT_PUR_STATUS_CODE, timestamp=1522953014328, value=10 8528228 column=0:PUR_DET_ID, timestamp=1522953014328, value=661585236 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select count(*) from PUR_ACCT_PHX; +-----------+ | COUNT(1) | +-----------+ | 5517 | +-----------+ 1 row selected (0.07 seconds) 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> !columns PUR_ACCT_PHX +------------+--------------+---------------+----------------------------+------------+------------+--------------+----------------+----------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | BUFFER_LENGTH | DECIMAL_DIGITS | +------------+--------------+---------------+----------------------------+------------+------------+--------------+----------------+----------------+ | | | PUR_ACCT_PHX | ACCT_NUM | 4 | INTEGER | null | null | null | | | | PUR_ACCT_PHX | PUR_ID | 4 | INTEGER | null | null | null | | | | PUR_ACCT_PHX | PUR_DET_ID | 4 | INTEGER | null | null | null | | | | PUR_ACCT_PHX | PRODUCT_PUR_PRODUCT_CODE | 12 | VARCHAR | null | null | null | | | | PUR_ACCT_PHX | PROD_AMT | 3 | DECIMAL | null | null | null | | | | PUR_ACCT_PHX | PUR_TRANS_DATE | 91 | DATE | null | null | null | | | | PUR_ACCT_PHX | ACCTTYPE_ACCT_TYPE_CODE | 12 | VARCHAR | null | null | null | | | | PUR_ACCT_PHX | ACCTSTAT_ACCT_STATUS_CODE | 12 | VARCHAR | null | null | null | | | | PUR_ACCT_PHX | EMP_EMP_CODE | 12 | VARCHAR | null | null | null | | | | PUR_ACCT_PHX | PLAZA_PLAZA_ID | 12 | VARCHAR | null | null | null | | | | PUR_ACCT_PHX | PURSTAT_PUR_STATUS_CODE | 12 | VARCHAR | null | null | null | +------------+--------------+---------------+----------------------------+------------+------------+--------------+----------------+----------------+ 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select * from PUR_ACCT_PHX where PUR_ID=752531746; +-----------+---------+-------------+---------------------------+-----------+-----------------+--------------------------+--------------------------+ | ACCT_NUM | PUR_ID | PUR_DET_ID | PRODUCT_PUR_PRODUCT_CODE | PROD_AMT | PUR_TRANS_DATE | ACCTTYPE_ACCT_TYPE_CODE | ACCTSTAT_ACCT_STATUS_COD | +-----------+---------+-------------+---------------------------+-----------+-----------------+--------------------------+--------------------------+ +-----------+---------+-------------+---------------------------+-----------+-----------------+--------------------------+--------------------------+ No rows selected (0.305 seconds) 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select * from PUR_ACCT_PHX where ACCTTYPE_ACCT_TYPE_CODE='01'; Error: ERROR 201 (22000): Illegal data. Expected length of at least 57 bytes, but had 6 (state=22000,code=201) java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at least 57 bytes, but had 6 at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:443) at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150) at org.apache.phoenix.schema.KeyValueSchema.next(KeyValueSchema.java:213) 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> SELECT acct_num,pur_id,pur_det_id,pur_trans_date from pur_acct_phx limit 10; +--------------+--------------+--------------+-------------------------------+ | ACCT_NUM | PUR_ID | PUR_DET_ID | PUR_TRANS_DATE | +--------------+--------------+--------------+-------------------------------+ | -1339018959 | -1221250507 | -1271581647 | 177617150-03-27 18:37:14.678 | | -1339018701 | -1221250507 | -1288293839 | 182183885-07-01 18:51:53.265 | | -1339018960 | -1221250507 | -1288097741 | 173050416-12-20 18:20:25.529 | | -1339019210 | -1221250507 | -1288293581 | 168483681-09-14 18:04:40.882 | | -1339018958 | -1221250507 | -1271450830 | 177617150-03-27 18:37:15.192 | | -1339018951 | -1221250507 | -1271910093 | 168483681-09-14 18:04:41.657 | | -1339019207 | -1221250507 | -1288294094 | 168483681-09-14 18:04:40.880 | | -1339017928 | -1221250507 | -1288293838 | 168483681-09-14 18:04:40.881 | | -1339019212 | -1221250507 | -1288293580 | 182183885-07-01 18:51:53.266 | | -1339018702 | -1221250507 | -1271844041 | 168483681-09-14 18:04:42.422 | +--------------+--------------+--------------+-------------------------------+ 10 rows selected (0.106 seconds) 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> SELECT acct_num,pur_id,pur_det_id,pur_trans_date,plaza_plaza_id from pur_acct_phx limit 10; Error: ERROR 201 (22000): Illegal data. Expected length of at least 50 bytes, but had 29 (state=22000,code=201) java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at least 50 bytes, but had 29 at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:443) at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150) at org.apache.phoenix.schema.KeyValueSchema.next(KeyValueSchema.java:213) at org.apache.phoenix.expression.ProjectedColumnExpression.evaluate(ProjectedColumnExpression.java:115) at org.apache.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:69) at org.apache.phoenix.jdbc.PhoenixResultSet.getString(PhoenixResultSet.java:608) at sqlline.Rows$Row.<init>(Rows.java:183) at sqlline.BufferedRows.<init>(BufferedRows.java:38) at sqlline.SqlLine.print(SqlLine.java:1650) at sqlline.Commands.execute(Commands.java:833) at sqlline.Commands.sql(Commands.java:732) at sqlline.SqlLine.dispatch(SqlLine.java:808) at sqlline.SqlLine.begin(SqlLine.java:681) at sqlline.SqlLine.start(SqlLine.java:398) at sqlline.SqlLine.main(SqlLine.java:292) 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure>
Created 04-05-2018 08:23 PM
I am getting strange data in phoenix
0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select count(*) from PUR_ACCT_PHX; +-----------+ | COUNT(1) | +-----------+ | 8299 | +-----------+ 1 row selected (0.081 seconds) 0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select acct_num,pur_id from PUR_ACCT_PHX LIMIT 10; +--------------+--------------+ | ACCT_NUM | PUR_ID | +--------------+--------------+ | -1322241999 | -1221250507 | | -1322241998 | -1221250507 | | -1322241998 | -1221250507 |
Created 04-05-2018 09:05 PM
doing further testing revealed interesting results. I changed the column data types to varchar instead of integer and now atleast the PUR_ID column is giving right values but look at the primary key ACC_NUM its showing correct value to the six position and missing the most significant value.
source data
SQL> select acct_num,pur_id from tab4 where pur_id=752545412; ACCT_NUM PUR_ID ---------- ---------- 1001181 752545412 SQL> select acct_num,pur_id from tab4 where pur_id=752541376; ACCT_NUM PUR_ID ---------- ---------- 1002226 752541376 SQL>
phoenix data
0: jdbc:phoenix:hadoop1:2181:/hbase-unsecure> select acct_num,pur_id from pur_acct_phx2 limit 10; +-----------+------------+ | ACCT_NUM | PUR_ID | +-----------+------------+ | 001181 | 752545412 | | 002226 | 752541376 |