cdh phoenix 安装配置

张映 发表于 2020-01-19

分类目录: hadoop/spark/scala

标签:, ,

phoenix是构建在Hbase之上,使用标准的SQL操作Hbase,可以做联机事务处理,拥有低延迟的特性。

cdh的安装,请参考:cloudera cdh 6.3 安装配置

impala可以整合Hbase,但是不支持delete,upsert。如果想要impala的delete,upsert,存储要用kudu。请参考:impala与hbase整合

hive可以整合Hbase,受限于hive自己的原因,和hbase并不能配合的很好。请参考:hive与hbase整合

一,下载,并安装phoenix

# wget http://archive.apache.org/dist/phoenix/apache-phoenix-5.0.0-HBase-2.0/bin/apache-phoenix-5.0.0-HBase-2.0-bin.tar.gz
# tar zxvf apache-phoenix-5.0.0-HBase-2.0-bin.tar.gz
# mv apache-phoenix-5.0.0-HBase-2.0-bin /bigdata/phoenix

# scp -r /bigdata/phoenix bigserver2:/bigdata/
# scp -r /bigdata/phoenix bigserver3:/bigdata/
# scp -r /bigdata/phoenix bigserver4:/bigdata/
# scp -r /bigdata/phoenix bigserver5:/bigdata/

二,配置phoenix

1,配置环境变量

# vim ~/.bashrc //追加
export PHOENIX_HOME=/bigdata/phoenix
export PATH=$PHOENIX_HOME/bin:$PATH

# source ~/.bashrc

2,copy phoenix jar包到hbase lib中

# cp phoenix-5.0.0-HBase-2.0-* /opt/cloudera/parcels/CDH/lib/hbase/lib
# scp phoenix-5.0.0-HBase-2.0-* bigserver1:/opt/cloudera/parcels/CDH/lib/hbase/lib
# scp phoenix-5.0.0-HBase-2.0-* bigserver2:/opt/cloudera/parcels/CDH/lib/hbase/lib
# scp phoenix-5.0.0-HBase-2.0-* bigserver3:/opt/cloudera/parcels/CDH/lib/hbase/lib
# scp phoenix-5.0.0-HBase-2.0-* bigserver4:/opt/cloudera/parcels/CDH/lib/hbase/lib

3,配置服务端(hbase)hbase-site.xml

phoenix hbase-site配置

phoenix hbase-site配置

<property>
    <name>hbase.table.sanity.checks</name>
    <value>false</value>
</property>
<property>
    <name>phoenix.schema.isNamespaceMappingEnabled</name>
    <value>true</value>
</property>
<property>
    <name>phoenix.schema.mapSystemTablesToNamespace</name>
    <value>true</value>
</property>

配置完成后,要重启cdh。

4,配置客户端(phoenix)hbase-site.xml

# cd /bigdata/phoenix/bin
# cp hbase-site.xml hbase-site.xml_bak20200119
# cp /etc/hbase/conf/hbase-site.xml ./

# vim hbase-site.xml //添加以下内容
<property>
    <name>hbase.table.sanity.checks</name>
    <value>false</value>
</property>
<property>
    <name>phoenix.schema.isNamespaceMappingEnabled</name>
    <value>true</value>
</property>
<property>
    <name>phoenix.schema.mapSystemTablesToNamespace</name>
    <value>true</value>
</property>

# scp /bigdata/phoenix/bin/hbase-site.xml bigserver2:/bigdata/phoenix/bin/
# scp /bigdata/phoenix/bin/hbase-site.xml bigserver3:/bigdata/phoenix/bin/
# scp /bigdata/phoenix/bin/hbase-site.xml bigserver4:/bigdata/phoenix/bin/
# scp /bigdata/phoenix/bin/hbase-site.xml bigserver5:/bigdata/phoenix/bin/

服务端和客户端的配置,最好是一样。不然有可能会报以一下错误。

