如何建立表及對錶資料進行點查詢 - 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。