【HBase】HBase系列之HBase集成Phoenix及简单使用

上一篇：HBase系列之HBase HA集群搭建

Phoenix集成

Phoenix是构建在HBase上的一个SQL层，能让我们用标准的JDBC API而不是HBase客户端API来创建表，插入数据和对HBase数据进行查询。Phoenix完全使用Java编写，作为HBase内嵌的JDBC驱动。Phoenix查询引擎会将SQL查询转换为一个或多个HBase扫描，并编排执行以生成标准的JDBC结果集。下载apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz,注意下载的Phoenix版本必须和hbase目标版本保持一致。

安装

以下环境基于单机环境搭建，即单台机器搭建

1、确保HDFS/HBase正常运行

# 启动HDFS服务
[root@CentOS ~]# start-dfs.sh
# 启动HBase服务
[root@CentOS ~]# start-hbase.sh

2、解压Phoenix的安装包，将phoenix-[version]-server.jar和phoenix-[version]-client.jar拷贝到所有运行HBase的节点的lib目录下

[root@CentOS ~]# tar -zxf apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz -C /usr/
[root@CentOS ~]# mv  /usr/apache-phoenix-4.10.0-HBase-1.2-bin/ /usr/phoenix-4.10.0
[root@CentOS phoenix-4.10.0]# cp phoenix-4.10.0-HBase-1.2-client.jar /usr/hbase-1.2.4/lib/
[root@CentOS phoenix-4.10.0]# cp phoenix-4.10.0-HBase-1.2-server.jar /usr/hbase-1.2.4/lib/
[root@CentOS phoenix-4.10.0]#

3、为保证Phoenix能够正常启动，建议将HBase的数据清空后再启动HBase

[root@CentOS ~]# hbase clean --cleanAll
[root@CentOS ~]# rm -rf /usr/hbase-1.2.4/logs/*
[root@CentOS ~]# start-hbase.sh

4、通过sqlline.py链接Hbase

[root@CentOS phoenix-4.10.0]# ./bin/sqlline.py CentOS
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:CentOS none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:CentOS
....
Connected to: Phoenix (version 4.10)
Driver: PhoenixEmbeddedDriver (version 4.10)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
91/91 (100%) Done
Done
sqlline version 1.2.0
0: jdbc:phoenix:CentOS>

5、退出交互窗口

0: jdbc:phoenix:CentOS> !quit

基本使用

1、查看所有表

0: jdbc:phoenix:CentOS> !tables

2、创建表

0: jdbc:phoenix:CentOS> create table t_user(
. . . . . . . . . . . >   id integer primary key,
. . . . . . . . . . . >   name varchar(32),
. . . . . . . . . . . >   age integer,
. . . . . . . . . . . >   sex boolean
. . . . . . . . . . . > );
No rows affected (1.348 seconds)

3、查看表的字段信息

0: jdbc:phoenix:CentOS> !column t_user

4、插入/更新数据

