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

Data Integration can import data to AnalyticDB for MySQL 2.0 in real time. This method requires you to create real-time tables, which are fact tables, in the destination AnalyticDB for MySQL 2.0 database in advance. In real-time import mode, data is imported efficiently and the process is simple.

You must configure a connection before configuring AnalyticDB for MySQL 2.0 Writer. For more information, see Add an AnalyticDB for MySQL 2.0 data source.

The following table lists the data types supported by AnalyticDB for MySQL 2.0 Writer.

Type AnalyticDB for MySQL 2.0 data type
Integer INT, TINYINT, SMALLINT, and BIGINT
Floating point FLOAT and DOUBLE
String VARCHAR
Date and time DATE and TIMESTAMP
Boolean BOOLEAN

Parameters

Parameter Description Required Default value
url The URL for connecting to the AnalyticDB for MySQL 2.0 database. Specify the parameter in the Address:Port format. Yes None
database The name of the AnalyticDB for MySQL 2.0 database. Yes None
Access Id The AccessKey ID used for connecting to the AnalyticDB for MySQL 2.0 database. Yes None
Access Key The AccessKey secret used for connecting to the AnalyticDB for MySQL 2.0 database. Yes None
datasource The connection name. It must be identical to the name of the added connection. You can add connections in the code editor. Yes None
table The name of the destination table. Yes None
partition The partition name of the destination table. If the destination table is partitioned, this parameter is required. No None
writeMode The write mode. Set the value to insert. In this mode, if a primary key conflict occurs, the conflicting rows are overwritten. Yes None
column The columns in the destination table to which data is written. Separate the columns with commas (,), for example, ["a", "b", "c"]. Set the value to an asterisk (*) if data is written to all the columns in the destination table. Yes None
suffix Optional. The suffix to the AnalyticDB for MySQL 2.0 URL that is in the format of Address:Port. This suffix is a custom connection string. For more information, see the Java Database Connectivity (JDBC) URL parameters supported by MySQL. After this parameter is set, the URL changes to a JDBC connection string for accessing AnalyticDB for MySQL 2.0. For example, set the suffix parameter to autoReconnect=true&failOverReadOnly=false&maxReconnects=10. No None
batchSize The number of data records to write at a time. The parameter is required and takes effect only when the writeMode parameter is set to insert. None
bufferSize The size of the Data Integration data buffer, which is designed to improve the performance of AnalyticDB for MySQL 2.0. Data from the source database is sorted in the buffer before being committed to AnalyticDB for MySQL 2.0. The data in the buffer is sorted based on the partition key columns in AnalyticDB for MySQL 2.0. In this way, the data is organized in an order that is friendly to the AnalyticDB for MySQL 2.0 server.

The data in the buffer is committed to AnalyticDB for MySQL 2.0 in batches based on the batchSize parameter. We recommend that you set the bufferSize parameter to a multiple of the value of the batchSize parameter.

The parameter is required and takes effect when the writeMode parameter is set to insert. Disabled by default

Configure AnalyticDB for MySQL 2.0 Writer by using the codeless UI

  1. Configure the connections.
    Configure the source and destination connections for the sync node.Connections section
    Parameter Description
    Connection The connection name. In this example, select AnalyticDB for MySQL 2.0.
    Table The name of the destination table. Select a table in the AnalyticDB for MySQL 2.0 database to which data is synchronized from the source database.
    Write Method The method in which data is written to the destination table. Select the write method based on the update mode of the destination table in AnalyticDB for MySQL 2.0. Valid values: Batch and Real-Time.
    Note The Batch mode is supported only for synchronizing data from MaxCompute to AnalyticDB for MySQL 2.0. To import data in batches, configure two sync nodes. Configure one sync node to write data in batches to MaxCompute. Configure the other sync node to synchronize data in batches from MaxCompute to AnalyticDB for MySQL 2.0.
    Writing Rule The writing rule. If you select Write with Original Data Deleted, all data in the table or partition is cleared before new data is imported. This rule is equivalent to the INSERT OVERWRITE statement.
    Partition Key Column 1 The partition to which data is written. The default value cannot be modified.
  2. Configure field mapping, that is, the column parameter in the preceding parameter description.
    Fields in the source table on the left have a one-to-one mapping with fields in the destination table on the right. You can click Add to add a field, or move the pointer over a field and click the Delete icon to delete the field.Mappings section
    Button or icon Description
    Map Fields with the Same Name Click Map Fields with the Same Name to establish a mapping between fields with the same name. Note that the data types of the fields must match.
    Map Fields in the Same Line Click Map Fields in the Same Line to establish a mapping for fields in the same row. Note that the data types of the fields must match.
    Delete All Mappings Click Delete All Mappings to remove mappings that have been established.
    Auto Layout Click Auto Layout. The fields are automatically sorted based on specified rules.
    Change Fields Click the Change Fields icon. In the Change Fields dialog box that appears, you can manually edit fields in the source table. Each field occupies a row. The first and the last blank rows are included, whereas other blank rows are ignored.
    Add
    • Click Add to add a field. You can enter constants. Each constant must be enclosed in single quotation marks (' '), such as 'abc' and '123'.
    • You can use scheduling parameters, such as ${bizdate}.
    • You can enter functions supported by relational databases, such as now() and count(1).
    • Fields that cannot be parsed are indicated by Unidentified.
  3. Configure channel control policies.Channel section
    Parameter Description
    Expected Maximum Concurrency The maximum number of concurrent threads to read data from or write data to data storage within the sync node. You can configure the concurrency for a node on the codeless UI.
    Bandwidth Throttling Specifies whether to enable bandwidth throttling. You can enable bandwidth throttling and set a maximum transmission rate to avoid heavy read workload of the source. We recommend that you enable bandwidth throttling and set the maximum transmission rate to a proper value.
    Dirty Data Records Allowed The maximum number of dirty data records allowed.
    Resource Group The resource group used for running the sync node. If a large number of nodes including this sync node are deployed on the default resource group, the sync node may need to wait for resources. We recommend that you purchase an exclusive resource group for data integration or add a custom resource group.

Configure AnalyticDB for MySQL 2.0 Writer by using the code editor

{
    "type":"job",
    "version":"2.0",
    "steps":[// The following template is used to configure Stream Reader. For more information, see the corresponding topic.
        {
            "stepType":"stream",
            "parameter":{
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"ads",// The writer type.
            "parameter":{
                "partition":"",// The name of the destination partition in the destination table.
                "datasource":"",// The connection name.
                "column":[// The columns to which data is written.
                     "id"
                ],
                "writeMode":"insert",// The write mode.
                "batchSize":"256",// The number of data records to write at a time.
                "table":"",// The name of the destination table.
                "overWrite":"true"// Specifies whether to overwrite the destination table when data is written to AnalyticDB for MySQL 2.0. A value of true indicates that the destination table is overwritten. A value of false indicates that the destination table is not overwritten and the new data is appended to the existing data. This value only takes effect when the writeMode parameter is set to load.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed.
        },
        "speed":{
            "throttle":false,// Specifies whether to enable bandwidth throttling. A value of false indicates that the bandwidth is not throttled. A value of true indicates that the bandwidth is throttled. The maximum transmission rate takes effect only if you set this parameter to true.
            "concurrent":1,// The maximum number of concurrent threads.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}