Phoenix 5.1.3

# 解压
[root@node01 ~]# tar -zxvf phoenix-hbase-2.5-5.1.3-bin.tar.gz -C /opt/yjx/
[root@node01 ~]# rm phoenix-hbase-2.5-5.1.3-bin.tar.gz -rf
# 切换目录
[root@node01 ~]# cd /opt/yjx/phoenix-hbase-2.5-5.1.3-bin/
# 拷贝
[root@node01 phoenix-hbase-2.5-5.1.3-bin]# cp phoenix-server-hbase-2.5-5.1.3.jar /opt/yjx/hbase-
2.5.3/lib/
[root@node01 phoenix-hbase-2.5-5.1.3-bin]# scp phoenix-server-hbase-2.5-5.1.3.jar
root@node02:/opt/yjx/hbase-2.5.3/lib/
[root@node01 phoenix-hbase-2.5-5.1.3-bin]# scp phoenix-server-hbase-2.5-5.1.3.jar
root@node03:/opt/yjx/hbase-2.5.3/lib/

<!-- 二级索引 -->
<property>
<name>hbase.regionserver.wal.codec</name>
<value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
<!-- 开启 Schema 与 NameSpace 的映射 -->
<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>
<property>
<name>phoenix.schema.mapSystemTablesToNamespace</name>
<value>true</value>
</property>

<!-- 二级索引 -->
<property>
<name>hbase.regionserver.wal.codec</name>
<value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
<!-- 开启 Schema 与 NameSpace 的映射 -->
<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>
<property>
<name>phoenix.schema.mapSystemTablesToNamespace</name>
<value>true</value>
</property>

[root@node01 ~]# scp /opt/yjx/hbase-2.5.3/conf/hbase-site.xml root@node02:/opt/yjx/hbase-2.5.3/conf/
[root@node01 ~]# scp /opt/yjx/hbase-2.5.3/conf/hbase-site.xml root@node03:/opt/yjx/hbase-2.5.3/conf/
# 或者使用分发脚本
[root@node01 ~]# yjxrsync /opt/yjx/hbase-2.5.3/conf/hbase-site.xml

[root@node02 ~]# scp -r root@node01:/opt/yjx/phoenix-hbase-2.5-5.1.3-bin/ /opt/yjx/
[root@node03 ~]# scp -r root@node01:/opt/yjx/phoenix-hbase-2.5-5.1.3-bin/ /opt/yjx/
# 或者使用分发脚本
[root@node01 ~]# yjxrsync /opt/yjx/phoenix-hbase-2.5-5.1.3-bin

export PHOENIX_HOME=/opt/yjx/phoenix-hbase-2.5-5.1.3-bin
export PATH=$PHOENIX_HOME/bin:$PATH

[root@node01 ~]# sqlline.py node01,node02,node03:2181

SYSTEM.CATALOG
SYSTEM.CHILD_LINK
SYSTEM.FUNCTION
SYSTEM.LOG
SYSTEM.MUTEX
SYSTEM.SEQUENCE
SYSTEM.STATS
SYSTEM.TASK

0: jdbc🐦‍🔥node01,node02,node03:2181> !quit

CREATE TABLE test (
"id" VARCHAR(20) PRIMARY KEY,
"info"."name" VARCHAR(20),
"info"."age" VARCHAR(20)
);

CREATE TABLE test (
"id" VARCHAR(20),
"name" VARCHAR(20),
"info"."age" VARCHAR(20),
CONSTRAINT PK PRIMARY KEY("id", "name")
);

UPSERT INTO test VALUES ('1', 'a', '18');

!tables

CREATE SCHEMA school;
CREATE SCHEMA "school";

# 创建完的表名和字段名都会自动转成大写，如需小写，需在建表时给表名和字段名前后加双引号
CREATE TABLE school.teacher (
tno INTEGER NOT NULL PRIMARY KEY,
tname VARCHAR,
age INTEGER
);
============================== 华丽的分割线 ==============================
CREATE TABLE "school"."teacher" (
"tno" INTEGER NOT NULL PRIMARY KEY,
"info"."tname" VARCHAR,
"info"."age" INTEGER
);

