Hologres provides interactive analysis and real-time data warehousing services. It supports tens of billions of datasets. Compatible with SQL syntax, Hologres provides a low latency of less than 10 milliseconds for point queries of key-value pairs and can handle millions of queries per second (QPS). This topic describes the best practices of Hologres in point query scenarios, such as creating a table and querying data.

Create a table

When you create a Hologres table that supports point queries of key-value pairs, take note of the following items:

  • The key field must be set as the primary key.
  • The primary key must be the same as the clustering key.
  • The query conditions of a point query must include the distribution key. By default, the primary key is set as the distribution key.
  • The table must be a row-oriented table.
  • We recommend that you connect to the Hologres instance by using its Virtual Private Cloud (VPC) endpoint.
  • If table fields of the TEXT, VARCHAR, and CHAR types are available, we recommend that you use fields of the TEXT type instead of the fields of the VARCHAR or CHAR type.
For example, you can execute the following statements to create a table named test_kv_table:
-- Create a row-oriented table named test_kv_table and set the key field as the primary 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;

Query data

After you import data to a table, you can perform point queries on the data. You can execute the following sample statements based on different scenarios:

  • Query a single key-value pair at a time.
    select * from test_kv_table where key  = '1';
  • Query multiple key-value pairs at a time.
    select * from test_kv_table where key  in ('1', '2', '3');
  • Query data in Java.
    In Java, you can use a PreparedStatement object to query key-value pairs, as demonstrated in the following example.
    Note
    • We recommend that you connect to a Hologres instance by using its VPC endpoint.
    • The PreparedStatement object provides better performance for querying key-value pairs.
    • The PreparedStatement object is reusable. You do not need to create the PreparedStatement object for each query. 
    // Query multiple key-value pairs in which the values of the key field are integers from 1 to 100.   
 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");
                    }
                }
            }
        }
    }
  • Query data by using Holo Client.
    Holo Client combines multiple queries into a single SQL statement to simplify development. The following sample code provides an example on how to query data by using Holo Client. We recommend that you use the latest version of Holo Client released in the Maven Central Repository. 
    <dependency>
  <groupId>com.alibaba.hologres</groupId>
  <artifactId>holo-client</artifactId>
  <version>{1.2.16.5}</version>
</dependency>


// Set the parameters. Specify the Java Database Connectivity (JDBC) URL in the format of jdbc:postgresql://host:port/db.
HoloConfig config = new HoloConfig();
config.setJdbcUrl(url);
config.setUsername(username);
config.setPassword(password);
config.setReadThreadCount(10);// Specify the maximum number of concurrent read threads allowed. In this example, a maximum of 10 JDBC connections can be occupied at a time.
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){
}

Sample Java code for querying key-value pairs

The following sample code shows how to query key-value pairs in Java. In this example, a row-oriented table named test_kv_table is created and the key field is set as the primary key. Multiple key-value pairs are queried at a time and the query result is returned. 
package test;

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

// Create a row-oriented table named test_kv_table and set the key field as the primary 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");
        }
    }
// Query multiple key-value pairs in which the values of the key field are integers from 1 to 100.
    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");
                    }
                }
            }
        }
    }
// Obtain the query result.
    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);
        }
    }
}