Phoenix doesn't display negative integer value

Question

I am creating a HBASE table with a value of integer -17678. But when i retrieve it from pheonix it gives me a different positive value. RowKey is a composite rowkey and there is no problem with rowkey.

Hbase insertion:

public class test
{
public static void main(String args[])
{
        Configuration config = HBaseConfiguration.create();

          Connection connection = ConnectionFactory.createConnection(config);
          Table table = connection.getTable(TableName.valueOf("TEST"));
          Integer i=-17678;

          try
          {
          Put p = new Put(Bytes.toBytes("rowkey"));
          p.addColumn(Bytes.toBytes("test"),Bytes.toBytes("test"),Bytes.toBytes(i));
          table.put(p);
          }
          finally
          {
             table.close();
             connection.close();
          }

    }
}

Phoenix retrieval:

select CAST("Value" AS INTEGER) from TEST ;

+------------------------------------------+
|         TO_INTEGER(test."Value")         | 
+------------------------------------------+
| 2147465970                               | 
+------------------------------------------+

Anything wrong here? or a phoenix issue?

Answer 1

Try something like :

//Add dependencies in your pom.xml
<dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>${phoenix.core.version}</version>
            <scope>provided</scope>
</dependency>

And than:

//Import these classes. More types available
import org.apache.phoenix.schema.types.{PDecimal, PInteger, PVarchar}
//and replace your addColumn method by this one:
p.addColumn(Bytes.toBytes("test"),Bytes.toBytes("test"),PInteger.INSTANCE.toBytes(i));
//Or if you wish a Decimal
p.addColumn(Bytes.toBytes("test"),Bytes.toBytes("test"),PDecimal.INSTANCE.toBytes(i));

Answer 2

http://phoenix.apache.org/language/datatypes.html

The binary representation is a 4 byte integer with the sign bit flipped (so that negative values sorts before positive values).

So to convert from HBase serialization format to Phoenix format:

(-17678)10 = (11111111111111111011101011110010)2
=> (01111111111111111011101011110010)2 = (2147465970)10

Thus the output is as expected. You need to be aware of the binary representation when inserting data using HBase.

Direct HBase toByte to Phoenix reads is only possible with CHAR and UNSIGNED_* data types. You'd have to serialize appropriately for other data types. ie. setting i = 2147465970 when you mean to insert -17678.

I recommend using Phoenix to insert data. If you are worried about keeping your application light on dependencies, Phoenix offers a "thin" jdbc driver (4mb instead of 86mb).

https://phoenix.apache.org/server.html

---

If you absolutely must use HBase, you can serialize signed numbers by using a bitwise XOR.

For integers, you would want to XOR your i with a bitmask to flip the sign bit.

The bitmask to apply to a 4-byte Integer is:

(10000000000000000000000000000000)2 = (-2147483648)10

From http://ideone.com/anhgs5 , we get 2147465970. If you insert that using HBase, when you read using Phoenix, you will read -17678).

You will need a different bitmask for Bigint (shared bitmask with the date-time types), Smallint, Float, and Double.