Phoenix基本使用与常见的问题

最新推荐文章于 2022-07-26 18:00:02 发布

trayvontang

最新推荐文章于 2022-07-26 18:00:02 发布

阅读量1.3k

点赞数

分类专栏： Bigdata

本文链接：https://blog.csdn.net/trayvontang/article/details/109339982

版权

Bigdata 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

文章目录

简介

其实一直不太想用Phoenix，因为HBase的设计本身不太适合SQL那一套，否则直接用MySQL就可以了。

不过如果设计得当，不乱使用join、group by等骚操作，使用Phoenix来做二级索引还是不错的，可以减少很大的工作量。

这样，如果的确因为历史余留或者资源等问题，不得不使用HBase做一些复杂的查询，也不必再引入Elasticsearch来做索引。

下面就介绍一下，Phoenix基本使用和一些常见的问题。

Phoenix下载安装

Phoenix下载地址

# 解压包
tar -zxvf apache-phoenix-4.11.0-HBase-1.3-bin.tar.gz

# 将对应的phoenix的jar包拷贝到hbase的lib目录下，RegionServer需要,所以主节点和从节点都需要
cp phoenix-core-xx-HBase-xx.jar /home/hadoop/hbase/lib
scp -r phoenix-core-xxx-HBase-xx.jar hadoop@172.18.68.88:/usr/local/hbase/lib

# 重启hbase
cd /url/local/hbase/bin
stop-hbase.sh
start-hbase.sh

命令行连接Phoenix

# 使用sqlline.py脚本连接，指定HBase使用的Zookeeper
sqlline.py 192.168.8.216:2181

sqlline.py hostname:2181

# /usr/local/phoenix/bin/sqlline.py
sqlline.py 192.168.8.216,192.168.8.217,192.168.8.218:2181

基本命令

# 查看帮助
!help
!?

# 查看连接
!list

# 查看所有表
!table
!tables

# 查看表结构
!describe table_name

# 指定schema
use schema_name

# 退出命令行
!quit

Phoenix数据类型

Phoenix数据类型	Java对应数据类型
CHAR	java.lang.String
TIME	java.sql.Time
DATE	java.sql.Date
ARRAY	java.sql.Array
FLOAT	java.lang.Float
BINARY	byte[]
DOUBLE	java.lang.Double
BIGINT	java.lang.Long
TINYINT	java.lang.Byte
DECIMAL	java.math.BigDecimal
BOOLEAN	java.lang.Boolean
INTEGER	java.lang.Integer
VARCHAR	java.lang.String
SMALLINT	java.lang.Short
VARBINARY	byte[]
TIMESTAMP	java.sql.Timestamp
UNSIGNED_INT	java.lang.Integer
UNSIGNED_LONG	java.lang.Long
UNSIGNED_TIME	java.sql.Time
UNSIGNED_DATE	java.sql.Date
UNSIGNED_FLOAT	java.lang.Float
UNSIGNED_DOUBLE	java.lang.Double
UNSIGNED_TINYINT	java.lang.Byte
UNSIGNED_SMALLINT	java.lang.Short
UNSIGNED_TIMESTAMP	java.sql.Timestamp

创建表

CREATE TABLE IF NOT EXISTS test (
    dt bigint NOT NULL,
    city VARCHAR NOT NULL,
    population BIGINT
    /* constraint表示主键约束 */
    constraint my_pk PRIMARY KEY (dt, city)
);

/* 创建表的时候可以通过指定字段前缀来指定列簇 */
CREATE TABLE TEST (
    id INTEGER PRIMARY KEY, 
    columnFamilyNameA.colNameA VARCHAR, 
    columnFamilyNameA.colNameB VARCHAR, 
    columnFamilyNameB.colNameC VARCHAR, 
    columnFamilyNameB.colNameD VARCHAR
    );

Phoenix创建表的时候，column family默认为’0’，也可以在建表的时候指定column family。

默认rowkey是通过主键计算。

Phoenix默认表名、字段名等会自动转换为大写，若要小写要加双引号，如"test"

Phoenix支持的基本SQL

/* 删除表 */
drop table test;

/* 插入数据 */
upsert into test values(1,'BJ',8143197);

/* 删除数据 */
delete from test where city='BJ';

/* 查看执行计划 */
explain select * from test where age>0;

/* 查询数据 */
select * from test;
select * from test where city='BJ';
select * from namespance.test where rk='215463197';
select * from namespance.test limit 10;
select * from namespance.test where dt='2020-01-01' and id=9999;

explain select * from TEST where "info"."no1" = '110' AND "no2" = '1204' and ROW = '200000'

Phoenix中使用SQL需要注意：对于常量字符串使用单引号，对于表名、字段名的小写使用双引号，如果是大写可以不用加引号

Phoenix创建视图和表映射

