如何创建表及对表数据进行点查询 - 实时数仓Hologres

Hologres是一款服务分析一体化的实时数仓，支持海量数据集（百亿以上），基于SQL提供低延时（10ms以下）、高QPS（上百万每秒）的Key/Value点查服务。本文主要指导您在点查场景的最佳实践，包括建表和查询方式等。

创建表

创建点查场景的表时，您需要注意如下几点：

Key/Value点查的Key字段需要设置为主键（Primary Key）。
Primary Key与Clustering Key保持一致。
需要将查询条件中的列设置为Distribution Key，一般默认主键为Distribution Key。
表的存储类型需要设置为行存。
建议连接Hologres实例时使用VPC网络的域名。
当有TEXT、VARCHAR、CHAR类型时，建议使用TEXT类型，而不是VARCHAR或者CHAR类型。

具体创建表的示例语句如下：

--创建一张行存表test_kv_table并将key字段设置为主键
begin;
create table test_kv_table(
  key text primary key,
  value text
);
call set_table_property('test_kv_table', 'orientation', 'row');
call set_table_property('test_kv_table', 'clustering_key', 'key');
call set_table_property('test_kv_table', 'distribution_key', 'key');
commit;

数据查询

当您将数据导入表之后就可以对表数据进行点查询。具体查询方式如下所示：

一次查询单个Key

select * from test_kv_table where key  = '1';

一次查询多个Key

select * from test_kv_table where key  in ('1', '2', '3');

使用Java查询

以Java为例，您可以使用Prepared Statement来进行Key/Value查询，示例如下。

说明

建议使用Hologres的VPC网络的域名连接。
使用Prepared Statement进行Key/Value查询会得到更好的查询性能。
Prepared Statement可以复用，不需要每次查询都新建一个Prepared Statement对象。

//查询取值1-100的多个key   
 private static void testKV(Connection conn) throws Exception {
        String sql = "select * from test_kv_table where key = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < 100; ++i) {
                stmt.setString(1, Integer.toString(i));
                long begin = System.currentTimeMillis();
                try (ResultSet rs = stmt.executeQuery()) {
                long cost = System.currentTimeMillis() - begin;
                    while(rs.next()) {
                        System.out.println("data => " + rs.getObject(1).toString() + " " + rs.getObject(2).toString() + " latency => [" + cost + "]ms");
                    }
                }
            }
        }
    }

使用HoloClient查询

HoloClient会将多个查询合并为一个SQL语句，简化开发复杂度，示例如下，建议使用Maven上发布的最新版本。

<dependency>
  <groupId>com.alibaba.hologres</groupId>
  <artifactId>holo-client</artifactId>
  <version>{1.2.16.5}</version>
</dependency>


// 配置参数,url格式为 jdbc:postgresql://host:port/db
HoloConfig config = new HoloConfig();
config.setJdbcUrl(url);
config.setUsername(username);
config.setPassword(password);
config.setReadThreadCount(10);//读并发，最多占用10个jdbc连接
try (HoloClient client = new HoloClient(config)) {
    //create table t0(id int not null,name0 text,address text,primary key(id))
    TableSchema schema0 = client.getTableSchema("t0");
    
    Get get = Get.newBuilder(schema).setPrimaryKey("id", 0).build(); // where id=0;
    client.get(get).thenAcceptAsync((record)->{
        // do something after get result
    });
    Get get1 = Get.newBuilder(schema).setPrimaryKey("id", 1).build(); // where id=1;
    client.get(get1).thenAcceptAsync((record)->{
        // do something after get result
    });
catch(HoloClientException e){
}

Java使用示例

如下内容为完整的Java使用示例，指导您创建一张行存表test_kv_table并将key字段设置为主键，一次查询多个Key并输出查询结果。

package test;

import org.postgresql.jdbc.PgConnection;
import java.sql.*;

//创建一张行存表test_kv_table并将key字段设置为主键
public class TestPointQuery {

    private static void init(Connection conn) throws Exception {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("drop table if exists test_kv_table;");
            stmt.execute("begin;");
            stmt.execute("create table if not exists test_kv_table(key text primary key, value text);");
            stmt.execute("call set_table_property('test_kv_table', 'orientation', 'row');");
            stmt.execute("call set_table_property('test_kv_table', 'shard_count', '20');");
            stmt.execute("end;");
            stmt.execute("insert into test_kv_table select i, i from generate_series(1, 10000)i");
        }
    }
//查询取值1-100的多个key
    private static void testKV(Connection conn) throws Exception {
        String sql = "select *from test_kv_table where key = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < 100; ++i) {
                stmt.setString(1, Integer.toString(i));
                long begin = System.currentTimeMillis();
                try (ResultSet rs = stmt.executeQuery()) {
                long cost = System.currentTimeMillis() - begin;
                    while(rs.next()) {
                        System.out.println("data => " + rs.getObject(1).toString() + " " + rs.getObject(2).toString() + " latency => [" + cost + "]ms");
                    }
                }
            }
        }
    }
//输出查询结果
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver").newInstance();
        String host = "";
        String db = "";
        String user = "";
        String password = "";
        String url = "jdbc:postgresql://" + host + "/" + db;

        try (PgConnection conn = (PgConnection) DriverManager.getConnection(url, user, password)) {
            System.out.println("init the test_kv_table for testing");
            init(conn);
            System.out.println("run test on test_kv_table");
            testKV(conn);
        }
    }
}

参数说明：

host：Hologres实例的域名，您可在Hologres控制台实例详情页的网络信息区域获取域名。
db：Hologres实例下的数据库名称。
user：阿里云账号的AccessKey ID。您可以进入AccessKey管理页面获取AccessKey ID。
password：对应阿里云账号的AccessKey Secret。