【Phoenix】在Kerberos认证下使用JDBC连接Phoenix 和 Phoenix各数据类型测试表创建

绝地灬酷狼 2024-02-24 06:24 67阅读 0赞

本案例使用的是CDH集群。

[phoenix语法][phoenix]

### 一、Phoenix连接sqlline ###

# 先检查一下kerberos cache是否过期了。
    klist
    
    # 使用Phoenix的sqlline连接集群
    sqlline.py zkhost1,zkhost2,zkhost3:2181

### 二、sqlline中创建测试表 ###

-- 查看帮助
    !help
    
    -- 查看所有表
    !tables
    
    -- 创建schema
    CREATE SCHEMA IF NOT EXISTS TEST;
    
    -- 创建测试表
    CREATE TABLE TEST.TEST_TYPE_META_BASIC(
    INTEGER_TYPE INTEGER NOT NULL,
    UNSIGNED_INT_TYPE UNSIGNED_INT,
    BIGINT_TYPE BIGINT,
    UNSIGNED_LONG_TYPE UNSIGNED_LONG,
    TINYINT_TPYE TINYINT NOT NULL,
    UNSIGNED_TINYINT_TPYE UNSIGNED_TINYINT,
    SMALLINT_TYPE SMALLINT,
    UNSIGNED_SMALLINT_TYPE UNSIGNED_SMALLINT,
    FLOAT_TYPE FLOAT,
    UNSIGNED_FLOAT_TYPE UNSIGNED_FLOAT,
    DOUBLE_TYPE DOUBLE,
    UNSIGNED_DOUBLE_TYPE UNSIGNED_DOUBLE,
    DECIMAL_TYPE DECIMAL,
    DECIMAL_TYPE103 DECIMAL(10,3),
    BOOLEAN_TYPE BOOLEAN,
    TIME_TYPE TIME,
    DATE_TYPE DATE,
    TIMESTAMP_TYPE TIMESTAMP,
    UNSIGNED_TIME_TYPE UNSIGNED_TIME,
    UNSIGNED_DATE_TYPE UNSIGNED_DATE,
    UNSIGNED_TIMESTAMP_TYPE UNSIGNED_TIMESTAMP,
    VARCHAR_TYPE VARCHAR,
    VARCHAR_TYPE10 VARCHAR(10),
    CHAR_TYPE CHAR(10),
    BINARY_TYPE BINARY(30),
    VARBINARY_TYPE VARBINARY,
    VARBINARY_TYPE30 VARBINARY(30),
    CONSTRAINT pk PRIMARY KEY (INTEGER_TYPE,TINYINT_TPYE) 
    );
    
    -- 插入一条数据
    UPSERT INTO TEST.TEST_TYPE_META_BASIC VALUES(
    888,
    888,
    888,
    888,
    8,
    8,
    999,
    999,
    8.888,
    8.888,
    9.999,
    9.999,
    888.888,
    888.888,
    TRUE,
    time '2020-01-01 18:01:01.666',
    date '2020-01-01 18:01:01.666',
    timestamp '2020-01-01 18:01:01.666',
    time '2020-01-01 18:01:01.666',
    date '2020-01-01 18:01:01.666',
    timestamp '2020-01-01 18:01:01.666',
    'abc',
    'abc',
    'abc',
    'cba',
    'cba',
    'cba'
    );
    -- 只能在sqlline中使用
    !describe TEST.TEST_TYPE_META_BASIC;
    
    -- 查看表
    SELECT * FROM TEST.TEST_TYPE_META_BASIC;

### 三、java JDBC 连接 ###

有两种方式jdbc连接Phoenix  
![在这里插入图片描述][a9fd1679a74345e3888cfe80d5c16401.png]使用方式就是`选择驱动URL模板就选择了连接方式`：

*  thick 方式，phoenix-client 直连hbase，速度更快，驱动url模板如下格式：

jdbc:phoenix:zkHost:zkPort:zkHbasePath

*  thin 方式，phoenix-client 连接 Phoenix Query Server，驱动url模板如下格式：

jdbc:phoenix:thin:url=http://localhost:8765;serialization=PROTOBUF; authentication=SPENGO;principal=Prinicpal@EXAMPLE.com;keytab=xxx.keytab

