DataWorks:Configure batch synchronization tasks for sharded databases and tables

Sharded data source and codeless UI

1. Log on to the DataWorks console. In the target region, click Data Integration > Data Integration in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Integration.

2. In the left-side navigation pane, click Data Source, click Add Data Source, and then select a data source type. This method supports MySQL, PolarDB, and PolarDB-O.

3. Create a standard data source for each sharded database. This topic uses MySQL as an example. For detailed steps, see Configure a MySQL data source.

   After the creation is complete, you can view the MySQL data source for each sharded database on the Data Source List page. For example, the data source name is MySQL_20241114, corresponding to the database user_info_20241114.

4. Create a Sharded Data Source to merge multiple standard data sources into a single data source as the source for data synchronization.

   1. Click Add Data Source and select Sharded Data Source. In the Add Data Source dialog, click the Sharded Data Source tab. The available data source types include MySQL (sharded), PolarDB-O (sharded), and PolarDB (sharded).

   2. Select a sharded data source type as needed and configure the relevant parameters. This topic uses MySQL (sharded) as an example.

      In the Data Source Name field, enter a name such as Database_and_Table_Sharding_of_MySQL. After the configuration is complete, click OK to create the sharded data source.

      Key parameter description:

      • Customize the Data Source Name.

      • Select Data Sources: Select the standard data sources that you have created for each sharded database.

      • Meta Data Source: For sharding synchronization, all data sources must have identical table schemas. Select one of the data sources as the default metadata template for retrieving the default table schema when you configure a synchronization task.

        Important

        If the table schemas across data sources are inconsistent, subsequent synchronization tasks will fail.

5. Create a batch synchronization node

6. Configure a sharding synchronization task.

   Configure sharding in wizard mode: For Data Source, select MySQL (sharded), and then select the sharded data source that you have created. For the complete task configuration steps, see Configure a batch synchronization task in wizard mode.

   Note

   This topic uses MySQL (sharded) as an example. Select the appropriate type based on your needs.

   On the network connectivity test page, the source data source name is Database_and_Table_Sharding_of_MySQL, the resource group is Serveless_Resource (China (Shanghai), pay-as-you-go, 0.5 CU), and the destination is MaxCompute(ODPS) with the data source name doc_test_project. The network connectivity test results show that all three are connected.

7. Click Next.

8. Select the tables to synchronize, click Save and Publish, and complete the remaining steps.

   On the Configure Source and Destination page, in Step 2 Configure Task, the left-side Source section is configured as follows: the data source is MySQL (sharded), the table selection method is Fixed Table, the table is user_info, and the split key is id. (Sharding requires all tables to have identical schemas; otherwise, execution errors occur.) The right-side Destination section is configured as follows: the data source is MaxCompute(ODPS), the table is user_info, the partition information is pt=${bizdate}, the write mode is Clear existing data before writing (Insert Overwrite), and convert empty strings to NULL is set to No.

Standard data source and codeless UI

1. Create a standard data source for each sharded database. This topic uses MySQL as an example. For detailed steps, see Configure a MySQL data source.

2. Create a batch synchronization node

3. Configure a sharding synchronization task.

   Configure sharding in wizard mode: In the Data Source section, click + Edit Source. In the data source selection panel that appears, select and add the data sources corresponding to multiple sharded databases. For the complete task configuration steps, see Configure a batch synchronization task in wizard mode.

4. Add multiple standard data sources to Selected Data Sources and click Confirm.

5. Click Next.

6. Select the tables to synchronize from each data source, click Save and Publish, and complete the remaining steps.

   Important

   In this configuration, the Data Sources for Sharded Databases Use Same Account and Password option is selected by default. This means that all sharded data sources will use the account and password configured in the first data source to access the databases. If your sharded databases use different accounts and passwords, clear this option so that each data source uses its own account and password.

   On the Configure Source and Destination page, the left-side Source section has three MySQL sharded data sources configured (such as MySQL_20241112, MySQL_20241114, and MySQL_20241113), and each data source selects the user_info table. The page displays a warning: "Sharding requires all tables to have identical schemas; otherwise, execution errors occur." The right-side Destination section selects the MaxCompute(ODPS) data source, the table is user_info, the partition information is set to pt=${bizdate}, and the write mode is Clear existing data before writing (Insert Overwrite).

Code editor

1. Create a batch synchronization node

2. Configure a sharding synchronization task.

   Configure sharding in script mode: The following is a script example. For the complete task configuration steps, see Configure a batch synchronization task in script mode.

   Important

   Before running the task, remove the comments from the following code.

   {
       "type":"job",
       "version":"2.0",
       "steps":[
           {
               "stepType":"mysql",
               "parameter":{
                   "envType":0,
                   "column":[
                       "id",
                       "name"
                   ],
                   "socketTimeout":3600000,
                   "tableComment":"",
                   "connection":[    //Configure connections based on the number of sharded databases
                       {
                           "datasource":"datasourceName1",  //Data source 1 for sharded databases and tables
                           "table":[           //Table list 1 for sharded databases and tables
                               "tb1"
                           ]
                       },
                       {
                           "datasource":"datasourceName2", //Data source 2 for sharded databases and tables
                           "table":[          //Table list 2 for sharded databases and tables
                               "tb2",
                               "tb3"
                           ]
                       }
                   ],
                   "useSpecialSecret":true,//Each data source uses its own password
                   "where":"",
                   "splitPk":"id",
                   "encoding":"UTF-8"
                   },
               "name":"Reader",
               "category":"reader"
               },
           {
               "stepType":"odps",
               "parameter":{
                   "partition":"pt=${bizdate}",
                   "truncate":true,
                   "datasource":"odpsname",
                   "envType":0,
                   "isSupportThreeModel":false,
                   "column":[
                       "id",
                       "name"
                   ],
                   "emptyAsNull":false,
                   "tableComment":"",
                   "table":"t1",
                   "consistencyCommit":false
                   },
               "name":"Writer",
               "category":"writer"
               }
       ],
       "setting":{
           "executeMode":null,
           "errorLimit":{
               "record":""
               },
           "speed":{
               "concurrent":2,
               "throttle":false
               }
       },
       "order":{
           "hops":[
               {
                   "from":"Reader",
                   "to":"Writer"
                   }
           ]
       }
   }

FAQ