AnalyticDB:IMPORT FOREIGN SCHEMA

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for MySQL cluster running V3.2.2.0 or later

  To view and update the minor version, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

• Elastic Network Interface (ENI) enabled on the cluster

  Important

  To enable ENI, log on to the AnalyticDB for MySQL console, go to the cluster details page, and choose Cluster Management > Cluster Information in the left-side navigation pane. In the Network Information section, turn on ENI. Enabling or disabling ENI interrupts database connections for approximately 2 minutes. No read or write operations can be performed during this period.

• A MaxCompute project in the same region as your AnalyticDB for MySQL cluster

• The CIDR blocks of the virtual private cloud (VPC) where your cluster resides added to the IP address whitelist of the MaxCompute project

  To find the CIDR blocks, view the VPC ID on the Cluster Information page of the AnalyticDB for MySQL console, then look up the VPC in the VPC console. For how to configure the whitelist in MaxCompute, see Manage IP address whitelists.

• A foreign server created for the cluster — see Create a foreign server in this topic

Quick start

The following end-to-end example shows the complete workflow: create a foreign server, then import foreign tables.

Step 1: Create a foreign server.

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

Step 2: Import all tables from a MaxCompute project.

IMPORT FOREIGN SCHEMA my_project
FROM maxcompute_server
INTO my_adb_schema;

This creates a foreign table in AnalyticDB for MySQL for every table in my_project.

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

Examples

IMPORT FOREIGN SCHEMA my_project
FROM maxcompute_server
INTO my_adb_schema;

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

A foreign server stores the connection configuration for a MaxCompute project. Create one before running IMPORT FOREIGN SCHEMA.

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

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

SHOW SERVER

SHOW SERVER WHERE SERVER_NAME='<server_name>';

SHOW SERVER WHERE SERVER_NAME='maxcompute_server';

SHOW SERVER WHERE SERVER_TYPE='<server_type>';

SHOW SERVER WHERE SERVER_TYPE='ODPS';

ALTER SERVER <server_name> RENAME <new_server_name>

ALTER SERVER maxcompute_server RENAME mc_server;

DROP SERVER <server_name>

DROP SERVER maxcompute_server;