Batch create MaxCompute foreign tables - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL allows you to use the IMPORT FOREIGN SCHEMA statement to batch create MaxCompute foreign tables.

Prerequisites

An AnalyticDB for MySQL cluster of V3.2.2.0 or later is created.
Note
- To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.
- For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.
Elastic Network Interface (ENI) is enabled for the AnalyticDB for MySQL cluster.
Note
You can log on to the AnalyticDB for MySQL console to turn on ENI in the Network Information section of the Cluster Information page.
A MaxCompute project is created in the same region as the AnalyticDB for MySQL cluster.
The CIDR blocks of the virtual private cloud (VPC) in which the AnalyticDB for MySQL cluster resides are added to an IP address whitelist of the MaxCompute project.
Note
You can log on to the AnalyticDB for MySQL console to view the VPC ID on the Cluster Information page. Then, you can log on to the VPC console and find the VPC ID on the VPC page to view the CIDR blocks. For information about how to configure an IP address whitelist in MaxCompute, see Manage IP address whitelists.
A foreign server is created for the AnalyticDB for MySQL cluster. For syntax about foreign servers, see the "Manage foreign servers" section of this topic.

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 name of the MaxCompute table that you want to import. If no limit is imposed on the table name, the system batch creates all tables that match the MaxCompute project in the AnalyticDB for MySQL cluster.
`server_name`	Yes	The name of the foreign server. For syntax about foreign servers, see the "Manage foreign servers" section of this topic.
`adb_schema`	Yes	The name of the database in the AnalyticDB for MySQL cluster.
`if_table_exist`	No	The method that is used to handle table name conflicts in AnalyticDB for MySQL. Valid values: error (default): reports an error and lists the tables that have the same name. In this case, no tables are created. ignore: skips the creation of tables that have the same name. In this case, other tables are created as expected. update: overwrites the original table that has the same name and creates a new table. In this case, other tables are created as expected.
`if_type_unsupport`	No	The method that is used to handle unsupported data types in AnalyticDB for MySQL. Valid values: error (default): reports an error and lists the unsupported data types and relevant tables. ignore: skips the creation of tables that have unsupported data types.
`table_prefix`	No	The prefix of the name of the foreign table. By default, the foreign table in AnalyticDB for MySQL uses the same name as the matching table in MaxCompute.
`table_suffix`	No	The suffix of the name of the foreign table. By default, the foreign table in AnalyticDB for MySQL uses the same name as the matching table in MaxCompute.

Example

Create MaxCompute foreign tables named pre_customer_suf and pre_order_suf in AnalyticDB for MySQL.

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 a 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 can be up to 64 characters in length and can contain letters, digits, and underscores (_). The name must start with a letter or an underscore (_).
`TYPE='ODPS'`	Yes	The type of the foreign server. Set this parameter to ODPS.
`endpoint`	Yes	The endpoint of MaxCompute. Note You can access MaxCompute only by using VPC endpoints. For information about MaxCompute endpoints, see Endpoints.
`accessid`	Yes	The AccessKey ID of an Alibaba Cloud account or a Resource Access Management (RAM) user that has the permissions to access MaxCompute. For information about how 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 RAM user that has the permissions to access MaxCompute. For information about how 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":"LTAI5t1****",
    "accesskey":"mng0dqJ4dBsCY0KTNG****"
}';

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 of a specific type

Syntax

SHOW SERVER WHERE SERVER_TYPE='<server_type>'

Parameters

Parameter	Required	Description
`server_type`	Yes	The foreign server type. Set this parameter to ODPS for MaxCompute.

Example

SHOW SERVER WHERE SERVER_TYPE='ODPS';

Rename a 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 a foreign server

Note

After you delete a foreign server, the connection that is established between AnalyticDB for MySQL and MaxCompute by using the foreign server is disconnected.

Syntax

DROP SERVER <server_name>

Parameters

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

Example

DROP SERVER maxcompute_server;