Use AnalyticDB for PostgreSQL Client SDK to write data - AnalyticDB

This topic describes how to use AnalyticDB for PostgreSQL Client SDK to write data to AnalyticDB for PostgreSQL by calling API operations.

AnalyticDB for PostgreSQL Client SDK supports custom development and integration of data write programs. A custom data write program that is developed by using AnalyticDB for PostgreSQL Client SDK simplifies the data write process and provides internal mechanisms, such as parallel processing. The write performance of this method is several times higher than the write performance of COPY and INSERT statements. You do not need to worry about connection pool or cache issues.

Note

AnalyticDB for PostgreSQL Client SDK is designed to efficiently write data. It does not read or process raw data.

Maven repositories

You can use Maven to configure the version of AnalyticDB for PostgreSQL Client SDK. Sample code:

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

Note

AnalyticDB for PostgreSQL Client SDK depends on the following packages: druid(1.1.17), postgresql(jdbc 42.2.5), commons-lang3(3.4), slf4j-api(1.7.24), and slf4j-log4j12(1.7.24).
If the system prompts version conflicts with AnalyticDB for PostgreSQL Client SDK, verify that you are using the correct versions of these packages.

JAR package of AnalyticDB for PostgreSQL Client SDK: adb4pgclient-1.0.4.jar.

Related operations

Table 1. Operations for the DatabaseConfig class
Operation	Description
setHost(String adbHost)	Specifies the endpoint that is used to connect to the AnalyticDB for PostgreSQL database.
setPort(int port)	Specifies the port number that is used to connect to the AnalyticDB for PostgreSQL database. Default value: 5432.
setDatabase(String database)	Specifies the name of the AnalyticDB for PostgreSQL database.
setUser(String username)	Specifies the database account that is used to connect to the AnalyticDB for PostgreSQL database.
setPassword(String pwd)	Specifies the password that is used to connect to the AnalyticDB for PostgreSQL database.
addTable(List<String> table, String schema)	Specifies the tables into which you want to insert data. You can call this operation multiple times. Each time you call this operation, you can specify tables that use the same schema. After you use the DatabaseConfig class to create an Adb4pgClient object, this operation no longer takes effect.
setColumns(List<String> columns, String tableName, String schemaName)	Specifies the columns that you want to insert into a table. If you want to insert all columns into a table, set the columns parameter to `columnList.add("*")`. You must specify columns for all tables that are listed in the table parameter. Otherwise, this operation fails the system check.
setInsertIgnore(boolean insertIgnore)	Specifies whether to ignore the rows that have conflicting primary keys. This operation takes effect on all the specified tables. Default value: false. In this case, the rows that have conflicting primary keys are overwritten.
setEmptyAsNull(boolean emptyAsNull)	Specifies whether to set empty values to null. This operation takes effect on all the specified tables. Default value: false.
setParallelNumber(int parallelNumber)	Specifies the maximum number of concurrent threads that are used to write data to the AnalyticDB for PostgreSQL database. This operation takes effect on all the specified tables. Default value: 4. We recommend that you retain the default value.
setLogger(Logger logger)	Specifies the logger that is used by the Adb4pgClient object. In AnalyticDB for PostgreSQL, slf4j.Logger is used.
setRetryTimes(int retryTimes)	Specifies the maximum number of retries to commit data when an error occurs. Default value: 3.
setRetryIntervalTime(long retryIntervalTime)	Specifies the interval between every two retries. Unit: milliseconds. Default value: 1000.
setCommitSize(long commitSize)	Specifies the amount of data that is automatically committed. Unit: bytes. Default value: 10,000,000. We recommend that you retain the default value.

Table 2. Operations for the Row class
Operation	Description
setColumn(int index, Object value)	Specifies the value of a column in the row. You can call this operation multiple times based on the sequence of columns in the row to specify multiple columns. This operation does not support the reuse of row objects. Each data record must be associated with a specific row object.
setColumnValues(List<Object> values)	Specifies the values of multiple columns in the row.
updateColumn(int index, Object value)	Updates the value of a column in the row. This operation allows you to reuse a row object by updating data.

Table 3. Operations for the Adb4pgClient class
Operation	Description
addRow(Row row, String tableName, String schemaName) / addRows(List<Row> rows, String tableName, String schemaName)	Inserts one or more row-formatted data records into a table. The data records are stored in the buffer of AnalyticDB for PostgreSQL Client SDK until the records are committed. If the amount of data records that are stored in the buffer reaches the value of the commitSize parameter, the system automatically commits the data records when you call the addRow or addRows operation. If the automatic commit fails, the system reports an error that contains the failed data records. You must handle the error based on the error information.
addMap(Map<String, String> dataMap,String tableName, String schemaName) / addMaps(List<Map<String, String>> dataMaps, String tableName, String schemaName)	Inserts one or more map-formatted data records into a table. The data records are stored in the buffer of AnalyticDB for PostgreSQL Client SDK. If the amount of data records that are stored in the buffer reaches the value of the commitSize parameter, the system automatically commits the data records when you call the addMap or addMaps operation. If the automatic commit fails, the system reports an error that contains the failed data records. You must handle the error based on the error information.
commit()	Commits the data records that are stored in the buffer of AnalyticDB for PostgreSQL Client SDK. If the commit fails, the system reports an error that contains the failed statements. You must handle the error based on the error information.
TableInfo getTableInfo(String tableName, String schemaName)	Obtains the schema of a table.
List<ColumnInfo> getColumnInfo(String tableName, String schemaName)	Obtains the columns of a table. You can call the `columnInfo.isNullable()` operation to check whether the ColumnInfo class can be null.
stop()	Releases the internal thread pools and resources that are used by the Adb4pgClient object after you use the object. If data records in the buffer of AnalyticDB for PostgreSQL Client SDK are not committed when you call this operation, the system reports an error. To forcefully release the internal thread pools and resources, call the `forceStop()` operation.
forceStop()	Forcefully releases the internal thread pools and resources that are used by the Adb4pgClient object. After you call this operation, data records that are not committed in the buffer of AnalyticDB for PostgreSQL Client SDK are lost. We recommend that you do not call this operation unless necessary.
Connection getConnection() throws SQLException	Retrieves the connection to the AnalyticDB for PostgreSQL database from the connection pool of the Adb4pgClient object. The retrieved connection works in the same manner as a Java Database Connectivity (JDBC) connection. You can use the retrieved connection to perform write operations except COPY. Note You must release the resources such as ResultSet, Statement, and Connection used by the Adb4pgClient object after you use the object.

