Data Integration allows you to synchronize data from tables in multiple databases to a specific table at a time. Specifically, you can create a batch sync node, specify tables in multiple databases as the source tables, and then specify the destination table. After that, you can run the batch sync node to synchronize the data from the source tables to the destination table.

Background information

When you configure a batch sync node for synchronizing data from tables in multiple databases to a specific table, you must specify source tables. Make sure that all the source tables have the same schema.

You can specify source tables in various types of databases such as MySQL, SQL Server, Oracle, PostgreSQL, PolarDB, and AnalyticDB databases. You can use the codeless user interface (UI) to configure a batch sync node for synchronizing data from tables in multiple MySQL databases to a specific table.

Procedure

  1. Go to the DataStudio page.
    1. Log on to the DataWorks console.
    2. In the left-side navigation pane, click Workspaces.
    3. In the top navigation bar, select the region where your workspace resides, find the workspace, and then click Data Analytics in the Actions column.
  2. Create a batch sync node.
    1. Move the pointer over the Create icon icon and choose Data Integration > Batch Synchronization.
      Alternatively, you can click the desired workflow in the Business Flow pane, right-click Data Integration, and then choose Create > Batch Synchronization.
    2. In the Create Node dialog box, set the Node Name and Location parameters.
      Note The node name must be 1 to 128 characters in length and can contain letters, digits, underscores (_), and periods (.).
    3. Click Commit.
  3. Configure the batch sync node.
    You can configure the batch sync node on the codeless UI or in the code editor.
    • If you configure the batch sync node on the codeless UI, specify the source databases and tables in the Connections section. For more information, see Configure a sync node by using the codeless UI.
      Note You can use the codeless UI to configure a batch sync node for synchronizing data from tables only in multiple MySQL databases. For more information about how to use the code editor to configure batch sync nodes, see Create a sync node by using the code editor.
      Connections section

      If you want to add data sources, click New data source. For more information, see Add a MySQL data source.

    • If you configure the batch sync node in the code editor, write the code for synchronizing data from the source tables in multiple databases to a specific table. The following code provides an example: For more information, see Create a sync node by using the code editor.
      Notice Delete the comments from the following code before you run the code:
      {
          "type": "job",
          "version": "1.0",
          "configuration": {
              "reader": {
                  "plugin": "mysql",
                  "parameter": {
                      "connection": [
                          {
                              "table": [ // The source tables in the first database.
                                  "tbl1",
                                  "tbl2",
                                  "tbl3"
                              ],
                              "datasource": "datasourceName1" // The name of the first database.
                          },
                          {
                              "table": [ // The source tables in the second database.
                                  "tbl4",
                                  "tbl5",
                                  "tbl6"
                              ],
                              "datasource": "datasourceName2" // The name of the second database.
                          }
                      ],
                      "singleOrMulti": "multi",
                      "splitPk": "db_id",
                      "column": [
                          "id", "name", "age"
                      ],
                      "where": "1 < id and id < 100"
                  }
              },
              "writer": {
              }
          }
      }
  4. Commit the node.
    Notice You must set the Rerun and Parent Nodes parameters before you can commit the node.
    1. Click the Commit icon in the toolbar.
    2. In the Commit Node dialog box, enter your comments in the Change description field.
    3. Click OK.
    In a workspace in standard mode, you must click Deploy in the upper-right corner after you commit the do-while node.
  5. Test the node. For more information, see View auto triggered nodes.