Hologres:Best practices for key-value pair queries

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 specified 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 specified as the distribution key.

• The table must be a row-oriented table.

• We recommend that you connect to a 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 fields of the VARCHAR or CHAR type.

-- Create a row-oriented table named test_kv_table and specify 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 into a table, you can perform point queries on the data. You can execute the following sample statements in 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 enhances performance of querying key-value pairs.

  • The PreparedStatement object is reusable. You do not need to create a 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>
  
  
  // Configure the parameters. Specify the Java Database Connectivity (JDBC) URL in the jdbc:postgresql://host:port/db format.
  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

package test;

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

// Create a row-oriented table named test_kv_table and specify 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");
                    }
                }
            }
        }
    }
// Return 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);
        }
    }
}