phoenix query issues

aliyesami · ‎04-05-2018

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>

aliyesami · ‎04-05-2018

I also testing creating the table without the SALT_BUCKETS but the behavior remains same .

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  |

aliyesami · ‎04-05-2018

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  |