Holo Client is developed by the Hologres team based on Java Database Connectivity (JDBC). Holo Client allows you to write large amounts of data at a time and perform point queries that support a high volume of queries per second (QPS). For example, you can use Holo Client to synchronize large amounts of data to Hologres in real time or query data in a table that is associated with a dimension table. Holo Client automatically batch collects data to improve read/write performance and throughput. You can use Holo Client together with JDBC and still use JDBC for queries. This topic describes how to use Holo Client.

Note Holo Client does not replace the role of JDBC, but rather provides features that are not supported by JDBC. In scenarios where JDBC is available, you can still use JDBC to query data.

Usage notes

When you use Holo Client in Hologres, take note of the following rules:
  • You can use Holo Client to read and write data only if you use an instance of Hologres V0.9 or later. To upgrade an instance, submit a ticket or join the Hologres DingTalk group for technical support.
  • You cannot use Holo Client to query foreign tables, views, or system tables of Hologres.
  • Holo Client works based on JDBC. Before you use Holo Client, you can use the following method to check the remaining quota of connections for your Hologres instance:
    • Execute the following statement to query the maximum number of connections allowed for a single frontend node:
      -- Query the maximum number of connections allowed for a single frontend node.
      show max_connections;
    • Execute the following statement to query the number of connections that you have created:
      select count(*) from pg_stat_activity where backend_type='client backend';

Holo Client versions

  • All Holo Client versions are published in the Maven repository. For more information about Holo Client versions, visit Holo Client.
  • For information about the source code of Holo Client, see holo-client.

Connect Hologres to Holo Client

You can use one of the following methods to connect Hologres to Holo Client:
  • Maven
    <dependency>
      <groupId>com.alibaba.hologres</groupId>
      <artifactId>holo-client</artifactId>
      <version>1.2.16.5</version>
    </dependency>
  • Gradle
    implementation 'com.alibaba.hologres:holo-client:1.2.16.5'

Known issues in Holo Client versions

  • In Holo Client V1.2.8, if the INSERT_OR_IGNORE or INSERT_OR_UPDATE policy is used, data may not be sorted for INSERT and DELETE operations. This issue is fixed as of Holo Client V1.2.10.3.
  • In Holo Client V1.2.6, the GetBuilder.withSelectedColumns method does not take effect. All columns instead of selected columns are specified each time. This issue is fixed as of Holo Client V1.2.12.1.
  • In Holo Client V1.2.9.1, if withSelectedColumn is specified for the SCAN operation, data cannot be queried. This issue is fixed as of Holo Client V1.2.12.1.
  • In Holo Client V1.2.0, if the primary key contains columns of the BYTEA type, results cannot be returned for GET requests and data may not be sorted for PUT requests. This issue is fixed as of Holo Client V1.2.12.1.
  • In Holo Client V1.2.0, if the value of a hash partition key is Integer.MIN_VALUE, data may fail to be written. This issue is fixed as of Holo Client V1.2.12.1.

Notes on connections

  • The number of connections that Holo Client establishes at a time cannot exceed the result of the Max(writeThreadSize,readThreadSize) function.
  • If a connection does not send or receive data by the time when the idle timeout period that is specified by the connectionMaxIdleMs parameter ends, the connection is released.
  • If the remaining quota of connections for your Hologres instance is insufficient, Holo Client automatically creates connections to meet your business needs.

Write data