直接在HBase中创建的表，通过Phoenix查看不到，想要在Phoenix中查看或者操作HBase表中的的数据需要在Phoenix中进行表的映射。

映射方式有两种：

视图映射，不修改原表，只读
表映射，修改原表，可读写

创建表映射，如果对表进行了修改，源数据也会改变，同时如果关联表被删除，源表也会被删除。但是视图就不会，如果删除视图，源数据不会发生改变。

视图映射

Phoenix创建的视图是只读的，只能用于查询，无法通过视图对源数据进行修改等操作。

与直接创建映射表，视图的查询效率会低，原因是创建映射表的时候，Phoenix会在表中创建一些空的键值对，这些空键值对可以用来提高查询效率。

/* 创建视图 */
create view "test"(
    id bigint primary key,
    "name"."firstname" varchar,
    "name"."lastname" varchar,
    "company"."name"  varchar,
    "company"."address"varchar
);

/* 删除视图 */
drop view "test";

表映射

创建表映射和前面创建表的语法一样。

create table "test"(
    id bigint primary key,
    "cfA"."ca" varchar,
    "cfA"."cb"varchar,
    "cfB"."cc" varchar,
    "cfB"."cd"varchar
);

创建删除索引

/* 创建全局索引，读多写少 */
create index index_name on namespace.tablename(cfname.columnname desc)
create index index_name on namespace.tablename(cfname.columnnameA desc, cfname.columnnameB) 

/* 创建本地索引local，写多读少 */
create local index index_name ON namespace.tablename(cfname.columnname)

/* 使用include创建覆盖索引 */
create index index_name on namespace.tablename(cfname.columnnameA) include(cfname.columnnameB)

/* 异步创建索引 */
create index index_name on namespace.tablename(columnnameA) async

/* 删除索引 */
drop index index_name on namespace.tablename
drop index if exists index_name on namespace.tablename

全局索引适合读多写少，include覆盖索引意味不必回表，直接通过RowKey获取数据

使用二级索引，需要添加配置，RegionServer需要，所以每一台都需要添加：

<property>
  <name>hbase.regionserver.wal.codec</name>
  <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
<property>
  <name>hbase.region.server.rpc.scheduler.factory.class</name>
  <value>org.apache.hadoop.hbase.ipc.PhoenixRpcSchedulerFactory</value>
  <description>Factory to create the Phoenix RPC Scheduler that uses separate queues for index and metadata updates</description>
</property>
<property>
  <name>hbase.rpc.controllerfactory.class</name>
  <value>org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory</value>
  <description>Factory to create the Phoenix RPC Scheduler that uses separate queues for index and metadata updates</description>
</property>
<property>
  <name>hbase.coprocessor.regionserver.classes</name>
  <value>org.apache.hadoop.hbase.regionserver.LocalIndexMerger</value>
</property>

Master还需要添加配置：

<property>
  <name>hbase.master.loadbalancer.class</name>
  <value>org.apache.phoenix.hbase.index.balancer.IndexLoadBalancer</value>
</property>
<property>
  <name>hbase.coprocessor.master.classes</name>
  <value>org.apache.phoenix.hbase.index.master.IndexMasterObserver</value>
</property>

Java操作Phoenix

import org.apache.phoenix.query.QueryServices;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Random;

public class PhoenixBaseTest {

   private static final String JDBC_PHOENIX_URL = "jdbc:phoenix:192.168.8.216,192.168.8.217,192.168.8.218:2181?useUnicode=true&characterEncoding=utf-8";
//    private static final String JDBC_PHOENIX_URL = "jdbc:phoenix:192.168.8.216,192.168.8.217,192.168.8.218:2181";

    private static Connection connection;

    @Before
    public void setUp() throws SQLException {
        Properties properties = new Properties();
        properties.setProperty(QueryServices.THREAD_TIMEOUT_MS_ATTRIB, "1200000");
        properties.setProperty("hbase.rpc.timeout", "1200000");
        properties.setProperty("hbase.client.scanner.timeout.period", "1200000");
        properties.setProperty("phoenix.schema.isNamespaceMappingEnabled", Boolean.toString(true));
//        connection = DriverManager.getConnection(JDBC_PHOENIX_URL, properties);
        connection = DriverManager.getConnection(JDBC_PHOENIX_URL, properties);
    }

    @Test
    public void create() throws SQLException {
//        String createSql = "CREATE TABLE user (id varchar PRIMARY KEY,amount decimal(11,3),age INTEGER)";
        String createSql = "CREATE TABLE user4 (\"id\" varchar PRIMARY KEY,\"amount\" decimal(11,3),\"age\" INTEGER)";
        PreparedStatement ps = connection.prepareStatement(createSql);
        ps.execute();
        ps.close();
    }

    @Test
    public void upsert() throws SQLException {
        String upsertSql = "upsert into user(id, amount, age) values(?, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(upsertSql);
        ps.setString(1, "3");
        ps.setBigDecimal(2, new BigDecimal("-12.2345"));
        ps.setInt(3, 20);
        ps.executeUpdate();
        connection.commit();
        ps.close();
    }

