Phoenix JDBC开发

Phoenix JDBC开发

Apache Phoenix

Apache Phoenix基于Hbase的一个SQL引擎，我们可以使用Phoenix在Hbase之上提供SQL语言的支持。Phoenix是可以支持二级索引的，而且Phoenix它自动帮助我们管理二级索引，底层是通过Hbase的协处理器来实现的，通过配合二级索引和Hbase rowkey，可以提升hbase的查询效率Phoenix底层还是将SQL语言解析为Hbase的原生查询（put/get/scan），所以它的定位还是在随机实时查询——OLTP领域Apache Phoenix不是独立运行的，而是提供一些JAR包，扩展了Hbase的功能 Phoenix的建表语法

在Phoenix中，要执行SQL，必须要建立表的结构，然后才能查询。默认Phoenix不会之前在Hbase使用create创建的表加载进来。

create table if not exists ORDER_DTL(
    "id" varchar primary key,
    "C1"."status" varchar,
    "C1"."money" double,
    "C1"."pay_way" integer,
    "C1"."user_id" varchar,
    "C1"."operation_time" varchar,
    "C1"."category" varchar
);

注意事项：

每个表必须要有rowkey，通过指定某一个列后面的primary key，就表示该列就是rowkey每个除了rowkey的列必须要带列蔟名，Phoenix会自动帮助我们创建列蔟大小写的问题，在Phoenix如果要使用小写，必须得带双引号。否则会自动转换为大小，如果使用小写将来编写的任何SQL语句都得带双引号

Phoenix创建视图

将Hbase已经存在的表进行映射

名称空间和表名一模一样

列蔟名和列名也必须要一一对应

create view "名称空间"."表名"(
   rowkey对应列名 varchar primary key,
   "列蔟名"."列名" varchar,
);

创建跟之前一样的的视图

create view if not exists "MOMO_CHAT"."MSG"(
    id varchar primary key,
    "C1"."msg_time" varchar,
    "C1"."sender_nickyname" varchar,
    "C1"."sender_account" varchar,
    "C1"."sender_sex" varchar,
    "C1"."sender_ip" varchar,
    "C1"."sender_os" varchar,
    "C1"."sender_phone_type" varchar,
    "C1"."sender_network" varchar,
    "C1"."sender_gps" varchar,
    "C1"."receiver_nickyname" varchar,
    "C1"."receiver_ip" varchar,
    "C1"."receiver_account" varchar,
    "C1"."receiver_os" varchar,
    "C1"."receiver_phone_type" varchar,
    "C1"."receiver_network" varchar,
    "C1"."receiver_gps" varchar,
    "C1"."receiver_sex" varchar,
    "C1"."msg_type" varchar,
    "C1"."distance" varchar,
    "C1"."message" varchar
);

Table is read only异常解决

这个时候遇到一个异常：Table is read only

在hbase-site.xml添加如下配置

    
      phoenix.schema.isNamespaceMappingEnabled
      true
    
    
      phoenix.schema.mapSystemTablesToNamespace
      true
    

复制到phoneix目录下一份

cp hbase-site.xml /apache-phoenix-4.13.1-Hbase-1.3-bin/bin/

再重新执行下建表命令，可以了

使用jdbc开发

pom新增phonex 依赖配置

         
            org.apache.phoenix
            phoenix-core
            4.13.1-Hbase-1.3
        
        
        
            org.apache.phoenix
            phoenix-queryserver-client
            4.13.1-Hbase-1.3
        

核心查询逻辑

public class PhoenixChatMessageService implements ChatMessageService {

    private Connection connection;

    public PhoenixChatMessageService() throws Exception {
        // 1. 加载驱动
        Class.forName(PhoenixDriver.class.getName());
        // 2. 获取JDBC连接
        Properties props = new Properties();
        props.setProperty("phoenix.schema.isNamespaceMappingEnabled", "true");
        connection = DriverManager.getConnection("jdbc:phoenix:master:2181",props);
    }

    @Override
    public List getMessage(String date, String sender, String receiver) throws Exception {
        // 1. SQL语句
        String sql = "select * from "MOMO_CHAT"."MSG" where substr("msg_time", 0, 10) = ? and "sender_account" = ? and "receiver_account" = ?";

        // 2. 构建一个prepareStatement
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        // 3. 设置Prestatement对应的参数
        preparedStatement.setString(1, date);
        preparedStatement.setString(2, sender);
        preparedStatement.setString(3, receiver);

        // 4. 执行SQL语句，获取到一个ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList msgList = new ArrayList<>();

        // 5. 迭代ResultSet将数据封装在Msg里面
        while(resultSet.next()) {
            Msg msg = new Msg();
            msg.setMsg_time(resultSet.getString("msg_time"));
            msg.setSender_nickyname(resultSet.getString("sender_nickyname"));
            msg.setSender_account(resultSet.getString("sender_account"));
            msg.setSender_sex(resultSet.getString("sender_sex"));
            msg.setSender_ip(resultSet.getString("sender_ip"));
            msg.setSender_os(resultSet.getString("sender_os"));
            msg.setSender_phone_type(resultSet.getString("sender_phone_type"));
            msg.setSender_network(resultSet.getString("sender_network"));
            msg.setSender_gps(resultSet.getString("sender_gps"));
            msg.setReceiver_nickyname(resultSet.getString("receiver_nickyname"));
            msg.setReceiver_ip(resultSet.getString("receiver_ip"));
            msg.setReceiver_account(resultSet.getString("receiver_account"));
            msg.setReceiver_os(resultSet.getString("receiver_os"));
            msg.setReceiver_phone_type(resultSet.getString("receiver_phone_type"));
            msg.setReceiver_network(resultSet.getString("receiver_network"));
            msg.setReceiver_gps(resultSet.getString("receiver_gps"));
            msg.setReceiver_sex(resultSet.getString("receiver_sex"));
            msg.setMsg_type(resultSet.getString("msg_type"));
            msg.setDistance(resultSet.getString("distance"));
            msg.setMessage(resultSet.getString("message"));

            msgList.add(msg);
        }

        // 关闭资源
        resultSet.close();
        preparedStatement.close();

        return msgList;
    }

    @Override
    public void close() throws Exception {
        connection.close();
    }
}

运行测试方法：

@Test
    public void getMesage() throws Exception {
        List message = chatMessageService.getMessage("2022-01-29", "13514684105", "13647128512");
        for (Msg msg : message) {
            System.out.println(msg);
        }
    }

4. 测试下sql

select
 *
from
"MOMO_CHAT"."MSG"
where
substr("msg_time", 0, 10) = '2022-01-29'
and "sender_account" = '13514684105'
and "receiver_account" = '13647128512';