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

迈不过友情╰ 2023-10-05 11:53 36阅读 0赞

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

#### zlp ####

*   *  Phoenix集成
     *   *  安装
         *  基本使用

### 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目标版本保持一致。

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mXgCoFzR-1602993674154)(assets/image-20201013103324186.png)\]][img-mXgCoFzR-1602993674154_assets_image-20201013103324186.png]

#### 安装 ####

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

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系列完！-----**

[HBase_HBase HA]: https://blog.csdn.net/qq_44664231/article/details/109142996
[apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz]: http://archive.apache.org/dist/phoenix/apache-phoenix-4.10.0-HBase-1.2/bin/apache-phoenix-4.10.0-HBase-1.2-bin.tar.gz
[img-mXgCoFzR-1602993674154_assets_image-20201013103324186.png]: https://img-blog.csdnimg.cn/20201018120320397.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjY0MjMx,size_16,color_FFFFFF,t_70#pic_center