Batch create MaxCompute foreign tables-AnalyticDB(AnalyticDB)-阿里云帮助中心

Prerequisites

The AnalyticDB for MySQL cluster is version 3.2.2.0 or later.

Note
To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.
The elastic network interface (ENI) must be enabled for the AnalyticDB for MySQL cluster.
Important
- Log on to the AnalyticDB for MySQL console. On the Cluster Information page, turn on the ENI network switch in the Network Information section.
- Enabling or disabling the ENI network interrupts database connections for about two minutes. During this time, read and write operations are unavailable. Evaluate the potential impact before you proceed.
The MaxCompute project and the AnalyticDB for MySQL cluster must be in the same region.
The VPC CIDR block for the AnalyticDB for MySQL cluster must be in the IP address whitelist of the MaxCompute project.

Note
Log on to the AnalyticDB for MySQL console and find the VPC ID on the Cluster Information page. Then, log on to the VPC console and find the CIDR block on the VPC page based on the VPC ID. For more information about how to configure an IP address whitelist for MaxCompute, see Manage IP address whitelists.
A foreign server must be created in AnalyticDB for MySQL. For more information about the syntax, see Manage foreign servers.

Syntax

IMPORT FOREIGN SCHEMA odps_project
[LIMIT TO (table_name[, …])]
FROM server_name
INTO adb_schema
[OPTIONS (
 if_table_exist 'error|ignore|update', 
 if_type_unsupport 'error|ignore',
 table_prefix 'table_prefix',
 table_suffix 'table_suffix')]

Parameters

Parameter	Required	Description
`odps_project`	Yes	The name of the MaxCompute project.
`table_name[,…]`	No	The names of the MaxCompute tables to import. If you do not specify table names, the system imports all tables from the specified MaxCompute project as foreign tables into AnalyticDB for MySQL.
`server_name`	Yes	The name of the foreign server. For syntax details, see Manage foreign servers.
`adb_schema`	Yes	The name of the destination AnalyticDB for MySQL database.
`if_table_exist`	No	The policy for handling naming conflicts if a table with the same name already exists in the destination AnalyticDB for MySQL database. Valid values: error (Default): Reports an error and lists the conflicting tables. The operation fails and no tables are imported. ignore: Skips creating the tables that have naming conflicts and creates the remaining tables. update: Overwrites the existing tables and creates the remaining tables.
`if_type_unsupport`	No	The policy for handling data types that AnalyticDB for MySQL does not support. Valid values: error (Default): Reports an error, listing the tables that contain unsupported data types and specifying those types. ignore: Skips creating tables that contain unsupported data types.
`table_prefix`	No	The prefix to add to the name of each imported foreign table. By default, the foreign tables in AnalyticDB for MySQL have the same names as the source tables in MaxCompute.
`table_suffix`	No	The suffix to add to the name of each imported foreign table. By default, the foreign tables in AnalyticDB for MySQL have the same names as the source tables in MaxCompute.

Example

In AnalyticDB for MySQL, create the MaxCompute foreign tables pre_customer_suf and pre_order_suf.

IMPORT FOREIGN SCHEMA my_project
LIMIT TO (customer, order)
FROM maxcompute_server
INTO my_adb_schema
OPTIONS (
    if_table_exist 'update',
    if_type_unsupport 'ignore',
    table_prefix 'pre_',
    table_suffix '_suf'
);

Manage foreign servers

Create foreign server

Syntax

CREATE SERVER <server_name>
TYPE = 'ODPS'
SERVER_PROPERTIES = '{
	"endpoint":"<endpoint>",
	"accessid":"<accesskey_id>",
	"accesskey":"<accesskey_secret>"
}'

Parameters

Parameter	Required	Description
`server_name`	Yes	The name of the foreign server. The name must start with a letter or underscore (_), contain only letters, digits, and underscores, and be no more than 64 characters long.
`TYPE='ODPS'`	Yes	The type of the foreign server. Set the value to ODPS.
`endpoint`	Yes	The endpoint of MaxCompute. Note You can access MaxCompute only through a VPC endpoint. For more information about MaxCompute endpoints, see Endpoints.
`accessid`	Yes	The AccessKey ID of an Alibaba Cloud account or a MaxCompute that has permissions to access MaxCompute. To obtain an AccessKey ID and an AccessKey secret, see Accounts and permissions.
`accesskey`	Yes	The AccessKey secret of an Alibaba Cloud account or a MaxCompute that has permissions to access MaxCompute. To obtain an AccessKey ID and an AccessKey secret, see Accounts and permissions.

Example

CREATE SERVER maxcompute_server
TYPE = 'ODPS'
SERVER_PROPERTIES = '{
    "endpoint":"http://service.cn-hangzhou-vpc.maxcompute.aliyun-inc.com/api",
    "accessid":"STS.****************",
    "accesskey":"yourAccessKeySecret"
}';

Query foreign servers

Query all foreign servers

SHOW SERVER

Query a specific foreign server

Syntax

SHOW SERVER WHERE SERVER_NAME='<server_name>'

Parameters

Parameter	Required	Description
`server_name`	Yes	The name of the foreign server.

Example

SHOW SERVER WHERE SERVER_NAME='maxcompute_server';

Query foreign servers by type

Syntax

SHOW SERVER WHERE SERVER_TYPE='<server_type>'

Parameters

Parameter	Required	Description
`server_type`	Yes	The foreign server type. The server type for MaxCompute is ODPS.

Example

SHOW SERVER WHERE SERVER_TYPE='ODPS';

Rename foreign server

Syntax

ALTER SERVER <server_name> RENAME <new_server_name>

Parameters

Parameter	Required	Description
`server_name`	Yes	The original name of the foreign server.
`new_server_name`	Yes	The new name of the foreign server.

Example

ALTER SERVER maxcompute_server RENAME mc_server;

Delete foreign server

Note

Deleting a foreign server terminates the connection between AnalyticDB for MySQL and MaxCompute.

Syntax

DROP SERVER <server_name>

Parameters

Parameter	Required	Description
`server_name`	Yes	The name of the foreign server.

Example

DROP SERVER maxcompute_server;