1.Phoenix 简介
- 官网
在这里插入代码片
官网： http://phoenix.apache.org/Phoenix-in-15-minutes-or-less.html
源码： https://github.com/phoenixframework/phoenix
2. 特点：
优点：
1. 编译SQL查询为原生HBase的scan语句
2. 检测scan语句最佳的开始和结束的key（确定扫描 Rowkey 的最佳开始和结束位置）
3. 精心编排你的scan语句让他们并行执行
4. 让计算去接近数据
5. 推送你的WHERE子句的谓词到服务端过滤器处理
6. 执行聚合查询通过服务端钩子（称为协同处理器）
7. 完美支持 HBase 二级索引创建
8. DML命令以及通过DDL命令创建和操作表和版本化增量更改。
9. 容易集成：如Spark，Hive，Pig，Flume和Map Reduce
缺点：
缺点：
1.不支持事务处理
2.不支持复杂的条件
3.架构
在这里插入图片描述
4.安装省略， 因为ambari 中已经自动部署了Phoenix
在这里插入图片描述
5. 启动命令：
/usr/hdp/current/phoenix-client/bin/sqlline.py master01.pxx.com:2181/hbase-unsecure
6.Phoenix 的常用命令
- 可以使用` !table `查看表信息
- 使用 !describe tablename 可以查看表字段信息
- 使用 !history可以查看执行的历史SQL
- 使用 !dbinfo 可以查看Phoenix所有的属性配置
- 使用 !help 可以查看Phoenix所有的属性配置
- 使用!indexes tablename 列出指定表名的所有索引
比如：
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> !table
+------------+---------------+------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+---+
| 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 | V |
+------------+---------------+------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+---+
| | 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 | |
| | | US_POPULATION | TABLE | | | | | | false | null | false | |
| | TEST_PHOENIX | STUDENTS | TABLE | | | | | | false | null | false | |
| | wudl | wutable | TABLE | | | | | | false | null | false | |
+------------+---------------+------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+---+
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u>
创建表语句
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> CREATE TABLE IF NOT EXISTS wudl_table (
. . . . . . . . . . . . . . . . . . . . . . .> id bigint primary key,
. . . . . . . . . . . . . . . . . . . . . . .> name VARCHAR ,
. . . . . . . . . . . . . . . . . . . . . . .> address varchar
. . . . . . . . . . . . . . . . . . . . . . .> );
No rows affected (1.38 seconds)
插入数据命令：
upsert into wudl_table values(0001,'flink','深圳');
upsert into wudl_table values(0002,'spark','上海');
查询数据命令：
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> select * from wudl_table;
+-----+--------+----------+
| ID | NAME | ADDRESS |
+-----+--------+----------+
| 1 | flink | 深圳 |
| 2 | spark | 上海 |
+-----+--------+----------+
2 rows selected (0.035 seconds)
删除表命令：
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> DELETE FROM wudl_table WHERE address = '上海';
1 row affected (0.026 seconds)
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> select * from wudl_table;
+-----+--------+----------+
| ID | NAME | ADDRESS |
+-----+--------+----------+
| 1 | flink | 深圳 |
+-----+--------+----------+
1 row selected (0.027 seconds)
删除表
DELETE FROM wudl_table;
更新表数据
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> UPSERT INTO wudl_table (id,name,address) VALUES(0001,'flink','深圳上海');
1 row affected (0.023 seconds)
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> select * from wudl_table;
+-----+--------+----------+
| ID | NAME | ADDRESS |
+-----+--------+----------+
| 1 | flink | 深圳上海 |
+-----+--------+----------+
1 row selected (0.045 seconds)
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u>
查看表的描述：
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> !describe wudl_table;
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+---------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | BUFFER_LENGTH | DECIMAL_DIGITS | NUM_PREC_RADIX | NULLABLE | REMARKS | COLUMN_DEF | SQL_DATA_TYPE |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+---------------+
| | | WUDL_TABLE | ID | -5 | BIGINT | null | null | null | null | 0 | | | null |
| | | WUDL_TABLE | NAME | 12 | VARCHAR | null | null | null | null | 1 | | | null |
| | | WUDL_TABLE | ADDRESS | 12 | VARCHAR | null | null | null | null | 1 | | | null |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+---------------+
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u>
查看历史命令：
jdbc:phoenix:master01.pxx.com:2181/hbase-u> !history
179. 177: CREATE TABLE IF NOT EXISTS wudl_table (
180. 178: id bigint primary key,
181. 179: name VARCHAR ,
182. 180: address varchar
183. 181: );
184. 182: upsert into wudl_table values(0001,'flink','深圳');
185. 183: upsert into wudl_table values(0002,'spark','上海');
186. 184: select * from wudl_table;
187. 185: DELETE FROM wudl_table WHERE name = '上海';
188. 186: select * from wudl_table;
189. 187: DELETE FROM wudl_table WHERE NAME = '上海';
190. 188: select * from wudl_table;
191. 189: DELETE FROM wudl_table WHERE address = '上海';
192. 190: select * from wudl_table;
193. 191: !describe wudl_table;
194. 192: !history
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u>
!dbinfo 可以查看Phoenix所有的属性配置：
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> !dbinfo
allProceduresAreCallable false
allTablesAreSelectable true
dataDefinitionCausesTransactionCommit false
dataDefinitionIgnoredInTransactions false
doesMaxRowSizeIncludeBlobs false
getCatalogSeparator .
getCatalogTerm Tenant
getDatabaseProductName Phoenix
getDatabaseProductVersion 5.0
查看表的索引：
!indexes wudl_table;
更多的命令：
+------------+--------------+-------------+-------------+------------------+-------------+-------+-------------------+--------------+--------------+--------------+--------+-------------------+------------+
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> !help
!all Execute the specified SQL against all the current
connections
!autocommit Set autocommit mode on or off
!batch Start or execute a batch of statements
!brief Set verbose mode off
!call Execute a callable statement
!close Close the current connection to the database
!closeall Close all current open connections
!columns List all the columns for the specified table
!commit Commit the current transaction (if autocommit is off)
!connect Open a new connection to the database.
!dbinfo Give metadata information about the database
!describe Describe a table
!dropall Drop all tables in the current database
!exportedkeys List all the exported keys for the specified table
!go Select the current connection
!help Print a summary of command usage
!history Display the command history
!importedkeys List all the imported keys for the specified table
!indexes List all the indexes for the specified table
!isolation Set the transaction isolation for this connection
!list List the current connections
...............................
7.重点---- Hbase 映射到phoenx ----
7.1 需要注意的点：
1. sql语句需要分号“；”结束。
2. 带!号开始的命令可以不使用分号结束
3. 表名小写的phoenix会自动转大写，小写可以使用"table_name"即双引号引起来即可是小写
4. 通过Phoenix建的表都会自动转成大写，如果需要使用小写的表，请使用`create table "tablename"。
7.2 需要配置映射文件
这里一定要注意：如果设置为true，创建的带有schema的表将映射到一个namespace，这个需要客户
端和服务端同时设置。一旦设置为true，就不能回滚了。旧的客户端将无法再正常工作。所以建议大家
都查看官方文档，确定后再进行设置
<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>
<property>
<name>phoenix.schema.mapSystemTablesToNamespace</name>
<value>true</value>
</property>
7.2.1如果是ambari 需要在添加 这两个属性
在这里插入图片描述
7.2.2 启动如果出现错误：
如果报以下错误提示
Traceback (most recent call last):
File "./sqlline.py", line 27, in
import argparse
ImportError: No module named argparse
解决办法：
在安装phoenix的服务器上安装该模块。
yum -y install python-argparse
7.3 视图的映射：
默认情况下, 直接在 HBase 中创建的表通过 Phoenix 是查不到的.
7.3.1hbase 创建命名空间类似于mysql 的数据库
hbase(main):002:0> create_namespace 'wudl002'
Took 0.6196 seconds
hbase(main):003:0> create 'wudl002:wudl002',{NAME=>'cf',BLOCKCACHE=>true,BLOOMFILTER=>'ROW', BLOCKSIZE => '65536'}
Created table wudl002:wudl002
Took 2.6594 seconds
=> Hbase::Table - wudl002:wudl002
7.3.2插入数据：
hbase(main):004:0> put 'wudl002:wudl002','1DE5555520201231155811','cf:id','17560'
Took 1.0791 seconds
hbase(main):005:0> put 'wudl002:wudl002','1DE5555520201231155811','cf:creator','15088888888'
Took 0.0158 seconds
hbase(main):006:0> put 'wudl002:wudl002','1DE5555520201231155811','cf:creator_id','10201000064'
Took 0.0097 seconds
hbase(main):007:0> put 'wudl002:wudl002','1DE5555520201231155811','cf:create_time','2021-01-04 10:45:58'
Took 0.0099 seconds
hbase(main):008:0> put 'wudl002:wudl002','1DE5555520201231155811','cf:updater','15088888888'
Took 0.0650 seconds
hbase(main):009:0> put 'wudl002:wudl002','1DE5555520201231155811','cf:updater_id','10201000064'
Took 0.0698 seconds
7.3.3查询数据
hbase(main):010:0> scan 'wudl002:wudl002'
ROW COLUMN+CELL
1DE5555520201231155811 column=cf:create_time, timestamp=1614909429189, value=2021-01-04 10:45:58
1DE5555520201231155811 column=cf:creator, timestamp=1614909429093, value=15088888888
1DE5555520201231155811 column=cf:creator_id, timestamp=1614909429147, value=10201000064
1DE5555520201231155811 column=cf:id, timestamp=1614909428803, value=17560
1DE5555520201231155811 column=cf:updater, timestamp=1614909429327, value=15088888888
1DE5555520201231155811 column=cf:updater_id, timestamp=1614909430434, value=10201000064
1 row(s)
Took 0.2956 seconds
7.4 在phoenix
7.4.1创建命名空间：
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> CREATE SCHEMA IF NOT EXISTS "wudl002";
No rows affected (0.19 seconds)
7.4.2 创建表：
特别注意 创建表 是类型最好用verchar 类型 还有结尾要用column_encoded_bytes = 0; 不然映射不到
CREATE SCHEMA IF NOT EXISTS "wudl002";
CREATE TABLE "wudl002"."wudl002" (
"ROW" VARCHAR PRIMARY KEY,
"cf"."id" VARCHAR,
"cf"."creator" VARCHAR,
"cf"."creator_id" VARCHAR,
"cf"."create_time" VARCHAR,
"cf"."updater" VARCHAR,
"cf"."updater_id" VARCHAR
) column_encoded_bytes = 0;
7.4.3查看结果：
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u> select * from "wudl002"."wudl002";
+-------------------------+--------+--------------+--------------+----------------------+--------------+--------------+
| ROW | id | creator | creator_id | create_time | updater | updater_id |
+-------------------------+--------+--------------+--------------+----------------------+--------------+--------------+
| 1DE5555520201231155811 | 17560 | 15088888888 | 10201000064 | 2021-01-04 10:45:58 | 15088888888 | 10201000064 |
+-------------------------+--------+--------------+--------------+----------------------+--------------+--------------+
1 row selected (0.367 seconds)
0: jdbc:phoenix:master01.pxx.com:2181/hbase-u>
完成****
