Phoenix 入门基础

最新推荐文章于 2022-06-02 16:47:08 发布

XK&RM

最新推荐文章于 2022-06-02 16:47:08 发布

阅读量642

点赞数

分类专栏： Phoenix 文章标签： phoenix 大数据

本文链接：https://blog.csdn.net/qq_41301707/article/details/113756994

版权

Phoenix 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Phoenix是构建在HBase上的一个SQL层，能让我们用标准的JDBC APIs而不是HBase客户端APIs来创建表，插入数据和对HBase数据进行查询。Phoenix完全使用Java编写，作为HBase内嵌的JDBC驱动。Phoenix查询引擎会将SQL查询转换为一个或多个HBase扫描，并编排执行以生成标准的JDBC结果集。

1. 部署 Phoenix

本次部署 Phoenix 使用的是 cdh 5.16.2 系列

1.1 部署前提

需要部署 HBase

1.2 下载并解压 Phoenix

下载的是 CDH 5.14.2 系列的 Phoenix ，与 CDH 5.16.2 系列的 HBase 是可以兼容的

下载地址

[hadoop@bigdata ~]$ cd software/
[hadoop@bigdata software]$ wget http://www.apache.org/dyn/closer.lua/phoenix/apache-phoenix-4.14.0-cdh5.14.2/bin/apache-phoenix-4.14.0-cdh5.14.2-bin.tar.gz
[hadoop@bigdata software]$ tar -xzvf apache-phoenix-4.14.0-cdh5.14.2-bin.tar.gz -C ~/app/
[hadoop@bigdata software]$ cd ~/app/
[hadoop@bigdata app]$ ln -s apache-phoenix-4.14.0-cdh5.14.2-bin phoenix
[hadoop@bigdata app]$ cd phoenix/

1.3 重新部署 HBase

停止 HBase，删除 HBase 在 HDFS 上面的数据

[hadoop@bigdata phoenix]$ hdfs dfs -rmr /hbase
rmr: DEPRECATED: Please use 'rm -r' instead.
21/02/08 13:23:48 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Deleted /hbase

删除 ZK 上面的 HBase 元数据信息

[hadoop@bigdata phoenix]$ zkCli.sh 
[zk: localhost:2181(CONNECTED) 1] ls /hbase
[replication, meta-region-server, rs, splitWAL, backup-masters, table-lock, flush-table-proc, master-maintenance, region-in-transition, online-snapshot, switch, master, running, recovering-regions, draining, namespace, hbaseid, table]
[zk: localhost:2181(CONNECTED) 2] rmr /hbase
[zk: localhost:2181(CONNECTED) 3] ls /
[cluster, controller_epoch, brokers, zookeeper, kafka, admin, isr_change_notification, consumers, log_dir_event_notification, latest_producer_id_block, config]

修改 HBase 配置文件并启动

[hadoop@bigdata phoenix]$ cd ~
[hadoop@bigdata ~]$ cd app/hbase
[hadoop@bigdata hbase]$ vim conf/hbase-site.xml 

<property>
        <name>hbase.table.sanity.checks</name>
        <value>false</value>
</property>
<property>
        <name>hbase.regionserver.wal.codec</name>
        <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
<property>
        <name>phoenix.schema.isNamespaceMappingEnabled</name>
        <value>true</value>
</property>
<property>
        <name>phoenix.schema.mapSystemTablesToNamespace</name>
        <value>true</value>
</property>

增加 Phoenix 里面的 jar

[hadoop@bigdata hbase]$ cd ~/app/phoenix/
[hadoop@bigdata phoenix]$ cp phoenix-4.14.0-cdh5.14.2-server.jar ~/app/hbase/lib/

重启 HBase

[hadoop@bigdata phoenix]$ start-hbase.sh
[hadoop@bigdata phoenix]$ hbase shell
hbase(main):001:0> list
TABLE                                                                                                                                                                                                                                            
0 row(s) in 0.1220 seconds

=> []

说明没有问题

1.4 启动 PHoenix

修改 Phoenix 里面 hbase-site.xml文件

[hadoop@bigdata phoenix]$ cd ~/app/phoenix/
[hadoop@bigdata phoenix]$ cd bin
[hadoop@bigdata bin]$ rm -rf hbase-site.xml 
[hadoop@bigdata bin]$ ln -s /home/hadoop/app/hbase/conf/hbase-site.xml hbase-site.xml

启动 Phoenix

[hadoop@bigdata bin]$ ./sqlline.py bigdata:2181
0: jdbc:phoenix:bigdata:2181>

2. Phoenix 数据类型

数据类型（官网）

常用的数据类型有：

INTEGER
BIGINT
FLOAT
DECIMAL
BOOLEAN
TIMESTAMP
VARCHAR

对标 Mysql 数据库：
- Mysql 中的 int 改成了 INTEGER
- Mysql 中的日期类型可以在 Phoenix 中统一使用 TIMESTAMP
- Mysql 中的 char、varchar 在 Phoenix 中统一用 VARCHAR

3. Phoenix 常用语法

基础语法

3.1 创建一个 DataBase

官方案例

Example:

CREATE SCHEMA IF NOT EXISTS my_schema
CREATE SCHEMA my_schema

0: jdbc:phoenix:bigdata:2181> CREATE SCHEMA bigdata;
No rows affected (0.021 seconds)

查看 HBase 里面的数据

hbase(main):003:0> list_namespace
NAMESPACE
BIGDATA
SYSTEM
default
hbase
4 row(s) in 0.0240 seconds

3.2 创建表

官方案例

Example:

CREATE TABLE my_schema.my_table ( id BIGINT not null primary key, date Date)
CREATE TABLE my_table ( id INTEGER not null primary key desc, date DATE not null,
    m.db_utilization DECIMAL, i.db_utilization)
    m.DATA_BLOCK_ENCODING='DIFF'
