实现Phoenix入门到精通

快速入门

Phoenix是一个开源的HBASE SQL层。Phoeinx可以用标准的JDBC API替代HBASE client API来创建表，插入和查询查询HBASE中的数据。

Phoenix作为应用层和HBASE之间的中间件,以下特性使它在大数据量的简单查询场景有着独有的优势

 1. 二级索引支持(global index + local index)

2. 编译SQL成为原生HBASE的可并行执行的scan

3. 在数据层完成计算，server端的coprocessor执行聚合

4. 下推where过滤条件到server端的scan filter上

5. 利用统计信息优化、选择查询计划（5.x版本将支持CBO）

6. skip scan功能提高扫描速度

一般可以使用以下三种方式访问Phoenix

1. JDBC API

2. 使用Python编写的命令行工具（sqlline, sqlline-thin和psql等）

3. SQuirrel

一.命令行工具psql使用示例

1.创建一个建表的sql脚本文件us_population.sql：

CREATE TABLE IF NOT EXISTS us_population (

state CHAR(2) NOT NULL,  

 city VARCHAR NOT NULL,  

 population BIGINT    CONSTRAINT my_pk PRIMARY KEY (state, city)

);

2. 创建csv格式的数据文件us_population.csv：

NY,New York,8143197

CA,Los Angeles,3844829

IL,Chicago,2842518

TX,Houston,2016582

PA,Philadelphia,1463281

AZ,Phoenix,1461575

TX,San Antonio,1256509

CA,San Diego,1255540

TX,Dallas,1213825

CA,San Jose,912332

3. 创建一个查询sql脚本文件us_population_queries.sql：

SELECT state as &＃8220;State&＃8221;,count(city) as &＃8220;City Count&＃8221;,sum(population) as &＃8220;Population Sum&＃8221;FROM us_populationGROUP BY stateORDER BY sum(population) DESC;

4. 执行psql.py工具运行sql脚本：

./psql.py  us_population.sql us_population.csv us_population_queries.sql

二.JDBC API使用示例

1. 使用Maven构建工程时，需要添加以下依赖

    com.aliyun.phoenix

    ali-phoenix-core    

    ${version}

2. 创建名为test.java的文件

importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.PreparedStatement;importjava.sql.Statement;publicclasstest{publicstaticvoidmain(String[] args)throwsSQLException{        Statement stmt =null;        ResultSet rset =null;                Connection con = DriverManager.getConnection(&＃8220;jdbc:phoenix:[zookeeper]&＃8221;);        stmt = con.createStatement();                stmt.executeUpdate(&＃8220;create table test (mykey integer not null primary key, mycolumn varchar)&＃8221;);        stmt.executeUpdate(&＃8220;upsert into test values (1,&＃8217;Hello&＃8217;)&＃8221;);        stmt.executeUpdate(&＃8220;upsert into test values (2,&＃8217;World!&＃8217;)&＃8221;);        con.commit();                PreparedStatement statement = con.prepareStatement(&＃8220;select * from test&＃8221;);        rset = statement.executeQuery();while(rset.next()) {            System.out.println(rset.getString(&＃8220;mycolumn&＃8221;));        }        statement.close();        con.close();    }}

3.执行test.java

javac test.javajava -cp&＃8221;../phoenix-[version]-client.jar:.&＃8221;test

三.SQuirrel使用示例

参考： SQuirrel使用示例

数据类型

目前Phoenix支持24种简单数据类型和1个一维Array的复杂类型。以下是对支持数据类型的说明:

DML语法

云HBASE上Phoenix支持的DML

1. SELECT

从一个或者多个表中查询数据。

LIMIT(或者FETCH FIRST) 在ORDER BY子句后将转换为top-N查询。

OFFSET子句指定返回查询结果前跳过的行数。

示例

SELECT * FROM TEST LIMIT 1000;SELECT * FROM TEST LIMIT 1000 OFFSET 100;SELECT full_name FROM SALES_PERSON WHERE ranking >= 5.0    UNION ALL SELECT reviewer_name FROM    CUSTOMER_REVIEW WHERE score >= 8.0

2. UPSERT VALUES

此处upsert语义有异于标准SQL中的Insert，当写入值不存在时，表示写入数据，否则更新数据。其中列的声明是可以省略的，当省略时，values指定值的顺序和目标表中schema声明列的顺序需要一致。

ON DUPLICATE KEY是4.9版本中的功能，表示upsert原子写入的语义，在写入性能上弱于非原子语义。相同的row在同一batch中按照执行顺序写入。

示例