We recommend that you use Holo Client in a singleton pattern and manage the concurrency of read and write requests by using the writeThreadSize and readThreadSize parameters.
Note Each parallel task occupies one JDBC connection. The connectionMaxIdleMs parameter specifies an idle timeout period that applies to connections. If a connection does not send or receive data by the time when the idle timeout period ends, the connection is automatically released.
  • Write data to a standard table
    • Sample code
      // 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);
      
      try (HoloClient client = new HoloClient(config)) {
          TableSchema schema0 = client.getTableSchema("t0");
          Put put = new Put(schema0);
          put.setObject("id", 1);
          put.setObject("name", "name0");
          put.setObject("address", "address0");
          client.put(put); 
          ...
          client.flush(); // Forcibly submit all PUT requests that have not been submitted. Holo Client submits requests based on the values of the writeBatchSize, writeBatchByteSize, and writeMaxIntervalMs parameters.
      }catch(HoloClientException e){
      }
    • For more information, see Parameters in the HoloConfig object.
  • Write data to a partitioned table
    • If partitions exist, Holo Client routes data to the corresponding partitions in the partitioned table regardless of the value of the DynamicPartition parameter.
    • If partitions do not exist and the DynamicPartition parameter is set to true, Holo Client creates the specified partitions. If partitions do not exist and the DynamicPartition parameter is set to false, an error is returned.
    • You can obtain favorable performance when you write data to a partitioned table in an instance of Hologres V0.9 or later. If you want to write data to a partitioned table in an instance of Hologres V0.8, we recommend that you write the data to a temporary table and then execute the INSERT INTO SELECT statement.
      • Sample code
        // 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.setDynamicPartition(true); // Create partitions if the specified partitions do not exist.
        
        try (HoloClient client = new HoloClient(config)) {
            //create table t0(id int not null,region text not null,name text,primary key(id,region)) partition by list(region)
            TableSchema schema0 = client.getTableSchema("t0");
            Put put = new Put(schema0);
            put.setObject("id", 1);
            put.setObject("region", "SH");
            put.setObject("name", "name0");
            client.put(put); 
            ...
            // Holo Client submits requests in batches based on the values of the writeBatchSize, writeBatchByteSize, and writeMaxIntervalMs parameters. You do not need to call the flush() method in most cases. 
            // If you call the flush() method, all PUT requests that have not been submitted are forcibly submitted. This method has a great influence on the performance of Holo Client and needs to be called only when necessary. 
            //client.flush();
        }catch(HoloClientException e){
        }
      • For more information, see Parameters in the HoloConfig object.
  • Write data to a table that contains a primary key
    • Sample code
      // 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.setWriteMode(WriteMode.INSERT_OR_REPLACE); // Configure a policy that handles primary key conflicts.
      
      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");
          Put put = new Put(schema0);
          put.setObject("id", 1);
          put.setObject("name0", "name0");
          put.setObject("address", "address0");
          client.put(put); 
          ...
          put = new Put(schema0);
          put.setObject(0, 1);
          put.setObject(1, "newName");
          put.setObject(2, "newAddress");
          client.put(put);
          ...
          // Holo Client submits requests in batches based on the values of the writeBatchSize, writeBatchByteSize, and writeMaxIntervalMs parameters. You do not need to call the flush() method in most cases. 
          // If you call the flush() method, all PUT requests that have not been submitted are forcibly submitted. This method has a great influence on the performance of Holo Client and needs to be called only when necessary. 
          //client.flush();
      }catch(HoloClientException e){
      }
    • For more information, see Parameters in the HoloConfig object.
  • Delete data based on a primary key
    • Sample code
      // 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.setWriteMode(WriteMode.INSERT_OR_REPLACE); // Configure a policy that handles primary key conflicts.
      
      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");
          Put put = new Put(schema0);
          put.getRecord().setType(SqlCommandType.DELETE);
          put.setObject("id", 1);
          client.put(put); 
          ...
          // Holo Client submits requests in batches based on the values of the writeBatchSize, writeBatchByteSize, and writeMaxIntervalMs parameters. You do not need to call the flush() method in most cases. 
          // If you call the flush() method, all PUT requests that have not been submitted are forcibly submitted. This method has a great influence on the performance of Holo Client and needs to be called only when necessary. 
          //client.flush();
      }catch(HoloClientException e){
      }
    • For more information, see Parameters in the HoloConfig object.

Read data

Holo Client allows you to read data by using different methods. For example, you can read data based on a complete primary key or by performing the SCAN operation.
  • Read data based on a complete primary key
    • Sample code
      // 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);
      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=1;
          client.get(get).thenAcceptAsync((record)->{
              // do something after get result
          });
      }catch(HoloClientException e){
      }
    • For more information, see Parameters in the HoloConfig object.
  • Read data by performing the SCAN operation
    • Sample code
      // 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);
      try (HoloClient client = new HoloClient(config)) {
          //create table t0 (id int not null,name text not null,address text,primary key(id,name))
          TableSchema schema0 = client.getTableSchema("t0");
          
          Scan scan = Scan.newBuilder(schema0).addEqualFilter("id", 102).addRangeFilter("name", "3", "4").withSelectedColumn("name").withSelectedColumn("address").build();
          // This statement is equivalent to the select name, address from t0 where id=102 and name>=3 and name<4; statement. 
          int size = 0;
          try (RecordScanner rs = client.scan(scan)) {
              while (rs.next()) {
                  Record record = rs.getRecord();
                  //handle record
              }
          }
      }catch(HoloClientException e){
      }
    • For more information, see Parameters in the HoloConfig object.
  • Read data by performing other operations

    For more information, see the JDBC topic.