0: jdbc:phoenix:CentOS> upsert into t_user values(1,'jiangzz',18,false);
1 row affected (0.057 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(1,'jiangzz',18,true);
1 row affected (0.006 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(2,'lisi',20,true);
1 row affected (0.023 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user values(3,'wangwu',18,false);
1 row affected (0.006 seconds)

0: jdbc:phoenix:CentOS> select * from t_user;
+-----+----------+------+--------+
| ID  |   NAME   | AGE  |  SEX   |
+-----+----------+------+--------+
| 1   | jiangzz  | 18   | true   |
| 2   | lisi     | 20   | true   |
| 3   | wangwu   | 18   | false  |
+-----+----------+------+--------+
3 rows selected (0.085 seconds)

5、更改某个字段值

0: jdbc:phoenix:CentOS> upsert into  t_user(id,name) values(1,'win7');
1 row affected (0.024 seconds)

0: jdbc:phoenix:CentOS> select * from t_user;
+-----+---------+------+--------+
| ID  |  NAME   | AGE  |  SEX   |
+-----+---------+------+--------+
| 1   | win7    | 18   | true   |
| 2   | lisi    | 20   | true   |
| 3   | wangwu  | 18   | false  |
+-----+---------+------+--------+
3 rows selected (0.201 seconds)

6、执行某些统计操作

0: jdbc:phoenix:CentOS> select sex,avg(age),max(age),min(age),sum(age) from t_user group by sex;
+--------+-----------+-----------+-----------+-----------+
|  SEX   | AVG(AGE)  | MAX(AGE)  | MIN(AGE)  | SUM(AGE)  |
+--------+-----------+-----------+-----------+-----------+
| false  | 18        | 18        | 18        | 18        |
| true   | 19        | 20        | 18        | 38        |
+--------+-----------+-----------+-----------+-----------+
2 rows selected (0.123 seconds)
0: jdbc:phoenix:CentOS> select sex,avg(age),max(age),min(age),sum(age) total from t_user group by sex order by total  desc;
+--------+-----------+-----------+-----------+--------+
|  SEX   | AVG(AGE)  | MAX(AGE)  | MIN(AGE)  | TOTAL  |
+--------+-----------+-----------+-----------+--------+
| true   | 19        | 20        | 18        | 38     |
| false  | 18        | 18        | 18        | 18     |
+--------+-----------+-----------+-----------+--------+
2 rows selected (0.072 seconds)
0: jdbc:phoenix:CentOS>

7、数据库操作

0: jdbc:phoenix:CentOS> create schema if not exists baizhi;

PS：必须同时修改HASE_HOME/conf/hbase-site.xml文件和 ​PHOENIX_HOME/bin/hbase-site.xml文件，修改完成重启Hbase服务

<property>
  <name>phoenix.schema.isNamespaceMappingEnabled</name>
  <value>true</value>
</property>
<property>
  <name>phoenix.schema.mapSystemTablesToNamespace</name>
  <value>true</value>
</property>

0: jdbc:phoenix:CentOS> create schema if not exists baizhi;
No rows affected (0.046 seconds)
0: jdbc:phoenix:CentOS> use baizhi;
No rows affected (0.049 seconds)
0: jdbc:phoenix:CentOS> create table if not exists t_user(
. . . . . . . . . . . >  id integer primary key ,
. . . . . . . . . . . >  name varchar(128),
. . . . . . . . . . . >  sex boolean,
. . . . . . . . . . . >  birthDay date,
. . . . . . . . . . . >  salary decimal(7,2)
. . . . . . . . . . . > );

如果用户不指定schema，默认使用的是default数据库

8、查看建表详情，等价!column

0: jdbc:phoenix:CentOS> !desc baizhi.t_user;

9、删除表

0: jdbc:phoenix:CentOS> drop table if exists baizhi.t_user;
No rows affected (3.638 seconds)

如果有其他表指向该表，我们可以在删除的表时候添加cascade关键字

0: jdbc:phoenix:CentOS> drop table if exists baizhi.t_user cascade;
No rows affected (0.004 seconds)

10、修改表

①添加字段

0: jdbc:phoenix:CentOS> alter table t_user add age integer;
No rows affected (5.994 seconds)

②删除字段

0: jdbc:phoenix:CentOS> alter table t_user drop column age;
No rows affected (1.059 seconds)

③设置表的TimeToLive

0: jdbc:phoenix:CentOS> alter table t_user set TTL=100;
No rows affected (5.907 seconds)
0: jdbc:phoenix:CentOS> upsert into t_user(id,name,sex,birthDay,salary) values(1,'jiangzz',true,'1990-12-16',5000.00);
1 row affected (0.031 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+----------+-------+--------------------------+---------+
| ID  |   NAME   |  SEX  |         BIRTHDAY         | SALARY  |
+-----+----------+-------+--------------------------+---------+
| 1   | jiangzz  | true  | 1990-12-16 00:00:00.000  | 5E+3    |
+-----+----------+-------+--------------------------+---------+
1 row selected (0.074 seconds)

11、数据DML

①插入&更新

0: jdbc:phoenix:CentOS> upsert into t_user(id,name,sex,birthDay,salary) values(1,'jiangzz',true,'1990-12-16',5000.00);
1 row affected (0.014 seconds)

②删除记录

0: jdbc:phoenix:CentOS> delete from t_user where name='jiangzz';
1 row affected (0.014 seconds)
0: jdbc:phoenix:CentOS> select * from t_user;
+-----+-------+------+-----------+---------+
| ID  | NAME  | SEX  | BIRTHDAY  | SALARY  |
+-----+-------+------+-----------+---------+
+-----+-------+------+-----------+---------+
No rows selected (0.094 seconds)

③查询数据

0: jdbc:phoenix:CentOS> select * from t_user;
+-----+-----------+--------+--------------------------+---------+
| ID  |   NAME    |  SEX   |         BIRTHDAY         | SALARY  |
+-----+-----------+--------+--------------------------+---------+
| 1   | jiangzz   | true   | 1990-12-16 00:00:00.000  | 5E+3    |
| 2   | zhangsan  | false  | 1990-12-16 00:00:00.000  | 6E+3    |
+-----+-----------+--------+--------------------------+---------+
2 rows selected (0.055 seconds)
0: jdbc:phoenix:CentOS> select * from t_user where name like '%an%' order by salary desc limit 10;
+-----+-----------+--------+--------------------------+---------+
| ID  |   NAME    |  SEX   |         BIRTHDAY         | SALARY  |
+-----+-----------+--------+--------------------------+---------+
| 2   | zhangsan  | false  | 1990-12-16 00:00:00.000  | 6E+3    |
| 1   | jiangzz   | true   | 1990-12-16 00:00:00.000  | 5E+3    |
+-----+-----------+--------+--------------------------+---------+
2 rows selected (0.136 seconds)

-----HBase系列完！-----