AnalyticDB:Write data by using the Client SDK

Maven repositories

You can use Maven to manage and configure the SDK version. The following is the Maven configuration:

<dependency>
  <groupId>com.alibaba.cloud.analyticdb</groupId>
  <artifactId>adb4pgclient</artifactId>
  <version>1.0.16</version>
</dependency>

You can download the offline JAR packages from the following links: adb4pgclient-1.0.16.jar and adb4pgclient-1.0.16-jar-with-dependencies.jar.

Methods

Code example

public class Adb4pgClientUsage {
    public void demo() {
        DatabaseConfig databaseConfig = new DatabaseConfig();
        // Should set your database real host or url
        databaseConfig.setHost("100.100.100.100");
        // Should set your database real port
        databaseConfig.setPort(8888);
        // The username for the database connection.
        databaseConfig.setUser("your user name");
        // The password for the database connection.
        databaseConfig.setPassword("your password");
      // The database to connect to.
        databaseConfig.setDatabase("your database name");
        // Set the list of tables to write to.
        List<String> tables = new ArrayList<String>();
        tables.add("your table name 1");
        tables.add("your table name 2");

        // You can call addTable separately for tables in different schemas. However, after a Client instance is created from the DatabaseConfig, the table configuration cannot be changed. /
        // If you pass null for the schema, the default schema 'public' is used.
        databaseConfig.addTable(tables, "table schema name");

        // Set the columns of the table to write to.
        List<String> columns = new ArrayList<String>();
        columns.add("column1");
        columns.add("column2");
        // To write to all columns, use columns.add("*").
        databaseConfig.setColumns(columns, "your table name 1", "table schema name");
        databaseConfig.setColumns(Collections.singletonList("*"),"your table name 2", "table schema name");


        // If the value of column is empty, set null
        databaseConfig.setEmptyAsNull(false);
        // Use the 'insert ignore' method for insertion.
        databaseConfig.setInsertIgnore(true);
        // Retry three times if an exception occurs when writing to the database during a commit.
        databaseConfig.setRetryTimes(3);
        // The retry interval is 1s. The unit is ms.
        databaseConfig.setRetryIntervalTime(1000);
        // Initialize AdbClient. After the instance is initialized, the DatabaseConfig settings cannot be changed.
        Adb4pgClient adbClient = new Adb4pgClient(databaseConfig);

        // Batch the data. Add multiple times, then commit. For details on batch size, see the "Notes" section.
        for (int i = 0; i < 10; i++) {
            // Add row(s) to buffer. One instance for one record
            Row row = new Row(columns.size());
            // Set column
            // the column index must be same as the sequence of columns
            // the column value can be any type, internally it will be formatted according to column type
            row.setColumn(0, i); // Number value
            row.setColumn(1, "string value"); // String value
            // If the buffer is full, an autocommit is triggered during an addRow or addMap call.
            // If the commit fails, an AdbClientException is returned with the error code COMMIT_ERROR_DATA_LIST.
            adbClient.addRow(row, "your table name 1", "table schema name");
        }

        Row row = new Row();
        row.setColumn(0, 10); // Number value
        row.setColumn(1, "2018-01-01 08:00:00"); // Date/Timestamp/Time value
        adbClient.addRow(row, "your table name 1", "table schema name");
        // Update a column. The Row instance can be reused.
        row.updateColumn(0, 11);
        row.updateColumn(1, "2018-01-02 08:00:00");
        adbClient.addRow(row, "your table name 1", "table schema name");

        // Add map(s) to buffer
        Map<String, String> rowMap = new HashMap<String, String>();
        rowMap.put("t1", "12");
        rowMap.put("t2", "string value");
        // Batching is required here. We recommend committing after multiple add calls.
        adbClient.addMap(rowMap, "your table name 2", "table schema name");

        // Commit buffer to ADS
        // Buffer is cleaned after successfully commit to ADS
        try {
            adbClient.commit();
        } catch (Exception e) {
            // TODO: Handle exception here
        } finally {
            adbClient.stop();
        }
    }

}

Notes

• The AnalyticDB for PostgreSQL Client SDK is not thread-safe. In a multi-threaded environment, each thread must maintain its own `Client` object.

  Important

  We do not recommend sharing an SDK instance among multiple threads. In addition to thread safety issues, a shared client can easily become a write performance bottleneck.

• Data is considered successfully written to AnalyticDB for PostgreSQL only after the `commit` call succeeds.

• When the client throws an exception, you must decide how to handle it based on the error code. If a data writing error occurs, you can resubmit the data, or log the problematic data and then skip it.

• Using more write threads does not always result in better performance. Because applications often batch data, memory consumption can be high. Therefore, you must closely monitor the application's garbage collection (GC) status.

• Do not use a batch size that is too small. Otherwise, the benefits of batch writing are lost. If possible, we recommend adding at least 10,000 records before you commit the data.

• You must set all `DatabaseConfig` configuration items before the client object is initialized. These settings cannot be changed after the client object is instantiated.

• The Client SDK is designed to optimize write (INSERT) performance. For other SQL operations, retrieve a JDBC connection using the getConnection() method and handle the operations through standard JDBC interfaces.