UPSERT INTO school.teacher (tno, tname, age) VALUES (1, 'zhangsan', 18);
UPSERT INTO school.teacher (tno, tname, age) VALUES (2, 'lisi', 18);
UPSERT INTO school.teacher (tno, tname, age) VALUES (3, 'wangwu', 20);
============================== 华丽的分割线 ==============================
UPSERT INTO "school"."teacher" ("tno", "info"."tname", "info"."age") VALUES (1, 'zhangsan', 18);
UPSERT INTO "school"."teacher" ("tno", "info"."tname", "info"."age") VALUES (2, 'lisi', 18);
UPSERT INTO "school"."teacher" ("tno", "info"."tname", "info"."age") VALUES (3, 'wangwu', 20);

UPSERT INTO school.teacher (tno, tname, age) VALUES (1, 'laozhang', 38);
SELECT tno, "0".tname, "0".age FROM school.teacher;
+-----+----------+-----+
| TNO | TNAME | AGE |
+-----+----------+-----+
| 1 | laozhang | 38 |
| 2 | lisi | 18 |
| 3 | wangwu | 20 |
+-----+----------+-----+
============================== 华丽的分割线 ==============================
UPSERT INTO "school"."teacher" ("tno", "info"."tname", "info"."age") VALUES (1, 'laozhang', 38);
SELECT "tno", "info"."tname", "info"."age" FROM "school"."teacher";
+-----+----------+-----+
| tno | tname | age |
+-----+----------+-----+
| 1 | laozhang | 38 |
| 2 | lisi | 18 |
| 3 | wangwu | 20 |
+-----+----------+-----+

SELECT * FROM school.teacher;
SELECT tno, tname, age FROM school.teacher;
SELECT tno, "0".tname, "0".age FROM school.teacher;
============================== 华丽的分割线 ==============================
SELECT * FROM "school"."teacher";
SELECT "tno", "tname", "age" FROM "school"."teacher";
SELECT "tno", "info"."tname", "info"."age" FROM "school"."teacher";

DELETE FROM school.teacher WHERE tno = 1;
SELECT tno, "0".tname, "0".age FROM school.teacher;
+-----+--------+-----+
| TNO | TNAME | AGE |
+-----+--------+-----+
| 2 | lisi | 18 |
| 3 | wangwu | 20 |
+-----+--------+-----+
============================== 华丽的分割线 ==============================
DELETE FROM "school"."teacher" WHERE "tno" = 1;
SELECT "tno", "info"."tname", "info"."age" FROM "school"."teacher";
+-----+--------+-----+
| tno | tname | age |
+-----+--------+-----+
| 2 | lisi | 18 |
| 3 | wangwu | 20 |
+-----+--------+-----+

!tables school.teacher

DROP TABLE school.teacher;

DROP TABLE "school"."teacher";

DROP SCHEMA school;

DROP SCHEMA "school";

!quit

CREATE INDEX my_index ON SCHEMA_NAME.TABLE_NAME (BASICINFO."s1", BASICINFO."s2");

<!-- 设置一次 RPC 请求的超时时间。如果某次 RPC 时间超过该值，客户端就会主动关闭 Socket，服务端会抛出：java.io.IOException: Connection reset by peer，默认为 60000ms，即 1min -->
<property>
<name>hbase.rpc.timeout</name>
<value>6000000</value>
</property>
<!-- 设置一次 Scan 中，一次 RPC 请求的超时时间（一次 Scan 可能有多次 RPC 请求），默认为 60000ms，即 1min --
>
<property>
<name>hbase.client.scanner.timeout.period</name>
<value>6000000</value>
</property>
<!-- 设置客户端发起一次数据操作直至响应之间的总超时时间，数据操作包括 get、append、increment、delete、put等，默认为 1200000ms，即 20min -->
<property>
<name>hbase.client.operation.timeout</name>
<value>12000000</value>
</property>
<!-- 设置在客户端上查询的超时毫秒数，默认为 600000ms，即 10min -->
<property>
<name>phoenix.query.timeoutMs</name>
<value>6000000</value>
</property>

CREATE INDEX async_index ON SCHEMA_NAME.TABLE_NAME (BASICINFO."s1", BASICINFO."s2") ASYNC;

hbase org.apache.phoenix.mapreduce.index.IndexTool \
# 命名空间
--schema SCHEMA_NAME \
# 源数据表
--data-table TABLE_NAME \
# 索引表
--index-table async_index \
# MapReduce 任务写出的目标文件存放的 HDFS 路径
--output-path ASYNC_IDX_HFILES