    @Test
    public void upsertMany() throws SQLException {
        connection.setAutoCommit(false);
//        String upsertStatement = "upsert into user3 values(?,?,?)";
        String upsertStatement = "upsert into user4(\"id\", \"amount\", \"age\") values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(upsertStatement);

        Random random = new Random();
        for (int i = 20; i < 30; i++) {
            String id = String.valueOf(i);
            int age = random.nextInt(100);
            BigDecimal amount = new BigDecimal(1 + "." + (random.nextInt(100)+1));
            preparedStatement.setString(1, id);
            preparedStatement.setBigDecimal(2, amount);
            preparedStatement.setInt(3, age);
            preparedStatement.execute();
        }
        connection.commit();
        connection.setAutoCommit(true);
    }

    @Test
    public void query() throws SQLException {
        String sql = "select * from user";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData meta = rs.getMetaData();
        int colLength = meta.getColumnCount();
        List<String> colName = new ArrayList<>();
        for (int i = 1; i <= colLength; i++) {
            colName.add(meta.getColumnName(i));
        }

        List<String[]> result = new ArrayList<>();
        String[] colArr;
        while (rs.next()) {
            colArr = new String[colLength];
            for (int i = 0; i < colLength; i++) {
                colArr[i] = rs.getString(colName.get(i));
            }
            result.add(colArr);
        }
        ps.close();
        System.out.println(result);
    }

    @After
    public void destroy() throws SQLException {
        connection.close();
    }

}

两个值得注意的地方：

// 一定要设置
properties.setProperty("phoenix.schema.isNamespaceMappingEnabled", Boolean.toString(true));

// 指定编码，否则直接通过HBase命令行看到的字段名称是十六进制
JDBC_PHOENIX_URL = "jdbc:phoenix:192.168.8.216,192.168.8.217,192.168.8.218:2181?useUnicode=true&characterEncoding=utf-8";

HBase读写与Phoenix读写转换

HBase读写与Phoenix读写最好统一，否则就需要进行转换处理
创建Phoenix表的时候字段名称最好大写，否则使用HBase写数据，就算通过数据转换Phoenix也读不到

下面是可以用于HBase与Phoenix数据转换的代码：

public enum PhoenixType {
    DEFAULT(-1, "DEFAULT"),
    UNSIGNED_INT(4, "UNSIGNED_INT"),
    UNSIGNED_BIGINT(8, "UNSIGNED_BIGINT"),
    UNSIGNED_TINYINT(1, "UNSIGNED_TINYINT"),
    UNSIGNED_SMAILLINT(2, "UNSIGNED_SMAILLINT"),
    UNSIGNED_FLOAT(4, "UNSIGNED_FLOAT"),
    UNSIGNED_DOUBLE(8, "UNSIGNED_DOUBLE"),
    INTEGER(4, "INTEGER"),
    BIGINT(8, "BIGINT"),
    TINYINT(1, "TINYINT"),
    SMAILLINT(2, "SMAILLINT"),
    FLOAT(4, "FLOAT"),
    DOUBLE(8, "DOUBLE"),
    DECIMAL(-1, "DECIMAL"),
    BOOLEAN(1, "BOOLEAN"),
    TIME(8, "TIME"),                       //对应Phoenix的UNSIGNED_TIME
    DATE(8, "DATE"),                       //对应Phoenix的UNSIGNED_DATE
    TIMESTAMP(12, "TIMESTAMP"),            //对应Phoenix的UNSIGNED_TIMESTAMP
    VARCHAR(-1, "VARCHAR"),
    VARBINARY(-1, "VARBINARY");

    /**
     * -1：长度可变
     */
    private int len;
    private String type;

    PhoenixType(int len, String type) {
        this.len = len;
        this.type = type;
    }

    public int getLen() {
        return len;
    }

    public String getType() {
        return this.type;
    }