UPSERT INTO TEST VALUES(&＃8216;foo&＃8217;,&＃8217;bar&＃8217;,3);UPSERT INTO TEST(NAME,ID) VALUES(&＃8216;foo&＃8217;,123);UPSERT INTO TEST(ID, COUNTER) VALUES(123, 0) ON DUPLICATE KEYUPDATECOUNTER = COUNTER +1;UPSERT INTO TEST(ID, MY_COL) VALUES(123, 0) ON DUPLICATE KEY IGNORE;

3. UPSERT SELECT

从另外一张表中读取数据写入到目标表中，如果数据存在则更新，否则插入数据。插入目标表的值顺序和查询表指定查询字段一致。当auto commit被打开并且select子句没有聚合时，写入目标表这个过程是在server端完成的，否则查询的数据会先缓存在客户端再写入目标表中（phoenix.mutate.upsertBatchSize表示从客户端一次commit的行数，默认10000行）。

示例

UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM test.sourceTable WHERE col5 <100UPSERT INTO foo SELECT * FROM bar;

4. DELETE

删除选定的列。如果auto commit打开，删除操作将在server端执行。

示例

DELETEFROMTABLENAME;DELETEFROMTABLENAMEWHEREPK=123;DELETEFROMTABLENAMEWHERENAMELIKE&＃8217;%&＃8217;;

References

https://phoenix.apache.org/language/index.html

加盐表

1. 什么是加盐？

在密码学中，加盐是指在散列之前将散列内容（例如：密码）的任意固定位置插入特定的字符串。这个在散列中加入字符串的方式称为“加盐”。其作用是让加盐后的散列结果和没有加盐的结果不相同，在不同的应用情景中，这个处理可以增加额外的安全性。而Phoenix中加盐是指对pk对应的byte数组插入特定的byte数据。

2. 加盐能解决什么问题？

加盐能解决HBASE读写热点问题，例如:单调递增rowkey数据的持续写入，使得负载集中在某一个RegionServer上引起的热点问题。

3. 怎么对表加盐？

在创建表的时候指定属性值：SALT_BUCKETS，其值表示所分buckets(region)数量， 范围是1~256。

CREATE TABLE table (key VARCHAR PRIMARY KEY, col VARCHAR) SALT_BUCKETS = 8;

4. 加盐的原理是什么？

加盐的过程就是在原来key的基础上增加一个byte作为前缀,计算公式如下：

new_row_key = (++index % BUCKETS_NUMBER) + original_key

下图展示了自增rowkey通过加盐被打散写入到各个region中的过程

5. 一个表“加多少盐合适”？

当可用block cache的大小小于表数据大小时，较优的slated bucket是和region server数量相同，这样可以得到更好的读写性能。

当表的数量很大时，基本上会忽略blcok cache的优化收益，大部分数据仍然需要走磁盘IO。比如对于10个region server集群的大表，可以考虑设计64~128个slat buckets。

6. 加盐时需要注意

创建加盐表时不能再指定split key。

太大的slated buckets会减小range查询的灵活性，甚至降低查询性能。

References

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

https://zh.wikipedia.org/wiki/%E7%9B%90_(%E5%AF%86%E7%A0%81%E5%AD%A6)

https://community.hortonworks.com/questions/26269/how-many-salt-buckets-should-i-use-for-my-phoenix.html

二级索引

一.概要

目前HBASE只有基于字典序的主键索引，对于非主键过滤条件的查询都会变成扫全表操作，为了解决这个问题Phoenix引入了二级索引功能。然而此二级索引又有别于传统关系型数据库的二级索引，本文将详细描述Phoenix中二级索引功能、用法和原理，希望能够对大家在业务技术选型时起到一些帮助作用。

二.二级索引

示例表如下(为了能够容易通过HBASE SHELL对照表内容，我们对属性值COLUMN_ENCODED_BYTES设置为0，不对column family进行编码)：

CREATE TABLE  TEST (   ID VARCHAR NOT NULL  PRIMARY KEY,   COL1 VARCHAR,   COL2 VARCHAR  ) COLUMN_ENCODED_BYTES=0;

upsert into TEST values(&＃8216;1&＃8217;, &＃8216;2&＃8217;, &＃8216;3&＃8217;);

1. 全局索引

全局索引更多的应用在读较多的场景。它对应一张独立的HBASE表。对于全局索引，在查询中检索的列如果不在索引表中，默认的索引表将不会被使用，除非使用hint。

创建全局索引：

CREATE INDEX IDX_COL1 ON TEST(COL1)

通过HBASE SHELL观察生成的索引表IDX_COL1。我们发现全局索引表的RowKey存储了索引列的值和原表RowKey的值，这样编码更有利于提高查询的性能。

hbase(main):001:0> scan &＃8216;IDX_COL1&＃8217;ROW                        COLUMN+CELL 2\x001                    column=0:_0, timestamp=1520935113031, value=x1 row(s) in 0.1650 seconds