`本案例选择thick方式！！！`

#### 3.1 添加集群jar包纸maven ####

以下两个jar都可以从集群直接获取！！！

# hbase-client
    mvn install:install-file -DgroupId=com.cloudera.hbase -DartifactId=hbase-client -Dversion=2.1.0-cdh6.2.0 -Dpackaging=jar -Dfile=D:\xxx\hbase-client-2.1.0-cdh6.2.0.jar
    
    # phoenix-client
    mvn install:install-file -DgroupId=com.cloudera.phoenix -DartifactId=phoenix-client -Dversion=5.0.0-cdh6.2.0 -Dpackaging=jar -Dfile=D:\xxx\phoenix-5.0.0-cdh6.2.0-client.jar

#### 3.2 添加pom依赖 ####

<properties>
        <hadoop.version>3.0.0</hadoop.version>
        <hbase.version>2.1.0-cdh6.2.0</hbase.version>
        <phoenix.version>5.0.0-cdh6.2.0</phoenix.version>
      </properties>
      
        <dependency>
          <groupId>org.apache.hadoop</groupId>
          <artifactId>hadoop-common</artifactId>
          <version>${hadoop.version}</version>
          <scope>provided</scope>
        </dependency>
        <dependency>
          <groupId>org.apache.hadoop</groupId>
          <artifactId>hadoop-client</artifactId>
          <version>${hadoop.version}</version>
          <scope>provided</scope>
        </dependency>
        <dependency>
          <groupId>com.cloudera.hbase</groupId>
          <artifactId>hbase-client</artifactId>
          <version>${hbase.version}</version>
          <scope>provided</scope>
          <exclusions>
            <exclusion>
              <artifactId>hadoop-auth</artifactId>
              <groupId>org.apache.hadoop</groupId>
            </exclusion>
          </exclusions>
        </dependency>
        <dependency>
          <groupId>com.cloudera.phoenix</groupId>
          <artifactId>phoenix-client</artifactId>
          <version>${phoenix.version}</version>
          <scope>provided</scope>
        </dependency>

#### 3.3 添加hbase-site.xml至classpath目录 ####

简单点，添加至resources目录下即可。  
![在这里插入图片描述][0e24852dde24481295a86252af439697.png]

`注意：一定要手动添加，在环境变量中设置 HBASE_HOME 或者 HBASE_CONF_DIR 是无效的！`  
![在这里插入图片描述][589518369e124831bd1a4ea2a1b6b21e.png]  
[CDH官方指南][CDH]

#### 3.4 代码连接 ####

[KerberosProvider文章链接][KerberosProvider]

public class PhoenixTest {
        
    
        @Test
        public void testConn() throws SQLException {
        
            KerberosProvider.login();
            // "/hbase-secure"是从hbase-site.xml中zookeeper.znode.parent项获取的
            String jdbUrl = "jdbc:phoenix:zkhost:2181:/hbase-secure";
            Connection conn = DriverManager.getConnection(jdbUrl);
            DatabaseMetaData meta = conn.getMetaData();
            String[] type = {
        "TABLE"};
            String catalog=null;  // 此处写什么都所谓因为phoenix和hbase都没有catalog的概念。
            String schema="TEST";
            String table="TEST_TYPE_META_BASIC";
            ResultSet rs = meta.getTables(catalog, schema, table, type);
            if(rs.next()){
        
                System.out.printf("table [%s.%s] exists!%n",schema,table);
            }else{
        
                System.out.printf("table [%s.%s] does not exist!%n",schema,table);
            }
        }
    }

[phoenix]: https://phoenix.apache.org/language/index.htm
[a9fd1679a74345e3888cfe80d5c16401.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/24/a130f98892f64eb48312f865665ba87a.png
[0e24852dde24481295a86252af439697.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/24/e03e59fbab18452c962dee9c164f1df3.png
[589518369e124831bd1a4ea2a1b6b21e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/24/520ac488a4e34038a6bea05abff7ad59.png
[CDH]: https://docs.cloudera.com/cdp-private-cloud-base/7.1.6/phoenix-access-data/topics/phoenix-orchestrating-sql.html?
[KerberosProvider]: https://blog.csdn.net/lisacumt/article/details/133682160