    public static PhoenixType getType(String type) {
        if (type == null) return DEFAULT;
        PhoenixType phoenixType;
        if (type.equalsIgnoreCase(UNSIGNED_INT.type)) {
            phoenixType = UNSIGNED_INT;
        } else if (type.equalsIgnoreCase(UNSIGNED_BIGINT.type)) {
            phoenixType = UNSIGNED_BIGINT;
        } else if (type.equalsIgnoreCase(UNSIGNED_TINYINT.type)) {
            phoenixType = UNSIGNED_TINYINT;
        } else if (type.equalsIgnoreCase(UNSIGNED_SMAILLINT.type)) {
            phoenixType = UNSIGNED_SMAILLINT;
        } else if (type.equalsIgnoreCase(UNSIGNED_FLOAT.type)) {
            phoenixType = UNSIGNED_FLOAT;
        } else if (type.equalsIgnoreCase(UNSIGNED_DOUBLE.type)) {
            phoenixType = UNSIGNED_DOUBLE;
        } else if (type.equalsIgnoreCase(INTEGER.type)) {
            phoenixType = INTEGER;
        } else if (type.equalsIgnoreCase(BIGINT.type)) {
            phoenixType = BIGINT;
        } else if (type.equalsIgnoreCase(TINYINT.type)) {
            phoenixType = TINYINT;
        } else if (type.equalsIgnoreCase(SMAILLINT.type)) {
            phoenixType = SMAILLINT;
        } else if (type.equalsIgnoreCase(FLOAT.type)) {
            phoenixType = FLOAT;
        } else if (type.equalsIgnoreCase(DOUBLE.type)) {
            phoenixType = DOUBLE;
        } else if (type.equalsIgnoreCase(BOOLEAN.type)) {
            phoenixType = BOOLEAN;
        } else if (type.equalsIgnoreCase(TIME.type)) {
            phoenixType = TIME;
        } else if (type.equalsIgnoreCase(DATE.type)) {
            phoenixType = DATE;
        } else if (type.equalsIgnoreCase(TIMESTAMP.type)) {
            phoenixType = TIMESTAMP;
        } else if (type.equalsIgnoreCase(VARCHAR.type)) {
            phoenixType = VARCHAR;
        } else if (type.equalsIgnoreCase(VARBINARY.type)) {
            phoenixType = VARBINARY;
        } else if (type.equalsIgnoreCase(DECIMAL.type)) {
            phoenixType = DECIMAL;
        } else {
            phoenixType = DEFAULT;
        }
        return phoenixType;
    }
}

import com.google.common.math.LongMath;
import org.apache.hadoop.hbase.util.Bytes;
import org.curitis.type.PhoenixType;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.MathContext;
import java.math.RoundingMode;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;


public class PhoenixTypeHelper {
    private static final String DATE_FORMAT_PATTERN = "yyyy-MM-dd";
    private static final String TIME_FORMAT_PATTERN = "HH:mm:ss";
    private static final String DATETIME_FORMAT_PATTERN = "yyyy-MM-dd HH:mm:ss";
    private static final String DATETIME_TIMESTAMP_FORMAT_PATTERN = "yyyy-MM-dd HH:mm:ss.SSS";

