CDH集群：为hbase安装phoenix工具

妖狐艹你老母 2021-12-10 19:27 464阅读 0赞

### 文章目录 ###

*  1,下载cdh的phoenix安装包
 *  2, 快速入门
 *  3,　映射之前的hbase表
 *   *  a, 创建只读视图
     *  b, 创建可读写的hbae外表
 *  4, phoenix二级索引

# 1,下载cdh的phoenix安装包 #

*  [http://archive.cloudera.com/cloudera-labs/phoenix/parcels/latest/][http_archive.cloudera.com_cloudera-labs_phoenix_parcels_latest]
 *  移动parcel文件到cdh安装目录：　/opt/cloudera/parcel-repo/
 *  打开cloudera的管理7180 web管理界面, 选择parcels—> 检查新parcel: 分发, 激活
 *  重启hbase
 *  追加环境变量

echo 'export PATH=$PATH:/opt/cloudera/parcels/CLABS_PHOENIX/bin/' >> /etc/profile

*  测试使用:

source /etc/profile;   
    echo  ' !tables ' |  phoenix-sqlline.py localhost:2181
    ....
    #########打印如下：
    Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
    628/628 (100%) Done
    Done
    sqlline version 1.1.8
    0: jdbc:phoenix:localhost:2181> !tables
    +------------+--------------+----------------------------+---------------+----------+------------+----------------------------+--------------+
    | TABLE_CAT  | TABLE_SCHEM  |         TABLE_NAME         |  TABLE_TYPE   | REMARKS  | TYPE_NAME  | SELF_REFERENCING_COL_NAME  | REF_GENERATI |
    +------------+--------------+----------------------------+---------------+----------+------------+----------------------------+--------------+ 
    |            | SYSTEM       | CATALOG                    | SYSTEM TABLE  |          |            |                            |              |
    |            | SYSTEM       | FUNCTION                   | SYSTEM TABLE  |          |            |

# 2, 快速入门 #

*  phoenix-psql.py 执行sql脚本

#===========quick start =========
    # 1, 创建phoenix表, 执行sql统计　＝＝> csv数据 + 建表.sql + 统计.sql 
    phoenix-psql.py <your_zookeeper_quorum> us_population.createtab.sql us_population.csv us_population_queries.sql
    
    # 2, 创建phoenix表, 执行sql统计 ( 插入100000样例数据)
    [root@eadage t1]# phoenix-performance.py localhost
    #Usage: performance.sh <zookeeper> <row count>
    #Example: performance.sh localhost 100000
    [root@eadage t1]# phoenix-performance.py localhost 100000

# 3,　映射之前的hbase表 #

## a, 创建只读视图 ##

设置列族和列(cf:filed)

hbase(main):002:0> scan 't1'
    ROW                                  COLUMN+CELL                                                                                              
     r1                                  column=f:name, timestamp=1562681717484, value=a                                                          
     r9                                  column=f:name, timestamp=1562681717507, value=\xE4\xBD\xA0\xE5\xA5\xBD                                   
    2 row(s) in 0.1300 seconds
    
    #### 使用phoenix创建同名只读视图：映射hbase表
    0: jdbc:phoenix:localhost:2181> create view "t1"(pk varchar primary key, "f"."name" varchar);
    No rows affected (0.013 seconds)
    0: jdbc:phoenix:localhost:2181> select * from "t1";
    +-----+-------+
    | PK  | name  |
    +-----+-------+
    | r1  | a     |
    | r9  | 你好    |
    +-----+-------+
    2 rows selected (0.045 seconds)
    
    ### 视图中插入数据报错：　只读，不能修改数据
    0: jdbc:phoenix:localhost:2181> upsert into "t1" values('r2','b');
    Error: ERROR 505 (42000): Table is read only. (state=42000,code=505)
    org.apache.phoenix.schema.ReadOnlyTableException: ERROR 505 (42000): Table is read only.
    	at org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:296)

## b, 创建可读写的hbae外表 ##

#大小写敏感(使用""保留小写）：映射和hbaes同名的表test_ph，可写
    create table "test_ph" (
    rowkey varchar primary key, 
    "f"."name" varchar);

# 4, phoenix二级索引 #

*  基于表的索引，索引数据自动更新；基于视图的则不会
 *  若查询的所有字段都在索引范围，则从索引查找；否则从原表查找

