點擊關注上方“知了小巷”，

設為“置頂或星標”，第一時間送達干貨。

Phoenix定位為OLTP和操作型分析(operational analytics)，大多用于在線業務，穩定性要求第一位。Phoenix的功能很強大，也很靈活，Phoenix SQL基于SQL-92標準，但是還是有很多方言，使用時需要特別注意。

DataX是阿里巴巴集團內被廣泛使用的離線數據同步工具/平臺，實現包括 MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS 等各種異構數據源之間高效的數據同步功能。

這里主要用到oraclereader和hbase11xwriter。

基礎軟件版本：

Apache Hadoop：hadoop-2.8.5

Apache HBase：hbase-1.4.10

Apache Phoenix：phoenix-4.14.3-HBase-1.4-bin

Phoenix版本支持：

Phoenix Current release 4.15.0 can run on Apache HBase 1.3, 1.4 and 1.5. CDH HBase 5.11, 5.12, 5.13 and 5.14 is supported by 4.14.0. Apache HBase 2.0 is supported by 5.0.0.

hbase客戶端

hbase shell

phoenix客戶端

phoenix-4.14.3-HBase-1.4-bin/bin/sqlline.py

#?進入hbase shell客戶端

$ hbase shellSLF4J: Class path contains multiple SLF4J bindings.SLF4J: Found binding in [jar:file:/home/admin/hbase-1.4.10/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]SLF4J: Found binding in [jar:file:/home/admin/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.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]HBase ShellUse "help" to get list of supported commands.Use "exit" to quit this interactive shell.Version 1.4.10, r76ab087819fe82ccf6f531096e18ad1bed079651, Wed Jun 5 16:48:11 PDT 2019

#?創建命名空間ZLXX

hbase(main):006:0> create_namespace 'ZLXX'0 row(s) in 1.1150 seconds

#?創建hbase表，使用命名空間

hbase(main):001:0> create 'ZLXX:WT_TRADE_REFUND', 'INFO'0 row(s) in 1.7510 seconds => Hbase::Table - ZLXX:WT_TRADE_REFUND

查看表描述信息(desc)

