DataWorks:Migrate data from a self-managed MySQL database on an ECS instance to MaxCompute

Procedure

1. Add a MySQL data source in DataWorks.

   1. Go to the Data Source page.

      1. Log on to the DataWorks console. In the left-side navigation pane, click Management Center. On the page that appears, select the desired workspace from the drop-down list and click Go to Management Center.

      2. In the left-side navigation pane of the page that appears, click Data Source. The Data Source page appears.

   2. On the Data Source page, click Add Data Source.

   3. In the Add Data Source dialog box, select MySQL.

   4. In the Add MySQL Data Source dialog box, configure the parameters. For more information, see Add a MySQL data source.

      In this example, set the Data Source Type parameter to Connection String Mode. Enter a URL that contains the private IP address of the ECS instance and the default port number 3306 of the MySQL database in the JDBC URL field.

      Note

      DataWorks cannot test the connectivity of a self-managed MySQL database in a VPC. Therefore, it is normal that a connectivity test fails.

   5. Find the desired resource group and click Test Network Connectivity.

      You can use the shared resource group for Data Integration or an exclusive resource group for Data Integration to run data synchronization tasks. A data synchronization task can be run on only one resource group during each run. If multiple resource groups are available for a data synchronization task and you want the data synchronization task to be run as expected on every resource group, you must test the network connectivity between each of the selected resource groups and the data synchronization task. For more information, see Establish a network connection between a resource group and a data source.

   6. If the connectivity test is successful, click Complete.

2. Create a MaxCompute table.

   You must create a table in DataWorks to receive test data from the MySQL database.

   1. Click the icon in the upper-left corner and choose All Products > Data Development And Task Operation > DataStudio.

   2. Create a workflow. For more information, see Create an auto triggered workflow.

   3. Right-click the created workflow and choose Create Table > MaxCompute > Table.

   4. Enter the name for your MaxCompute table. In this example, set the Table Name parameter to good_sale, which is the same as the name of the table in the MySQL database. Click DDL, enter the table creation statement, and then click Generate Table Schema.

      In this example, enter the following table creation statement. Pay attention to data type conversion.

      CREATE TABLE IF NOT EXISTS good_sale(
         create_time string,
         category STRING,
         brand STRING,
         buyer_id STRING,
         trans_num BIGINT,
         trans_amount DOUBLE,
         click_cnt BIGINT
         );

   5. Set the Display Name parameter and click Commit to Production Environment. The MaxCompute table named good_sale is created.

3. Create and configure a data synchronization task.

   1. Right-click the workflow you created and choose Create Node > Data Integration > Offline synchronization to create a data synchronization task.

   2. Select the created MySQL data source as the source and the added MaxCompute data source as the destination. Click the Switch to Code Editor icon to switch to the code editor.

      If you cannot select the desired source table or an error is returned when you attempt to switch to the code editor, ignore the issue.

   3. Click the Resource Group configuration tab in the right-side navigation pane and select an exclusive resource group that you have purchased.

      If you do not select the exclusive resource group for Data Integration that you want to use to run the task, the task may fail to be run.

   4. Enter the following code for the data synchronization task:

      {
          "type": "job",
          "steps": [
              {
                  "stepType": "mysql",
                  "parameter": {
                      "column": [// The names of the columns.
                          "create_time",
                          "category",
                          "brand",
                          "buyer_id",
                          "trans_num",
                          "trans_amount",
                          "click_cnt"
                      ],
                      "connection": [
                          {
                              "datasource": "shuai",// The source.
                              "table": [
                                  "good_sale"// The name of the table in the source database. The name must be enclosed in brackets []. 
                              ]
                          }
                      ],
                      "where": "",
                      "splitPk": "",
                      "encoding": "UTF-8"
                  },
                  "name": "Reader",
                  "category": "reader"
              },
              {
                  "stepType": "odps",
                  "parameter": {
                      "partition": "",
                      "truncate": true,
                      "datasource": "odps_source",// The name of the MaxCompute data source.
                      "column": [// The columns in the destination table.
                          "create_time",
                          "category",
                          "brand",
                          "buyer_id",
                          "trans_num",
                          "trans_amount",
                          "click_cnt"
                      ],
                      "emptyAsNull": false,
                      "table": "good_sale"// The name of the destination table.
                  },
                  "name": "Writer",
                  "category": "writer"
              }
          ],
          "version": "2.0",
          "order": {
              "hops": [
                  {
                      "from": "Reader",
                      "to": "Writer"
                  }
              ]
          },
          "setting": {
              "errorLimit": {
                  "record": "0"
              },
              "speed": {
                  "throttle": false,
                  "concurrent": 2
              }
          }
      }

   5. Click the Run icon. You can click the Runtime Log tab in the lower part of the page to check whether the test data is synchronized to MaxCompute.