实际上全局索引的RowKey将会按照如下格式进行编码

SALT BYTE: 全局索引表和普通phoenix表一样，可以在创建索引时指定SALT_BUCKETS或者split key。此byte正是存储着salt。TENANT_ID: 当前数据对应的多租户ID。INDEX VALUE: 索引数据。PK VALUE: 原表的RowKey。

2. 本地索引

因为本地索引和原数据是存储在同一个表中的，所以更适合写多的场景。对于本地索引，查询中无论是否指定hint或者是查询的列是否都在索引表中，都会使用索引表。

创建本地索引：

create local index LOCAL_IDX_COL1 ON TEST(COL1);

通过HBASE SHELL观察表&＃8217;TEST&＃8217;, 我们可以看到表中多了一行column为L#0:_0的索引数据。

hbase(main):001:0> scan &＃8216;TEST&＃8217;ROW                        COLUMN+CELL \x00\x002\x001            column=L#0:_0, timestamp=1520935997600, value=_0 1                         column=0:COL1, timestamp=1520935997600, value=2 1                         column=0:COL2, timestamp=1520935997600, value=3 1                         column=0:_0, timestamp=1520935997600, value=x2 row(s) in 0.1680 seconds

本地索引的RowKey将会按照如下格式进行编码

REGION START KEY : 当前row所在region的start key。加上这个start key的好处是，可以让索引数据和原数据尽量在同一个region, 减小IO，提升性能。INDEX ID : 每个ID对应不同的索引表。TENANT ID :当前数据对应的多租户ID。INDEX VALUE: 索引数据。PK VALUE: 原表的RowKey。

3. 覆盖索引

覆盖索引的特点是把原数据存储在索引数据表中，这样在查询到索引数据时就不需要再次返回到原表查询，可以直接拿到查询结果。

创建覆盖索引：

create  index IDX_COL1_COVER_COL2 on TEST(COL1) include(COL2);

通过HBASE SHELL 查询表IDX_COL1_COVER_COL2, 我们发现include的列的值被写入到了value中。

hbase(main):003:0> scan &＃8216;IDX_COL1_COVER_COL2&＃8217;ROW                   COLUMN+CELL 2\x001               column=0:0:COL2, timestamp=1520943893821, value=3 2\x001               column=0:_0, timestamp=1520943893821, value=x1 row(s) in 0.0180 seconds

对于类似select col2 from TEST where COL1=&＃8217;2&＃8217;的查询，查询一次索引表就能获得结果。其查询计划如下：

+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;+|                                         PLAN                                         | EST_BYTES_READ  | EST_ROWS_READ  | E |+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;+| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER IDX_COL1_COVER_COL2 [&＃8216;2&＃8217;]  | null            | null           | n |+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;+

4. 函数索引

函数索引的特点是能根据表达式创建索引，适用于对查询表，过滤条件是表达式的表创建索引。例如：

//创建函数索引CREATE INDEX CONCATE_IDX ON TEST (UPPER(COL1||COL2))//查询函数索引SELECT * FROM TEST WHERE UPPER(COL1||COL2)=&＃8217;23&＃8217;

三.什么是Phoenix的二级索引？

Phoenix的二级索引我们基本上已经介绍过了，我们回过头来继续看Phoenix二级索引的官方定义:Secondary indexes are an orthogonal way to access data from its primary access path。简单理解为，在主访问路径（通过row key访问）上发生正交的一种方法，更清楚的应该描述为：索引列访问和row key访问产生交集时的一种索引方法。我们来通过一个例子说明：

1. 对表TEST的COL1创建全局索引

CREATEINDEXIDX_COL1ONTEST(COL1);

2. 对于如下查询必将发生FULL SCAN

select*fromTESTwhereCOL1=&＃8217;2&＃8242;;

以上查询的查询计划如下：

+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+|                              PLAN                              | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+| CLIENT1-CHUNK PARALLEL1-WAY ROUND ROBIN FULL SCAN OVER TEST  |null|null|null||     SERVER FILTER BY COL1 =&＃8217;2&＃8217;|null|null|null|+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;-+&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+

3. 对于以下查询将会形成点查。因为二级索引是RowKey的交集。

select * from TEST where id=&＃8217;1&＃8242; and COL1=&＃8217;2&＃8242;

查询计划如下

+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;-+|                                            PLAN                                             | EST_BYTES_READ  | EST_ROWS_RE |+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;-+| CLIENT 1-CHUNK 1 ROWS 203 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 1 KEY OVER TEST  | 203             | 1           ||     SERVER FILTER BY COL1 = &＃8216;2&＃8217;                                                             | 203             | 1           |+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;+&＃8212;&＃8212;&＃8212;&＃8212;&＃8212;&＃8211;+&＃8212;&＃8212;&＃8212;&＃8212;-+

