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

Prerequisites

  • The DataWorks environment is ready.
    1. Activate MaxCompute.
    2. Create a workspace. In this example, a workspace in basic mode is used.
    3. A workflow is created in the DataWorks console. For more information, see Create a workflow.
  • The Oracle database is ready.

    In this example, the Oracle database is installed on an Elastic Compute Service (ECS) instance. The following figure shows the configuration of the ECS instance. To enable network communication, 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. After data insertion, 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. Go to the DataStudio page.
    1. Log on to the DataWorks console.
    2. In the left-side navigation pane, click Workspaces.
    3. Select the region where the required workspace resides. Find the required workspace and click Data Analytics.
  2. On the DataStudio page, create a destination table 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 following statement and click Generate Table Schema:
      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 connection. For more information, see Configure an Oracle connection.
  4. Create a batch sync 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. After you create the batch sync node, set the Connection parameter to the created Oracle connection and the Table parameter to the Oracle table that you have created. Click Map Fields with the Same Name. Use the default values for other parameters.
    4. Click **icon to run the code.
    5. You can operation Log view the results.

Verify the result

  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.