hbase(main):009:0> desc 'ZLXX:WT_TRADE_REFUND'Table ZLXX:WT_TRADE_REFUND is ENABLED ZLXX:WT_TRADE_REFUND COLUMN FAMILIES DESCRIPTION {NAME => 'INFO', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'} 1 row(s) in 0.0730 seconds

#?使用DataX往hbase表里面添加測試數據(存量批量數據采集)

數據源來自Oracle，數據目標系統是HBase原生創建的表。

使用oraclereader和hbase11xwriter。

具體配置zlxx_test.json

$ vi zlxx_test.json{ "job": { "content": [ { "reader": { "name": "oraclereader", "parameter": { "column": [], "connection": [ { "fetchSize": "1024", "jdbcUrl": [ "$DW_ORCL_ZLXX_JDBCURL" ], "querySql": [??????????????????"select?id,?trade_id,?amount,?status,?TO_CHAR(modify_time,'YYYYMMDDhh24miss')?AS?modify_time?from?zlxx.trade_refund" ] } ], "password": "$DW_ORCL_ZLXX_PASSWORD", "username": "$DW_ORCL_ZLXX_USERNAME" } }, "writer": { "name": "hbase11xwriter", "parameter": { "hbaseConfig": {??????????????"hbase.zookeeper.quorum":?"192.168.10.211:12181,192.168.10.212:12181,192.168.10.213:12181" }, "table": "ZLXX:WT_TRADE_REFUND", "mode": "normal", "nullMode": "empty", "rowkeyColumn": [ { "index":0, "type":"string" } ], "column": [ { "index":1, "name": "INFO:TRADE_ID", "type": "string" }, { "index":2, "name": "INFO:AMOUNT", "type": "string" }, { "index":3, "name": "INFO:STATUS", "type": "string" }, { "index":4, "name": "INFO:MODIFY_TIME", "type": "string" } ], "encoding": "utf-8" } } } ], "setting": { "speed": { "channel": "10" } } }}

執行DataX的shell腳本

數據庫連接和用戶名密碼等配置在服務器的環境變量中

$ vi zlxx_test.shsource ~/.bash_profilepython datax/bin/datax.py -j "-Xms1g -Xmx1g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=datax/log" -p"-DDW_ORCL_ZLXX_JDBCURL=$DW_ORCL_ZLXX_JDBCURL -DDW_ORCL_ZLXX_PASSWORD=$DW_ORCL_ZLXX_PASSWORD -DDW_ORCL_ZLXX_USERNAME=$DW_ORCL_ZLXX_USERNAME"

執行shell腳本

$?sh?zlxx_test.sh......任務總計耗時 : 10s任務平均流量 : 1.77KB/s記錄寫入速度 : 52rec/s讀出記錄總數 : 528讀寫失敗總數 : 0

查看一下數據

hbase(main):012:0* scan 'ZLXX:WT_TRADE_REFUND', {LIMIT=>1}ROW COLUMN+CELL 1005528 column=INFO:AMOUNT, timestamp=1594783514554, value=100 1005528 column=INFO:MODIFY_TIME, timestamp=1594783514554, value=20200228185803 1005528 column=INFO:STATUS, timestamp=1594783514554, value=01 1005528 column=INFO:TRADE_ID, timestamp=1594783514554, value=T12059592 1 row(s) in 0.1220 seconds

Tips：Phoenix不管你輸入的是大寫還是小寫都默認把它轉成大寫的，如果要小寫的話必須加上引號；所以上面從hbase shell創建的表和列均使用了大寫，便于Phoenix自然映射。

#?Phoenix使用as select創建視圖映射到HBase表

先創建Phoenix schema：

0: jdbc:phoenix:> create schema ZLXX;No rows affected (0.012 seconds)

創建視圖，由于Phoenix大小寫最終都是大寫，所以這里可以使用小寫

create view ZLXX.WT_TRADE_REFUND ( id varchar primary key, info.trade_id varchar, info.amount varchar, info.status varchar, info.modify_time varchar) as select * from zlxx.wt_trade_refund;

視圖創建效果：

0: jdbc:phoenix:> create view ZLXX.WT_TRADE_REFUND (. . . . . . . . > id varchar primary key,. . . . . . . . > info.trade_id varchar,. . . . . . . . > info.amount varchar,. . . . . . . . > info.status varchar,. . . . . . . . > info.modify_time varchar. . . . . . . . > ) as select * from zlxx.wt_trade_refund;No rows affected (5.956 seconds)

# 走一個查詢試試看(完美)

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund limit 5;+----------+------------+---------+---------+-----------------+| ID | TRADE_ID | AMOUNT | STATUS | MODIFY_TIME |+----------+------------+---------+---------+-----------------+| 1005528 | T12059592 | 100 | 01 | 20200228185803 || 1005529 | T12059591 | 10 | 03 | 20200228185846 || 1005530 | T12059606 | 10 | 01 | 20200228195442 || 1005531 | T12059605 | 10 | 01 | 20200228195442 || 1005532 | noTradeId | 100 | 03 | 20200229224015 |+----------+------------+---------+---------+-----------------+5 rows selected (0.062 seconds)

# Phoenix使用column_encoded_bytes=0

創建視圖映射到HBase表(測試下多視圖)

由于需要名字相同，先把上面的view ZLXX.WT_TRADE_REFUND刪除，稍后再使用as select創建不同名稱的視圖。

刪除視圖：

drop view ZLXX.WT_TRADE_REFUND;

創建視圖：

create view ZLXX.WT_TRADE_REFUND ( id varchar primary key, info.trade_id varchar, info.amount varchar, info.status varchar, info.modify_time varchar) column_encoded_bytes=0;

也是正常的，能夠看到有數據查詢出來

0: jdbc:phoenix:> drop view ZLXX.WT_TRADE_REFUND;No rows affected (0.046 seconds)0: jdbc:phoenix:> create view ZLXX.WT_TRADE_REFUND (. . . . . . . . > id varchar primary key,. . . . . . . . > info.trade_id varchar,. . . . . . . . > info.amount varchar,. . . . . . . . > info.status varchar,. . . . . . . . > info.modify_time varchar. . . . . . . . > ) column_encoded_bytes=0;No rows affected (0.041 seconds)

limit查詢數據：

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund limit 5;+----------+------------+---------+---------+-----------------+| ID | TRADE_ID | AMOUNT | STATUS | MODIFY_TIME |+----------+------------+---------+---------+-----------------+| 1005528 | T12059592 | 100 | 01 | 20200228185803 || 1005529 | T12059591 | 10 | 03 | 20200228185846 || 1005530 | T12059606 | 10 | 01 | 20200228195442 || 1005531 | T12059605 | 10 | 01 | 20200228195442 || 1005532 | noTradeId | 100 | 03 | 20200229224015 |+----------+------------+---------+---------+-----------------+5 rows selected (0.056 seconds)

下面使用as select創建名稱不同的視圖，假設不需要status字段

create view ZLXX.WT_TRADE_REFUND2 ( id varchar primary key, info.trade_id varchar, info.amount varchar, info.modify_time varchar) as select INFO.TRADE_ID, INFO.AMOUNT, INFO.MODIFY_TIME from zlxx.wt_trade_refund;

會報錯

0: jdbc:phoenix:> create view ZLXX.WT_TRADE_REFUND2 (. . . . . . . . > id varchar primary key,. . . . . . . . > info.trade_id varchar,. . . . . . . . > info.amount varchar,. . . . . . . . > info.modify_time varchar. . . . . . . . > ) as select INFO.TRADE_ID, INFO.AMOUNT, INFO.MODIFY_TIME from zlxx.wt_trade_refund;Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "INFO" at line 6, column 13. (state=42P00,code=604)org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "INFO" at line 6, column 13.

同一張表創建名稱不同的視圖：?

create view ZLXX.WT_TRADE_REFUND2 ( id varchar primary key, info.trade_id varchar, info.amount varchar, info.status varchar, info.modify_time varchar) as select * from zlxx.wt_trade_refund;?

則是可以的

0: jdbc:phoenix:> select * from zlxx.wt_trade_refund2 limit 5;+----------+------------+---------+---------+-----------------+| ID | TRADE_ID | AMOUNT | STATUS | MODIFY_TIME |+----------+------------+---------+---------+-----------------+| 1005528 | T12059592 | 100 | 01 | 20200228185803 || 1005529 | T12059591 | 10 | 03 | 20200228185846 || 1005530 | T12059606 | 10 | 01 | 20200228195442 || 1005531 | T12059605 | 10 | 01 | 20200228195442 || 1005532 | noTradeId | 100 | 03 | 20200229224015 |+----------+------------+---------+---------+-----------------+5 rows selected (0.093 seconds)

簡單查看一下Phoenix視圖的描述信息(列信息)

0: jdbc:phoenix:> !describe zlxx.wt_trade_refund+------------+--------------+------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+------+| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | BUFFER_LENGTH | DECIMAL_DIGITS | NUM_PREC_RADIX | NULLABLE | REMARKS | COLU |+------------+--------------+------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+------+| | ZLXX | WT_TRADE_REFUND | ID | 12 | VARCHAR | null | null | null | null | 0 | | || | ZLXX | WT_TRADE_REFUND | TRADE_ID | 12 | VARCHAR | null | null | null | null | 1 | | || | ZLXX | WT_TRADE_REFUND | AMOUNT | 12 | VARCHAR | null | null | null | null | 1 | | || | ZLXX | WT_TRADE_REFUND | STATUS | 12 | VARCHAR | null | null | null | null | 1 | | || | ZLXX | WT_TRADE_REFUND | MODIFY_TIME | 12 | VARCHAR | null | null | null | null | 1 | | |+------------+--------------+------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+------+

簡單查看一下Phoenix表元數據信息(TABLE_TYPE為VIEW)

0: jdbc:phoenix:> !tables+------------+--------------+-------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+-+| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | |+------------+--------------+-------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+-+| | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | null | || | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | null | || | SYSTEM | LOG | SYSTEM TABLE | | | | | | true | 32 | || | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | null | || | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | null | || | ZLXX | WT_TRADE_REFUND | VIEW | | | | | | false | null | || | ZLXX | WT_TRADE_REFUND2 | VIEW | | | | | | false | null | |+------------+--------------+-------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+-+

#?在HBase里插入點數據看看Phoenix的視圖能否實時更新？答案是YES！

put 'ZLXX:WT_TRADE_REFUND', '2009999', 'INFO:TRADE_ID', 'ZXR3099999'put 'ZLXX:WT_TRADE_REFUND', '2009999', 'INFO:AMOUNT', '100'put 'ZLXX:WT_TRADE_REFUND', '2009999', 'INFO:STATUS', '00'put 'ZLXX:WT_TRADE_REFUND', '2009999', 'INFO:MODIFY_TIME', '20200715124015'

hbase shell端put數據

Phoenix端查詢數據

總結：

1.hbase shell這邊創建表時，命名空間namespace和表名稱都要大寫，這樣方便Phoenix這邊小寫。

2.在hbase shell這邊，命名空間與表名稱之間需要注意是雙引號，DataX里的全表名稱也是如此。

3.Phoenix這邊創建相同命名空間名稱的schema。

4.Phoenix這邊創建view視圖映射有兩種方式：as select和column_encoded_bytes=0。

附錄hbase整合Phoenix的基本配置：

hbase-site.xml<property> <name>phoenix.schema.isNamespaceMappingEnabledname> <value>truevalue>property><property> <name>phoenix.schema.mapSystemTablesToNamespacename> <value>truevalue>property>

【Phoenix視圖映射和DataX數據同步到HBase、END】

往期推薦：

Kafka消息送達語義說明

Kafka基礎知識總結

Hadoop YARN：ApplicationMaster向ResourceManager注冊AM源碼調試

Apache Hadoop YARN：ClientResourceManager源碼解析

Apache Hadoop YARN：ClientResourceManager源碼DEBUG

Hadoop YARN：ApplicationMaster與ResourceManager交互源碼解析

Hive企業級調優

HiveQL查詢連續三天有銷售記錄的店鋪

HiveQL實戰螞蟻森林低碳用戶排名分析：解法一

HiveQL實戰螞蟻森林低碳用戶排名分析：解法二

HiveQL實戰螞蟻森林植物申領統計分析

Hive-函數

Hive-查詢

Hive-DML(Data Manipulation Language)數據操作語言

Hive-DDL(Data Definition Language)數據定義

Hive優化(整理版)

Spark Core之Shuffle解析

數據倉庫開發規范

分享-點贊-在看，謝謝～～?

總結

以上是生活随笔為你收集整理的配置phoenix连接hbase_Phoenix视图映射的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。