Create OSS, ApsaraDB RDS for MySQL, Tablestore, and MaxCompute external tables - AnalyticDB

AnalyticDB for MySQL supports creating external tables for OSS, ApsaraDB RDS for MySQL, ApsaraDB for MongoDB, Tablestore, and MaxCompute.

Prerequisites

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
The minor version of the cluster is 3.1.8.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.
You must create an external database. For more information, see CREATE EXTERNAL DATABASE.

Usage notes

Only OSS external tables support cross-account creation.

OSS external tables

Important

The OSS bucket must be in the same region as the AnalyticDB for MySQL cluster.
To create Hudi, Iceberg, or Paimon external tables, your cluster must meet the following minor version requirements:
- Hudi external tables: cluster minor version 3.1.9.2 or later.
- Iceberg external tables: cluster minor version 3.2.3.0 or later.
- Paimon external tables: cluster minor version 3.2.6.1 or later.
To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.
After you create a partitioned OSS external table, run the MSCK REPAIR TABLE statement to synchronize partitions. Otherwise, you cannot query data from the external table.
To create an OSS external table across accounts, you must add the required parameters when you create the external database. For more information, see CREATE EXTERNAL DATABASE.

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, ...])
[PARTITIONED BY (column_name column_type[, ...])]
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS {TEXTFILE|ORC|PARQUET|JSON|RCFILE|HUDI|ICEBERG|PAIMON}
LOCATION 'OSS_LOCATION'
[TBLPROPERTIES (
 'type' = 'cow|mor',
 'auto.create.location' = 'true|false',
 'metadata_location' = 'METADATA_LOCATION'
)]

Parameters

Parameter	Required	Description
`table_name (column_name column_type[, …])`	Yes	Defines the name and schema of the table. For information about the naming conventions for tables and columns, see Naming conventions. Important When you create a Paimon external table, the table name, column names, and column data types must be the same as those in the Paimon file. If the table schema (field names or types) is inconsistent with the Paimon schema, the Paimon schema prevails.
`PARTITIONED BY (column_name column_type[, …])`	No	Specifies the partition key column. This parameter is required when you create a partitioned external table. To create a multi-level partitioned table, specify multiple partition key columns.
`ROW FORMAT DELIMITED FIELDS TERMINATED BY ','`	Yes	Specifies the column delimiter. You can specify any character, but it must match the delimiter in the source file. This topic uses a comma (,) as an example. Important This parameter is supported only when you specify `STORED AS TEXTFILE` or `STORED AS JSON`.
`STORED AS {TEXTFILE\|ORC\|PARQUET\|JSON\|RCFILE\|HUDI\|ICEBERG\|PAIMON}`	Yes	Specifies the file format. If the file is in .txt or .csv format, set this parameter to `STORED AS TEXTFILE`. Files in `PARQUET` format support the STRUCT data type and nested data structures. Important Only clusters with minor version 3.1.8.0 or later support `PARQUET` files that use the STRUCT data type.
`LOCATION`	Yes	Specifies the path of the OSS file or directory. When you specify an OSS directory path, follow these rules. Otherwise, query failures or unexpected results may occur. A directory path ends with `/`. All files in the directory must have the same file format. All files in the directory must have the same number of fields, field order, and field types. When you create a partitioned external table, set LOCATION to the parent directory of the partitions. For example, if the OSS file path is `oss://testBucketname/testfolder/p1=2023-06-13/data.csv`, you must specify `LOCATION 'oss://testBucketname/testfolder/'` to create a partitioned external table with `p1` as the partition key column. Important When you create a Hudi external table, make sure that a Hudi metadata file named `.hoodies` exists in the specified path. If you set `auto.create.location=true` and the path specified by `LOCATION` does not exist when you create a partitioned external table, the system automatically creates an OSS directory.
`type`	No	The Hudi external table type. Valid values: COW (default): Copy on Write. This type is suitable for read-heavy workloads. MOR: Merge on Read. This type is suitable for write-heavy workloads. Important This parameter is required only when `STORED AS HUDI` is specified.
`auto.create.location`	No	Specifies whether to automatically create the OSS file or directory path. Valid values: true: The path is automatically created. false (default): The path is not automatically created. Important This parameter takes effect only when you create a partitioned external table.
`metadata_location`	No	Specifies the path of the metadata file for the Iceberg external table. Important This parameter is required only when `STORED AS ICEBERG` is specified. Use the latest metadata file to ensure that you query the most recent data.

Examples

Example 1: Create a non-partitioned external table

