How to create OSS external tables, RDS MySQL external tables, Tablestore external tables, and MaxCompute external tables - AnalyticDB

AnalyticDB for MySQL supports creating various types of external tables, such as OSS, RDS MySQL, MongoDB, Tablestore, and MaxCompute external tables.

Prerequisites

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
The kernel version of the cluster is 3.1.8.0 or later.
Note
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.
An external database must be created. For more information, see CREATE EXTERNAL DATABASE.

Usage notes

Cross-account creation is supported only for OSS external tables.

OSS external tables

Important

The OSS bucket must be in the same region as the AnalyticDB for MySQL cluster.
When you create Hudi, Iceberg, or Paimon external tables, the kernel version of the cluster must meet the following requirements:
- Hudi external tables: The kernel version of the cluster must be 3.1.9.2 or later.
- Iceberg external tables: The kernel version of the cluster must be 3.2.3.0 or later.
- Paimon external tables: The kernel version of the cluster must be 3.2.4.0 or later.
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.
After you create a partitioned OSS external table, you must execute the MSCK REPAIR TABLE statement to synchronize the partitions of the external table. Otherwise, you cannot query data from the external table.
To create an OSS external table across Alibaba Cloud 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|RCFIL|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	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. The table name and column names must be in lowercase.
`PARTITIONED BY (column_name column_type[, …])`	No	When you create a partitioned external table, you must configure this parameter to specify partition key columns. If you specify multiple partition key columns, a multi-level partitioned table is created.
`ROW FORMAT DELIMITED FIELDS TERMINATED BY ','`	Yes	The column delimiter. You can specify any symbol, but it must be the same as the separator in the file. This topic uses a comma (,) as an example. Important This parameter is supported only when `STORED AS TEXTFILE` or `STORED AS JSON` is specified.
`STORED AS {TEXTFILE\|ORC\|PARQUET\|JSON\|RCFILE\|HUDI\|ICEBERG}`	Yes	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 STRUCTs. Important Only clusters with a kernel version of 3.1.8.0 or later support `PARQUET` files that contain the STRUCT data type.
`LOCATION`	Yes	The path of the OSS file or folder. When you specify the path of an OSS folder, follow these rules. Otherwise, queries may fail or return abnormal results. The folder path must end with a forward slash (`/`). All files in the folder must be in the same format. All files in the folder must have the same number of fields, field order, and field data types. When you create a partitioned external table, set LOCATION to the parent directory of the partition. For example, the path of an OSS file is `oss://testBucketname/testfolder/p1=2023-06-13/data.csv`. In this case, you must set `LOCATION 'oss://testBucketname/testfolder/'` to create a partitioned external table that uses p1 as a partition key column. Important When you create a Hudi external table, make sure that a Hudi metadata file, which is the `.hoodies` file, exists in the specified path. If you set `auto.create.location=true`, an OSS folder is automatically created if the path specified by `LOCATION` does not exist when you create a partitioned external table.
`type`	No	The type of the Hudi external table. Valid values: COW (default): This value is suitable for scenarios that require high read efficiency. MOR: This value is suitable for scenarios that require high write efficiency. Important This parameter is required only when `STORED AS HUDI` is specified.
`auto.create.location`	No	Specifies whether to automatically create the path for the OSS file or folder. Valid values: true: Yes. false (default): No. Important This parameter takes effect only when you create a partitioned external table.
`metadata_location`	No	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 can query the latest data.

Examples

Example 1: Create a non-partitioned external table

Set the file storage format to 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';

Set the file storage format to 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');

Set the file storage format to 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';

Set the file storage format to 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');

Set the file storage format to PAIMON.

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/';

RDS MySQL external tables

Important

To create ApsaraDB RDS for MySQL external tables, you must first enable the elastic network interface (ENI) on the Cluster Information page of the AnalyticDB for MySQL console. When you enable or disable the ENI, database connections may be interrupted for approximately 2 minutes. During this period, you cannot perform read or write operations. Enable or disable the ENI with caution.
The RDS MySQL 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='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	The name and schema of the table. For 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 and write data to RDS MySQL, set the value to MYSQL.
`TABLE_PROPERTIES`	Yes	The properties of the external table.
`url`	Yes	The internal endpoint, port number, and database name of the RDS MySQL instance. For information about how to obtain the internal endpoint of an RDS instance, see View or change the internal and public endpoints and ports.
`tablename`	Yes	The name of the table in RDS MySQL.
`username`	Yes	The database account of RDS MySQL.
`password`	Yes	The password of the RDS 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"
}';

MongoDB external tables

Important

Before you create a MongoDB external table, you must enable the elastic network interface (ENI) on the Cluster Information page of the AnalyticDB for MySQL console. When you enable or disable the ENI, database connections may be interrupted for approximately 2 minutes. During this period, you cannot perform read or write operations. Enable or disable the ENI with caution.
The MongoDB instance must be in the same 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	The name and schema of the table. For 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 and write data to MongoDB, set the value to MONGODB.
`TABLE_PROPERTIES`	Yes	The properties of the external table.
mapped_name	Yes	The name of the MongoDB collection.
location	Yes	The VPC endpoint of the ApsaraDB for MongoDB instance.
username	Yes	The name of the database account of the ApsaraDB for MongoDB instance. Note ApsaraDB for MongoDB verifies the specified database account name and password. You must use the database account name that is included in the VPC endpoint of the ApsaraDB for MongoDB instance. If you have any questions, contact technical support.
password	Yes	The password of the database account of the ApsaraDB for MongoDB instance.

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 attached to a VPC, the instance must be in the same VPC as the AnalyticDB for MySQL cluster.

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	The name and schema of the table. For information about the naming conventions for tables and columns, see Naming conventions.
`ENGINE='OTS’`	Yes	The storage engine of the external table. To read data from and write data to Tablestore, set the value to OTS.
`mapped_name`	Yes	The name of the table in the Tablestore instance. You can log on to the Tablestore console and view the table name on the Instance Management page.
`location`	Yes	The VPC endpoint of the Tablestore instance. You can log on to the Tablestore console and view 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 must be in the same region as the AnalyticDB for MySQL cluster.
To create MaxCompute external tables in batches, 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	Required	Description
`table_name (column_name column_type[, …])`	Yes	The name and schema of the table. The table schema must include partition key columns. table_name and column_name: the name of the table and the name of a column. For information about the naming conventions for tables and columns, see Naming conventions. column_type: MaxCompute basic data types and complex data types (ARRAY, MAP, and STRUCT) are supported. Note MaxCompute complex data types are supported in versions 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 of the external table. To read data from and write data to MaxCompute, set the value to ODPS.
`endpoint`	Yes	The Endpoint of MaxCompute. Note You can access MaxCompute only over a VPC Endpoint. For information about how to view 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.
`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"
}';

References

For more information about OSS external tables, see Import data from OSS to a Data Lakehouse Edition cluster using an external table.
For more information about RDS MySQL external tables, see Import data from RDS MySQL using an external table.
For more information about MongoDB external tables, see Import data from MongoDB using an external table.
For more information about Tablestore external tables, see Query and import data from Tablestore.
For more information about MaxCompute external tables, see Import data from MaxCompute using an external table.