CREATE TABLE my_table (
rowkey VARCHAR NOT NULL PRIMARY KEY,
v1 VARCHAR,
v2 VARCHAR,
v3 VARCHAR
);
UPSERT INTO my_table VALUES ('rowkey1', 'value1', 'value2', '100010001');
UPSERT INTO my_table VALUES ('rowkey2', 'value1', 'value2', '100010002');
UPSERT INTO my_table VALUES ('rowkey3', 'value1', 'value2', '100010003');
UPSERT INTO my_table VALUES ('rowkey4', 'value1', 'value2', '100010004');
UPSERT INTO my_table VALUES ('rowkey5', 'value1', 'value2', '100010005');
UPSERT INTO my_table VALUES ('rowkey6', 'value1', 'value2', '100010006');
UPSERT INTO my_table VALUES ('rowkey7', 'value1', 'value2', '100010007');
UPSERT INTO my_table VALUES ('rowkey8', 'value1', 'value2', '100010008');
UPSERT INTO my_table VALUES ('rowkey9', 'value1', 'value2', '100010009');
UPSERT INTO my_table VALUES ('rowkey10', 'value1', 'value2', '100010010');
UPSERT INTO my_table VALUES ('rowkey11', 'value1', 'value2', '100010011');
UPSERT INTO my_table VALUES ('rowkey12', 'value1', 'value2', '100010012');
UPSERT INTO my_table VALUES ('rowkey13', 'value1', 'value2', '100010013');
UPSERT INTO my_table VALUES ('rowkey14', 'value1', 'value2', '100010014');
UPSERT INTO my_table VALUES ('rowkey15', 'value1', 'value2', '100010015');
UPSERT INTO my_table VALUES ('rowkey16', 'value1', 'value2', '100010016');
UPSERT INTO my_table VALUES ('rowkey17', 'value1', 'value2', '100010017');
UPSERT INTO my_table VALUES ('rowkey18', 'value1', 'value2', '100010018');
UPSERT INTO my_table VALUES ('rowkey19', 'value1', 'value2', '100010019');
UPSERT INTO my_table VALUES ('rowkey20', 'value1', 'value2', '100010020');

CREATE INDEX my_index ON my_table (v3);

SELECT * FROM my_index;
+-----------+----------+
| 0:V3 | :ROWKEY |
+-----------+----------+
| 100010001 | rowkey1 |
| 100010002 | rowkey2 |
| 100010003 | rowkey3 |
| 100010004 | rowkey4 |
| 100010005 | rowkey5 |
| 100010006 | rowkey6 |
| 100010007 | rowkey7 |
| 100010008 | rowkey8 |
| 100010009 | rowkey9 |
| 100010010 | rowkey10 |
| 100010011 | rowkey11 |
| 100010012 | rowkey12 |
| 100010013 | rowkey13 |
| 100010014 | rowkey14 |
| 100010015 | rowkey15 |
| 100010016 | rowkey16 |
| 100010017 | rowkey17 |
| 100010018 | rowkey18 |
| 100010019 | rowkey19 |
| 100010020 | rowkey20 |
+-----------+----------+

SELECT * FROM my_table WHERE v3 = '100010010';
+----------+--------+--------+-----------+
| ROWKEY | V1 | V2 | V3 |
+----------+--------+--------+-----------+
| rowkey10 | value1 | value2 | 100010010 |
+----------+--------+--------+-----------+
1 row selected (2.337 seconds)
SELECT v3 FROM my_table WHERE v3 = '100010010';
+-----------+
| V3 |
+-----------+
| 100010010 |
+-----------+
1 row selected (2.155 seconds)
CREATE INDEX my_index ON my_table (v3);
1,076,190 rows affected (33.875 seconds)
SELECT * FROM my_table WHERE v3 = '100010010';
+----------+--------+--------+-----------+
| ROWKEY | V1 | V2 | V3 |
+----------+--------+--------+-----------+
| rowkey10 | value1 | value2 | 100010010 |
+----------+--------+--------+-----------+
1 row selected (3.296 seconds)
SELECT v3 FROM my_table WHERE v3 = '100010010';
+-----------+
| V3 |
+-----------+
| 100010010 |
+-----------+
1 row selected (0.02 seconds)