Error: ERROR 726 (43M10): Inconsistent namespace mapping properties. Ensure that config phoenix.schema.isNamespaceMappingEnabled is consistent on client and server. (state=43M10,code=726)
java.sql.SQLException: ERROR 726 (43M10): Inconsistent namespace mapping properties. Ensure that config phoenix.schema.isNamespaceMappingEnabled is consistent on client and server.
at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:494)
at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150)
at org.apache.phoenix.query.ConnectionQueryServicesImpl.checkClientServerCompatibility(ConnectionQueryServicesImpl.java:1318)

三,测试phoenix

1,查看hive与hbase整合

hive> select * from hive_hbase_test;
OK
1000320190925	1	tank	man
1000320190926	2	tank1	man
1000320190927	3	tank2	man1
Time taken: 0.326 seconds, Fetched: 3 row(s)

hive> show create table hive_hbase_test;
OK
CREATE EXTERNAL TABLE `hive_hbase_test`(
  `key` bigint COMMENT '',
  `id` bigint COMMENT '',
  `name` string COMMENT '',
  `sex` string COMMENT '')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.hbase.HBaseSerDe'
STORED BY
  'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
  'hbase.columns.mapping'=':key,login:id,login:username,info:sex',
  'serialization.format'='1')
TBLPROPERTIES (
  'hbase.table.name'='tank_ns:user',
  'transient_lastDdlTime'='1578455335')
Time taken: 0.186 seconds, Fetched: 15 row(s)

2,查看hbase的数据

hbase(main):003:0> list_namespace
NAMESPACE
default
hbase
tank_ns
3 row(s)
Took 0.0244 seconds

hbase(main):005:0* scan 'tank_ns:user'
ROW                  COLUMN+CELL
 1000320190925       column=info:sex, timestamp=1579424039575, value=man
 1000320190925       column=login:_0, timestamp=1579424039575, value=
 1000320190925       column=login:id, timestamp=1579424039495, value=1
 1000320190925       column=login:username, timestamp=1579424039542, value=tank
 1000320190926       column=info:sex, timestamp=1579424039733, value=man
 1000320190926       column=login:_0, timestamp=1579424039733, value=
 1000320190926       column=login:id, timestamp=1579424039633, value=2
 1000320190926       column=login:username, timestamp=1579424039666, value=tank1
 1000320190927       column=info:sex, timestamp=1579424040312, value=man1
 1000320190927       column=login:_0, timestamp=1579424040312, value=
 1000320190927       column=login:id, timestamp=1579424039771, value=3
 1000320190927       column=login:username, timestamp=1579424039800, value=tank2
3 row(s)
Took 0.0656 seconds

3,创建phoenix表

[root@bigserver4 bin]# sqlline.py bigserver4:2181
Setting property: [incremental, false]
Setting property: [isolation, TRANSACTION_READ_COMMITTED]
issuing: !connect jdbc:phoenix:bigserver4:2181 none none org.apache.phoenix.jdbc.PhoenixDriver
Connecting to jdbc:phoenix:bigserver4:2181
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/bigdata/phoenix/phoenix-5.0.0-HBase-2.0-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/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.
20/01/19 15:28:25 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
20/01/19 15:28:27 WARN query.ConnectionQueryServicesImpl: Expected 5 system tables but found 6:[SYSTEM.CATALOG, SYSTEM.FUNCTION, SYSTEM.LOG, SYSTEM.MUTEX, SYSTEM.SEQUENCE, SYSTEM.STATS]
Connected to: Phoenix (version 5.0)
Driver: PhoenixEmbeddedDriver (version 5.0)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
133/133 (100%) Done
Done
sqlline version 1.2.0
0: jdbc:phoenix:bigserver4:2181> CREATE TABLE "user" (   //创建user表,没有挂载schema
. . . . . . . . . . . . . . . .> "ROW" varchar primary key,
. . . . . . . . . . . . . . . .> "login"."id" bigint,
. . . . . . . . . . . . . . . .> "login"."name" varchar,
. . . . . . . . . . . . . . . .> "info"."sex" varchar
. . . . . . . . . . . . . . . .> )column_encoded_bytes=0;
No rows affected (2.479 seconds)