Consume binary logs

Hologres V1.1 and later allow you to use Holo Client to consume binary logs. For more information about Hologres binary logs, see Subscribe to Hologres binlogs and Use JDBC to consume Hologres binary logs (Beta).

Customize operations

  • Sample code
    HoloConfig config = new HoloConfig();
    config.setJdbcUrl(url);
    config.setUsername(username);
    config.setPassword(password);
    try (HoloClient client = new HoloClient(config)) {
        client.sql(conn -> {
                    try (Statement stat = conn.createStatement()) {
                        stat.execute("create table t0(id int)");
                    }
                    return null;
                }).get();
    }catch(HoloClientException e){
    }
  • For more information, see Parameters in the HoloConfig object.

Handle exceptions

You can capture the HoloClientWithDetailsException exceptions for PUT and FLUSH requests. These exceptions contain the details of entries that cannot be written. The following sample code provides an example on how to capture the HoloClientWithDetailsException exceptions:
public void doPut(HoloClient client, Put put) throws HoloClientException {
    try{
        client.put(put);
    }catch(HoloClientWithDetailsException e){
        for(int i=0;i<e.size();++i){
            // Obtain the entries that failed to be written.
            Record failedRecord = e.getFailRecord(i);
            // Obtain the reason why the entries failed to be written.
            HoloClientException cause = e.getException(i);
            // Process dirty data.
        }
    }catch(HoloClientException e){
        // The exceptions except for HoloClientWithDetailsException exceptions are fatal.
        throw e;
    }
}

public void doFlush(HoloClient client) throws HoloClientException {
    try{
        client.flush();
    }catch(HoloClientWithDetailsException e){
        for(int i=0;i<e.size();++i){
            // Obtain the entries that failed to be written.
            Record failedRecord = e.getFailRecord(i);
            // Obtain the reason why the entries failed to be written.
            HoloClientException cause = e.getException(i);
            // Process dirty data.
        }
    }catch(HoloClientException e){
        // The exceptions except for HoloClientWithDetailsException exceptions are fatal.
        throw e;
    }
}

Parameters in the HoloConfig object

