CDH中的Phoenix开启Schema支持

1. 添加配置

   HBase --> 配置 --> 高级 --> 搜索 hbase-site.xml。
   在服务端和客户端均添加下面配置：

   <property>
   <name>phoenix.schema.isNamespaceMappingEnabled</name>
   <value>true</value>
   </property>
   
   <property>
       <name>phoenix.schema.mapSystemTablesToNamespace</name>
       <value>true</value>
   </property>
   

   
   

2. 重启HBase服务

   如要使用客户端工具DBeaver连接Phoenix，参考使用客户端工具DBeaver连接Phoenix。

3. 验证

   root@bigdata-dev-41:/opt/cloudera/parcels/APACHE_PHOENIX/bin# ./phoenix-sqlline.py bigdata-dev-41:2181
   Setting property: [incremental, false]
   Setting property: [isolation, TRANSACTION_READ_COMMITTED]
   issuing: !connect jdbc:phoenix:bigdata-dev-41:2181 none none org.apache.phoenix.jdbc.PhoenixDriver
   Connecting to jdbc:phoenix:bigdata-dev-41:2181
   SLF4J: Class path contains multiple SLF4J bindings.
   SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/APACHE_PHOENIX-4.14.0-cdh5.12.2.p0.3/lib/phoenix/phoenix-4.14.0-cdh5.12.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
   SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-5.12.1-1.cdh5.12.1.p0.3/jars/slf4j-log4j12-1.7.5.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]
   19/04/19 09:24:54 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
   Connected to: Phoenix (version 4.14)
   Driver: PhoenixEmbeddedDriver (version 4.14)
   Autocommit status: true
   Transaction isolation: TRANSACTION_READ_COMMITTED
   Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
   166/166 (100%) Done
   Done
   sqlline version 1.2.0
   0: jdbc:phoenix:bigdata-dev-41: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_ |
   +------------+--------------+--------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+--------+
   |            | 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  |
   |            |              | HBASE_TEST   | TABLE         |          |            |                            |                 |              | false           | null          | false  |
   |            |              | HBASE_TEST2  | TABLE         |          |            |                            |                 |              | false           | null          | false  |
   +------------+--------------+--------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+--------+
   0: jdbc:phoenix:bigdata-dev-41:2181> CREATE SCHEMA IF NOT EXISTS my_schema;
   No rows affected (0.22 seconds)
   0: jdbc:phoenix:bigdata-dev-41:2181> use my_schema
   . . . . . . . . . . . . . . . . . . . . . > ;
   No rows affected (0.022 seconds)
   0: jdbc:phoenix:bigdata-dev-41:2181> create table schema_test 
   . . . . . . . . . . . . . . . . . . . . . > (                                                                       
   . . . . . . . . . . . . . . . . . . . . . > s1 varchar not null primary key,                                        
   . . . . . . . . . . . . . . . . . . . . . > s2 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s3 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s4 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s5 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s6 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s7 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s8 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s9 varchar,                                                             
   . . . . . . . . . . . . . . . . . . . . . > s10 varchar,                                                            
   . . . . . . . . . . . . . . . . . . . . . > s11 varchar                                                             
   . . . . . . . . . . . . . . . . . . . . . > );
   No rows affected (1.658 seconds)
   0: jdbc:phoenix:bigdata-dev-41: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_ |
   +------------+--------------+--------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+--------+
   |            | 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  |
   |            |              | HBASE_TEST   | TABLE         |          |            |                            |                 |              | false           | null          | false  |
   |            |              | HBASE_TEST2  | TABLE         |          |            |                            |                 |              | false           | null          | false  |
   |            | MY_SCHEMA    | SCHEMA_TEST  | TABLE         |          |            |                            |                 |              | false           | null          | false  |
   +------------+--------------+--------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+--------+
   0: jdbc:phoenix:bigdata-dev-41:2181> 
   

   
   注意：

   CDH的Phoenix不支持这样创建表
   CREATE TABLE MY_SCHEMA.MYTABLE (k BIGINT PRIMARY KEY, v VARCHAR);
   若想在某个SCHEMA下创建表，需先使用USE SCHEMA语句切换到该SCHEMA下，再执行建表语句。

   0: jdbc:phoenix:bigdata-dev-41:2181> CREATE TABLE MY_SCHEMA.MYTABLE (k BIGINT PRIMARY KEY, v VARCHAR);
   Error: ERROR 1013 (42M04): Table already exists. tableName=MY_SCHEMA.MYTABLE (state=42M04,code=1013)
   org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=MY_SCHEMA.MYTABLE
   	at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:2729)
   	at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:1114)
   	at org.apache.phoenix.compile.CreateTableCompiler$1.execute(CreateTableCompiler.java:192)
   	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:408)
   	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)
   	at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
   	at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:389)
   	at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
   	at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)
   	at sqlline.Commands.execute(Commands.java:822)
   	at sqlline.Commands.sql(Commands.java:732)
   	at sqlline.SqlLine.dispatch(SqlLine.java:813)
   	at sqlline.SqlLine.begin(SqlLine.java:686)
   	at sqlline.SqlLine.start(SqlLine.java:398)
   	at sqlline.SqlLine.main(SqlLine.java:291)
   0: jdbc:phoenix:bigdata-dev-41:2181>