DataWorks:PolarDB data source

Limits

Batch data read and write

Data of views can be read.

Real-time data read

If the source of a synchronization task is a PolarDB for MySQL cluster, you must enable the binary logging feature for the cluster. PolarDB for MySQL is fully compatible with MySQL and uses high-level physical logs to replace binary logs. To facilitate the integration between PolarDB and the MySQL ecosystem, you can enable the binary logging feature for PolarDB clusters.

Data type mappings

Batch data read

Batch data write

Similar to PolarDB Reader, PolarDB Writer supports most PolarDB data types. Make sure that the data types of your database are supported.

Prepare a PolarDB environment before data synchronization

Preparation 1: Configure an IP address whitelist

You must add the CIDR block of the virtual private cloud (VPC) where the exclusive resource group for Data Integration resides to an IP address whitelist of the PolarDB for MySQL cluster. To add the CIDR block, perform the following steps:

Preparation 2: Prepare an account that has the required permissions

Preparation 3: Enable the binary logging feature

For more information, see Enable binary logging.

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 a real-time synchronization task to synchronize data of a single table or synchronize all data of a database

For more information about the configuration procedure, see Configure a real-time synchronization task in DataStudio.

Configure synchronization settings to implement batch synchronization of all data in a database and real-time synchronization of data of a single table or full data or incremental data in a database

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

FAQ

Why are errors repeatedly reported when a real-time synchronization task is run to synchronize data from Oracle, PolarDB, or MySQL?

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 PolarDB Reader

{
    "type": "job",
    "steps": [
        {
            "parameter": {
                "datasource": "test_005",// The name of the data source. 
                "column": [// The names of the columns. 
                    "id",
                    "name",
                    "age",
                    "sex",
                    "salary",
                    "interest"
                ],
                "where": "id=1001",// The WHERE clause. 
                "splitPk": "id",// The shard key. 
                "table": "PolarDB_person"// The name of the table. 
              	"useReadonly": "false"// Specifies whether to read data from a secondary database. 
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "parameter": {}
    ],
    "version": "2.0",// The version number. 
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {// The maximum number of dirty data records allowed. 
            "record": ""
        },
        "speed": {
            "concurrent": 6,// The maximum number of parallel threads. 
            "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 PolarDB Reader

Code for PolarDB Writer

{
    "type": "job",
    "steps": [
        {
            "parameter": {},
            "name": "Reader",
            "category": "reader"
        },
        {
            "parameter": {
                "postSql": [],// The SQL statement that you want to execute after the batch synchronization task is run. 
                "datasource": "test_005",// The name of the data source. 
                "column": [// The names of the columns. 
                    "id",
                    "name",
                    "age",
                    "sex",
                    "salary",
                    "interest"
                ],
                "writeMode": "insert",// The write mode. 
                "batchSize": 256,// The number of data records to write at a time. 
                "table": "PolarDB_person_copy",// The name of the table. 
                "preSql": []// The SQL statement that you want to execute before the batch synchronization task is run. 
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "version": "2.0",// The version number. 
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {// The maximum number of dirty data records allowed. 
            "record": ""
        },
        "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":6, // The maximum number of parallel threads. 
            "mbps":"12",// The maximum transmission rate. Unit: MB/s. 
        }
    }
}

Parameters in code for PolarDB Writer

• Parameters in code for PolarDB Writer

  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 from which you want to read data.	Yes	No default value
  writeMode	The write mode. Valid values: insert: This mode is equivalent to INSERT INTO on the codeless UI. update (equivalent to ON DUPLICATE KEY UPDATE on the codeless UI) replace: This mode is equivalent to REPLACE INTO on the codeless UI. For more information about the write modes and data examples, see Description of the writeMode parameter.	No	insert
  column	The names of the columns to which you want to write data. Separate the names with commas (,). Example: "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":["*"].	Yes	No default value
  preSql	The SQL statement that you want to execute before the batch synchronization task 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.	No	No default value
  postSql	The SQL statement that you want to execute after the batch synchronization task 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.	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 PolarDB 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
  updateColumn	The names of the columns that are updated when a primary key conflict or unique index conflict occurs. This parameter takes effect only when the writeMode parameter is set to update. Separate the names with commas (,), such as "updateColumn": ["name", "age"]. Note Only PolarDB for MySQL data sources support this parameter.	No	No default value

• Description of the writeMode parameter

  Item	insert (equivalent to INSERT INTO on the codeless UI)	update (equivalent to ON DUPLICATE KEY UPDATE on the codeless UI)	replace (equivalent to REPLACE INTO on the codeless UI)
  Processing rule	If a primary key conflict or unique index conflict occurs, data is not written to conflicting rows, and the data that is not written to these rows is regarded as dirty data.	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, data in conflicting rows in the destination table is replaced by new data.	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, the original rows are deleted, and new rows are inserted. This indicates that all fields of the original rows are replaced.
  Data example	Data in the source table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | lisi | | +----+---------+-----+ Original data in the destination table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | wangwu | | +----+---------+-----+ After the batch synchronization task is run, one data record is written to the destination table, and one data record is generated. +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | wangwu | | +----+---------+-----+	Scenario 1: Only some columns are specified: "column": ["id","name"]. Data in the source table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | lisi | | +----+---------+-----+ Original data in the destination table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | wangwu | 3 | +----+---------+-----+ After the batch synchronization task is run, two data records are written to the destination table, and no dirty data record is generated. +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | lisi | 3 | +----+---------+-----+ Scenario 2: All columns are specified: "column": ["id","name","age"]. Data in the source table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | lisi | | +----+---------+-----+ Original data in the destination table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | wangwu | 3 | +----+---------+-----+ After the batch synchronization task is run, two data records are written to the destination table, and no dirty data record is generated. +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | lisi | | +----+---------+-----+	Data in the source table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | lisi | | +----+---------+-----+ Original data in the destination table +----+---------+-----+ | id | name | age | +----+---------+-----+ | 2 | wangwu | 3 | +----+---------+-----+ After the batch synchronization task is run, two data records are written to the destination table, and no dirty data record is generated. +----+---------+-----+ | id | name | age | +----+---------+-----+ | 1 | zhangsan| 1 | | 2 | lisi | | +----+---------+-----+