Export data to MaxCompute - AnalyticDB for MySQL - Alibaba Cloud Documentation Center

This topic describes how to use external tables to export AnalyticDB for MySQL Data Warehouse Edition (V3.0) data to MaxCompute partitioned tables.

Prerequisites

A destination table is created in MaxCompute. For more information, see Create an Alibaba Cloud account and Create tables.
For example, one of the following tables is created in MaxCompute by executing a table creation statement. For more information, see Table operations. If you have created a destination table, skip this step.
Note To export data to a MaxCompute partitioned table, you must specify a partition to write data. Data can be exported to multiple partitions only by executing multiple SQL statements. MaxCompute supports up to six levels of partitions. You can reference the preceding operations to create tables that have higher levels of partitions.
- Create a table that has hash partitions
```
CREATE TABLE IF NOT EXISTS odps_table
(
uid STRING
 )
PARTITIONED BY (ds STRING)
LIFECYCLE 3;     
```
- Create a table that has 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 is created, a whitelist is configured, and a database account and a database are created. For more information, see Create a cluster.
If the AnalyticDB for MySQL cluster is in elastic mode, you must turn on ENI in the Network Information section of the Cluster Information page.

Export data to a MaxCompute table that has hash partitions

In the following example, data is exported from the adb_table table in an AnalyticDB for MySQL Data Warehouse Edition (V3.0) cluster to the odps_table table that has hash partitions in MaxCompute:

Connect to the AnalyticDB for MySQL Data Warehouse Edition (V3.0) cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.

Create an external table that maps to the destination table in MaxCompute.

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


Parameter	Description
ENGINE='ODPS'	The engine that is used for the external table. Set the engine to MaxCompute.
TABLE_PROPERTIES	The connection information that is used by AnalyticDB for MySQL to access and write data to MaxCompute.
endpoint	The `endpoint` of MaxCompute. Note AnalyticDB for MySQL can access MaxCompute only by using virtual private cloud (VPC) endpoints. For more information about how to view MaxCompute endpoints, see Endpoints.
accessid	The AccessKey ID that is used to access the destination table in MaxCompute.
accesskey	The AccessKey secret that is used to access the destination table in MaxCompute.
project_name	The name of the project to which the destination table belongs in MaxCompute.
table_name	The name of the destination table in MaxCompute.
partition_column	The partition field.

Export data from the AnalyticDB for MySQL external table to the MaxCompute table that has hash partitions.
Note adb_table_column does not contain the partition field.
```
insert [overwrite] into odps_external_table partition(ds='20200401')
select [adb_table_column, ...] from adb_table [where ...]
```

Export data to a MaxCompute table that has list partitions

In the following example, data is exported from the adb_table table in an AnalyticDB for MySQL Data Warehouse Edition (V3.0) cluster to the odps_table table that has list partitions in MaxCompute:

Connect to the AnalyticDB for MySQL Data Warehouse Edition (V3.0) cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.

Create an external table that maps to the destination table in MaxCompute.

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

Export data from the AnalyticDB for MySQL external table to the MaxCompute table that has list partitions.
Note adb_table_column does not contain the partition field.
```
insert [overwrite] into odps_external_table partition(ds='20200401',other='hangzhou')
select [adb_table_column, ...] from adb_table [where ...]
```