CDH5.14集群下Phoenix4.14.0安装及性能对比phoenix/impala/hive

小鱼儿 2022-05-10 08:16 235阅读 0赞

**痛点****&****目的：**

1.  Hbase 作为NoSQL数据库，语法特别难用，为了方便我们使用标准SQL查询Hbase数据。
2.  Phoenix支持DDL与DML 增删改查。
3.  Impala给外部提供的接口难以提升并发量。
4.  减轻Impala的查询压力。

**官网**:

[**http://phoenix.apache.org**][http_phoenix.apache.org]

**Phoneix****安装****:**

SIT环境Cloudera5.14.4的hbase 为1.2版本，因此到phoneix官网下载对应版本的tar包并上传至其中一台机器，这里上传到gateway。

![70][]

解压tar包: *tar –zxvf apache-phoenix-4.14.0-cdh5.14.2-bin.tar.gz –C /opt/app*

建软连接: *ln –s /opt/app/apache-phoenix-4.14.0-cdh5.14.2-bin /opt/cloudera/parcels/phoenix*

环境变量：

![70 1][]

在CM里新增一个参数： 然后重启hbase服务

![70 2][]

将 hbase 集群中的配置文件 hbase-site.xml 拷贝到 Phoenix 的 bin 目录下，覆盖原有的配置文件

![70 3][]

将 hdfs 集群中的配置文件 core-site.xml、 hdfs-site.xml 拷贝到 Phoenix 的 bin 目录下

![70 4][]

Copy以下三个jar包到CDH集群每台机器的 hbase的lib下:

*cp $PHOENIX\_HOME/phoenix-4.14.0-cdh5.14.2-server.jar /opt/cloudera/parcels/CDH/lib/hbase/lib/*

*cp $PHOENIX\_HOME/phoenix-4.14.0-cdh5.14.2-client.jar /opt/cloudera/parcels/CDH/lib/hbase/lib/*

*cp $PHOENIX\_HOME/phoenix-core-4.14.0-cdh5.14.2.jar /opt/cloudera/parcels/CDH/lib/hbase/lib/*

在$PHOENIX\_HOME/bin下执行命令进入phoenix shell:

./sqlline.py sithd01,sithd02,sithd03:2181

![70 5][]

执行SQL文件:

![70 6][]

导数据文件至phoenix表:

![70 7][]

刚才的语法新建的phoenix表和数据：

![70 8][]

Phoenix的表在hbase中可以看到:

![70 9][]

与hbase已有的表做映射:

在hbase shell中准备一张表:

![70 10][]

在phoenix中创建映射视图：

![70 11][]

**Tips:**

**HBase****数据表默认主键列名是****ROW**

**表名和列族以及列名需要用双引号括起来****,** **因为****HBase****是区分大小写的，如果不用双引号括起来的话****Phoenix****在创建表的时候会自动将小写转换为大写字母，这样****HBase****中会创建另外一张表，大小写不同。**

**查询表名需要用双引号括起来****.**

**实施同步****hbase****的最新数据****:**

![70 12][]

![70 13][]

建phoenix表则可以与hbase相互操作.

**性能对比**

Phoenix查询Hbase  VS  impala 查询hive与hbase的映射表

数据量586968。

准备：

\--建hive的hbase映射表

CREATE TABLE IF NOT EXISTS dl\_noahwm\_cbs.hbase\_tbl\_doc\_record (

key string comment "hbase rowkey",

\`docrecordid\` STRING COMMENT '上传文档ID',

\`doccatcode\` STRING COMMENT '文档分类编码',

\`doccatindex\` STRING COMMENT '文档索引号',

\`docname\` STRING COMMENT '文档名称',

\`docextension\` STRING COMMENT '文档扩展名',

\`docsize\` STRING COMMENT '文档大小',

\`docdesc\` STRING COMMENT '文档说明',

\`version\` STRING COMMENT '版本号',

\`docaccessid\` STRING COMMENT 'ECM访问ID',

\`bizgroup\` STRING COMMENT '业务所属板块',

\`isdeleted\` STRING COMMENT '逻辑删除',

\`createdbyid\` STRING COMMENT '添加人',

\`createdtime\` STRING COMMENT '添加日期',

\`lastmodifiedbyid\` STRING COMMENT '最后更新人',

\`lastmodifiedtime\` STRING COMMENT '最后更新日期',

\`src\_sys\_name\` STRING COMMENT 'source system name',

\`ins\_gmt\_ts\` STRING COMMENT 'insert timestamp',

\`upt\_gmt\_ts\` STRING COMMENT 'update timestamp')

STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,record:docrecordid,record:doccatcode,record:doccatindex,record:docname,record:docextension,record:docsize,record:docdesc,record:version,record:docaccessid,record:bizgroup,record:isdeleted,record:createdbyid,record:createdtime,record:lastmodifiedbyid,record:lastmodifiedtime,record:src\_sys\_name,record:ins\_gmt\_ts,record:upt\_gmt\_ts")

TBLPROPERTIES("hbase.table.name" = "hbase\_tbl\_doc\_record");

\--插入数据： 需as字段否则会乱序

insert into table hbase\_tbl\_doc\_record

select docrecordid as key,docrecordid as docrecordid,

doccatcode as doccatcode,

doccatindex as doccatindex,

docname as docname,

docextension as docextension,

docsize as docsize,

docdesc as docdesc,

version as version,

docaccessid as docaccessid,

bizgroup as bizgroup,

isdeleted as isdeleted,

createdbyid as createdbyid,

createdtime as createdtime,

lastmodifiedbyid as lastmodifiedbyid,

lastmodifiedtime as lastmodifiedtime,

src\_sys\_name as src\_sys\_name,

ins\_gmt\_ts as ins\_gmt\_ts,

upt\_gmt\_ts as upt\_gmt\_ts from tbl\_doc\_record;

\--phoenix DDL

create view "hbase\_tbl\_doc\_record"(

"ROW" varchar primary key,

"record"."docrecordid" varchar,

"record"."doccatcode" varchar,

"record"."doccatindex" varchar,

"record"."docname" varchar,

"record"."docextension" varchar,

"record"."docsize" varchar,

"record"."docdesc" varchar,

"record"."version" varchar,

"record"."docaccessid" varchar,

"record"."bizgroup" varchar,

"record"."isdeleted" varchar,

"record"."createdbyid" varchar,

"record"."createdtime" varchar,

"record"."lastmodifiedbyid" varchar,

"record"."lastmodifiedtime" varchar,

"record"."src\_sys\_name" varchar,

"record"."ins\_gmt\_ts" varchar,

"record"."upt\_gmt\_ts" varchar

);

**查询性能对比**：

Query1: select count(1) from hbase\_tbl\_doc\_record where docsize>'200000';

Hive: 45.67s

![70 14][]

Impala: 15.56s

![70 15][]

Phoenix: 2.39s

![70 16][]

Query2: select distinct docsize  from hbase\_tbl\_doc\_record where docsize>'200000' limit 20;

Hive: 54.67s

![70 17][]

Impala: 10.81s

![70 18][]

Phoenix: 0.048s

![70 19][]

Query3: select count(1)  from hbase\_tbl\_doc\_record where docsize like '20%';

Hive:42.25s

![70 20][]

Impala: 11.8s

![70 21][]

Phoenix: 2.322s

![70 22][]

Query4: select \* from hbase\_tbl\_doc\_record where createdtime >'2016-01-01 00:00:00' limit 100;

Hive: 31.7s

![70 23][]

Impala: 2.17s

![70 24][]

Phoenix: 0.214s

![70 25][]

[http_phoenix.apache.org]: http://phoenix.apache.org
[70]: /images/20220510/5d2bd1c144f94e3a9e5c3e44b36dcf28.png
[70 1]: /images/20220510/761c10e808104163b60f1924157663a4.png
[70 2]: /images/20220510/8375521efc764d92be2ddfa2d2f8d6e1.png
[70 3]: /images/20220510/357ae91203b049e9b2ab89b078fd671a.png
[70 4]: /images/20220510/2cb843bb7baa42dfba5ff43a657ff764.png
[70 5]: /images/20220510/b8d0c91374704d8184565bd709d869b1.png
[70 6]: /images/20220510/cb19b4832f2e4e5280c39886802a43ec.png
[70 7]: /images/20220510/97209145c90e4227a9915330d6342665.png
[70 8]: /images/20220510/5ff7a8095b614e0f8fda1097f2526202.png
[70 9]: /images/20220510/ac87f6450fe649249298d9e5f15d8ef6.png
[70 10]: /images/20220510/ebdc3402f7bf470499c00e3e9a803d81.png
[70 11]: /images/20220510/a45ca596bf384d28970c90e4bd47931a.png
[70 12]: /images/20220510/c8e9385a4bfa4716a79e6bda5c6e9eba.png
[70 13]: /images/20220510/6831ad2c2701435b9ef09fc53683b5af.png
[70 14]: /images/20220510/5164ff79ef1e461696bebd564657d6ab.png
[70 15]: /images/20220510/42332748418b41cb85e5f32effec125c.png
[70 16]: /images/20220510/13eff1c753a54b169066174c3084c56d.png
[70 17]: /images/20220510/80d96a8f2c0c4dc5a2cd183cf077b0ca.png
[70 18]: /images/20220510/c392378afacb44038bae2d0bdd9293b7.png
[70 19]: /images/20220510/b6b5bd2b453e4705819936f17f78b07b.png
[70 20]: /images/20220510/48cc13645b81455995f6f6582c3d80ad.png
[70 21]: /images/20220510/381da332d322494bb90fd8acce893656.png
[70 22]: /images/20220510/c927324cfacc4187a9d2e7feded9acc5.png
[70 23]: /images/20220510/4fc9a4ab0e8b477db9fcd010d7fdfc08.png
[70 24]: /images/20220510/2bf7a502b1b84366a220f10633e821a5.png
[70 25]: /images/20220510/f897510fcb7647afb318b8a478ec13b8.png