    /**
     * 转换为凤凰有符号类型
     * @param data 数据
     * @param phoenixType Phoenix对应的数据类型
     * @return
     */
    public static byte[] toBytes(Object data, PhoenixType phoenixType) {
        if (data == null) return null;
        byte[] b = null;
        if (phoenixType == PhoenixType.INTEGER) {
            b = new byte[Bytes.SIZEOF_INT];
            encodeInt(((Number) data).intValue(), b, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_INT) {
            b = new byte[Bytes.SIZEOF_INT];
            encodeUnsignedInt(((Number) data).intValue(), b, 0);
        } else if (phoenixType == PhoenixType.BIGINT) {
            b = new byte[Bytes.SIZEOF_LONG];
            encodeLong(((Number) data).longValue(), b, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_BIGINT) {
            b = new byte[Bytes.SIZEOF_LONG];
            encodeUnsignedLong(((Number) data).longValue(), b, 0);
        } else if (phoenixType == PhoenixType.SMAILLINT) {
            b = new byte[Bytes.SIZEOF_SHORT];
            encodeShort(((Number) data).shortValue(), b, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_SMAILLINT) {
            b = new byte[Bytes.SIZEOF_SHORT];
            encodeUnsignedShort(((Number) data).shortValue(), b, 0);
        } else if (phoenixType == PhoenixType.TINYINT) {
            b = new byte[Bytes.SIZEOF_BYTE];
            encodeByte(((Number) data).byteValue(), b, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_TINYINT) {
            b = new byte[Bytes.SIZEOF_BYTE];
            encodeUnsignedByte(((Number) data).byteValue(), b, 0);
        } else if (phoenixType == PhoenixType.FLOAT) {
            b = new byte[Bytes.SIZEOF_FLOAT];
            encodeFloat(((Number) data).floatValue(), b, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_FLOAT) {
            b = new byte[Bytes.SIZEOF_FLOAT];
            encodeUnsignedFloat(((Number) data).floatValue(), b, 0);
        } else if (phoenixType == PhoenixType.DOUBLE) {
            b = new byte[Bytes.SIZEOF_DOUBLE];
            encodeDouble(((Number) data).doubleValue(), b, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_DOUBLE) {
            b = new byte[Bytes.SIZEOF_DOUBLE];
            encodeUnsignedDouble(((Number) data).doubleValue(), b, 0);
        } else if (phoenixType == PhoenixType.BOOLEAN) {
            if ((Boolean) data) {
                b = new byte[]{1};
            } else {
                b = new byte[]{0};
            }
        } else if (phoenixType == PhoenixType.TIME || phoenixType == PhoenixType.DATE) {
            b = new byte[Bytes.SIZEOF_LONG];
            encodeDate(data, b, 0);
        } else if (phoenixType == PhoenixType.TIMESTAMP) {
            b = new byte[Bytes.SIZEOF_LONG + Bytes.SIZEOF_INT];
            encodeTimestamp(data, b, 0);
        } else if (phoenixType == PhoenixType.VARBINARY) {
            b = (byte[]) data;
        } else if (phoenixType == PhoenixType.VARCHAR || phoenixType == PhoenixType.DEFAULT) {
            b = Bytes.toBytes(data.toString());
        } else if (phoenixType == PhoenixType.DECIMAL) {
            b = encodeDecimal(data);
        }
        return b;
    }

    /**
     * byte数组转换为java类型
     * @param byteData
     * @param phoenixType
     * @return
     */
    public static Object toObject(byte[] byteData, PhoenixType phoenixType) {
        if (byteData == null) return null;
        Object v = null;
        if (phoenixType == PhoenixType.INTEGER) {
            v = decodeInt(byteData, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_INT) {
            v = decodeUnsignedInt(byteData, 0);
        } else if (phoenixType == PhoenixType.BIGINT) {
            v = decodeLong(byteData, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_BIGINT) {
            v = decodeUnsignedLong(byteData, 0);
        } else if (phoenixType == PhoenixType.SMAILLINT) {
            v = decodeShort(byteData, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_SMAILLINT) {
            v = decodeUnsignedShort(byteData, 0);
        } else if (phoenixType == PhoenixType.TINYINT) {
            v = decodeByte(byteData, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_TINYINT) {
            v = decodeUnsignedByte(byteData, 0);
        } else if (phoenixType == PhoenixType.FLOAT) {
            v = decodeFloat(byteData, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_FLOAT) {
            v = decodeUnsignedFloat(byteData, 0);
        } else if (phoenixType == PhoenixType.DOUBLE) {
            v = decodeDouble(byteData, 0);
        } else if (phoenixType == PhoenixType.UNSIGNED_DOUBLE) {
            v = decodeUnsignedDouble(byteData, 0);
        } else if (phoenixType == PhoenixType.BOOLEAN) {
            checkForSufficientLength(byteData, 0, Bytes.SIZEOF_BOOLEAN);
            if (byteData[0] == 1) {
                v = true;
            } else if (byteData[0] == 0) {
                v = false;
            }
        } else if (phoenixType == PhoenixType.TIME || phoenixType == PhoenixType.DATE) {
            v = new Date(decodeUnsignedLong(byteData, 0));
        } else if (phoenixType == PhoenixType.TIMESTAMP) {
            long millisDeserialized = decodeUnsignedLong(byteData, 0);
            Timestamp ts = new Timestamp(millisDeserialized);
            int nanosDeserialized = decodeUnsignedInt(byteData, Bytes.SIZEOF_LONG);
            ts.setNanos(nanosDeserialized < 1000000 ? ts.getNanos() + nanosDeserialized : nanosDeserialized);
            v = ts;
        } else if (phoenixType == PhoenixType.VARBINARY) {
            v = byteData;
        } else if (phoenixType == PhoenixType.VARCHAR || phoenixType == PhoenixType.DEFAULT) {
            v = Bytes.toString(byteData);
        } else if (phoenixType == PhoenixType.DECIMAL) {
            v = decodeDecimal(byteData, 0, byteData.length);
        }
 
        return v;
    }
 
 
    private static int decodeInt(byte[] bytes, int o) {
        checkForSufficientLength(bytes, o, Bytes.SIZEOF_INT);
        int v;
        v = bytes[o] ^ 0x80; // Flip sign bit back
        for (int i = 1; i < Bytes.SIZEOF_INT; i++) {
            v = (v << 8) + (bytes[o + i] & 0xff);
        }
        return v;
    }
 
    private static int encodeInt(int v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_INT);
        b[o + 0] = (byte) ((v >> 24) ^ 0x80); // Flip sign bit so that INTEGER is binary comparable
        b[o + 1] = (byte) (v >> 16);
        b[o + 2] = (byte) (v >> 8);
        b[o + 3] = (byte) v;
        return Bytes.SIZEOF_INT;
    }
 
    private static int decodeUnsignedInt(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_INT);
 
        int v = Bytes.toInt(b, o);
        if (v < 0) {
            throw new RuntimeException();
        }
        return v;
    }
 
    private static int encodeUnsignedInt(int v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_INT);
        if (v < 0) {
            throw new RuntimeException();
        }
        Bytes.putInt(b, o, v);
        return Bytes.SIZEOF_INT;
    }
 
    private static long decodeLong(byte[] bytes, int o) {
        checkForSufficientLength(bytes, o, Bytes.SIZEOF_LONG);
        long v;
        byte b = bytes[o];
        v = b ^ 0x80; // Flip sign bit back
        for (int i = 1; i < Bytes.SIZEOF_LONG; i++) {
            b = bytes[o + i];
            v = (v << 8) + (b & 0xff);
        }
        return v;
    }
 
    private static int encodeLong(long v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_LONG);
        b[o + 0] = (byte) ((v >> 56) ^ 0x80); // Flip sign bit so that INTEGER is binary comparable
        b[o + 1] = (byte) (v >> 48);
        b[o + 2] = (byte) (v >> 40);
        b[o + 3] = (byte) (v >> 32);
        b[o + 4] = (byte) (v >> 24);
        b[o + 5] = (byte) (v >> 16);
        b[o + 6] = (byte) (v >> 8);
        b[o + 7] = (byte) v;
        return Bytes.SIZEOF_LONG;
    }
 
    private static long decodeUnsignedLong(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_LONG);
        long v = 0;
        for (int i = o; i < o + Bytes.SIZEOF_LONG; i++) {
            v <<= 8;
            v ^= b[i] & 0xFF;
        }
 
        if (v < 0) {
            throw new RuntimeException();
        }
        return v;
    }
 
    private static int encodeUnsignedLong(long v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_LONG);
        if (v < 0) {
            throw new RuntimeException();
        }
        Bytes.putLong(b, o, v);
        return Bytes.SIZEOF_LONG;
    }
 
    private static short decodeShort(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_SHORT);
        int v;
        v = b[o] ^ 0x80; // Flip sign bit back
        for (int i = 1; i < Bytes.SIZEOF_SHORT; i++) {
            v = (v << 8) + (b[o + i] & 0xff);
        }
        return (short) v;
    }
 
    private static int encodeShort(short v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_SHORT);
        b[o + 0] = (byte) ((v >> 8) ^ 0x80); // Flip sign bit so that Short is binary comparable
        b[o + 1] = (byte) v;
        return Bytes.SIZEOF_SHORT;
    }
 
    private static short decodeUnsignedShort(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_SHORT);
        short v = Bytes.toShort(b, o);
        if (v < 0) {
            throw new RuntimeException();
        }
        return v;
    }
 
    private static int encodeUnsignedShort(short v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_SHORT);
        if (v < 0) {
            throw new RuntimeException();
        }
        Bytes.putShort(b, o, v);
        return Bytes.SIZEOF_SHORT;
    }
 
    private static byte decodeByte(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_BYTE);
        int v;
        v = b[o] ^ 0x80; // Flip sign bit back
        return (byte) v;
    }
 
