Hologres is a real-time data warehouse that integrates serving and analytics. It supports massive datasets with over tens of billions of records and delivers low-latency (under 10 ms) Key/Value point query services with over one million queries per second (QPS) using SQL. This topic describes best practices for point query scenarios, including table creation and query methods.
Create a table
When you create a table for point query scenarios, keep the following points in mind:
-
Set the field used as the key in Key/Value point queries as the primary key.
-
Ensure that the primary key matches the clustering key.
-
Set the column used in query conditions as the distribution key. By default, the primary key is the distribution key.
-
Set the storage class of the table to row store.
-
Use the VPC domain name when you connect to your Hologres instance.
-
For columns of the TEXT, VARCHAR, or CHAR type, use TEXT instead of VARCHAR or CHAR.
The following example shows how to create such a table:
-- Create a row-oriented table named test_kv_table and set the key column 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 the table, you can perform point queries. You can use one of the following methods:
-
Query a single key:
select * from test_kv_table where key = '1'; -
Query multiple keys at once:
select * from test_kv_table where key in ('1', '2', '3'); -
Query data using Java
The following Java example uses a prepared statement for Key/Value queries:
Note-
Use the VPC domain name to connect to Hologres.
-
Using prepared statements improves query performance.
-
Reuse prepared statements instead of creating a new one for each query.
// Query multiple keys with values 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 using HoloClient
HoloClient simplifies development by merging multiple queries into a single SQL statement. Use the latest version published on Maven, as shown in the following example:
<dependency> <groupId>com.alibaba.hologres</groupId> <artifactId>holo-client</artifactId> <version>{1.2.16.5}</version> </dependency> // Configure parameters. The URL format is jdbc:postgresql://host:port/db HoloConfig config = new HoloConfig(); config.setJdbcUrl(url); config.setUsername(username); config.setPassword(password); config.setReadThreadCount(10); // Read concurrency; uses up to 10 JDBC connections 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 usage example
The following complete Java example shows how to create a row-oriented table named test_kv_table that uses the key column as the primary key, query multiple keys, and print the results:
package test;
import org.postgresql.jdbc.PgConnection;
import java.sql.*;
// Create a row-oriented table named test_kv_table and set the key column 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 keys with values 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");
}
}
}
}
}
// Print query results
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);
}
}
}Parameter descriptions:
-
host: The domain name of your Hologres instance. You can find this domain name in the Network Information section on the instance details page in the Hologres console.
-
db: The name of the database in your Hologres instance.
-
user: The AccessKey ID of your Alibaba Cloud account. You can obtain this ID on the AccessKey Management page.
-
password: The AccessKey secret of your Alibaba Cloud account.