This topic describes the data types and parameters that are supported by AnalyticDB for MySQL 3.0 Writer and how to configure AnalyticDB for MySQL 3.0 Writer by using the codeless user interface (UI) and code editor.

Before you configure AnalyticDB for MySQL 3.0 Writer, you must configure an AnalyticDB for MySQL 3.0 data source. For more information, see Configure an AnalyticDB for MySQL 3.0 connection.

Data types

The following table lists the data types that are supported by AnalyticDB for MySQL 3.0 Writer.
Category AnalyticDB for MySQL 3.0 data type
Integer INT, INTEGER, TINYINT, SMALLINT, and BIGINT
Floating point FLOAT, DOUBLE, and DECIMAL
String VARCHAR
Date and time DATE, DATETIME, TIMESTAMP, and TIME
Boolean BOOLEAN

Parameters

Parameter Description Required Default value
datasource The name of the data source. It must be the same as the name of the added data source. You can add data sources by using the code editor. Yes No default value
table The name of the table to which you want to write data. Yes No default value
writeMode The write mode. Valid values:
  • insert: If no primary key conflict or unique index conflict occurs, data is directly written to the destination table. If a primary key conflict or unique index conflict occurs, data cannot be written to conflicting rows, and the data that is not written to these rows is regarded as dirty data.
  • replace: If no primary key conflict or unique index conflict occurs, data is processed in the same way as that when you set this parameter to insert. If a conflict occurs, rows in the destination table are deleted, and new rows are inserted.
No insert
column The names of the columns to which you want to write data. Separate the names with commas (,), such as "column": ["id", "name", "age"]. If you want to write data to all the columns in the destination table, set this parameter to an asterisk (*), such as "column": ["*"].
Note If the column name contains select, enclose the column name in backticks (`). For example, item_select_no is presented as `item_select_no`.
Yes No default value
preSql The SQL statement that you want to execute before the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to delete outdated data. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor.
Note If you specify multiple SQL statements, the statements are not executed in the same transaction.
No No default value
postSql The SQL statement that you want to execute after the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to add a timestamp. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor.
Note If you specify multiple SQL statements, the statements are not executed in the same transaction.
No No default value
batchSize The number of data records to write at a time. Set this parameter to an appropriate value based on your business requirements. This greatly reduces the interactions between Data Integration and AnalyticDB for MySQL 3.0 and increases throughput. If you set this parameter to an excessively large value, an out of memory (OOM) error may occur during data synchronization. No 1,024

Configure AnalyticDB for MySQL 3.0 Writer by using the codeless UI

  1. Configure data sources.

    Configure Source and Target for the synchronization node.

    Parameter Description
    Connection The name of the data source to which you want to write data. This parameter is equivalent to the datasource parameter that is described in the preceding section.
    Table The name of the table to which you want to write data. This parameter is equivalent to the table parameter that is described in the preceding section.
    Statement Run Before Writing The SQL statement that you want to execute before the synchronization node is run. This parameter is equivalent to the preSql parameter that is described in the preceding section.
    Statement Run After Writing The SQL statement that you want to execute after the synchronization node is run. This parameter is equivalent to the postSql parameter that is described in the preceding section.
    Solution to Primary Key Violation The write mode. This parameter is equivalent to the writeMode parameter that is described in the preceding section.
    Data Records Per Write The number of data records to write at a time. This parameter is equivalent to the batchSize parameter that is described in the preceding section. The Data Records Per Write parameter takes effect only if the Solution to Primary Key Violation parameter is set to INSERT INTO.
  2. Configure field mappings. This operation is equivalent to setting the column parameter that is described in the preceding section. Fields in the source on the left have a one-to-one mapping with fields in the destination on the right.

Configure AnalyticDB for MySQL 3.0 Writer by using the code editor

For more information about how to configure a synchronization node by using the code editor, see Create a sync node by using the code editor.

In the following code, a synchronization node is configured to write data to an AnalyticDB for MySQL 3.0 database. For more information about the parameters, see the preceding parameter description.
Notice Delete the comments from the following code before you run the code.
{
    "type": "job",
    "steps": [
        {
            "stepType": "stream",
            "parameter": {},
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "analyticdb_for_mysql", // The writer type. 
            "parameter": {
                "postSql": [], // The SQL statement that you want to execute after the synchronization node is run. 
                "tableType": null, // The reserved field. Default value: null. 
                "datasource": "hangzhou_ads", // The name of the data source. 
                "column": [ // The names of the columns to which you want to write data. 
                    "id",
                    "value"
                ],
                "guid": null,
                "writeMode": "insert", // The write mode. For more information, see the description of the writeMode parameter. 
                "batchSize": 2048, // The number of data records to write at a time. For more information, see the description of the batchSize parameter. 
                "encoding": "UTF-8", // The encoding format. 
                "table": "t5", // The name of the table to which you want to write data. 
                "preSql": [] // The SQL statement that you want to execute before the synchronization node is run. 
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "version": "2.0", // The version number. 
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {
            "record": "0" // The maximum number of dirty data records allowed. 
        },
        "speed": {
            "throttle":true,// Specifies whether to enable bandwidth throttling. The value false indicates that bandwidth throttling is disabled, and the value true indicates that bandwidth throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
                        "concurrent":2, // The maximum number of parallel threads. 
                        "mbps":"12"// The maximum transmission rate.
        }
    }
}