The sample code in this topic involves parameters in the HoloConfig object. The following tables describe these parameters.
  • Basic configurations
    Parameter Required Description Version
    jdbcUrl Yes The URL of the JDBC connection. The URL is in the format of jdbc:postgresql://host:port/db and includes the following variables:
    • host:port: the endpoint of the destination Hologres instance.
    • db: the name of the destination Hologres database.
    You can view the preceding information on the Configurations tab of the instance details page in the Hologres console.
    1.2.3
    username Yes The AccessKey ID of your Alibaba Cloud account. You can obtain the AccessKey ID on the Security Management page. 1.2.3
    password Yes The AccessKey secret of your Alibaba Cloud account. You can obtain the AccessKey secret on the Security Management page. 1.2.3
    appName No The name of the application that uses the JBDC connection. Default value: holo-client. 1.2.9.1
  • Data writing configurations
    Parameter Default value Description Version
    dynamicPartition false Specifies whether to automatically create partitions if the specified partitions do not exist. Valid values:
    • true: automatically creates the specified partitions.
    • false: does not automatically create the specified partitions.
    1.2.3
    writeMode INSERT_OR_REPLACE The policy that is used to handle primary key conflicts. This parameter applies when the destination table contains primary keys. Valid values:
    • INSERT_OR_IGNORE: discards the data to be written if a conflict occurs.
    • INSERT_OR_UPDATE: updates the corresponding columns of the destination table if a conflict occurs.
    • INSERT_OR_REPLACE: updates all columns of the destination table if a conflict occurs.
    1.2.3
    writeBatchSize 512 The maximum number of requests allowed in a batch in a thread to write data. If the total number of PUT requests reaches the value of the writeBatchSize parameter after conflicts are handled based on the writeMode parameter, the data is submitted in a batch. 1.2.3
    writeBatchByteSize 2MB The maximum number of bytes allowed in a batch in a thread to write data. If the total number of bytes of the PUT requests reaches the value of the writeBatchByteSize parameter after conflicts are handled based on the writeMode parameter, the data is submitted in a batch. 1.2.3
    writeBatchTotalByteSize 20MB The maximum number of bytes allowed in a batch for all tables. If the total number of bytes of the PUT requests reaches the value of the writeBatchByteSize parameter after conflicts are handled based on the writeMode parameter, the data is submitted in a batch. 1.2.8.1
    writeMaxIntervalMs 10000 ms The maximum amount of time allowed for data to be accumulated in a batch. 1.2.4
    writeFailStrategy TYR_ONE_BY_ONE The policy that is used to handle submission failures. If a batch failed to be submitted, Holo Client submits the data entries in the batch in the specified sequence. If a data entry failed to be submitted, Holo Client throws a HoloClientWithDatailsException exception that contains details of the data entry. 1.2.4
    writerShardCountResizeIntervalMs 30s The minimum interval between two consecutive RESIZE operations that are triggered when you call the flush() method. 1.2.10.1
    flushMaxWaitMs 60000ms The maximum amount of time to wait for a FLUSH operation to complete. The FLUSH operation is performed to forcibly submit all PUT requests that have not been submitted. 1.2.5
    inputNumberAsEpochMsForDatetimeColumn false Specifies whether to convert an input number to a timestamp that indicates the number of milliseconds that have elapsed since 00:00:00 Thursday, 1 January 1970 if the number is written to a column of the DATE, TIMESTAMP, or TIMESTAMPTZ type. 1.2.5
    inputStringAsEpochMsForDatetimeColumn false Specifies whether to convert an input string to a timestamp that indicates the number of milliseconds that have elapsed since 00:00:00 Thursday, 1 January 1970 if the string is written to a column of the DATE, TIMESTAMP, or TIMESTAMPTZ type. 1.2.6
    removeU0000InTextColumnValue true Specifies whether to remove \u0000 from a string if the string is written to a column of the TEXT, CHAR, or VARCHAR type. 1.2.10.1
    enableDefaultForNotNullColumn true Specifies whether to convert the null value to the specified default value when the null value is written to a column that adopts the NOT NULL constraint and has no default value specified.
    • If the destination column is of the STRING type, the null value is converted to an empty string ("").
    • If the destination column is of the NUMBER type, the null value is converted to 0.
    • If the destination column is of the DATE, TIMESTAMP, or TIMESTAMPTZ type, the null value is converted to 1970-01-01 00:00:00.
    1.2.6
    defaultTimeStampText null The default value that is used to replace the null value to be written to a column of the DATE, TIMESTAMP, or TIMESTAMPTZ type. This parameter takes effect if the enableDefaultForNotNullColumn parameter is set to true. 1.2.6
    reWriteBatchedDeletes true Specifies whether to combine multiple DELETE requests in an SQL statement to improve performance. 1.2.12.1
  • Data reading configurations
    Parameter Default value Description Version
    readThreadSize 1 The number of concurrent threads to perform point queries. Each thread occupies one connection. 1.2.4
    readBatchSize 128 The maximum number of requests allowed in a batch in a thread to perform point queries. 1.2.3
    readBatchQueueSize 256 The maximum number of queued requests allowed in a thread to perform point queries. 1.2.4
    scanFetchSize 256 The number of rows of data fetched after a SCAN operation is performed. 1.2.9.1
    scanTimeoutSeconds 256 The maximum amount of time to wait for a SCAN operation to complete. 1.2.9.1
  • Connection configurations
    Parameter Default value Description Version
    retryCount 3 The maximum number of retries allowed to read and write data if a connection failure occurs. 1.2.3
    retrySleepInitMs 1000ms The amount of time required for the initialization. The amount of time consumed by the retries for a request is calculated by using the following formula: Value of the retrySleepInitMs parameter + Number of retries × Value of the retrySleepStepMs parameter. 1.2.3
    retrySleepStepMs 10*1000ms The intervals at which retries are performed. The amount of time consumed by the retries for a request is calculated by using the following formula: Value of the retrySleepInitMs parameter + Number of retries × Value of the retrySleepStepMs parameter. 1.2.3
    connectionMaxIdleMs 60000ms The idle timeout period that applies to the connections that are used to read and write data. If a connection does not send or receive data by the time when the idle timeout period ends, Holo Client automatically releases the connection. 1.2.4
    metaCacheTTL 1min The time-to-live (TTL) period of the table schema information in the cache. 1.2.6
    metaAutoRefreshFactor 4 The factor that determines when to automatically refresh the cache. If the remaining TTL period of the table schema information in the cache is less than the result of dividing the value of the metaCacheTTL parameter by the value of the metaAutoRefreshFactor parameter, Holo Client automatically refreshes the cache. 1.2.10.1