安装版本 CDH 6.2.0
1. 安装前准备
到https://archive.cloudera.com/phoenix/6.2.0/ 下载以下包,并放至相关位置
# 将下列包放至httpd的/var/www/html/cloudera-repo/parcel/phoenix
[cdh@c123m phoenix]$ ll -h
total 384M
-r-xr-xr-x 1 root root 2.5K Apr 28 22:06 manifest.json
-r-xr-xr-x 1 root root 384M Aug 1 2019 PHOENIX-5.0.0-cdh6.2.0.p0.1308267-el7.parcel
-r-xr-xr-x 1 root root 41 Apr 28 22:06 PHOENIX-5.0.0-cdh6.2.0.p0.1308267-el7.parcel.sha
#将下列包放至cloudera-scm-server端的/opt/cloudera/csd
[cdh@c123m csd]$ ll -h
total 8.0K
-r-xr-xr-x 1 cloudera-scm cloudera-scm 5.2K Apr 28 22:09 PHOENIX-1.0.jar
2. 配置phoenix的parcel
3. 对phoenix parcel 从"下载" -> “激活”
4. 添加属性
CDH -> “HBase配置” -> 搜"hbase-site.xml" -> hbase-site.xml的HBase 服务高级配置代码段、HBase 客户端高级配置代码,单击“以XML格式查看”,加入以下属性:
# 写入预写日志(“wal”)编码
<property>
<name>hbase.regionserver.wal.codec</name>
<value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
# 启用用户自定义函数(UDF)
<property>
<name>phoenix.functions.allowUserDefinedFunctions</name>
<value>true</value>
</property>
# phoenix的schema是否映射hbase namespace
<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>
# phoenix的system schema是否映射hbase namespace
<property>
<name>phoenix.schema.mapSystemTablesToNamespace </name>
<value>true</value>
</property>
5. 重启服务
- cloudera-scm-server 重启
- HBase重启
6. 集群添加 phoenix
通过CDH的 服务添加 功能,加入 phoenix服务
7. 验证
- 在WEB_STAT.sql文件加入 COLUMN_ENCODED_BYTES= 0
CREATE TABLE IF NOT EXISTS WEB_STAT (
HOST CHAR(2) NOT NULL,
DOMAIN VARCHAR NOT NULL,
FEATURE VARCHAR NOT NULL,
DATE DATE NOT NULL,
USAGE.CORE BIGINT,
USAGE.DB BIGINT,
STATS.ACTIVE_VISITOR INTEGER
CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)
) COLUMN_ENCODED_BYTES= 0;
- 创建表、并导入数据
[root@c123m ~]# phoenix-psql /opt/cloudera/parcels/PHOENIX-5.0.0-cdh6.2.0.p0.1308267/lib/phoenix/examples/WEB_STAT.sql \
> /opt/cloudera/parcels/PHOENIX-5.0.0-cdh6.2.0.p0.1308267/lib/phoenix/examples/WEB_STAT.csv \
> /opt/cloudera/parcels/PHOENIX-5.0.0-cdh6.2.0.p0.1308267/lib/phoenix/examples/WEB_STAT_QUERIES.sql
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/PHOENIX-5.0.0-cdh6.2.0.p0.1308267/lib/phoenix/phoenix-5.0.0-cdh6.2.0-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
no rows upserted
Time: 2.414 sec(s)
csv columns from database.
CSV Upsert complete. 39 rows upserted
Time: 0.246 sec(s)
DOMAIN AVERAGE_CPU_USAGE AVERAGE_DB_USAGE
---------------------------------------- ---------------------------------------- ----------------------------------------
Salesforce.com 260.727 257.636
Google.com 212.875 213.75
Apple.com 114.111 119.556
Time: 0.036 sec(s)
DAY TOTAL_CPU_USAGE MIN_CPU_USAGE MAX_CPU_USAGE
----------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
2013-01-01 00:00:00.000 35 35 35
2013-01-02 00:00:00.000 150 25 125
2013-01-03 00:00:00.000 88 88 88
2013-01-04 00:00:00.000 26 3 23
2013-01-05 00:00:00.000 550 75 475
2013-01-06 00:00:00.000 12 12 12
2013-01-08 00:00:00.000 345 345 345
2013-01-09 00:00:00.000 390 35 355
2013-01-10 00:00:00.000 345 345 345
2013-01-11 00:00:00.000 335 335 335
2013-01-12 00:00:00.000 5 5 5
2013-01-13 00:00:00.000 355 355 355
2013-01-14 00:00:00.000 5 5 5
2013-01-15 00:00:00.000 720 65 655
2013-01-16 00:00:00.000 785 785 785
2013-01-17 00:00:00.000 1590 355 1235
Time: 0.031 sec(s)
HO TOTAL_ACTIVE_VISITORS
-- ----------------------------------------
EU 150
NA 1
Time: 0.012 sec(s)
- 执行 phoenix-sqlline 在phoenix中查看数据
[cdh@c123m ~]$ phoenix-sqlline
# 列出全部表
0: jdbc:phoenix> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+
| | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false |
| | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false |
| | SYSTEM | LOG | SYSTEM TABLE | | | | | | true |
| | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false |
| | SYSTEM | STATS | SYSTEM TABLE | | | | | | false |
| | | WEB_STAT | TABLE | | | | | | false |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------+
- 在phoenix中select
0: jdbc:phoenix> select * from WEB_STAT;
+-------+-----------------+------------+--------------------------+-------+-------+-----------------+
| HOST | DOMAIN | FEATURE | DATE | CORE | DB | ACTIVE_VISITOR |
+-------+-----------------+------------+--------------------------+-------+-------+-----------------+
| EU | Apple.com | Mac | 2013-01-01 01:01:01.000 | 35 | 22 | 34 |
| EU | Apple.com | Store | 2013-01-03 01:01:01.000 | 345 | 722 | 170 |
| EU | Google.com | Analytics | 2013-01-13 08:06:01.000 | 25 | 2 | 6 |
| EU | Google.com | Search | 2013-01-09 01:01:01.000 | 395 | 922 | 190 |
| EU | Salesforce.com | Dashboard | 2013-01-06 05:04:05.000 | 12 | 22 | 43 |
| EU | Salesforce.com | Login | 2013-01-12 01:01:01.000 | 5 | 62 | 150 |
| EU | Salesforce.com | Reports | 2013-01-02 12:02:01.000 | 25 | 11 | 2 |
| EU | Salesforce.com | Reports | 2013-01-02 14:32:01.000 | 125 | 131 | 42 |
| EU | Salesforce.com | Reports | 2013-01-05 03:11:12.000 | 75 | 22 | 3 |
| EU | Salesforce.com | Reports | 2013-01-05 04:14:12.000 | 475 | 252 | 53 |
| EU | Salesforce.com | Reports | 2013-01-13 08:04:04.000 | 355 | 52 | 5 |
| NA | Apple.com | Login | 2013-01-01 01:01:01.000 | 35 | 22 | 40 |
| NA | Apple.com | Login | 2013-01-04 01:01:01.000 | 135 | 2 | 110 |
| NA | Apple.com | Mac | 2013-01-02 04:01:01.000 | 345 | 255 | 155 |
| NA | Apple.com | Mac | 2013-01-08 01:01:01.000 | 3 | 2 | 10 |
| NA | Apple.com | iPad | 2013-01-05 01:01:01.000 | 85 | 2 | 18 |
| NA | Apple.com | iPad | 2013-01-06 01:01:01.000 | 35 | 22 | 10 |
| NA | Apple.com | iPad | 2013-01-07 01:01:01.000 | 9 | 27 | 7 |
| NA | Google.com | Analytics | 2013-01-07 06:01:01.000 | 23 | 1 | 57 |
| NA | Google.com | Analytics | 2013-01-11 01:02:01.000 | 7 | 2 | 7 |
| NA | Google.com | Analytics | 2013-01-14 01:01:01.000 | 65 | 252 | 56 |
| NA | Google.com | Search | 2013-01-08 08:01:01.000 | 345 | 242 | 46 |
| NA | Google.com | Search | 2013-01-10 01:05:01.000 | 835 | 282 | 80 |
| NA | Google.com | Search | 2013-01-12 01:01:01.000 | 8 | 7 | 6 |
| NA | Salesforce.com | Dashboard | 2013-01-03 11:01:01.000 | 88 | 66 | 44 |
| NA | Salesforce.com | Dashboard | 2013-01-11 01:01:01.000 | 335 | 32 | 30 |
| NA | Salesforce.com | Dashboard | 2013-01-14 04:07:01.000 | 5 | 2 | 9 |
| NA | Salesforce.com | Login | 2013-01-01 01:01:01.000 | 35 | 42 | 10 |
| NA | Salesforce.com | Login | 2013-01-04 06:01:21.000 | 3 | 52 | 1 |
| NA | Salesforce.com | Login | 2013-01-04 11:01:11.000 | 23 | 56 | 45 |
| NA | Salesforce.com | Login | 2013-01-08 14:11:01.000 | 345 | 242 | 10 |
| NA | Salesforce.com | Login | 2013-01-10 01:01:01.000 | 345 | 252 | 150 |
| NA | Salesforce.com | Login | 2013-01-16 01:01:01.000 | 785 | 782 | 80 |
| NA | Salesforce.com | Login | 2013-01-17 01:01:01.000 | 355 | 242 | 33 |
| NA | Salesforce.com | Login | 2013-01-17 02:20:01.000 | 1235 | 2422 | 243 |
| NA | Salesforce.com | Reports | 2013-01-09 16:33:01.000 | 35 | 42 | 15 |
| NA | Salesforce.com | Reports | 2013-01-09 17:36:01.000 | 355 | 432 | 315 |
| NA | Salesforce.com | Reports | 2013-01-15 04:09:01.000 | 65 | 26 | 6 |
| NA | Salesforce.com | Reports | 2013-01-15 07:09:01.000 | 655 | 426 | 46 |
+-------+-----------------+------------+--------------------------+-------+-------+-----------------+
39 rows selected (0.078 seconds)
- 在hbase shell中查看
hbase(main):001:0> list
TABLE
SYSTEM.CATALOG
SYSTEM.FUNCTION
SYSTEM.LOG
SYSTEM.MUTEX
SYSTEM.SEQUENCE
SYSTEM.STATS
WEB_STAT
7 row(s)
Took 0.4811 seconds
=> ["SYSTEM.CATALOG", "SYSTEM.FUNCTION", "SYSTEM.LOG", "SYSTEM.MUTEX", "SYSTEM.SEQUENCE", "SYSTEM.STATS", "WEB_STAT"]
hbase(main):002:0>
hbase(main):007:0* scan 'WEB_STAT'
ROW COLUMN+CELL
EUApple.com\x00Mac\x00\x80\x00\x01;\xF3 column=STATS:ACTIVE_VISITOR, timestamp=1588230422816, value=\x80\x00\x00"
\xA04\xC8
EUApple.com\x00Mac\x00\x80\x00\x01;\xF3 column=USAGE:CORE, timestamp=1588230422816, value=\x80\x00\x00\x00\x00\x00\x00#
\xA04\xC8
EUApple.com\x00Mac\x00\x80\x00\x01;\xF3 column=USAGE:DB, timestamp=1588230422816, value=\x80\x00\x00\x00\x00\x00\x00\x16
\xA04\xC8
EUApple.com\x00Mac\x00\x80\x00\x01;\xF3 column=USAGE:_0, timestamp=1588230422816, value=x
\xA04\xC8
EUApple.com\x00Store\x00\x80\x00\x01;\x column=STATS:ACTIVE_VISITOR, timestamp=1588230422816, value=\x80\x00\x00\xAA
FD\xEC\xEC\xC8
EUApple.com\x00Store\x00\x80\x00\x01;\x column=USAGE:CORE, timestamp=1588230422816, value=\x80\x00\x00\x00\x00\x00\x01Y
FD\xEC\xEC\xC8