Phoenix 实现 SQL On HBase

友情提示：Ubuntu 14.04 下，HBase 单机伪分布式部署，不使用 HBase 自带的 Zookeeper

HBase 是基于 Hadoop 的分布式数据库，既然是数据库，增删改查是必备功能。但不知是何缘故，HBase 提供了一套自己的查询语言（HBase shell 常用命令），而不是类SQL。这无形中增加了 HBase 的使用成本。那么问题来了，可否使用 类SQL 语句操作 HBase 呢？

下载 Phoenix

Phoenix Downloads
去上述链接下载特定的版本，笔者选择的是 apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz 和 apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz.md5 。
校验下载的压缩文件

blockchain@Dao:~/Downloads$ 
blockchain@Dao:~/Downloads$ md5sum apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz
82996e0b3979e57a46e25e3c36a2ee98  apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz
blockchain@Dao:~/Downloads$ 
blockchain@Dao:~/Downloads$ cat apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz.md5 
82996e0b3979e57a46e25e3c36a2ee98 *apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz

校验通过。解压该压缩文件，

blockchain@Dao:~/Downloads$ tar -zxvf apache-phoenix-4.14.0-HBase-1.4-bin.tar.gz -C ~

配置

• 将 phoenix-4.14.0-HBase-1.4-client.jar 和 phoenix-core-4.14.0-HBase-1.4.jar 两个jar包拷贝至Hbase的 lib 文件夹

blockchain@Dao:~/apache-phoenix-4.14.0-HBase-1.4$ cp phoenix-4.14.0-HBase-1.4-client.jar ~/hbase-1.4.6/lib/
blockchain@Dao:~/apache-phoenix-4.14.0-HBase-1.4$ 
blockchain@Dao:~/apache-phoenix-4.14.0-HBase-1.4$ cp phoenix-core-4.14.0-HBase-1.4.jar ~/hbase-1.4.6/lib/
blockchain@Dao:~/apache-phoenix-4.14.0-HBase-1.4$ 

• 将Hbase的配置文件 hbase-site.xml 拷贝至 Phoenix根目录中的 bin 文件夹

blockchain@Dao:~/hbase-1.4.6$ 
blockchain@Dao:~/hbase-1.4.6$ cp conf/hbase-site.xml ~/apache-phoenix-4.14.0-HBase-1.4/bin/
blockchain@Dao:~/hbase-1.4.6$ 

至此，配置完成。

启动

• 启动HDFS
• 启动YARN
• 启动HBase
• 启动Phoenix

切换至 apache-phoenix-4.14.0-HBase-1.4 的 bin 目录

blockchain@Dao:~/apache-phoenix-4.14.0-HBase-1.4/bin$ ./sqlline.py localhost:2181
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:localhost:2181 none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:localhost:2181
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/blockchain/apache-phoenix-4.14.0-HBase-1.4/phoenix-4.14.0-HBase-1.4-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/blockchain/hadoop-2.6.5/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
18/09/04 10:44:35 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Connected to: Phoenix (version 4.14)
Driver: PhoenixEmbeddedDriver (version 4.14)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
133/133 (100%) Done
Done
sqlline version 1.2.0
0: jdbc:phoenix:localhost:2181> 

命令行测试

• 创建表

0: jdbc:phoenix:localhost:2181> 
0: jdbc:phoenix:localhost:2181> create table PhoenixTest (pk integer not null primary key, col1 varchar);
No rows affected (2.374 seconds)
0: jdbc:phoenix:localhost:2181> 

• ​增加，修改表数据

0: jdbc:phoenix:localhost:2181> 
0: jdbc:phoenix:localhost:2181> upsert into PhoenixTest values (1,'Hello');
1 row affected (0.047 seconds)
0: jdbc:phoenix:localhost:2181> 
0: jdbc:phoenix:localhost:2181> upsert into PhoenixTest values (2,'World');
1 row affected (0.009 seconds)
0: jdbc:phoenix:localhost:2181> 
0: jdbc:phoenix:localhost:2181> upsert into PhoenixTest values (3,'HBase');
1 row affected (0.005 seconds)
0: jdbc:phoenix:localhost:2181> 
0: jdbc:phoenix:localhost:2181> upsert into PhoenixTest values (4,'Phoenix');
1 row affected (0.005 seconds)
0: jdbc:phoenix:localhost:2181> 