0: jdbc:phoenix:bigserver4:2181> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+----------------+
| 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  | VIEW_STATEMENT |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+----------------+
|            | 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         |                |
|            |              | user        | TABLE         |          |            | |                 |              | false           | null          | false         |                |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+----------------+
0: jdbc:phoenix:bigserver4:2181> select * from "user"; //无数据
+------+-----+-------+------+
| ROW  | id  | name  | sex  |
+------+-----+-------+------+
+------+-----+-------+------+
No rows selected (0.052 seconds)

0: jdbc:phoenix:bigserver4:2181> create schema "tank_ns";  //创建schema,根hbase的namespace差不多,好比是mysql的数据库名
No rows affected (0.014 seconds)
0: jdbc:phoenix:bigserver4:2181> CREATE TABLE "tank_ns"."user" (
. . . . . . . . . . . . . . . .> "ROW" varchar primary key,
. . . . . . . . . . . . . . . .> "login"."id" bigint,
. . . . . . . . . . . . . . . .> "login"."name" varchar,
. . . . . . . . . . . . . . . .> "info"."sex" varchar
. . . . . . . . . . . . . . . .> )column_encoded_bytes=0;
4 rows affected (8.521 seconds)  //创建成功

0: jdbc:phoenix:bigserver4:2181> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+----------------+
| 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  | VIEW_STATEMENT |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+----------------+
|            | 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         |                |
|            |              | user        | TABLE         |          |            | |                 |              | false           | null          | false         |                |
|            | tank_ns      | user        | TABLE         |          |            | |                 |              | false           | null          | false         |                |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+----------------+

报错

0: jdbc:phoenix:bigserver4:2181> select * from "tank_ns"."user";
Error: ERROR 201 (22000): Illegal data. Expected length of at least 8 bytes, but had 5 (state=22000,code=201)
java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at least 8 bytes, but had 5
at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:494)
at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150)

报错原因是:hbase中的整形,不能被phoenix识别。但是通过phoenix创建的整形字段是可以的。所以创建phoenix表,映射hbase时,整形要换varchar

CREATE TABLE "tank_ns"."user" (
"ROW" varchar primary key,
"login"."id" varchar,
"login"."username" varchar,
"info"."sex" varchar
)column_encoded_bytes=0;

4,删除user表,重新建表

0: jdbc:phoenix:bigserver4:2181> drop table "tank_ns"."user";

hbase> create 'tank_ns:user','login','info'
hbase> put 'tank_ns:user','1000320190925','login:id','1'
hbase> put 'tank_ns:user','1000320190925','login:username','tank'
hbase> put 'tank_ns:user','1000320190925','info:sex','man'
hbase> put 'tank_ns:user','1000320190926','login:id','2'
hbase> put 'tank_ns:user','1000320190926','login:username','tank1'
hbase> put 'tank_ns:user','1000320190926','info:sex','man'
hbase> put 'tank_ns:user','1000320190927','login:id','3'
hbase> put 'tank_ns:user','1000320190927','login:username','tank2'
hbase> put 'tank_ns:user','1000320190927','info:sex','man1'

注意:

通过phoenix删除表,对应的hbase中的表,也会被删除。
phoenix 区分大小写,切默认情况下会将小写转成大写,所以表名、列簇、列名需要用双引号。
phoenix 4.10 版本之后,在创建表映射时需要将 COLUMN_ENCODED_BYTES 置为 0。

phoenix hbase 测试

phoenix hbase 测试



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/hadoop/2358.html

留下评论

留下评论
  • (必需)
  • (必需) (will not be published)
  • (必需)   5X8=?