Use the AnalyticDB for MySQL Client to efficiently write data to a 2.0 cluster

Background information

The AnalyticDB for MySQL Client provides an efficient and simple way to insert data into an AnalyticDB for MySQL 2.0 cluster. You can submit data to the AnalyticDB for MySQL Client interface to insert it directly into the AnalyticDB for MySQL 2.0 cluster. When you use the AnalyticDB for MySQL Client, you do not need to manage partition aggregation or connection pools. This provides you with more control over real-time data writing and removes the dependency on services such as DataHub.

Maven repositories

Use the following Maven dependency to manage the software development kit (SDK) version:

<dependency>
  <groupId>com.alibaba.cloud.analyticdb</groupId>
  <artifactId>adbclient</artifactId>
  <version>1.0.2</version>
</dependency>

API reference

DatabaseConfig class

Row class

AdbClient class

ColumnInfo class

AdbClientException error codes

Sample code

public class AdbClientUsage {
        public void demo() {
            DatabaseConfig databaseConfig = new DatabaseConfig();
            // The hostname or URL of the AnalyticDB for MySQL 2.0 cluster.
            databaseConfig.setHost("100.100.100.100");
            // The port number of the AnalyticDB for MySQL 2.0 cluster.
            databaseConfig.setPort(8888);
            // The AccessKey ID of the account for the AnalyticDB for MySQL 2.0 cluster.
            databaseConfig.setUser("your db username");
            // The AccessKey secret of the account for the AnalyticDB for MySQL 2.0 cluster.
            databaseConfig.setPassword("your db password");
            databaseConfig.setDatabase("your db name");
            // Set the list of table names to write to.
            List<String> tables = new ArrayList<String>();
            tables.add("your table name");
            tables.add("your table name 2");
            // After a new Client instance is created, the table configuration cannot be modified.
            databaseConfig.setTable(tables);

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

            // Specifies whether to skip failed insertions.
            // Ignoring insertion failures may cause data loss.
            databaseConfig.setIgnoreInsertError(false);
              // If a column value is empty, it is set to null.
            databaseConfig.setEmptyAsNull(true);
            // Use the insert ignore into method for insertion.
            databaseConfig.setInsertIgnore(true);
            // Retry 3 times if an exception occurs when writing to the AnalyticDB for MySQL 2.0 cluster during a commit.
            databaseConfig.setRetryTimes(3);
            // The retry interval is 1s (1000 ms).
            databaseConfig.setRetryIntervalTime(1000);
            // Initialize the AdbClient. After the instance is initialized, the DatabaseConfig settings cannot be changed.
            AdbClient adbClient = new AdbClient(databaseConfig);

            // Batch data and add it in multiple parts before committing.
            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 the same as the sequence of columns.
                // The column value can be any type. It will be formatted internally according to the column type.
                row.setColumn(0, i); // Number value
                row.setColumn(1, "string value"); // String value
                // If the SQL length limit is reached, an autocommit is triggered during addRow or addMap.
                // If the commit fails, an AdbClientException with the error code COMMIT_ERROR_DATA_LIST is returned.
                adbClient.addRow("your table name", row);
            }
            Row row = new Row();
            row.setColumn(0, 10); // Number value
            row.setColumn(1, "2018-01-01 08:00:00"); // The data type of the second parameter is Date, Timestamp, or Time value.
            adbClient.addRow("your table name 2", row);
            // Update column. The Row instance can be reused.
            row.updateColumn(0, 11);
            row.updateColumn(1, "2018-01-02 08:00:00");
            adbClient.addRow("your table name 2", row);

            // Add the map to the cache.
            Map<String, String> rowMap = new HashMap<String, String>();
            rowMap.put("column1", "124");
            rowMap.put("column2", "string value");
            // Batch data. It is best to add data multiple times before committing.
            adbClient.addMap("your table name", rowMap);

            // Commit the cached data to the AnalyticDB for MySQL 2.0 cluster.
            // After the data is successfully committed, the cached data is purged.
            try {
                adbClient.commit();
            } catch (Exception e) {
            } finally {
                adbClient.stop();
            }
        }
    }

Precautions

• The AnalyticDB for MySQL Client depends on druid(1.1.10), mysql-connector-java(5.1.45), commons-lang3(3.4), slf4j-api(1.7.25), and slf4j-log4j12(1.7.25). If a version conflict occurs, check the versions of these packages and resolve the conflict.

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

  Note

  We strongly recommend that you do not share an SDK instance across multiple threads. This can cause thread safety issues and create a performance bottleneck at the client.

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

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

• Using more write threads does not always improve performance. Because business applications often involve batching data, memory consumption can be significant. The caller must monitor the application's garbage collection (GC) status.

• Avoid setting a small data batch size, because this reduces the effectiveness of partition-based aggregate writes. You can estimate the number of messages per batch using the following formula: Number of messages * Message length ≈ Number of partitions * 32 KB.

• The DatabaseConfig settings cannot be changed after new AdbClient is called successfully. All configuration items must be set before the Client object is initialized.