DataWorks的AnalyticDB for MySQL 2.0数据同步的能力支持情况 - DataWorks

DataWorks provides AnalyticDB for MySQL 2.0 Reader and AnalyticDB for MySQL 2.0 Writer for you to read data from and write data to AnalyticDB for MySQL 2.0 data sources. This topic describes the capabilities of synchronizing data from or to AnalyticDB for MySQL 2.0 data sources.

Limits

Data of views can be read during batch synchronization.
AnalyticDB for MySQL 2.0 Reader cannot read data of the multivalue type. If you use AnalyticDB for MySQL 2.0 Reader to read data of this type, AnalyticDB for MySQL 2.0 Reader unexpectedly exits.

Data type mappings

Batch data read

The following table lists the data type mappings based on which AnalyticDB for MySQL 2.0 Reader converts data types.

AnalyticDB for MySQL 2.0 data type	Data Integration data type	MaxCompute data type
BIGINT	LONG	BIGINT
TINYINT	LONG	INT
TIMESTAMP	DATE	DATETIME
VARCHAR	STRING	STRING
SMALLINT	LONG	INT
INT	LONG	INT
FLOAT	STRING	DOUBLE
DOUBLE	STRING	DOUBLE
DATE	DATE	DATETIME
TIME	DATE	DATETIME

Batch data write

The following table lists the data type mappings based on which AnalyticDB for MySQL 2.0 Writer converts data types.

Category	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

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.
For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure synchronization settings to implement batch synchronization of all data in a database

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for AnalyticDB for MySQL 2.0 Reader

{
    "type": "job",
  "steps": [
    {
            "stepType": "ads",
      "parameter": {
        "datasource": "ads_demo",
        "table": "th_test",
        "column": [
          "id",
          "testtinyint",
          "testbigint",
          "testdate",
          "testtime",
          "testtimestamp",
          "testvarchar",
          "testdouble",
          "testfloat"
        ],
        "odps": {
          "accessId": "<yourAccessKeyId>",
          "accessKey": "<yourAccessKeySecret>",
          "account": "*********@aliyun.com",
          "odpsServer": " http://service.cn-shanghai-vpc.maxcompute.aliyun-inc.com/api",
          "tunnelServer": "http://dt.cn-shanghai-vpc.maxcompute.aliyun-inc.com",
          "accountType": "aliyun",
          "project": "odps_test"
        },
        "mode": "ODPS"
      },
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Writer",
      "category": "writer"
    }
  ],
  "version": "2.0",
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  },
  "setting": {
    "errorLimit": {
      "record": ""
    },
    "speed": {
      "concurrent": 2,
      "throttle": true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
                  "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
    }
  }
}

Parameters in code for AnalyticDB for MySQL 2.0 Reader

Parameter	Description	Required	Default value
table	The name of the table from which you want to read data.	Yes	No default value
column	The names of the columns. If this parameter is not specified, all columns are read.	No	*
limit	The maximum number of records that you can read on one page.	No	No default value
where	The WHERE clause based on which data records are filtered. The string specified by this parameter, such as `where id < 100`, is added to SQL statements as the query condition.	No	No default value
mode	The read mode. Valid values: Select: reads data on multiple pages based on the value specified for the limit parameter. ODPS: reads data by using MaxCompute dump. To read data in this mode, you must have the access permissions on MaxCompute.	No	Select
odps.accessKey	The AccessKey secret of the Alibaba Cloud account that is used by AnalyticDB for MySQL 2.0 Reader to access MaxCompute. The account must have the Describe, Create, Select, Alter, Update, and Drop permissions. This parameter is required if the mode parameter is set to ODPS.	No	No default value
odps.accessId	The AccessKey ID of the Alibaba Cloud account that is used by AnalyticDB for MySQL 2.0 Reader to access MaxCompute. The account must have the Describe, Create, Select, Alter, Update, and Drop permissions. This parameter is required if the mode parameter is set to ODPS.	No	No default value
odps.odpsServer	The endpoint of the MaxCompute API. This parameter is required if the mode parameter is set to ODPS.	No	No default value
odps.tunnelServer	The endpoint of MaxCompute Tunnel. This parameter is required if the mode parameter is set to ODPS.	No	No default value
odps.project	The name of the MaxCompute project. This parameter is required if the mode parameter is set to ODPS.	No	No default value
odps.accountType	The type of the account that is used to access MaxCompute. This parameter is required if the mode parameter is set to ODPS.	No	aliyun