SELECT /*+ INDEX(my_table local_index) */ v1 FROM my_table WHERE v3 = '100010010';

DROP INDEX my_index ON my_table;

CREATE LOCAL INDEX local_index ON my_table (v3);

SELECT * FROM my_table WHERE v3 = '100010010';
+----------+--------+--------+-----------+
| ROWKEY | V1 | V2 | V3 |
+----------+--------+--------+-----------+
| rowkey10 | value1 | value2 | 100010010 |
+----------+--------+--------+-----------+
1 row selected (3.545 seconds)
SELECT v3 FROM my_table WHERE v3 = '100010010';
+-----------+
| V3 |
+-----------+
| 100010010 |
+-----------+
1 row selected (2.946 seconds)
CREATE LOCAL INDEX local_index ON my_table (v3);
1,076,190 rows affected (24.67 seconds)
SELECT * FROM my_table WHERE v3 = '100010010';
+----------+--------+--------+-----------+
| ROWKEY | V1 | V2 | V3 |
+----------+--------+--------+-----------+
| rowkey10 | value1 | value2 | 100010010 |
+----------+--------+--------+-----------+
1 row selected (0.055 seconds)
SELECT v3 FROM my_table WHERE v3 = '100010010';
+-----------+
| V3 |
+-----------+
| 100010010 |
+-----------+
1 row selected (0.013 seconds)

DROP INDEX local_index ON my_table;

CREATE INDEX cover_index ON my_table (v2, v3) INCLUDE (v1);

SELECT * FROM my_table WHERE v3 = '100010010' AND v2 = 'value2';
+----------+--------+--------+-----------+
| ROWKEY | V1 | V2 | V3 |
+----------+--------+--------+-----------+
| rowkey10 | value1 | value2 | 100010010 |
+----------+--------+--------+-----------+
1 row selected (2.42 seconds)
CREATE INDEX cover_index ON my_table (v2, v3) INCLUDE (v1);
1,076,190 rows affected (47.432 seconds)
SELECT * FROM my_table WHERE v3 = '100010010' AND v2 = 'value2';
+----------+--------+--------+-----------+
| ROWKEY | V1 | V2 | V3 |
+----------+--------+--------+-----------+
| rowkey10 | value1 | value2 | 100010010 |
+----------+--------+--------+-----------+
1 row selected (0.031 seconds)

DROP INDEX cover_index ON my_table;

CREATE INDEX func_index ON my_table (SUBSTR(v3, 5, 5)) INCLUDE (v1);

SELECT v1, SUBSTR(v3, 5, 5) FROM my_table WHERE SUBSTR(v3, 5, 5) = '10006';
+--------+------------------+
| V1 | SUBSTR(V3, 5, 5) |
+--------+------------------+
| value1 | 10006 |
+--------+------------------+
1 row selected (3.656 seconds)
SELECT v1, v3 FROM my_table WHERE SUBSTR(v3, 5, 5) = '10006';
+--------+-----------+
| V1 | V3 |
+--------+-----------+
| value1 | 100010006 |
+--------+-----------+
1 row selected (3.969 seconds)
CREATE INDEX func_index ON my_table (SUBSTR(v3, 5, 5)) INCLUDE (v1);
1,076,190 rows affected (45.833 seconds)
SELECT v1, v3 FROM my_table WHERE SUBSTR(v3, 5, 5) = '10006';
+--------+-----------+
| V1 | V3 |
+--------+-----------+
| value1 | 100010006 |
+--------+-----------+
1 row selected (3.44 seconds)
SELECT v1, v3, SUBSTR(v3, 5, 5) FROM my_table WHERE SUBSTR(v3, 5, 5) = '10006';
+--------+-----------+------------------+
| V1 | V3 | SUBSTR(V3, 5, 5) |
+--------+-----------+------------------+
| value1 | 100010006 | 10006 |
+--------+-----------+------------------+
1 row selected (3.327 seconds)
SELECT v1, SUBSTR(v3, 5, 5) FROM my_table WHERE SUBSTR(v3, 5, 5) = '10006';
+--------+-------------------+
| V1 | " SUBSTR(V3,5,5)" |
+--------+-------------------+
| value1 | 10006 |
+--------+-------------------+
1 row selected (0.013 seconds)
SELECT v1 FROM my_table WHERE SUBSTR(v3, 5, 5) = '10006';
+--------+
| V1 |
+--------+
| value1 |
+--------+
1 row selected (0.011 seconds)

