AnalyticDB:Export data to MaxCompute

Prerequisites

Before you begin, make sure you have:

• A destination table created in MaxCompute. To get started, see Create an Alibaba Cloud account. To create the table, see Create tables. For reference, the following examples show how to create MaxCompute partitioned tables. For more information, see Table operations.

  • Create a table with hash partitions:

    CREATE TABLE IF NOT EXISTS odps_table
    (
    uid STRING
     )
    PARTITIONED BY (ds STRING)
    LIFECYCLE 3;     

  • Create a table with list partitions:

    CREATE TABLE IF NOT EXISTS odps_table
    (
    uid STRING
     )
    PARTITIONED BY (ds STRING,other STRING)
    LIFECYCLE 3;     

• An AnalyticDB for MySQL Data Warehouse Edition (V3.0) cluster with a whitelist configured and a database account and database created. See Create a cluster

• (If using elastic mode) Elastic network interface (ENI) turned on in the Network Information section of the Cluster Information page

How it works

Each export follows three steps:

1. Connect to the AnalyticDB for MySQL cluster.

2. Create an external table in AnalyticDB for MySQL that maps to the destination MaxCompute table.

3. Run an INSERT statement to write data from the AnalyticDB for MySQL table to the MaxCompute partition.

The external table uses ENGINE='ODPS' and a TABLE_PROPERTIES JSON object to establish the connection. AnalyticDB for MySQL can only reach MaxCompute through virtual private cloud (VPC) endpoints — public endpoints are not supported.

Export data to a MaxCompute table with hash partitions

This example exports data from adb_table in AnalyticDB for MySQL to odps_table, a MaxCompute table with a single-level hash partition on the ds column.

Step 1: Connect to your AnalyticDB for MySQL cluster

See Connect to an AnalyticDB for MySQL cluster.

Step 2: Create an external table

CREATE TABLE odps_external_table
(
uid string,
ds string
)
ENGINE='ODPS'
TABLE_PROPERTIES='{
"endpoint":"http://service.odps.aliyun-inc.com/api",
"accessid":"<your-accesskey-id>",
"accesskey":"<your-accesskey-secret>",
"project_name":"<your-maxcompute-project>",
"table_name":"odps_table",
"partition_column":"ds"
}'

Step 3: Export data to the target partition

INSERT [OVERWRITE] INTO odps_external_table PARTITION(ds='20200401')
SELECT [adb_table_column, ...] FROM adb_table [WHERE ...]

Export data to a MaxCompute table with list partitions

This example exports data from adb_table to odps_table, a MaxCompute table with a two-level list partition on ds and other.

Step 1: Connect to your AnalyticDB for MySQL cluster

See Connect to an AnalyticDB for MySQL cluster.

Step 2: Create an external table

CREATE TABLE odps_external_table
(
uid string,
ds string,
other string
)
ENGINE='ODPS'
TABLE_PROPERTIES='{
"endpoint":"http://service.odps.aliyun-inc.com/api",
"accessid":"<your-accesskey-id>",
"accesskey":"<your-accesskey-secret>",
"project_name":"<your-maxcompute-project>",
"table_name":"odps_table",
"partition_column":"ds,other"
}'

For a list-partitioned table with multiple partition levels, set partition_column to a comma-separated list of partition columns in level order.

Step 3: Export data to the target partition

INSERT [OVERWRITE] INTO odps_external_table PARTITION(ds='20200401',other='hangzhou')
SELECT [adb_table_column, ...] FROM adb_table [WHERE ...]

What's next

• Table operations — create MaxCompute tables with higher partition levels

• Endpoints — look up the VPC endpoint for your MaxCompute region