DataWorks support for AnalyticDB for MySQL 2.0 data synchronization-DataWorks(DataWorks)-阿里云帮助中心

The AnalyticDB for MySQL 2.0 data source enables bidirectional data synchronization between AnalyticDB for MySQL 2.0 and other data systems in DataWorks. Use it to read data from or write data to AnalyticDB for MySQL 2.0 as part of an offline synchronization task.

Limitations

Offline synchronization supports reading data from views.
The AnalyticDB for MySQL 2.0 Reader does not support multivalue types. If the source data contains multivalue types, the sync task fails.

Supported field types

Offline read

The following table lists the data type mappings for the AnalyticDB for MySQL 2.0 Reader. Data flows from AnalyticDB for MySQL 2.0 through DataX to MaxCompute.

AnalyticDB for MySQL 2.0 type	DataX type	MaxCompute type	Notes
BIGINT	LONG	BIGINT
TINYINT	LONG	INT
SMALLINT	LONG	INT
INT	LONG	INT
FLOAT	STRING	DOUBLE	AnalyticDB for MySQL 2.0 transmits FLOAT as a string. MaxCompute stores the value as DOUBLE.
DOUBLE	STRING	DOUBLE	AnalyticDB for MySQL 2.0 transmits DOUBLE as a string. MaxCompute stores the value as DOUBLE.
VARCHAR	STRING	STRING
DATE	DATE	DATETIME
TIME	DATE	DATETIME
TIMESTAMP	DATE	DATETIME

Offline write

The following table lists the data type mappings for the AnalyticDB for MySQL 2.0 Writer.

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

Add a data source

Before developing a synchronization task in DataWorks, add the required data source by following the instructions in Data source management. Parameter descriptions are available in the DataWorks console when you add a data source.

Develop a data synchronization task

Configure an offline synchronization task for a single table

Use either of the following methods to configure the task:

Codeless UI: See Configure a task in the codeless UI.
Code editor: See Configure a task in the code editor. For a complete parameter reference and script examples, see Appendix: Script examples and parameter descriptions.

Configure an offline read synchronization task for an entire database

See Batch database synchronization configuration.

Appendix: Script examples and parameter descriptions

Reader

Script example

{
  "type": "job",
  "version": "2.0",
  "steps": [
    {
      "stepType": "ads",
      "parameter": {
        "datasource": "ads_demo",
        "table": "th_test",
        "column": [
          "id",
          "testtinyint",
          "testbigint",
          "testdate",
          "testtime",
          "testtimestamp",
          "testvarchar",
          "testdouble",
          "testfloat"
        ],
        "mode": "ODPS",
        "odps": {
          "accessId": "<yourAccessKeyId>",
          "accessKey": "<yourAccessKeySecret>",
          "account": "<yourAccount>",
          "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"
        }
      },
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Writer",
      "category": "writer"
    }
  ],
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  },
  "setting": {
    "errorLimit": {
      "record": ""
    },
    "speed": {
      "concurrent": 2,
      "throttle": true,
      "mbps": "12"
    }
  }
}

Reader parameters

Parameter	Description	Required	Default value
`table`	The name of the table from which to export data.	Yes	None
`column`	The columns to export. If not specified, all columns are exported.	No	`*`
`limit`	The maximum number of records to export.	No	None
`where`	A WHERE clause to filter exported data. The string is appended directly to the query. Example: `id < 100`.	No	None
`mode`	The export mode. See Choose an export mode.	No	`Select`
`odps.accessId`	The AccessKey ID of the Alibaba Cloud account that AnalyticDB for MySQL 2.0 uses to access ODPS (MaxCompute). The account must have the Describe, Create, Select, Alter, Update, and Drop permissions. Required when `mode` is `ODPS`.	Conditional	None
`odps.accessKey`	The AccessKey secret of the Alibaba Cloud account that AnalyticDB for MySQL 2.0 uses to access ODPS. The account must have the Describe, Create, Select, Alter, Update, and Drop permissions. Required when `mode` is `ODPS`.	Conditional	None
`odps.odpsServer`	The endpoint of the ODPS API. Required when `mode` is `ODPS`.	Conditional	None
`odps.tunnelServer`	The endpoint of the ODPS Tunnel. Required when `mode` is `ODPS`.	Conditional	None
`odps.project`	The name of the ODPS project. Required when `mode` is `ODPS`.	Conditional	None
`odps.accountType`	The type of the account used to access ODPS. Takes effect only when `mode` is `ODPS`.	No	`aliyun`