    private static int encodeByte(byte v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_BYTE);
        b[o] = (byte) (v ^ 0x80); // Flip sign bit so that Short is binary comparable
        return Bytes.SIZEOF_BYTE;
    }
 
    private static byte decodeUnsignedByte(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_BYTE);
        byte v = b[o];
        if (v < 0) {
            throw new RuntimeException();
        }
        return v;
    }
 
    private static int encodeUnsignedByte(byte v, byte[] b, int o) {
        if (v < 0) {
            throw new RuntimeException();
        }
        Bytes.putByte(b, o, v);
        return Bytes.SIZEOF_BYTE;
    }
 
    private static float decodeFloat(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_INT);
        int value;
        value = Bytes.toInt(b, o);
        value--;
        value ^= (~value >> Integer.SIZE - 1) | Integer.MIN_VALUE;
        return Float.intBitsToFloat(value);
    }
 
 
    private static int encodeFloat(float v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_FLOAT);
        int i = Float.floatToIntBits(v);
        i = (i ^ ((i >> Integer.SIZE - 1) | Integer.MIN_VALUE)) + 1;
        Bytes.putInt(b, o, i);
        return Bytes.SIZEOF_FLOAT;
    }
 
    private static float decodeUnsignedFloat(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_FLOAT);
        float v = Bytes.toFloat(b, o);
        if (v < 0) {
            throw new RuntimeException();
        }
        return v;
    }
 
    private static int encodeUnsignedFloat(float v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_FLOAT);
        if (v < 0) {
            throw new RuntimeException();
        }
        Bytes.putFloat(b, o, v);
        return Bytes.SIZEOF_FLOAT;
    }
 
    private static double decodeDouble(byte[] bytes, int o) {
        checkForSufficientLength(bytes, o, Bytes.SIZEOF_LONG);
        long l;
        l = Bytes.toLong(bytes, o);
        l--;
        l ^= (~l >> Long.SIZE - 1) | Long.MIN_VALUE;
        return Double.longBitsToDouble(l);
    }
 
 
    private static int encodeDouble(double v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_LONG);
        long l = Double.doubleToLongBits(v);
        l = (l ^ ((l >> Long.SIZE - 1) | Long.MIN_VALUE)) + 1;
        Bytes.putLong(b, o, l);
        return Bytes.SIZEOF_LONG;
    }
 
    private static double decodeUnsignedDouble(byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_DOUBLE);
        double v = Bytes.toDouble(b, o);
        if (v < 0) {
            throw new RuntimeException();
        }
        return v;
    }
 
    private static int encodeUnsignedDouble(double v, byte[] b, int o) {
        checkForSufficientLength(b, o, Bytes.SIZEOF_DOUBLE);
        if (v < 0) {
            throw new RuntimeException();
        }
        Bytes.putDouble(b, o, v);
        return Bytes.SIZEOF_DOUBLE;
    }
 
    private static int encodeDate(Object v, byte[] b, int o) {
        if (v instanceof Date) {
            encodeUnsignedLong(((Date) v).getTime(), b, 0);
        } else if (v instanceof String) {
            String dateStr = (String) v;
            int len = dateStr.length();
            Date date = null;
            try {
                if (len == 10 && dateStr.charAt(4) == '-' && dateStr.charAt(7) == '-') {
                    SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT_PATTERN);
                    date = format.parse(dateStr);
                } else if (len == 8 && dateStr.charAt(2) == ':' && dateStr.charAt(5) == ':') {
                    SimpleDateFormat format = new SimpleDateFormat(TIME_FORMAT_PATTERN);
                    date = format.parse(dateStr);
                } else if (len == 19 && dateStr.charAt(4) == '-' && dateStr.charAt(7) == '-'
                        && dateStr.charAt(13) == ':' && dateStr.charAt(16) == ':') {
                    SimpleDateFormat format = new SimpleDateFormat(DATETIME_FORMAT_PATTERN);
                    date = format.parse(dateStr);
                } else if (len == 23 && dateStr.charAt(4) == '-' && dateStr.charAt(7) == '-'
                        && dateStr.charAt(13) == ':' && dateStr.charAt(16) == ':'
                        && dateStr.charAt(19) == '.') {
                    SimpleDateFormat format = new SimpleDateFormat(DATETIME_TIMESTAMP_FORMAT_PATTERN);
                    date = format.parse(dateStr);
                }
                if (date != null) {
                    encodeUnsignedLong(date.getTime(), b, 0);
                }
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }
        }
        return Bytes.SIZEOF_LONG;
    }
 
    private static int encodeTimestamp(Object v, byte[] b, int o) {
        if (v instanceof Timestamp) {
            Timestamp ts = (Timestamp) v;
            encodeUnsignedLong(ts.getTime(), b, o);
            Bytes.putInt(b, Bytes.SIZEOF_LONG, ts.getNanos() % 1000000);
        } else {
            encodeDate(v, b, o);
        }
        return Bytes.SIZEOF_LONG + Bytes.SIZEOF_INT;
    }
 
    private static byte[] encodeDecimal(Object object) {
        if (object == null) {
            return new byte[0];
        }
        BigDecimal v = (BigDecimal) object;
        v = v.round(DEFAULT_MATH_CONTEXT).stripTrailingZeros();
        int len = getLength(v);
        byte[] result = new byte[Math.min(len, 21)];
        decimalToBytes(v, result, 0, len);
        return result;
    }
 
 
    private static BigDecimal decodeDecimal(byte[] bytes, int offset, int length) {
        if (length == 1 && bytes[offset] == ZERO_BYTE) {
            return BigDecimal.ZERO;
        }
        int signum = ((bytes[offset] & 0x80) == 0) ? -1 : 1;
        int scale;
        int index;
        int digitOffset;
        long multiplier = 100L;
        int begIndex = offset + 1;
        if (signum == 1) {
            scale = (byte) (((bytes[offset] & 0x7F) - 65) * -2);
            index = offset + length;
            digitOffset = POS_DIGIT_OFFSET;
        } else {
            scale = (byte) ((~bytes[offset] - 65 - 128) * -2);
            index = offset + length - (bytes[offset + length - 1] == NEG_TERMINAL_BYTE ? 1 : 0);
            digitOffset = -NEG_DIGIT_OFFSET;
        }
        length = index - offset;
        long l = signum * bytes[--index] - digitOffset;
        if (l % 10 == 0) { // trailing zero
            scale--; // drop trailing zero and compensate in the scale
            l /= 10;
            multiplier = 10;
        }
        // Use long arithmetic for as long as we can
        while (index > begIndex) {
            if (l >= MAX_LONG_FOR_DESERIALIZE || multiplier >= Long.MAX_VALUE / 100) {
                multiplier = LongMath.divide(multiplier, 100L, RoundingMode.UNNECESSARY);
                break; // Exit loop early so we don't overflow our multiplier
            }
            int digit100 = signum * bytes[--index] - digitOffset;
            l += digit100 * multiplier;
            multiplier = LongMath.checkedMultiply(multiplier, 100);
        }
 
        BigInteger bi;
        // If still more digits, switch to BigInteger arithmetic
        if (index > begIndex) {
            bi = BigInteger.valueOf(l);
            BigInteger biMultiplier = BigInteger.valueOf(multiplier).multiply(ONE_HUNDRED);
            do {
                int digit100 = signum * bytes[--index] - digitOffset;
                bi = bi.add(biMultiplier.multiply(BigInteger.valueOf(digit100)));
                biMultiplier = biMultiplier.multiply(ONE_HUNDRED);
            } while (index > begIndex);
            if (signum == -1) {
                bi = bi.negate();
            }
        } else {
            bi = BigInteger.valueOf(l * signum);
        }
        // Update the scale based on the precision
        scale += (length - 2) * 2;
        BigDecimal v = new BigDecimal(bi, scale);
        return v;
    }
 
 
    private static int getLength(BigDecimal v) {
        int signum = v.signum();
        if (signum == 0) { // Special case for zero
            return 1;
        }
        return (signum < 0 ? 2 : 1) + (v.precision() + 1 + (v.scale() % 2 == 0 ? 0 : 1)) / 2;
    }
 
    private static final int MAX_PRECISION = 38;
    private static final MathContext DEFAULT_MATH_CONTEXT = new MathContext(MAX_PRECISION, RoundingMode.HALF_UP);
    private static final Integer MAX_BIG_DECIMAL_BYTES = 21;
    private static final byte ZERO_BYTE = (byte) 0x80;
    private static final byte NEG_TERMINAL_BYTE = (byte) 102;
    private static final int EXP_BYTE_OFFSET = 65;
    private static final int POS_DIGIT_OFFSET = 1;
    private static final int NEG_DIGIT_OFFSET = 101;
    private static final BigInteger MAX_LONG = BigInteger.valueOf(Long.MAX_VALUE);
    private static final BigInteger MIN_LONG = BigInteger.valueOf(Long.MIN_VALUE);
    private static final BigInteger ONE_HUNDRED = BigInteger.valueOf(100);
    private static final long MAX_LONG_FOR_DESERIALIZE = Long.MAX_VALUE / 1000;
 
 
    private static int decimalToBytes(BigDecimal v, byte[] result, final int offset, int length) {
        int signum = v.signum();
        if (signum == 0) {
            result[offset] = ZERO_BYTE;
            return 1;
        }
        int index = offset + length;
        int scale = v.scale();
        int expOffset = scale % 2 * (scale < 0 ? -1 : 1);
        int multiplyBy;
        BigInteger divideBy;
        if (expOffset == 0) {
            multiplyBy = 1;
            divideBy = ONE_HUNDRED;
        } else {
            multiplyBy = 10;
            divideBy = BigInteger.TEN;
        }
        // Normalize the scale based on what is necessary to end up with a base 100 decimal (i.e. 10.123e3)
        int digitOffset;
        BigInteger compareAgainst;
        if (signum == 1) {
            digitOffset = POS_DIGIT_OFFSET;
            compareAgainst = MAX_LONG;
            scale -= (length - 2) * 2;
            result[offset] = (byte) ((-(scale + expOffset) / 2 + EXP_BYTE_OFFSET) | 0x80);
        } else {
            digitOffset = NEG_DIGIT_OFFSET;
            compareAgainst = MIN_LONG;
            // Scale adjustment shouldn't include terminal byte in length
            scale -= (length - 2 - 1) * 2;
            result[offset] = (byte) (~(-(scale + expOffset) / 2 + EXP_BYTE_OFFSET + 128) & 0x7F);
            if (length <= MAX_BIG_DECIMAL_BYTES) {
                result[--index] = NEG_TERMINAL_BYTE;
            } else {
                // Adjust length and offset down because we don't have enough room
                length = MAX_BIG_DECIMAL_BYTES;
                index = offset + length;
            }
        }
        BigInteger bi = v.unscaledValue();
        // Use BigDecimal arithmetic until we can fit into a long
        while (bi.compareTo(compareAgainst) * signum > 0) {
            BigInteger[] dandr = bi.divideAndRemainder(divideBy);
            bi = dandr[0];
            int digit = dandr[1].intValue();
            result[--index] = (byte) (digit * multiplyBy + digitOffset);
            multiplyBy = 1;
            divideBy = ONE_HUNDRED;
        }
        long l = bi.longValue();
        do {
            long divBy = 100 / multiplyBy;
            long digit = l % divBy;
            l /= divBy;
            result[--index] = (byte) (digit * multiplyBy + digitOffset);
            multiplyBy = 1;
        } while (l != 0);
 
        return length;
    }
 
    private static void checkForSufficientLength(byte[] b, int offset, int requiredLength) {
        if (b.length < offset + requiredLength) {
            throw new RuntimeException
                    ("Expected length of at least " + requiredLength + " bytes, but had " + (b.length - offset));
        }
    }
}