DROP INDEX func_index ON my_table;

SELECT
TABLE_NAME, -- 表名（索引表也是个表）
DATA_TABLE_NAME, -- 数据表（我们保存数据的表，不包括索引表，虽然他也存数据）
INDEX_TYPE, -- 索引类型
INDEX_STATE, -- 索引状态
INDEX_DISABLE_TIMESTAMP -- DISABLE 的时间
FROM SYSTEM.CATALOG
WHERE INDEX_TYPE IS NOT NULL;
+------------+-----------------+------------+-------------+-------------------------+
| TABLE_NAME | DATA_TABLE_NAME | INDEX_TYPE | INDEX_STATE | INDEX_DISABLE_TIMESTAMP |
+------------+-----------------+------------+-------------+-------------------------+
| MY_INDEX | MY_TABLE | 1 | a | 0 |
+------------+-----------------+------------+-------------+-------------------------+

SELECT
TABLE_NAME, -- 表名（索引表也是个表）
DATA_TABLE_NAME, -- 数据表（我们保存数据的表，不包括索引表，虽然他也存数据）
INDEX_TYPE, -- 索引类型
INDEX_STATE, -- 索引状态
INDEX_DISABLE_TIMESTAMP -- DISABLE 的时间
FROM SYSTEM.CATALOG
WHERE INDEX_TYPE IS NOT NULL AND INDEX_STATE IN ('w', 'x', 'i', 'd');

ALTER INDEX my_index ON my_table DISABLE;

ALTER INDEX IF EXISTS my_index ON my_table REBUILD;

# 创建 test 命令空间
create_namespace 'test'
# 创建 hbase_user 表并添加 personal 列族和 office 列族
create 'test:t_user', {NAME => 'personal', VERSIONS => 3}, {NAME => 'office', VERSIONS => 3}, {SPLITS
=> ['user-1', 'user-2', 'user-4']}

put 'test:t_user', 'user-1', 'personal:name', 'zhangsan'
put 'test:t_user', 'user-1', 'personal:gender', 1
put 'test:t_user', 'user-1', 'office:phone', '13800000000'
put 'test:t_user', 'user-1', 'office:address', 'beijing'
put 'test:t_user', 'user-1', 'office:phone', '13000000000'
put 'test:t_user', 'user-1', 'office:address', 'tianjin'
put 'test:t_user', 'user-1', 'office:phone', '13800000000'
put 'test:t_user', 'user-1', 'office:address', 'shanghai'
put 'test:t_user', 'user-10', 'personal:name', 'lisi'
put 'test:t_user', 'user-10', 'personal:gender', 0
put 'test:t_user', 'user-10', 'office:address', 'shanghai'
put 'test:t_user', 'user-11', 'personal:name', 'wangwu'
put 'test:t_user', 'user-11', 'personal:gender', 1
put 'test:t_user', 'user-11', 'personal:age', 20
put 'test:t_user', 'user-11', 'office:phone', '13900000000'
put 'test:t_user', 'user-11', 'office:address', 'guangzhou'
put 'test:t_user', 'user-2', 'personal:name', 'zhaoliu'
put 'test:t_user', 'user-2', 'personal:gender', 1
put 'test:t_user', 'user-2', 'personal:age', 19
put 'test:t_user', 'user-2', 'office:phone', '13100000000'
put 'test:t_user', 'user-3', 'personal:name', 'tianqi'
put 'test:t_user', 'user-3', 'personal:gender', 0
put 'test:t_user', 'user-3', 'office:address', 'shaanxi'

CREATE VIEW "test"."t_user" (
user_id VARCHAR PRIMARY KEY,
"personal"."name" VARCHAR,
"personal"."gender" VARCHAR,
"personal"."age" VARCHAR,
"office"."phone" VARCHAR,
"office"."address" VARCHAR
);

