Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-665

How to resolve the data type problem in phoenix?

Details

    • Task
    • Status: Resolved
    • Resolution: Fixed
    • None
    • None
    • None
    • 534

    Description

      I have create a table via hive in hbase. When I insert integer data into the table, it can be retrieved by hive or hbase, but can not be retrieved correctly via phoenix, and the error is :
      java.lang.IllegalArgumentException: offset (715) + length (8) exceed the capacity of the array: 720
      at org.apache.hadoop.hbase.util.Bytes.explainWrongLengthOrOffset(Bytes.java:543)
      at org.apache.hadoop.hbase.util.Bytes.toLong(Bytes.java:521)
      at org.apache.hadoop.hbase.util.Bytes.toDouble(Bytes.java:620)
      at com.salesforce.phoenix.schema.PDataType$UnsignedDoubleCodec.decodeDouble(PDataType.java:4152)
      at com.salesforce.phoenix.schema.PDataType$18.toObject(PDataType.java:2756)
      at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4585)
      at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4564)
      at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4573)
      at com.salesforce.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:85)
      at com.salesforce.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:476)
      at sqlline.SqlLine$Rows$Row.<init>(SqlLine.java:2314)
      at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2430)
      at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
      at sqlline.SqlLine.print(SqlLine.java:1735)
      at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
      at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
      at sqlline.SqlLine.dispatch(SqlLine.java:821)
      at sqlline.SqlLine.begin(SqlLine.java:699)
      at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
      at sqlline.SqlLine.main(SqlLine.java:424)

      The data type in hive is integer and the data type in phoenix is unsigned_int.
      Also, if insert data by phoenix, it will be displayed correctly in phoenix but null in hive or hbase.
      How can I resolve this problem?
      Any help will be appreciated.

      Attachments

        Activity

          pctony Tony Stevenson added a comment -

          Comment:jamestaylor:11/08/13 06:07:14 AM:

          @liuziliang - please include your Phoenix schema and let us know which version of Phoenix you're using. Also helpful would be the UPSERT statement you're using. Most helpful is always an actual junit test. Is this a column that's part of your primary key constraint?

          pctony Tony Stevenson added a comment - Comment:jamestaylor:11/08/13 06:07:14 AM: @liuziliang - please include your Phoenix schema and let us know which version of Phoenix you're using. Also helpful would be the UPSERT statement you're using. Most helpful is always an actual junit test. Is this a column that's part of your primary key constraint?
          pctony Tony Stevenson added a comment -

          Comment:liuziliang:11/08/13 06:07:14 AM:

          mentioned

          pctony Tony Stevenson added a comment - Comment:liuziliang:11/08/13 06:07:14 AM: mentioned
          pctony Tony Stevenson added a comment -

          Comment:liuziliang:11/11/13 06:00:17 AM:

          @jamestaylor - The version of Phoenix is 2.1.0 and also the same error in version 2.0.2. The version of hbase is 0.94.10. I have tried the simplest case as below:
          create table in hbase:
          create 'test','fact'

          create table in phoenix:
          create table if not exists "test" ("key_id" varchar not null primary key, "fact"."number" unsigned_int);

          put data into table test in hbase:
          put 'test','1','fact:number','123'

          upsert data into table test in phoenix:
          UPSERT INTO "test" ("key_id", "fact"."number") VALUES ('2', 234);
          UPSERT INTO "test" ("key_id", "fact"."number") VALUES ('3', 345);

          then, check the datas in hbase:
          hbase(main):022:0> scan 'test'
          ROW COLUMN+CELL
          1 column=fact:_0, timestamp=1384148743618, value=
          1 column=fact:number, timestamp=1384148796520, value=123
          2 column=fact:_0, timestamp=1384148946799, value=
          2 column=fact:number, timestamp=1384148946799, value=\x00\x00\x00\xEA
          3 column=fact:_0, timestamp=1384148918216, value=
          3 column=fact:number, timestamp=1384148918216, value=\x00\x00\x01Y
          3 row(s) in 0.0440 seconds

          and check in phoenix:
          0: jdbc:phoenix:master> select * from "test";
          ------------------+

          key_id number

          ------------------+

          1 825373510
          2 234
          3 345

          ------------------+

          Why the value inserted in hbase can not display correctly in phoenix and which type should be when create table in phoenix?
          Thanks.

          pctony Tony Stevenson added a comment - Comment:liuziliang:11/11/13 06:00:17 AM: @jamestaylor - The version of Phoenix is 2.1.0 and also the same error in version 2.0.2. The version of hbase is 0.94.10. I have tried the simplest case as below: create table in hbase: create 'test','fact' create table in phoenix: create table if not exists "test" ("key_id" varchar not null primary key, "fact"."number" unsigned_int); put data into table test in hbase: put 'test','1','fact:number','123' upsert data into table test in phoenix: UPSERT INTO "test" ("key_id", "fact"."number") VALUES ('2', 234); UPSERT INTO "test" ("key_id", "fact"."number") VALUES ('3', 345); then, check the datas in hbase: hbase(main):022:0> scan 'test' ROW COLUMN+CELL 1 column=fact:_0, timestamp=1384148743618, value= 1 column=fact:number, timestamp=1384148796520, value=123 2 column=fact:_0, timestamp=1384148946799, value= 2 column=fact:number, timestamp=1384148946799, value=\x00\x00\x00\xEA 3 column=fact:_0, timestamp=1384148918216, value= 3 column=fact:number, timestamp=1384148918216, value=\x00\x00\x01Y 3 row(s) in 0.0440 seconds and check in phoenix: 0: jdbc:phoenix:master> select * from "test"; ----------- -------+ key_id number ----------- -------+ 1 825373510 2 234 3 345 ----------- -------+ Why the value inserted in hbase can not display correctly in phoenix and which type should be when create table in phoenix? Thanks.
          pctony Tony Stevenson added a comment -

          Comment:jamestaylor:11/11/13 06:00:17 AM:

          mentioned

          pctony Tony Stevenson added a comment - Comment:jamestaylor:11/11/13 06:00:17 AM: mentioned
          pctony Tony Stevenson added a comment -

          Comment:jamestaylor:11/11/13 07:19:58 PM:

          When you do the Put from HBase, you need to use an integer instead of a string for the fact:number value. Something like this:

          put data into table test in hbase:
          put 'test','1','fact:number',123

          pctony Tony Stevenson added a comment - Comment:jamestaylor:11/11/13 07:19:58 PM: When you do the Put from HBase, you need to use an integer instead of a string for the fact:number value. Something like this: put data into table test in hbase: put 'test','1','fact:number',123
          pctony Tony Stevenson added a comment -

          Comment:liuziliang:11/12/13 01:58:25 AM:

          @jamestaylor - I tried with an integer instead of a string but the same error as below:
          When I put data in hbase with:
          put 'test','1','fact:number',123

          Select in phoenix:
          0: jdbc:phoenix:master> select * from "test";
          ------------------+

          key_id number

          ------------------+
          java.lang.IllegalArgumentException: offset (36) + length (4) exceed the capacity of the array: 39
          at org.apache.hadoop.hbase.util.Bytes.explainWrongLengthOrOffset(Bytes.java:543)
          at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:690)
          at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:676)
          at com.salesforce.phoenix.schema.PDataType$UnsignedIntCodec.decodeInt(PDataType.java:3887)
          at com.salesforce.phoenix.schema.PDataType$14.toObject(PDataType.java:2078)
          at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4585)
          at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4564)
          at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4573)
          at com.salesforce.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:85)
          at com.salesforce.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:476)
          at sqlline.SqlLine$Rows$Row.<init>(SqlLine.java:2314)
          at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2430)
          at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
          at sqlline.SqlLine.print(SqlLine.java:1735)
          at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
          at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
          at sqlline.SqlLine.dispatch(SqlLine.java:821)
          at sqlline.SqlLine.begin(SqlLine.java:699)
          at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
          at sqlline.SqlLine.main(SqlLine.java:424)

          Then, put another in hbase:
          put 'test','2','fact:number',1

          Select in phoenix again:
          0: jdbc:phoenix:master> select * from "test";
          ------------------+

          key_id number

          ------------------+

          1 825373476

          java.lang.IllegalArgumentException: offset (75) + length (4) exceed the capacity of the array: 76
          at org.apache.hadoop.hbase.util.Bytes.explainWrongLengthOrOffset(Bytes.java:543)
          at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:690)
          at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:676)
          at com.salesforce.phoenix.schema.PDataType$UnsignedIntCodec.decodeInt(PDataType.java:3887)
          at com.salesforce.phoenix.schema.PDataType$14.toObject(PDataType.java:2078)
          at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4585)
          at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4564)
          at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4573)
          at com.salesforce.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:85)
          at com.salesforce.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:476)
          at sqlline.SqlLine$Rows$Row.<init>(SqlLine.java:2314)
          at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2430)
          at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074)
          at sqlline.SqlLine.print(SqlLine.java:1735)
          at sqlline.SqlLine$Commands.execute(SqlLine.java:3683)
          at sqlline.SqlLine$Commands.sql(SqlLine.java:3584)
          at sqlline.SqlLine.dispatch(SqlLine.java:821)
          at sqlline.SqlLine.begin(SqlLine.java:699)
          at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441)
          at sqlline.SqlLine.main(SqlLine.java:424)
          0: jdbc:phoenix:master> 0: jdbc:phoenix:master> select * from "test";
          Error: ERROR 601 (42P00): Syntax error. Encountered "0" at line 1, column 1. (state=42P00,code=601)

          But scan in hbase, everything is ok:
          hbase(main):017:0> scan 'test'
          ROW COLUMN+CELL
          1 column=fact:number, timestamp=1384220921197, value=123
          2 column=fact:number, timestamp=1384221038418, value=1
          2 row(s) in 0.0430 seconds

          pctony Tony Stevenson added a comment - Comment:liuziliang:11/12/13 01:58:25 AM: @jamestaylor - I tried with an integer instead of a string but the same error as below: When I put data in hbase with: put 'test','1','fact:number',123 Select in phoenix: 0: jdbc:phoenix:master> select * from "test"; ----------- -------+ key_id number ----------- -------+ java.lang.IllegalArgumentException: offset (36) + length (4) exceed the capacity of the array: 39 at org.apache.hadoop.hbase.util.Bytes.explainWrongLengthOrOffset(Bytes.java:543) at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:690) at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:676) at com.salesforce.phoenix.schema.PDataType$UnsignedIntCodec.decodeInt(PDataType.java:3887) at com.salesforce.phoenix.schema.PDataType$14.toObject(PDataType.java:2078) at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4585) at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4564) at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4573) at com.salesforce.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:85) at com.salesforce.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:476) at sqlline.SqlLine$Rows$Row.<init>(SqlLine.java:2314) at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2430) at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074) at sqlline.SqlLine.print(SqlLine.java:1735) at sqlline.SqlLine$Commands.execute(SqlLine.java:3683) at sqlline.SqlLine$Commands.sql(SqlLine.java:3584) at sqlline.SqlLine.dispatch(SqlLine.java:821) at sqlline.SqlLine.begin(SqlLine.java:699) at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441) at sqlline.SqlLine.main(SqlLine.java:424) Then, put another in hbase: put 'test','2','fact:number',1 Select in phoenix again: 0: jdbc:phoenix:master> select * from "test"; ----------- -------+ key_id number ----------- -------+ 1 825373476 java.lang.IllegalArgumentException: offset (75) + length (4) exceed the capacity of the array: 76 at org.apache.hadoop.hbase.util.Bytes.explainWrongLengthOrOffset(Bytes.java:543) at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:690) at org.apache.hadoop.hbase.util.Bytes.toInt(Bytes.java:676) at com.salesforce.phoenix.schema.PDataType$UnsignedIntCodec.decodeInt(PDataType.java:3887) at com.salesforce.phoenix.schema.PDataType$14.toObject(PDataType.java:2078) at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4585) at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4564) at com.salesforce.phoenix.schema.PDataType.toObject(PDataType.java:4573) at com.salesforce.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:85) at com.salesforce.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:476) at sqlline.SqlLine$Rows$Row.<init>(SqlLine.java:2314) at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2430) at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2074) at sqlline.SqlLine.print(SqlLine.java:1735) at sqlline.SqlLine$Commands.execute(SqlLine.java:3683) at sqlline.SqlLine$Commands.sql(SqlLine.java:3584) at sqlline.SqlLine.dispatch(SqlLine.java:821) at sqlline.SqlLine.begin(SqlLine.java:699) at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441) at sqlline.SqlLine.main(SqlLine.java:424) 0: jdbc:phoenix:master> 0: jdbc:phoenix:master> select * from "test"; Error: ERROR 601 (42P00): Syntax error. Encountered "0" at line 1, column 1. (state=42P00,code=601) But scan in hbase, everything is ok: hbase(main):017:0> scan 'test' ROW COLUMN+CELL 1 column=fact:number, timestamp=1384220921197, value=123 2 column=fact:number, timestamp=1384221038418, value=1 2 row(s) in 0.0430 seconds
          pctony Tony Stevenson added a comment -

          Comment:jamestaylor:11/12/13 01:58:25 AM:

          mentioned

          pctony Tony Stevenson added a comment - Comment:jamestaylor:11/12/13 01:58:25 AM: mentioned
          pctony Tony Stevenson added a comment -

          Comment:skanda83:11/20/13 04:39:05 PM:

          Inserting integers through hbase shell is a bit tricky...use hbase api to insert integers, any datatype other than strings.It should definitely work

          pctony Tony Stevenson added a comment - Comment:skanda83:11/20/13 04:39:05 PM: Inserting integers through hbase shell is a bit tricky...use hbase api to insert integers, any datatype other than strings.It should definitely work
          pctony Tony Stevenson added a comment -

          Comment:jamestaylor:11/28/13 06:19:36 AM:

          Closing as it seems to be an hbase shell issue, not a phoenix issue.

          pctony Tony Stevenson added a comment - Comment:jamestaylor:11/28/13 06:19:36 AM: Closing as it seems to be an hbase shell issue, not a phoenix issue.
          pctony Tony Stevenson added a comment -

          Comment:jamestaylor:11/28/13 06:19:37 AM:

          closed

          pctony Tony Stevenson added a comment - Comment:jamestaylor:11/28/13 06:19:37 AM: closed
          gabriel.reid Gabriel Reid added a comment -

          Bulk resolve of closed issues imported from GitHub. This status was reached by first re-opening all closed imported issues and then resolving them in bulk.

          gabriel.reid Gabriel Reid added a comment - Bulk resolve of closed issues imported from GitHub. This status was reached by first re-opening all closed imported issues and then resolving them in bulk.

          People

            Unassigned Unassigned
            liuziliang liuziliang
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: