Migrate data from Oracle to MaxCompute by using DataWorks - MaxCompute - Alibaba Cloud - MaxCompute

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

Prerequisites

Prepare a DataWorks environment.
1. Activate MaxCompute.
2. The example in this topic uses a workspace in basic mode.
3. Create a workflow in DataWorks. For more information, see Create a workflow.
Prepare an Oracle environment.

In this topic, the Oracle database is installed on an ECS instance. To ensure network connectivity, you must assign a public IP address to the ECS instance and configure a security group rule to allow access on the standard Oracle database port, 1521. For more information about how to configure security group rules, see Modify security group rules. In the ECS console, on the Instances page, find the target ECS instance and record its public IP address, such as 47.111.xxx.xxx.

The ECS instance used in this topic is an ecs.c5.xlarge instance in a Virtual Private Cloud (VPC) in the China (Hangzhou) region.

Background information

This topic uses Oracle Reader in DataWorks to read test data from an Oracle database. For more information, see Oracle Reader.

Prepare test data in Oracle

In your Oracle GUI, create a table named good_sale. The table must contain the following seven columns: create_time, category, brand, buyer_id, trans_num, trans_amount, and click_cnt.

The data types of the columns are as follows: create_time is TIMESTAMP(6); category, brand, and buyer_id are VARCHAR2(20 BYTE); trans_num and click_cnt are NUMBER(38,0); and trans_amount is FLOAT. All columns can be null.

Insert the following test data.

insert into good_sale values('28-DEC-19','Kitchenware','Brand A','hanmeimei','6','80.6','4');
insert into good_sale values('21-DEC-19','Fresh Food','Brand B','lilei','7','440.6','5');
insert into good_sale values('29-DEC-19','Clothing','Brand C','lily','12','351.9','9');
commit;

After inserting the data, run the following statement to view the data in the table.
```
select * from good_sale;
```

Migrate data by using DataWorks

Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.
On the DataStudio page, create a destination table to store the Oracle data.
1. Right-click your workflow and choose New Table > MaxCompute > Table.
2. On the New Table page, configure the basic information for the table.
3. In the toolbar of the table editor, click DDL.
4. In the DDL dialog box, enter the CREATE TABLE 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 creating the table, you must map the Oracle data types to the corresponding MaxCompute data types. For more information about the data type mappings supported by Oracle Reader, see Data type mappings.
5. Click Submit to Production Environment.
Create an Oracle data source. For more information, see Configure an Oracle data source.
Create a batch synchronization node.
1. Go to the data analytics page. Right-click the specified workflow and choose Create Node > Data Integration > Offline synchronization.
2. In the New Node dialog box, configure the basic information for the node and click Confirm.
3. After creating the batch synchronization node, on the Network and Resource Configuration tab, select the Oracle data source you added for Data Source and the MaxCompute data source for Data Destination. Under My Resource Group, select the resource group to use and click Test Connectivity. Click Next. On the Configure Source and Destination tab, select your test table for the Table parameter in both the Data Source and Data Destination sections. For Field Mapping, select the Same-name Mapping option. Keep the default settings for other parameters.
4. Click icon to run the code.
5. You can operation Log view the results.

Verify the results

Right-click the workflow and choose new > MaxCompute > ODPS SQL.
In the New Node dialog box, configure the basic information for the node and click Confirm.

On the editor tab for the ODPS SQL node, enter the following statement.

-- Check whether the data is successfully written to MaxCompute.
select * from good_sale;

Click icon to run the code.
You can Runtime Log view the results.