• 查询表数据

0: jdbc:phoenix:localhost:2181> select * from PhoenixTest;
+-----+----------+
| PK  |   COL1   |
+-----+----------+
| 1   | Hello    |
| 2   | World    |
| 3   | HBase    |
| 4   | Phoenix  |
+-----+----------+
4 rows selected (0.044 seconds)
0: jdbc:phoenix:localhost:2181> 

• 删除某一行

0: jdbc:phoenix:localhost:2181> upsert into PhoenixTest values (0, 'hehe');
1 row affected (0.017 seconds)
0: jdbc:phoenix:localhost:2181> 
0: jdbc:phoenix:localhost:2181> select * from PhoenixTest;
+-----+----------+
| PK  |   COL1   |
+-----+----------+
| 0   | hehe     |
| 1   | Hello    |
| 2   | World    |
| 3   | HBase    |
| 4   | Phoenix  |
+-----+----------+
5 rows selected (0.018 seconds)
0: jdbc:phoenix:localhost:2181> 
0: jdbc:phoenix:localhost:2181> delete from PhoenixTest where pk = 0 ;
1 row affected (0.01 seconds)
0: jdbc:phoenix:localhost:2181> select * from PhoenixTest;
+-----+----------+
| PK  |   COL1   |
+-----+----------+
| 1   | Hello    |
| 2   | World    |
| 3   | HBase    |
| 4   | Phoenix  |
+-----+----------+
4 rows selected (0.019 seconds)
0: jdbc:phoenix:localhost:2181> 

• 查看表

0: jdbc:phoenix:localhost:2181> !tables
+------------+--------------+--------------+---------------+----------+------------+-----------------+
| TABLE_CAT  | TABLE_SCHEM  |  TABLE_NAME  |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCIN |
+------------+--------------+--------------+---------------+----------+------------+-----------------+
|            | SYSTEM       | CATALOG      | SYSTEM TABLE  |          |            |                 |
|            | SYSTEM       | FUNCTION     | SYSTEM TABLE  |          |            |                 |
|            | SYSTEM       | LOG          | SYSTEM TABLE  |          |            |                 |
|            | SYSTEM       | SEQUENCE     | SYSTEM TABLE  |          |            |                 |
|            | SYSTEM       | STATS        | SYSTEM TABLE  |          |            |                 |
|            |              | PHOENIXTEST  | TABLE         |          |            |                 |
+------------+--------------+--------------+---------------+----------+------------+-----------------+

• 删除表

0: jdbc:phoenix:localhost:2181> drop table PhoenixTest ;
No rows affected (3.818 seconds)
0: jdbc:phoenix:localhost:2181> !tables
+------------+--------------+-------------+---------------+----------+------------+------------------+
| TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCING |
+------------+--------------+-------------+---------------+----------+------------+------------------+
|            | SYSTEM       | CATALOG     | SYSTEM TABLE  |          |            |                  |
|            | SYSTEM       | FUNCTION    | SYSTEM TABLE  |          |            |                  |
|            | SYSTEM       | LOG         | SYSTEM TABLE  |          |            |                  |
|            | SYSTEM       | SEQUENCE    | SYSTEM TABLE  |          |            |                  |
|            | SYSTEM       | STATS       | SYSTEM TABLE  |          |            |                  |
+------------+--------------+-------------+---------------+----------+------------+------------------+
0: jdbc:phoenix:localhost:2181> 

• 退出命令行

0: jdbc:phoenix:localhost:2181> !quit
Closing: org.apache.phoenix.jdbc.PhoenixConnection
blockchain@Dao:~/apache-phoenix-4.14.0-HBase-1.4/bin$ 

http://phoenix.apache.org/installation.html

HBase Phoenix助力海量数据实时分析

使用 Phoenix-4.11.0连接 Hbase 集群 ,并使用 JDBC 查询测试