Hologres provides interactive analysis and real-time data warehousing services. It supports tens of billions of datasets. Compatible with the SQL syntax, Hologres provides low latency of less than 10 milliseconds and million-level queries per second (QPS) for point queries of key-value pairs. This topic provides some 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.
  • You can involve only the distribution key in the query conditions of a point query. 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 a Hologres instance by using its virtual private cloud (VPC) endpoint.
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 the newly created 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.
    • A PreparedStatement object provides better performance for querying key-value pairs.
    • A PreparedStatement object is reusable. You do not need to create a PreparedStatement object for each query.
    // Query multiple key-value pairs where 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 one SQL statement to simplify development. The following sample code provides an example on how to query data by using Holo Client:
    <dependency>
      <groupId>com.alibaba.hologres</groupId>
      <artifactId>holo-client</artifactId>
      <version>1.2.13.5</version>
    </dependency>
    
    
    // Set the parameters. Specify the 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 you how to query key-value pairs in Java. The sample code can be invoked to perform the following operations: Create a row-oriented table named test_kv_table and set the key field as the primary key. Query multiple key-value pairs at a time. Return the query result.
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 where 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);
        }
    }
}