0: jdbc🐦‍🔥node01,node02,node03:2181> SELECT * FROM "test"."t_user";
+---------+----------+--------+-----+-------------+-----------+
| USER_ID | name | gender | age | phone | address |
+---------+----------+--------+-----+-------------+-----------+
| user-1 | zhangsan | 1 | | 13800000000 | shanghai |
| user-2 | zhaoliu | 1 | 19 | 13100000000 | |
| user-10 | lisi | 0 | | | shanghai |
| user-3 | tianqi | 0 | | | shaanxi |
| user-11 | wangwu | 1 | 20 | 13900000000 | guangzhou |
+---------+----------+--------+-----+-------------+-----------+
5 rows selected (0.032 seconds)

DROP VIEW "test"."t_user";

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.mrhelloworld</groupId>
    <artifactId>phoenix-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <!-- Phoenix 核心包 -->
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-core</artifactId>
            <version>5.1.3</version>
        </dependency>
        <!-- Phoenix 与 HBase 的兼容包 -->
        <dependency>
            <groupId>org.apache.phoenix</groupId>
            <artifactId>phoenix-hbase-compat-2.5.0</artifactId>
            <version>5.1.3</version>
            <scope>runtime</scope>
        </dependency>
        <!-- JUnit 单元测试 -->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.9.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

package com.yjxxt.phoenix;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.sql.*;
import java.util.Properties;

@DisplayName("Phoenix JDBC 测试类")
public class PhoenixJdbcTest {

    private Connection connection = null;
    private Statement statement = null;