Code for AnalyticDB for MySQL 2.0 Writer

{
    "type":"job",
    "version":"2.0",
    "steps":[ 
        {
            "stepType":"stream",
            "parameter":{
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"ads",// The plug-in name. 
            "parameter":{
                "partition":"",// The names of the partitions to which you want to write data. 
                "datasource":"",// The name of the data source. 
                "column":[// The names of the columns. 
                     "id"
                ],
                "writeMode":"insert",// The write mode. 
                "batchSize":"256",// The number of data records to write at a time. 
                "table":"",// The name of the table. 
                "overWrite":"true"// Specifies whether to overwrite data in the destination table. The value true indicates that data in the destination table is overwritten. The value false indicates that data in the destination table is not overwritten and new data is appended to the existing data. This parameter takes effect only when the writeMode parameter is set to load. 
                "options.ignoreEmptySource":true// Specifies whether the system reports an error if the source contains no data. The value true indicates that the system does not report an error, and the value false indicates that the system reports an error. The default value of this parameter is true.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed. 
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Parameters in code for AnalyticDB for MySQL 2.0 Writer

Parameter	Description	Required	Default value
url	The URL used to connect to the AnalyticDB for MySQL 2.0 database. Specify this parameter in the IP address:Port format.	Yes	No default value
database	The name of the AnalyticDB for MySQL 2.0 database.	Yes	No default value
Access Id	The AccessKey ID used to connect to the AnalyticDB for MySQL 2.0 database.	Yes	No default value
Access Key	The AccessKey secret used to connect to the AnalyticDB for MySQL 2.0 database.	Yes	No 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
partition	The names of the partitions to which you want to write data. This parameter is required only when the destination table is a partitioned table.	No	No default value
writeMode	AnalyticDB for MySQL 2.0 Writer can be used to write data to an AnalyticDB for MySQL 2.0 database in two modes. In insert mode, if a primary key conflict occurs, data in the conflicting rows is overwritten. In load mode, data is transferred and imported by using a third-party system.	Yes	No default value
column	The names of the columns to which you want to write data. Separate the names with commas (,), such as ["a", "b", "c"].	Yes	No default value
suffix	Optional. The suffix to the AnalyticDB for MySQL 2.0 URL that is in the format of `IP address:Port`. This suffix is a custom connection string. After this parameter is specified, the URL changes to a Java Database Connectivity (JDBC) connection string that is used to connect to the AnalyticDB for MySQL 2.0 database. For example, set the suffix parameter to `autoReconnect=true&failOverReadOnly=false&maxReconnects=10`.	No	No default value
batchSize	The number of data records to write at a time.	This parameter is required only when the writeMode parameter is set to insert.	No default value
bufferSize	The size of the Data Integration data buffer, which is designed to improve the performance of AnalyticDB for MySQL 2.0. Source data is sorted in the buffer before the data is committed to AnalyticDB for MySQL 2.0. The data in the buffer is sorted based on the partition key columns in the AnalyticDB for MySQL 2.0 table. In this way, the data is organized in an order that can improve the performance of the AnalyticDB for MySQL 2.0 server. Data in the buffer is committed to AnalyticDB for MySQL 2.0 in batches based on the value of the batchSize parameter. We recommend that you set the bufferSize parameter to a value that is a multiple of the value of the batchSize parameter. This parameter takes effect only when the writeMode parameter is set to insert.	This parameter is required only when the writeMode parameter is set to insert.	No default value