#######################################################1, 普通表
    0: jdbc:phoenix:localhost> create index deployee_idx1 on deployee(id,name,dept_id);
    2 rows affected (7.486 seconds)
    
    #######################################################2, hbase表映射
    ##无索引
    
    0: jdbc:phoenix:localhost> select * from "hbase_view1";
    +---------+----------------------+-------------------+----------------------+-----------+---------------+
    | ROWKEY  |       end_time       |    patient_id     |      start_time      | trans_no  |   visit_id    |
    +---------+----------------------+-------------------+----------------------+-----------+---------------+
    | r1      | 2019-06-24 16:18:16  | patient_id_34546  | 2019-01-04 13:18:16  | 0         | visit_id_123  |
    | r2      | 2019-10-24 16:18:16  | patient_id_34546  | 2019-08-04 03:15:16  | 0         | visit_id_124  |
    +---------+----------------------+-------------------+----------------------+-----------+---------------+
    2 rows selected (0.059 seconds)
    
    0: jdbc:phoenix:localhost> explain select * from "hbase_view1";
    +----------------------------------------------------------------------+
    |                                 PLAN                                 |
    +----------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1  |
    +----------------------------------------------------------------------+
    1 row selected (0.042 seconds)
    
    ##有索引1(查询的字段都在索引表范围，则从索引表读数据；否则从原表读)
    0: jdbc:phoenix:localhost> create index "hbase_view1_idx1" on "hbase_view1"("f"."patient_id");
    2 rows affected (7.364 seconds)
    0: jdbc:phoenix:localhost> explain select "patient_id" from "hbase_view1";
    +---------------------------------------------------------------------------+
    |                                   PLAN                                    |
    +---------------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1_idx1  |
    |     SERVER FILTER BY FIRST KEY ONLY                                       |
    +---------------------------------------------------------------------------+
    2 rows selected (0.015 seconds)
    0: jdbc:phoenix:localhost> explain select "visit_id" from "hbase_view1";
    +----------------------------------------------------------------------+
    |                                 PLAN                                 |
    +----------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1  |
    +----------------------------------------------------------------------+
    1 row selected (0.012 seconds)
    
    0: jdbc:phoenix:localhost> explain select "visit_id","patient_id" from "hbase_view1";
    +----------------------------------------------------------------------+
    |                                 PLAN                                 |
    +----------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1  |
    +----------------------------------------------------------------------+
    1 row selected (0.013 seconds)
    
    0: jdbc:phoenix:localhost> explain select "ROWKEY","patient_id" from "hbase_view1";
    +---------------------------------------------------------------------------+
    |                                   PLAN                                    |
    +---------------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1_idx1  |
    |     SERVER FILTER BY FIRST KEY ONLY                                       |
    +---------------------------------------------------------------------------+
    2 rows selected (0.019 seconds)
    
    
    ##有索引2(查询的字段都在索引表范围，则从索引表读数据；否则从原表读)
    0: jdbc:phoenix:localhost> drop index "hbase_view1_idx1" on "hbase_view1";
    No rows affected (4.533 seconds)
    0: jdbc:phoenix:localhost> create index "hbase_view1_idx1" on "hbase_view1"("f"."patient_id") include("visit_id");
    2 rows affected (7.376 seconds)
    0: jdbc:phoenix:localhost>  explain select "ROWKEY","patient_id" from "hbase_view1";
    +---------------------------------------------------------------------------+
    |                                   PLAN                                    |
    +---------------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1_idx1  |
    |     SERVER FILTER BY FIRST KEY ONLY                                       |
    +---------------------------------------------------------------------------+
    2 rows selected (0.034 seconds)
    0: jdbc:phoenix:localhost> explain select "visit_id","patient_id" from "hbase_view1";
    +---------------------------------------------------------------------------+
    |                                   PLAN                                    |
    +---------------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1_idx1  |
    +---------------------------------------------------------------------------+
    1 row selected (0.012 seconds)
    0: jdbc:phoenix:localhost> 
    0: jdbc:phoenix:localhost> explain select "visit_id","patient_id","start_time" from "hbase_view1";
    +----------------------------------------------------------------------+
    |                                 PLAN                                 |
    +----------------------------------------------------------------------+
    | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER hbase_view1  |
    +----------------------------------------------------------------------+
    1 row selected (0.015 seconds)
    
    
    ##############################################################3, 插入原表数据，索引表自动更新
    0: jdbc:phoenix:localhost> select * from DEPLOYEE;
    +-----+-------+----------+
    | ID  | NAME  | DEPT_ID  |
    +-----+-------+----------+
    | 1   | e1    | null     |
    | 2   | e2    | null     |
    | 3   | e3    | 1        |
    | 4   | e4    | 2        |
    | 5   | e5    | 1        |
    +-----+-------+----------+
    5 rows selected (0.047 seconds)
    0: jdbc:phoenix:localhost> create index DEPLOYEE_idx4 on DEPLOYEE(name);
    5 rows affected (6.346 seconds)
    0: jdbc:phoenix:localhost>  select * from DEPLOYEE_idx4;
    +---------+------+
    | 0:NAME  | :ID  |
    +---------+------+
    | e1      | 1    |
    | e2      | 2    |
    | e3      | 3    |
    | e4      | 4    |
    | e5      | 5    |
    +---------+------+
    5 rows selected (0.023 seconds)
    0: jdbc:phoenix:localhost> upsert into DEPLOYEE values(6,'e6',1);
    1 row affected (0.076 seconds)
    0: jdbc:phoenix:localhost>  select * from DEPLOYEE_idx4;
    +---------+------+
    | 0:NAME  | :ID  |
    +---------+------+
    | e1      | 1    |
    | e2      | 2    |
    | e3      | 3    |
    | e4      | 4    |
    | e5      | 5    |
    | e6      | 6    |
    +---------+------+
    6 rows selected (0.023 seconds)

[http_archive.cloudera.com_cloudera-labs_phoenix_parcels_latest]: http://archive.cloudera.com/cloudera-labs/phoenix/parcels/latest/