Table 4. Operations for the ColumnInfo class
Operation	Description
boolean isNullable()	Specifies whether the ColumnInfo class can be null.

Error code	HTTP status code	Description
COMMIT_ERROR_DATA_LIST	101	The error returned because several data records failed to be committed. Note You can call the `e.getErrData()` operation to obtain the list of failed data records (List<String>). This error may be returned for the `addMap`, addMaps, `addRow`, addRows, and `commit` operations. You must separately handle this error for each operation.
COMMIT_ERROR_OTHER	102	The error returned because an exception other than a commit failure occurred.
ADD_DATA_ERROR	103	The error returned because data records failed to be added.
CREATE_CONNECTION_ERROR	104	The error returned because a connection failed to be established.
CLOSE_CONNECTION_ERROR	105	The error returned because a connection failed to be closed.
CONFIG_ERROR	106	The error returned because the configuration of the DatabaseConfig class failed.
STOP_ERROR	107	The error returned because the Adb4pgClient object failed to be stopped.
OTHER	999	The default error returned.

Sample code

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);
        // Specify the database account that is used to connect to your AnalyticDB for PostgreSQL database. 
        databaseConfig.setUser("your user name");
        // Specify the password that is used to connect to your AnalyticDB for PostgreSQL database. 
        databaseConfig.setPassword("your password");
      // Specify the name of your AnalyticDB for PostgreSQL database. 
        databaseConfig.setDatabase("your database name");
        // Specify the tables into which you want to insert data. 
        List<String> tables = new ArrayList<String>();
        tables.add("your table name 1");
        tables.add("your table name 2");

        // You can call the addTable operation to specify tables that use the same schema. After you use the DatabaseConfig class to create an Adb4pgClient object, the specified tables cannot be changed. /
        // If you set the table schema name parameter to null, the public schema is used. 
        databaseConfig.addTable(tables, "table schema name");

        // Specify the columns that you want to insert into a table. 
        List<String> columns = new ArrayList<String>();
        columns.add("column1");
        columns.add("column2");
        // If you want to insert all columns into a table, set the columns parameter to 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);
        // Specify whether to ignore the rows that have conflicting primary keys. 
        databaseConfig.setInsertIgnore(true);
        // If data fails to be committed into AnalyticDB for PostgreSQL, you can retry to commit the data up to three times. 
        databaseConfig.setRetryTimes(3);
        // Specify a 1000-millisecond retry interval. 
        databaseConfig.setRetryIntervalTime(1000);
        // Initialize the Adb4pgClient object. After you initialize the Adb4pgClient object, you cannot modify the configuration of the DatabaseConfig class. 
        Adb4pgClient adbClient = new Adb4pgClient(databaseConfig);

        // Store data records to the buffer of AnalyticDB for PostgreSQL Client SDK and then commit the records at a time. For more information, see the "Usage notes" section of this topic. 
        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 amount of data records in the buffer reaches the upper limit, the system automatically commits the records when you call the addRow or addMap operation. 
            // If the automatic commit fails, the system returns the COMMIT_ERROR_DATA_LIST error. 
            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 columns. A Row object 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");
        // If you want to commit multiple data records, we recommend that you store the records to the buffer of AnalyticDB for PostgreSQL Client SDK and then commit the records at a time. 
        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();
        }
    }

}

Usage notes

AnalyticDB for PostgreSQL Client SDK is not thread-safe. If you call more than one thread, make sure that each thread maintains its own Adb4pgClient object.
Important
If multiple threads share the same Adb4pgClient object, security issues arise and data write performance declines.
A data record is considered written to AnalyticDB for PostgreSQL only after the record is committed.
If the Adb4pgClient object returns an error, you must handle the error based on the error information. If a data record cannot be committed, you can retry to commit the record. You can also skip the failed data record after you take note of the record.
When you write data to AnalyticDB for PostgreSQL, the data records may be stored to the buffer and batch committed. This increases CPU utilization. We recommend that you maintain an appropriate number of threads and monitor the garbage collection status of your application.
We recommend that you commit 10,000 data records at a time.
You must complete the configuration of the DatabaseConfig class before you create an Adb4pgClient object. After you create the Adb4pgClient object, you cannot modify the configuration of the DatabaseConfig class.
AnalyticDB for PostgreSQL Client SDK aims to optimize data writes (INSERT statements). To optimize the execution of other SQL statements, call the getConnection() operation to establish a JDBC connection.