Specify the file storage format as TEXTFILE.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest1
(id INT,
name STRING,
age INT,
city STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://testBucketName/osstest/p1=hangzhou/p2=2023-06-13/data.csv';

Specify the file storage format as HUDI.
```
CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest2
(id INT,
name STRING,
age INT,
city STRING)
STORED AS HUDI
LOCATION  'oss://testBucketName/osstest/test'
TBLPROPERTIES ('type' = 'cow');
```
Important
When you create a Hudi external table, five fixed columns are automatically created: _hoodie_commit_time, _hoodie_commit_seqno, _hoodie_record_key, _hoodie_partition_path, and _hoodie_file_name.

Specify the file storage format as PARQUET.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest3
(
A STRUCT < var1:STRING, var2:INT >) 
STORED AS PARQUET 
LOCATION 'oss://testBucketName/osstest/Parquet';

Specify the file storage format as ICEBERG.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest4
(
user_id BIGINT)  
STORED AS ICEBERG  
LOCATION 'oss://testBucketName/osstest/no_partition_table/' 
TBLPROPERTIES (metadata_location='oss://testBucketName/osstest/no_partition_table/metadata/00000-a32d6136-8490-4ad2-ada3-fe2f7204199f.metadata.json');

Specify the file storage format as P*****.

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest5
(
a INT,
b BIGINT,
aCa STRING, 
d VARCHAR(1))
STORED AS PAIMON 
LOCATION 'oss://testBucketName/osstest/default.db/t1/';

Example 2: Create a partitioned external table

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest6
(id int,
name string,
age int,
city string)
PARTITIONED BY (p2 string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://testBucketName/osstest/p1=hangzhou/';

Example 3: Create a multi-level partitioned external table

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest7
(id int,
name string,
age int,
city string)
PARTITIONED BY (p1 string,p2 string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://testBucketName/osstest/';

ApsaraDB RDS for MySQL external tables

Important

Before you create an ApsaraDB RDS for MySQL external table, go to the AnalyticDB for MySQL console and enable the elastic network interface (ENI) switch on the Cluster Information page. Enabling or disabling the ENI network interrupts database connections for about 2 minutes. During this time, you cannot read data from or write data to the database. Carefully evaluate the impact before you enable or disable the ENI network.
The ApsaraDB RDS for MySQL instance and the AnalyticDB for MySQL cluster must be in the same virtual private cloud (VPC).

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='MYSQL'
TABLE_PROPERTIES='{  
	"url":"mysql_vpc_address",  
	"tablename":"mysql_table_name",  
	"username":"mysql_user_name",  
	"password":"mysql_user_password"
	[,"charset":"{gbk|utf8|utf8mb4}"]
  }';

Parameters

Parameter	Required	Description
`table_name (column_name column_type[, …])`	Yes	Defines the table name and structure. For more information about the naming conventions for tables and columns, see Naming conventions.
`ENGINE='MYSQL'`	Yes	The storage engine of the external table. To read data from or write data to an ApsaraDB RDS for MySQL database, set this parameter to MYSQL.
`TABLE_PROPERTIES`	Yes	The properties of the external table.
`url`	Yes	The VPC endpoint, port number, and database name of the ApsaraDB RDS for MySQL instance. For more information about how to obtain the VPC endpoint, see View or modify the internal and public endpoints and port numbers.
`tablename`	Yes	The name of the table in the ApsaraDB RDS for MySQL database.
`username`	Yes	The username for the ApsaraDB RDS for MySQL database.
`password`	Yes	The password for the ApsaraDB RDS for MySQL database account.
`charset`	No	The character set of the MySQL external table. Valid values: gbk utf8 (default) utf8mb4

Example

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.mysqltest (
	id int,
	name varchar(1023),
	age int
 ) ENGINE = 'MYSQL'
 TABLE_PROPERTIES = '{
   "url":"jdbc:mysql://rm-bp1gx6********.mysql.rds.aliyuncs.com:3306/test_adb",
   "tablename":"person",
   "username":"testUserName",
   "password":"testUserPassword",
   "charset":"utf8"
}';

ApsaraDB for MongoDB external tables

Important

Before creating an ApsaraDB for MongoDB external table, enable the elastic network interface (ENI) switch on the Cluster Information page of the AnalyticDB for MySQL console. Enabling or disabling the ENI network interrupts database connections for about 2 minutes. During this time, you cannot read from or write to the database. Carefully assess the impact before you enable or disable the ENI network.
The ApsaraDB for MongoDB external table instance must be in the same virtual private cloud (VPC) as the AnalyticDB for MySQL cluster.

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='MONGODB'
TABLE_PROPERTIES = '{
	"mapped_name":"table",
  "location":"location",
  "username":"user",
  "password":"password",
}';

Parameters

