Migrate data from a user-created MySQL database on an ECS instance to MaxCompute

This topic describes how to use an exclusive resource group for Data Integration to migrate data from a user-created MySQL database on an Elastic Compute Service (ECS) instance to MaxCompute.

1. Create a connection to the MySQL database in the DataWorks console.
   1. Log on to the DataWorks console by using your Alibaba Cloud account.
   2. On the Workspaces page, find the required workspace and click Data Integration.
   3. In the left-side navigation pane, click Connection.
   4. On the Data Source page, click New data source in the upper-right corner.
   5. In the Add data source dialog box, select MySQL.
   6. In the Add MySQL data source dialog box, set the parameters. For more information, see Configure a MySQL connection.
      For example, set the Data source type parameter to Connection string mode. Use the private IP address of the ECS instance and the default port number 3306 of the MySQL database when you specify the Java Database Connectivity (JDBC) URL.

      Note DataWorks cannot test the connectivity of a user-created MySQL database in a VPC. Therefore, it is normal that a connectivity test fails.
   7. Find the required resource group and click Test connectivity.
      During data synchronization, a sync node uses only one resource group. You must test the connectivity of all the resource groups for Data Integration on which your sync nodes will be run and make sure that the resource groups can connect to the data store. This ensures that your sync nodes can be run as expected. For more information, see Test data store connectivity.
   8. After the connection passes the connectivity test, 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 > DataStudio.
   2. Create a workflow. For more information, see Create a workflow.
   3. Right-click the created workflow and choose Create > MaxCompute > Table.
   4. Enter a 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 Statement, 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. Configure a data integration node.
   1. Right-click the workflow you just created and choose Create > Data Integration > Batch Synchronization to create a data integration node.
   2. Set the Connection parameter under Source to the created MySQL connection and the Connection parameter under Target to odps_first. Click the Switch to Code Editor icon to switch to the code editor.
      If you cannot set the Table parameter under Source 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 as the resource group for Data Integration of your node, the node may fail to be run.
   4. Enter the following code for the data integration node:

      {
          "type": "job",
          "steps": [
              {
                  "stepType": "mysql",
                  "parameter": {
                      "column": [// The columns in the source table.
                          "create_time",
                          "category",
                          "brand",
                          "buyer_id",
                          "trans_num",
                          "trans_amount",
                          "click_cnt"
                      ],
                      "connection": [
                          {
                              "datasource": "shuai",// The source connection.
                              "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_first",// The destination connection.
                      "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 view the Runtime Log tab in the lower part of the page to check whether the test data is synchronized to MaxCompute.