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

Procedure

1. Create a MySQL data source in DataWorks.

   1. Go to the Data Sources page.

      1. Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose More > 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 SettingCenter page, click Data Sources.

   2. Click Add Data Source.

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

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

      This topic uses the Connection String Mode as an example. For Connection Address, enter the private IP address of the ECS instance that you noted and the default MySQL port number, 3306.

      Note

      The Test Connectivity feature is not supported for self-managed MySQL data sources in a VPC. Therefore, it is normal for the connectivity test to fail.

   5. Click Test Connectivity for the resource group.

      You can use an exclusive resource group for Data Integration to run data sync tasks. A task can use only one resource group for each run. If multiple resource groups are available, you must test the connectivity of each resource group that you want to use. This ensures that the resource group used by the sync task can connect to the data source. Otherwise, the data sync task cannot run as expected. For more information, see Network connectivity solutions.

   6. After the connectivity test passes, click Complete.

2. Create a MaxCompute table.

   Create a table in DataWorks to receive the 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 a scheduled workflow.

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

   4. Enter a name for your MaxCompute table. This example uses good_sale, which is the same name as the table in the MySQL database. Click DDL, enter the table creation statement, and then click Generate Table Schema.

      The following table creation statement is used in this example. Note the data type conversions.

      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. Enter a Display Name for the table and click Commit To Production Environment to create the good_sale table in MaxCompute.

3. Configure a data integration task.

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

   2. For the source, select the MySQL data source that you added. For the destination, select the added MaxCompute data source. Click Switch To Code Editor to switch the data integration task to the code editor.

      It is normal if an error occurs or if you cannot select a source Table. Switch to the code editor directly.

   3. In the right-side pane, click the Data Integration Resource Group Configuration tab and select the exclusive resource group that you purchased.

      If you do not switch the task to the exclusive resource group for Data Integration, the task cannot run.

   4. Enter the following content for the data integration task script.

      {
          "type": "job",
          "steps": [
              {
                  "stepType": "mysql",
                  "parameter": {
                      "column": [// The names of the source columns.
                          "create_time",
                          "category",
                          "brand",
                          "buyer_id",
                          "trans_num",
                          "trans_amount",
                          "click_cnt"
                      ],
                      "connection": [
                          {
                              "datasource": "shuai",// The source data source.
                              "table": [
                                  "good_sale"// The name of the source database table. The name must be in an array format enclosed in square brackets [].
                              ]
                          }
                      ],
                      "where": "",
                      "splitPk": "",
                      "encoding": "UTF-8"
                  },
                  "name": "Reader",
                  "category": "reader"
              },
              {
                  "stepType": "odps",
                  "parameter": {
                      "partition": "",
                      "truncate": true,
                      "datasource": "odps_source",// The name of the destination MaxCompute data source.
                      "column": [// The names of the destination columns.
                          "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 Run. You can view the Runtime Log at the bottom to check whether the data was transferred to MaxCompute.