CREATE TABLE stats.prod_metrics ( host char(50) not null, created_date date not null,
    txn_count bigint CONSTRAINT pk PRIMARY KEY (host, created_date) )
CREATE TABLE IF NOT EXISTS "my_case_sensitive_table"
    ( "id" char(10) not null primary key, "value" integer)
    DATA_BLOCK_ENCODING='NONE',VERSIONS=5,MAX_FILESIZE=2000000 split on (?, ?, ?)
CREATE TABLE IF NOT EXISTS my_schema.my_table (
    org_id CHAR(15), entity_id CHAR(15), payload binary(1000),
    CONSTRAINT pk PRIMARY KEY (org_id, entity_id) )
    TTL=86400

!tables：查看当前所有的表

0: jdbc:phoenix:bigdata:2181> CREATE TABLE bigdata.test ( id BIGINT not null primary key, name varchar)
. . . . . . . . . . . . . . > ;
No rows affected (2.26 seconds)

0: jdbc:phoenix:bigdata:2181> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+--------------+
| TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATION  | INDEX_STATE  | IMMUTABLE_ROWS  | SALT_BUCKETS  | MULTI_TENANT  | VIEW_STATEMENT  | VIEW_TYPE  | INDEX_TYPE   |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+--------------+
|            | SYSTEM       | CATALOG     | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
|            | SYSTEM       | FUNCTION    | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
|            | SYSTEM       | LOG         | SYSTEM TABLE  |          |            |                            |                 |              | true            | 32            | false         |                 |            |              |
|            | SYSTEM       | SEQUENCE    | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
|            | SYSTEM       | STATS       | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
|            | BIGDATA      | TEST        | TABLE         |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+--------------+

Phoenix 里面指定了 primary key 对应了 HBase 中的 RowKey
查看 HBase 中 bigdata namespace 下面的所有表

hbase(main):005:0> list_namespace_tables 'BIGDATA'
TABLE

TEST

1 row(s) in 0.0090 seconds

3.3 插入数据

官方案例

Example:

UPSERT INTO TEST VALUES('foo','bar',3);
UPSERT INTO TEST(NAME,ID) VALUES('foo',123);
UPSERT INTO TEST(ID, COUNTER) VALUES(123, 0) ON DUPLICATE KEY UPDATE COUNTER = COUNTER + 1;
UPSERT INTO TEST(ID, MY_COL) VALUES(123, 0) ON DUPLICATE KEY IGNORE;

Example:

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

0: jdbc:phoenix:bigdata:2181> upsert into bigdata.test values (1,'hadoop');
1 row affected (0.012 seconds)

查看 HBase 中的数据

hbase(main):007:0> scan 'BIGDATA:TEST'
ROW                                                           COLUMN+CELL
          
 \x80\x00\x00\x00\x00\x00\x00\x01                             column=0:\x00\x00\x00\x00, timestamp=1612765603313, value=x
 \x80\x00\x00\x00\x00\x00\x00\x01                             column=0:\x80\x0B, timestamp=1612765603313, value=hadoop

3.4 查询数据

官方案例

Example:

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

0: jdbc:phoenix:bigdata:2181> select * from bigdata.test;
+-----+---------+
| ID  |  NAME   |
+-----+---------+
| 1   | hadoop  |
+-----+---------+
1 row selected (0.026 seconds)

3.5 删除数据

官方案例

Example:

DELETE FROM TEST;
DELETE FROM TEST WHERE ID=123;
DELETE FROM TEST WHERE NAME LIKE 'foo%';

0: jdbc:phoenix:bigdata:2181> delete from bigdata.test;
1 row affected (0.009 seconds)
0: jdbc:phoenix:bigdata:2181> select * from bigdata.test;
+-----+-------+
| ID  | NAME  |
+-----+-------+
+-----+-------+
No rows selected (0.015 seconds)

查看 HBase 里面的数据

hbase(main):008:0> scan 'BIGDATA:TEST'
ROW                                                           COLUMN+CELL
0 row(s) in 0.0130 seconds

3.6 删除表

官方案例

Example:

DROP TABLE my_schema.my_table;
DROP TABLE IF EXISTS my_table;
DROP TABLE my_schema.my_table CASCADE;

0: jdbc:phoenix:bigdata:2181> DROP TABLE bigdata.test;
No rows affected (3.469 seconds)
0: jdbc:phoenix:bigdata:2181> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+--------------+
| TABLE_CAT  | TABLE_SCHEM  | TABLE_NAME  |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATION  | INDEX_STATE  | IMMUTABLE_ROWS  | SALT_BUCKETS  | MULTI_TENANT  | VIEW_STATEMENT  | VIEW_TYPE  | INDEX_TYPE   |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+--------------+
|            | SYSTEM       | CATALOG     | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
|            | SYSTEM       | FUNCTION    | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
|            | SYSTEM       | LOG         | SYSTEM TABLE  |          |            |                            |                 |              | true            | 32            | false         |                 |            |              |
|            | SYSTEM       | SEQUENCE    | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
|            | SYSTEM       | STATS       | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false         |                 |            |              |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+--------------+

查看 HBase bigdata namespace 下面的表

hbase(main):009:0> list_namespace_tables 'BIGDATA'
TABLE

0 row(s) in 0.0090 seconds

Phoenix 入门基础

目录

1. 部署 Phoenix

1.1 部署前提

1.2 下载并解压 Phoenix

1.3 重新部署 HBase

1.4 启动 PHoenix

2. Phoenix 数据类型

3. Phoenix 常用语法

3.1 创建一个 DataBase

3.2 创建表

3.3 插入数据

3.4 查询数据

3.5 删除数据

3.6 删除表