MaxCompute:Best practice for migrating data from Oracle to MaxCompute

Prerequisites

Before you begin, ensure that you have:

• MaxCompute and DataWorks activated. See Activate MaxCompute and DataWorks

• A workspace created in DataWorks (this example uses basic mode). See Create a workspace

• A workflow created in your workspace. See Create a workflow

• An Oracle database accessible over the network. In this example, Oracle is installed on an Elastic Compute Service (ECS) instance (type: ecs.c5.xlarge) in a virtual private cloud (VPC) in the China (Hangzhou) region. The ECS instance has a public IP address and a security group rule that opens port 1521. See Modify a security group rule

The following figure shows the ECS instance configuration used in this example:

How it works

DataWorks uses Oracle Reader to connect to the Oracle database and read data. A batch synchronization task (offline synchronization) pulls data from the Oracle source table and writes it to a MaxCompute destination table. After the task runs, query the MaxCompute table with an ODPS SQL node to confirm the data arrived correctly.

Step 1: Prepare test data in Oracle

1. Create a table named DTSTEST.GOOD_SALE in your Oracle database with the following columns: create_time, category, brand, buyer_id, trans_num, trans_amount, and click_cnt.

2. Insert test data into the table:

   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. Verify the data was inserted:

   select * from good_sale;

Step 2: Create a MaxCompute table

1. Log in to the DataWorks console. In the top navigation bar, select the target region. In the left-side navigation pane, choose Data Development and O\&M > Data Development, select your workspace, and click Go to Data Development.

2. In the Scheduled Workflow pane on the DataStudio page, right-click your workflow, then choose Create Table > MaxCompute > Table.

3. In the Create Table dialog box, configure the basic table information.

4. In the top toolbar of the table configuration tab, click DDL.

5. In the DDL dialog box, enter the following statement and click Generate Table Schema:

   Match the MaxCompute column data types to the Oracle source column types. For the full Oracle-to-MaxCompute type mapping, see Oracle Reader data type mappings.

   CREATE TABLE good_sale
   (
       create_time     string,
       category        string,
       brand           string,
       buyer_id        string,
       trans_num       bigint,
       trans_amount    double,
       click_cnt       bigint
   );

6. Click Submit to Production Environment.

Step 3: Add an Oracle data source

Add an Oracle connection in DataWorks so the synchronization task can reach the database. See Add an Oracle data source.

Step 4: Create a batch synchronization task

1. In the Scheduled Workflow pane, right-click your workflow, then choose new > data integration > offline synchronization.

2. In the Create Node dialog box, enter a node name and click Confirm.

3. In the Configure Network Connections and Resource Group step, set the Oracle data source as the source and the MaxCompute data source as the destination. Select the resource group, then click Test Connectivity of All Data Sources to verify network connectivity between the resource group and both data sources. If the test passes, click Next.

4. On the Configure Source and Destination page, set Table to the Oracle table (DTSTEST.GOOD_SALE).

5. On the Field Mapping page, click Map with the same name to align source and destination columns automatically. Keep the default values for all other parameters.

6. Click to run the task.

7. Check operation Log to confirm the task completed without errors.

Verify the migration result

Run a query against the MaxCompute table to confirm all rows were migrated:

1. In the Scheduled Workflow pane, right-click your workflow, then choose new > MaxCompute > ODPS SQL.

2. In the Create Node dialog box, enter a node name and click Confirm.

3. Enter the following query in the ODPS SQL node:

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

4. Click to run the query.

5. In operation Log, confirm the query returns the three rows inserted in Step 1. Validate the migration by checking:

   • Row count: The result contains exactly 3 rows

   • Field values: Each field matches the Oracle source data

   • Data types: Numeric columns (trans_num, trans_amount, click_cnt) contain the correct values without truncation or type conversion errors

What's next

• Configure a scheduled run for the synchronization task to keep MaxCompute in sync with Oracle on an ongoing basis.

• Review Oracle Reader for supported data types, advanced configuration options, and known limitations before migrating production tables.