This topic describes how to use the data integration feature of DataWorks to migrate data from an Oracle database to MaxCompute.

Prerequisites

  • The DataWorks environment is ready.
    1. Activate MaxCompute.
    2. DataWorks is activated.
    3. A workflow is created in the DataWorks console. In this example, a workflow is created in a DataWorks workspace in basic mode. For more information, see Create a workflow.
  • The Elastic Compute Service (ECS) instance where the Oracle database resides is configured.
    In this example, the Oracle database is installed on an ECS instance. The following figure shows the configuration of the ECS instance. To enable communication between the ECS instance and the resource group for running the data synchronization node, you must configure a public IP address for the ECS instance. In addition, you must configure a security group rule for the ECS instance to ensure that the common port 1521 of the Oracle database is accessible. For more information about how to configure a security group rule for an ECS instance, see Modify security group rules.

    As shown in the preceding figure, the type of the ECS instance is ecs.c5.xlarge. The ECS instance resides in a Virtual Private Cloud (VPC) in the China (Hangzhou) region.

Background information

In this example, DataWorks Oracle Reader is used to read test data from the Oracle database. For more information, see Configure Oracle Reader.

Prepare test data in the Oracle database

  1. In the Oracle database, create the DTSTEST.GOOD_SALE table that contains the CREATE_TIME, CATEGORY, BRAND, BUYER_ID, TRANAS_NUM, TRANS_AMOUNT, and CLICK_CNT columns.
  2. Insert test data to the DTSTEST.GOOD_SALE table. In this example, the following statements are executed to insert test data:
    insert into good_sale values('28-December-19','Kitchenware','Brand A','hanmeimei','6','80.6','4');
    insert into good_sale values('21-December-19','Fresh food','Brand B','lilei','7','440.6','5');
    insert into good_sale values('29-December-19','Clothing','Brand C','lily','12','351.9','9');
    commit;
  3. Execute the following statement to view the data in the table:
    select * from good_sale;

Use DataWorks to migrate data from the Oracle database to MaxCompute

  1. Login DataWorks console.
  2. Create a table in the DataWorks console. The table is used to receive data migrated from the Oracle database.
    1. Right-click a created workflow, Select new > MaxCompute > table.
    2. In create a table page, select the engine type, and enter table name.
    3. On the table editing page, click DDL Statement.
    4. In the DDL Statement dialog box, enter the table creation statement and click Generate Table Schema. In this topic, the following statement is used as an example:
      CREATE TABLE good_sale 
      (
          create_time     string,
          category        string,
          brand           string,
          buyer_id        string,
          trans_num       bigint,
          trans_amount    double,
          click_cnt       bigint
      ) ;

      When you create the MaxCompute table, make sure that the data types of the MaxCompute table match those of the Oracle table. For more information about the data types supported by Oracle Reader, see Data types.

    5. Click Submit to Production Environment.
  3. Create an Oracle data source. For more information, see Configure an Oracle connection.
  4. Create a batch synchronization node.
    1. Go to the data analytics page. Right-click the specified workflow and choose new > data integration > offline synchronization.
    2. In create a node dialog box, enter node name, and click submit.
    3. On the tab of the created node, set Connection to the Oracle connection and Table to the Oracle table in the Source section of the Connections step. Click Map Fields with the Same Name in the Mappings step. Retain the default values for other parameters.
    4. Click **icon to run the code.
    5. You can operation Log view the results.

Verify the configuration

  1. Right-click the workflow and choose new > MaxCompute > ODPS SQL.
  2. In create a node dialog box, enter node name, and click submit.
  3. On the configuration tab of the ODPS SQL node, enter the following statement:
    --Check whether the data is written to MaxCompute.
    select * from good_sale;
  4. Click **icon to run the code.
  5. You can operation Log view the results.