Data synchronization capabilities of AnalyticDB for MySQL 3.0 - DataWorks

AnalyticDB for MySQL 3.0 data source lets you read data from and write data to AnalyticDB for MySQL 3.0 over a bidirectional channel. This topic describes the AnalyticDB for MySQL 3.0 data synchronization capabilities supported by DataWorks.

Limits

You cannot configure or run synchronization tasks for an ADB Lakehouse Edition data source on a public resource group.
If an instance configured in an ADB data source is switched from Data Warehouse Edition to Lakehouse Edition, synchronization tasks that use this data source on a public resource group will fail. Before switching editions, check whether any tasks run on a public resource group and move them to a Serverless resource group (recommended) or an exclusive resource group for Data Integration.
Batch synchronization supports reading views (VIEW tables).

Supported field types

Batch read

The following table lists the AnalyticDB for MySQL 3.0 type mappings for AnalyticDB for MySQL 3.0 Reader.

Type category	AnalyticDB for MySQL 3.0 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

Batch write

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

Type	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

Add a data source

Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source configuration. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.

Develop data synchronization tasks

For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.

Appendix: Sample scripts and parameters

Configure a batch synchronization task by using the code editor

If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Script mode configuration. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.

Reader sample script

{
  "type": "job",
  "steps": [
    { 
      "stepType": "analyticdb_for_mysql", //Plug-in name.
      "parameter": {
        "column": [ //Column names.
          "id",
          "value",
          "table"
        ],
        "connection": [
          {
            "datasource": "xxx", //Data source.
            "table": [ //Table name.
              "xxx"
            ]
          }
        ],
        "where": "", //Filter condition.
        "splitPk": "", //Split key.
        "encoding": "UTF-8" //Encoding format.
      },
      "name": "Reader",
      "category": "reader"
    },
    { 
      "stepType": "stream",
      "parameter": {},
      "name": "Writer",
      "category": "writer"
    }
  ],
  "version": "2.0",
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  },
  "setting": {
    "errorLimit": {
      "record": "0" //Maximum number of error records during synchronization.
    },
    "speed": {
      "throttle":true,//When throttle is false, mbps is ignored and throughput is unlimited; when true, throughput is limited.
      "concurrent":1 //Job concurrency.
      "mbps":"12"//Rate limit. Here, 1 mbps = 1 MB/s.
    }
  }
}

Reader script parameters

Parameter	Description	Required	Default value
datasource	The data source name. In script mode, you can add a data source. The value must match the name of the added data source.	Yes	None
table	The table to synchronize.	Yes	None
column	The columns to synchronize from the configured table. Describe the fields in a JSON array. By default, all columns are selected, for example, [ * ]. Column selection is supported, so you can export only selected columns. Column reordering is supported, so the export order may differ from the table schema order. Constant values are supported. Follow MySQL syntax, for example, [“id”, “`table`“, “1”, “‘bazhen.csy’”, “null”, “to_char(a + 1)”, “2.3” , “true”]. id is a regular column name. table is a reserved column name. 1 is an integer constant. bazhen.csy is a string constant. null is a null pointer. to_char(a + 1) is a function expression for calculating string length. 2.3 is a floating-point number. true is a Boolean value. columnYou must explicitly specify a nonempty set of columns to synchronize.	Yes	None
splitPk	When AnalyticDB for MySQL 3.0 Reader extracts data, specifying splitPk means that you want to use the field represented by splitPk to split the data. Data synchronization starts concurrent tasks to improve efficiency. We recommend that splitPk you use the table primary key. Primary key values are usually distributed evenly, making data hotspots less likely in the resulting shards. Currently, splitPk supports splitting only integer data, not strings, floating-point numbers, dates, or other types. If you specify an unsupported type, the splitPk setting is ignored and synchronization uses one channel. If splitPk is not specified, either because the splitPk parameter is absent or its splitPk value is empty, data synchronization reads the table through one channel.	No	None
where	A filter condition. In many business scenarios, you synchronize the current day’s data. Setting the where condition to `gmt_create>$bizdate`. where enables incremental business synchronization. If the where statement is not specified, either because the where key or value is absent, all data is synchronized. Do not set the where condition to LIMIT 10. This does not satisfy the constraints of a MySQL SQL WHERE clause.	No	None

Writer sample script

{
  "type": "job",
  "steps": [
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "analyticdb_for_mysql", //Plug-in name.
      "parameter": {
        "postSql": [], //Statements to run after import.
        "tableType": null, //Reserved field, empty by default.
        "datasource": "hangzhou_ads", //Data source name.
        "column": [ //Fields to synchronize.
          "id",
          "value"
        ],
        "guid": null,
        "writeMode": "insert", //Write mode. See the writeMode parameter description.
        "batchSize": 2048, //Batch size. See the batchSize parameter description.
        "encoding": "UTF-8", //Encoding format.
        "table": "t5", //Name of the table to write to.
        "preSql": [] //Statements to run before import.
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "version": "2.0", //Configuration file format version.
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  },
  "setting": {
    "errorLimit": {
      "record": "0" //Number of error records.
    },
    "speed": {
      "throttle":true,//When throttle is false, mbps is ignored and throughput is unlimited; when true, throughput is limited.
      "concurrent":2, //Job concurrency.
      "mbps":"12"//Rate limit. Here, 1 mbps = 1 MB/s.
    }
  }
}

Writer script parameters

Parameter	Description	Required	Default value
datasource	The data source name. In script mode, you can add a data source. The value must match the name of the added data source.	Yes	None
table	The name of the table to synchronize.	Yes	None
writeMode	The write mode. Choose insert, replace, or update. insert: If no primary key or unique index conflict occurs, the data is written normally. If a conflict occurs, the current row is ignored and is not updated. replace: If no primary key or unique index conflict occurs, the data is written normally. If a conflict occurs, the conflicting row is deleted before the new row is inserted, so all fields in the original row are replaced. update: If no primary key or unique index conflict occurs, the data is written normally. If a conflict occurs, the new row replaces all fields of the original row. Note This mode is supported only in script mode.	No	insert
column	Fields to write to the destination table, separated by commas, for example, `"column": ["id", "name", "age"]`. To write all columns in order, use , for example, `"column": [""]`. Note If a field name contains select, enclose the field name in backticks. For example, write item_select_no as `item_select_no`.	Yes	None
preSql	SQL statements to run before the synchronization task. Wizard mode allows only one SQL statement; script mode supports multiple statements, such as clearing old data. Note Transactions are not supported for multiple SQL statements.	No	None
postSql	SQL statements to run after the synchronization task. Wizard mode allows only one SQL statement; script mode supports multiple statements, such as adding a timestamp. Note Transactions are not supported for multiple SQL statements.	No	None
batchSize	The number of records submitted in one batch. This can greatly reduce network interactions between the data synchronization system and MySQL and improve overall throughput. An excessively large value can cause an out-of-memory (OOM) error in the synchronization process.	No	1,024