Choose an export mode

The mode parameter controls how the Reader exports data from AnalyticDB for MySQL 2.0.

Mode	How it works	When to use
`Select`	Uses the SQL LIMIT clause for paginated export. No additional credentials required.	Default. Use for most cases.
`ODPS`	Uses ODPS DUMP to export data directly. Requires ODPS access credentials (`odps.*` parameters).	Use for large-volume exports where ODPS DUMP performance is preferred.

Writer

Script example

{
  "type": "job",
  "version": "2.0",
  "steps": [
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "ads",
      "parameter": {
        "datasource": "",
        "table": "",
        "partition": "",
        "column": [
          "id"
        ],
        "writeMode": "insert",
        "batchSize": "256",
        "overWrite": "true",
        "options.ignoreEmptySource": true
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  },
  "setting": {
    "errorLimit": {
      "record": "0"
    },
    "speed": {
      "throttle": true,
      "concurrent": 1,
      "mbps": "12"
    }
  }
}

Writer parameters

Parameter	Description	Required	Default value
`Connection URL`	The connection information for the AnalyticDB for MySQL 2.0 instance. Format: `Address:Port`.	Yes	None
`Database`	The name of the AnalyticDB for MySQL 2.0 database.	Yes	None
`Access Id`	The AccessKey ID for the AnalyticDB for MySQL 2.0 instance.	Yes	None
`Access Key`	The AccessKey secret for the AnalyticDB for MySQL 2.0 instance.	Yes	None
`datasource`	The name of the data source as added in DataWorks. Must match the data source name exactly.	Yes	None
`table`	The name of the destination table.	Yes	None
`writeMode`	The write mode. See Choose a write mode.	Yes	None
`column`	The fields to write. Set to `["*"]` to include all fields, or specify a list such as `["a", "b", "c"]`.	Yes	None
`partition`	The name of the partition in the destination table. Required if the destination table is a standard (partitioned) table.	No	None
`suffix`	A custom connection string appended to the JDBC (Java Database Connectivity) URL (`ip:port`). Example: `autoReconnect=true&failOverReadOnly=false&maxReconnects=10`.	No	None
`batchSize`	The number of records to write per batch. Required when `writeMode` is `insert`.	Conditional	None
`bufferSize`	The size of the DataX data collection buffer. Source data is sorted in this buffer by the partition key columns of the destination table before being submitted in batches. Set `bufferSize` to several times the value of `batchSize` for best performance. Required when `writeMode` is `insert`.	Conditional	Disabled
`overWrite`	Whether to overwrite existing data in the destination table. `true` overwrites existing data; `false` appends new data. Takes effect only when `writeMode` is `Load`.	No	`true`
`options.ignoreEmptySource`	Whether to ignore errors when the source data is empty. If `false` and the source data cannot be read, the task fails.	No	`true`

Choose a write mode

The writeMode parameter controls how the Writer imports data into AnalyticDB for MySQL 2.0.

Mode	How it works	When to use
`insert`	Inserts records row by row. If a primary key conflict occurs, the new record overwrites the existing record. Requires `batchSize` and `bufferSize`.	Use for incremental or upsert-style writes.
`Load`	Imports data through a third-party system. The `overWrite` parameter controls whether to overwrite or append.	Use for full-table or bulk-load scenarios.