MaxCompute: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 top navigation bar, select the desired region. Then, click Management Center in the left-side navigation pane. 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 SettingCenter page, choose Data Sources > Data Sources.

   2. On the Data Sources 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 Configuration Mode parameter to Connection String Mode. Enter the private IP address of the ECS instance and the default port number 3306 of the MySQL database in the Connection Address 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 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 Network connectivity solutions.

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

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 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.