    @BeforeEach
    public void init() throws IOException {
        try {
            Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
            // 配置 ZooKeeper 地址
            String zkUrl = "jdbc🐦‍🔥node01,node02,node03:2181";
            // 开启 Schema 与 NameSpace 的映射
            Properties properties = new Properties();
            properties.setProperty("phoenix.schema.isNamespaceMappingEnabled", "true");
            // 创建数据库连接
            connection = DriverManager.getConnection(zkUrl, properties);
            // 创建 Statement 对象
            statement = connection.createStatement();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    @AfterEach
    public void destory() throws IOException {
        try {
            if (statement != null && !statement.isClosed()) {
                statement.close();
            }
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

 @DisplayName("创建库")
    @Test
    public void testCreateSchema() throws SQLException {
        // 编写 SQL（JDBC 代码中不需要分号，否则报错）
        String sql = "CREATE SCHEMA school";
        // 执行 SQL
        statement.executeUpdate(sql);
        connection.commit();
        System.out.println("创建数据库成功！");
    }  

	@DisplayName("创建表")
    @Test
    public void testCreateTable() throws SQLException {
        // 编写 SQL
        StringBuffer sql = new StringBuffer();
        sql.append("CREATE TABLE school.teacher (")
                .append("tno INTEGER NOT NULL PRIMARY KEY,")
                .append("tname VARCHAR,")
                .append("age INTEGER)");
        // 执行 SQL
        statement.executeUpdate(sql.toString());
        connection.commit();
        System.out.println("创建数据表成功！");
    }

	@DisplayName("插入或修改记录")
    @Test
    public void testUpsert() throws SQLException {
        // 编写 SQL
        String sql1 = "UPSERT INTO school.teacher (tno, tname, age) VALUES (1, 'zhangsan', 18)";
        String sql2 = "UPSERT INTO school.teacher (tno, tname, age) VALUES (2, 'lisi', 18)";
        String sql3 = "UPSERT INTO school.teacher (tno, tname, age) VALUES (3, 'wangwu', 20)";
        // 执行 SQL
        statement.executeUpdate(sql1);
        statement.executeUpdate(sql2);
        statement.executeUpdate(sql3);
        // Phoenix 批量插入优化（一次 commit 和多次 commit）
        // 相同环境下，同时插入 50 条，第二种速度是第一种的 5-10 倍
        // 并且数据量越大，第二种效果更明显，甚至能达到几十倍几百倍
        connection.commit();
        System.out.println("插入或更新记录成功！");
    }

	@DisplayName("删除记录")
    @Test
    public void testDelete() throws SQLException {
        // 编写 SQL
        String sql = "DELETE FROM school.teacher WHERE tno = 1";
        // 执行 SQL 并接收执行成功条数
        int result = statement.executeUpdate(sql);
        connection.commit();
        System.out.format("成功删除 %d 条记录！", result);
    }

	@DisplayName("查询记录")
    @Test
    public void testSelect() throws SQLException {
        // 编写 SQL
        String sql = "SELECT tno, \"0\".tname, \"0\".age FROM school.teacher";
        // 执行 SQL 并接收返回对象
        ResultSet resultSet = statement.executeQuery(sql);
        connection.commit();
        while (resultSet.next()) {
            // 获取字段值
            int tno = resultSet.getInt("tno");
            String tname = resultSet.getString("tname");
            int age = resultSet.getInt("age");
            System.out.println("tno = " + tno);
            System.out.println("tname = " + tname);
            System.out.println("age = " + age);
        }
        resultSet.close();
    }

# 解压
[root@node01 ~]# tar -zxvf phoenix-queryserver-6.0.0-bin.tar.gz -C /opt/yjx/
# 切换目录
[root@node01 ~]# cd /opt/yjx/phoenix-queryserver-6.0.0/
# 拷贝
[root@node01 phoenix-queryserver-6.0.0]# cp phoenix-queryserver-6.0.0.jar /opt/yjx/hbase-2.5.3/lib/
[root@node01 phoenix-queryserver-6.0.0]# scp phoenix-queryserver-6.0.0.jar root@node02:/opt/yjx/hbase-
2.5.3/lib/
[root@node01 phoenix-queryserver-6.0.0]# scp phoenix-queryserver-6.0.0.jar root@node03:/opt/yjx/hbase-
2.5.3/lib/

[root@node01 ~]# cp phoenix-server-hbase-2.5-5.1.3.jar /opt/yjx/hbase-2.5.3/lib/
[root@node01 ~]# scp phoenix-server-hbase-2.5-5.1.3.jar root@node02:/opt/yjx/hbase-2.5.3/lib/
[root@node01 ~]# scp phoenix-server-hbase-2.5-5.1.3.jar root@node03:/opt/yjx/hbase-2.5.3/lib/

[root@node01 ~]# mv /root/phoenix-client-hbase-2.5-5.1.3.jar /opt/yjx/phoenix-queryserver-6.0.0/

<!-- 二级索引 -->
<property>
<name>hbase.regionserver.wal.codec</name>
<value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>
</property>
<!-- 开启 Schema 与 NameSpace 的映射 -->
<property>
<name>phoenix.schema.isNamespaceMappingEnabled</name>
<value>true</value>
</property>
<property>
<name>phoenix.schema.mapSystemTablesToNamespace</name>
<value>true</value>
</property>

[root@node01 ~]# scp /opt/yjx/hbase-2.5.3/conf/hbase-site.xml root@node02:/opt/yjx/hbase-2.5.3/conf/
[root@node01 ~]# scp /opt/yjx/hbase-2.5.3/conf/hbase-site.xml root@node03:/opt/yjx/hbase-2.5.3/conf/

# 或者使用分发脚本
[root@node01 ~]# yjxrsync /opt/yjx/hbase-2.5.3/conf/hbase-site.xml

[root@node02 ~]# scp -r root@node01:/opt/yjx/phoenix-queryserver-6.0.0/ /opt/yjx/
[root@node03 ~]# scp -r root@node01:/opt/yjx/phoenix-queryserver-6.0.0/ /opt/yjx/

# 或者使用分发脚本
[root@node01 ~]# yjxrsync /opt/yjx/phoenix-queryserver-6.0.0

[root@node01 ~]# mkdir /tmp/phoenix

export PHOENIX_QUERYSERVER_HOME=/opt/yjx/phoenix-queryserver-6.0.0
export PATH=$PHOENIX_QUERYSERVER_HOME/bin:$PATH

[root@node01 ~]# queryserver.py start
# 查看 Phoenix 日志是否正常启动
cat /opt/yjx/hbase-2.5.3/logs/phoenix-root-queryserver.log
# 如果看到以下两行信息说明已正常启动
INFO org.eclipse.jetty.server.Server: Started @1203ms
INFO org.apache.calcite.avatica.server.HttpServer: Service listening on port 8765.

[root@node01 ~]# sqlline-thin.py http://node01:8765

SYSTEM.CATALOG
SYSTEM.CHILD_LINK
SYSTEM.FUNCTION
SYSTEM.LOG
SYSTEM.MUTEX
SYSTEM.SEQUENCE
SYSTEM.STATS
SYSTEM.TASK

0: jdbc🐦‍🔥thin:url=http://node01:8765> !quit