对于2中所描述的查询为什么会发生FULL SCAN? 正如Phoenix二级索引官方定义的一样，因为“没有和RowKey列的查询发生正交关系”，除非使用Hint强制指定索引表。

四.索引Building

Phoenix的二级索引创建有同步和异步两种方式。

在执行CREATE INDEX IDX_COL1 ON TEST(COL1)时会进行索引数据的同步。此方法适用于数据量较小的情况。

异步build索引需要借助MR，创建异步索引语法和同步索引相差一个关键字：ASYNC。

//创建异步索引CREATE INDEX ASYNC_IDX ON DB.TEST (COL1) ASYNC//build 索引数据${HBASE_HOME}/bin/hbase org.apache.phoenix.mapreduce.index.IndexTool &＃8211;schema DB &＃8211;data-table TEST &＃8211;index-table ASYNC_IDX  &＃8211;output-path ASYNC_IDX_HFILES

五.索引问题汇总

1. 创建同步索引超时怎么办？

在客户端配置文件hbase-site.xml中，把超时参数设置大一些，足够build索引数据的时间。

2. 索引表最多可以创建多少个？

建议不超过10个

3. 为什么索引表多了，单条写入会变慢？

索引表越多写放大越严重。写放大情况可以参考下图。

References

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

https://community.hortonworks.com/articles/61705/art-of-phoenix-secondary-indexes.html

MR在Ali-Phoenix上的使用

一.MR在Phoenix上的用途

利用MR对Phoenix表（可带有二级索引表）进行Bulkload入库, 其原理是直接生成主表（二级索引表）的HFILE写入HDFS。相对于走API的数据导入方式，不仅速度更快，而且对HBASE集群的负载也会小很多。目前云HBASE上的Phoenix支持以下数据源的Bulkload工具:

CsvBulkLoadTool

JsonBulkLoadTool

RegexBulkLoadTool

ODPSBulkLoadTool（待上线）

利用MR Building二级索引。当主表数据量较大时，可以通过创建异步索引，使用MR快速同步索引数据。

二.如何访问云HBASE的HDFS？

由于云HBASE上没有MR，需要借助外部的计算引擎（自建的HADOOP集群或者EMR），而使用外部的计算引擎的首先面临的问题是，如何跨集群访问HDFS。

由于云HBASE的HDFS端口默认是不开的，需要联系工作人员开通。

端口开通以后，要想顺利的访问HDFS是HA配置的云HBASE集群，需要向工作人员获取云HBASE的主备(emr-header-1,emr-header-2)namenode host/IP。参考如下配置模板，设置hadoop客户端配置文件：

core-site.xml

hdfs-site.xml

验证访问云HBASE HDFS

hadoop dfs -ls hdfs://emr-cluster/

三.BULKLOAD PHOENIX表

1. 由于要和云HBASE通信，所以客户端的依赖的hbase-protocol.jar需要是1.1.x版本。可以使用链接：http://central.maven.org/maven2/org/apache/hbase/hbase-protocol/1.1.1/hbase-protocol-1.1.1.jar 下载。

2. 以EMR访问云HBASE为例。EMR集群需要把云HBASE HDFS的emr-cluster 相关配置和当前EMR的HDFS配置合在一起形成新的配置文件，单独存放在一个目录（${conf-dir}）下。通过yarn命令的&＃8211;config参数指定新的配置目录，使这些配置文件放在CLASSPATH最前面覆盖掉当前EMR集群hadoop_conf_dir下的配置，以便bulkload程序能识别到云HBASE HA的HDFS URL。

3. 执行BULKLOAD命令

yarn &＃8211;config ${CONF_DIR} jar ${PHOENIX_HOME}/phoenix-${version}-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool &＃8211;table &＃8220;TABLENAME&＃8221; &＃8211;input &＃8220;hdfs://emr-header-1.cluster-55090:9000/tmp/test_data&＃8221;  &＃8211;zookeeper &＃8220;zk1,zk2,zk3&＃8221; &＃8211;output &＃8220;hdfs://emr-cluster/tmp/tmp_data&＃8221;

注意: &＃8211;output 配置的是云HBASE的临时文件，这样直接把生成的HFILE存储在云HBASE的HDFS上，后续的只有简单的move操作。否则，如果生成在EMR集群还需要走网络发送到云HBASE HDFS上。

四.参考

how to access a remote ha-enabled hdfs in a (oozie) distcp action

添加好友，回复： HBase 加群

2. 钉钉群：

钉钉群