Parameter	Required	Description
`table_name (column_name column_type[, …])`	Yes	Defines the table name and structure. For more information about the naming conventions for tables and columns, see Naming conventions.
`ENGINE='MONGODB'`	Yes	The storage engine for the external table. To read data from and write data to ApsaraDB for MongoDB, set this parameter to MONGODB.
`TABLE_PROPERTIES`	Yes	The properties of the external table.
mapped_name	Yes	The name of the ApsaraDB for MongoDB collection.
location	Yes	ApsaraDB for MongoDB VPC endpoint.
username	Yes	ApsaraDB for MongoDB database account. Note ApsaraDB for MongoDB needs to verify the account and password for the target database. Use the database account specified in the ApsaraDB for MongoDB VPC endpoint. If you encounter any problems, contact technical support.
password	Yes	The password for the ApsaraDB for MongoDB database username.

Example

CREATE EXTERNAL TABLE adb_external_demo.mongodbtest (
  id int,
  name string,
  age int
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"person",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
}';

Tablestore external tables

Important

If a Tablestore instance is associated with a virtual private cloud (VPC), the associated VPC must be the same as the VPC where the AnalyticDB for MySQL cluster resides.

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OTS'
TABLE_PROPERTIES = '{
	"mapped_name":"table_name",
	"location":"tablestore_vpc_address"
}';

Parameters

Parameter	Required	Description
`table_name (column_name column_type[, …])`	Yes	Defines the table name and structure. For the naming conventions for table and column names, see Naming conventions.
`ENGINE='OTS’`	Yes	The storage engine for the external table. Set this parameter to `OTS` to read data from and write data to Tablestore.
`mapped_name`	Yes	The name of the table in the Tablestore instance. Log on to the Tablestore console and find the table name on the Instance Management page.
`location`	Yes	The VPC endpoint of the Tablestore instance. Log on to the Tablestore console and find the VPC endpoint of the instance on the Instance Management page.

Example

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.otstest (
	id int,
	name string,
	age int
) ENGINE = 'OTS' 
TABLE_PROPERTIES = '{
	"mapped_name":"person",
	"location":"https://w0****la.cn-hangzhou.vpc.tablestore.aliyuncs.com"
}';

MaxCompute external tables

Important

The MaxCompute project and the AnalyticDB for MySQL cluster must be in the same region.
To create MaxCompute external tables in bulk, see IMPORT FOREIGN SCHEMA.

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='ODPS'
TABLE_PROPERTIES='{
	"endpoint":"endpoint",
	"accessid":"accesskey_id",
	"accesskey":"accesskey_secret",
	["partition_column":"partition_column"],
	"project_name":"project_name",
	"table_name":"table_name"
}';

Parameters

Parameter	是否必填	Description
`table_name (column_name column_type[, …])`	Yes	Defines the table name and schema. The schema must include the partition key column. `table_name` and `column_name`: The names of the table and columns. For more information about naming conventions, see Naming conventions. `column_type`: Supports basic and complex MaxCompute data types, such as ARRAY, MAP, and STRUCT. Note MaxCompute complex data types are supported in minor version 3.2.1.0 and later. For more information about complex data types, see Complex data types. To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.
`ENGINE='ODPS'`	Yes	The storage engine for the external table. Set this to `ODPS` to read data from or write data to MaxCompute.
`endpoint`	Yes	The endpoint of MaxCompute. Note You can access MaxCompute only through a virtual private cloud (VPC) endpoint. For more information about how to view the MaxCompute endpoint, see Endpoints.
`accessid`	Yes	The AccessKey ID of your Alibaba Cloud account or a RAM user that has permissions to access MaxCompute. For more information about how to obtain an AccessKey ID and AccessKey Secret, see Account and permissions.
`accesskey`	Yes	The AccessKey Secret of your Alibaba Cloud account or a RAM user that has permissions to access MaxCompute. For more information about how to obtain an AccessKey ID and AccessKey Secret, see Account and permissions.
`partition_column`	No	The partition key column. This parameter is required if the MaxCompute table is a partitioned table.
`project_name`	Yes	The name of the MaxCompute project.
`table_name`	Yes	The name of the MaxCompute table.

Example

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.mctest (
	id int,
	name varchar(1023),
	age int,
	dt string
) ENGINE='ODPS'
TABLE_PROPERTIES='{
	"accessid":"LTAI****************",
	"endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
	"accesskey":"yourAccessKeySecret",
	"partition_column":"dt",
	"project_name":"test_adb",
	"table_name":"person"
}';

AnalyticDB:CREATE EXTERNAL TABLE

Prerequisites

Usage notes

OSS external tables

Syntax

Parameters

Examples

Example 1: Create a non-partitioned external table

Example 2: Create a partitioned external table

Example 3: Create a multi-level partitioned external table

ApsaraDB RDS for MySQL external tables

Syntax

Parameters

Example

ApsaraDB for MongoDB external tables

Syntax

Parameters

Example

Tablestore external tables

Syntax

Parameters

Example

MaxCompute external tables

Syntax

Parameters

Example

Related documents