How to create an external table based on OSS, ApsaraDB RDS for MySQL, Tablestore, or MaxCompute - AnalyticDB for MySQL

AnalyticDB for MySQL Data Lakehouse Edition (V3.0) allows you to execute the CREATE EXTERNAL TABLE statement to create external tables based on a variety of services, such as Object Storage Service (OSS), ApsaraDB RDS for MySQL, ApsaraDB for MongoDB, Tablestore, and MaxCompute.

Prerequisites

An AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster is created.
The minor version of the cluster is 3.1.8.0 or later.
Note
For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.
An external database is created. For more information, see CREATE EXTERNAL DATABASE.

Usage notes

You cannot create an external table across Alibaba Cloud accounts.

OSS external tables

Important

The OSS bucket in which the OSS external table is created must reside in the same region as the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster.
You can create Hudi external tables only for AnalyticDB for MySQL clusters of V3.1.9.2 or later.

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}
LOCATION 'OSS_LOCATION';
[TBLPROPERTIES (
 'type' = 'cow|mor'
 'auto.create.location' = 'true|false')]

Parameters

Parameter	Required	Description
`table_name (column_name column_type[, ...])`	Yes	The name and schema of the external table. The table name and column names must comply with the naming conventions. For more information, see the "Naming limits" section of the Limits topic.
`PARTITIONED BY (column_name column_type[, ...])`	No	The partition key column. If the external table is a partitioned table, you must specify this parameter. To create a multi-level partitioned table, you can specify multiple partitions.
`ROW FORMAT DELIMITED FIELDS TERMINATED BY ','`	Yes	The column delimiter. You can specify a character that is the same as the delimiter in the file. In this example, a comma (,) is used. Important You can configure this parameter only for `STORED AS TEXTFILE` and `STORED AS JSON`.
`STORED AS {TEXTFILE\|ORC\|PARQUET\|JSON\|RCFILE\|HUDI}`	Yes	The storage format of the file. For TXT and CSV files, set this parameter to `STORED AS TEXTFILE`. `PARQUET` files support the STRUCT data type and nested data types. Important Only AnalyticDB for MySQL clusters of V3.1.8.0 or later support `PARQUET` files of the STRUCT data type.
`LOCATION`	Yes	The path or directory of the OSS object. When you specify an OSS directory, we recommend that you comply with the following rules. Otherwise, a query error or data exception may occur. The OSS directory must end with a forward slash (`/`). All objects in the directory must be stored in the same format. All objects in the directory must use the same quantity, order, and data types of fields. When you create a partitioned external table, you must set the LOCATION parameter to the upper-level directory of partitions. For example, the path of an OSS object is `oss://testBucketname/testfolder/p1=2023-06-13/data.csv`. In this case, you must specify `LOCATION 'oss://testBucketname/testfolder/'` to create a partitioned external table that has the p1 partition key column. Important When you create a Hudi external table, make sure that the Hudi metadata file named `.hoodies` exists in the OSS path. Assume that you set the `auto.create.location` parameter to true. If the OSS path or directory specified by the `LOCATION` parameter does not exist when you create a partitioned external table, an OSS path or directory is automatically created.
`type`	No	The type of the Hudi external table. Valid values: COW (default): suitable for scenarios that have high requirements on read efficiency. MOR: suitable for scenarios that have high requirements on write efficiency. Important You must specify this parameter only for `STORED AS HUDI`.
`auto.create.location`	No	Specifies whether to make an OSS path or directory automatically created. Valid values: true false (default) Important This parameter takes effect only when you create a partitioned external table.

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');
```
Important
When you create a Hudi external table, the _hoodie_commit_time, _hoodie_commit_seqno, _hoodie_record_key, _hoodie_partition_path, and _hoodie_file_name columns are automatically created.

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

Example 2: Create a partitioned external table

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_demo.osstest4
(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.osstest5
(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

When you create an ApsaraDB RDS for MySQL external table, you must enable Elastic Network Interface (ENI) on the Cluster Information page of the AnalyticDB for MySQL console.
The ApsaraDB RDS for MySQL instance must reside in the same virtual private cloud (VPC) as the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) 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 external table. The table name and column names must comply with the naming conventions. For more information, see the "Naming limits" section of the Limits topic.
`ENGINE='MYSQL'`	Yes	The storage engine of the external table. To read and write ApsaraDB RDS for MySQL data, set the storage engine to MYSQL.
`TABLE_PROPERTIES`	Yes	The properties of the external table.
`url`	Yes	The VPC endpoint and port number of the ApsaraDB RDS for MySQL instance and the name of the database in the instance. For information about how to obtain the VPC endpoint of an ApsaraDB RDS for MySQL instance, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for MySQL instance.
`tablename`	Yes	The name of the ApsaraDB RDS for MySQL external table.
`username`	Yes	The database account of the ApsaraDB RDS for MySQL instance.
`password`	Yes	The password of the database account.
`charset`	No	The character set that is used by the database in the ApsaraDB RDS for MySQL instance. Valid values: gbk utf8 (default) utf8mb4

Examples

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-bp1gx6h1tyd04****.mysql.rds.aliyuncs.com:3306/test_adb",
   "tablename":"person",
   "username":"testUserName",
   "password":"testUserPassword",
   "charset":"utf8"
}';

ApsaraDB for MongoDB external tables

Important

When you create an ApsaraDB for MongoDB external table, you must enable ENI on the Cluster Information page of the AnalyticDB for MySQL console.
The ApsaraDB for MongoDB instance must reside in the same VPC as the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) 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 external table. The table name and column names must comply with the naming conventions. For more information, see the "Naming limits" section of the Limits topic.
`ENGINE='MYSQL'`	Yes	The storage engine of the external table. To read and write ApsaraDB for MongoDB data, set the storage engine to MONGODB.
`TABLE_PROPERTIES`	Yes	The properties of the external table.
	Yes	The name of the ApsaraDB for MongoDB collection.
	Yes
	Yes	Note ApsaraDB for MongoDB verifies the specified database account and the password. You must use the database account that is contained in the VPC endpoint of the ApsaraDB for MongoDB instance. If you have any questions, contact technical support.
	Yes

Examples

CREATE EXTERNAL TABLE adb_external_demo.person (
  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

The VPC to which the Tablestore instance is bound must be the same as the VPC in which the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) 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	The name and schema of the external table. The table name and column names must comply with the naming conventions. For more information, see the "Naming limits" section of the Limits topic.
`ENGINE='OTS'`	Yes	The storage engine of the external table. To read and write Tablestore data, set the storage engine to OTS.
`mapped_name`	Yes	The name of the table in the Tablestore instance. To view the name of the table, log on to the Tablestore console and go to the Instance Management page.
`location`	Yes	The VPC endpoint of the Tablestore instance. To view the VPC endpoint of the instance, log on to the Tablestore console and go to the Instance Management page.

Examples

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 reside in the same region as the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster.

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 external table. The table name and column names must comply with the naming conventions. For more information, see the "Naming limits" section of the Limits topic. Important The defined table schema must contain the partition_column parameter.
`ENGINE='ODPS'`	Yes	The storage engine of the external table. To read and write MaxCompute data, set the storage engine to ODPS.
`endpoint`	Yes	The endpoint of the MaxCompute project. Note You can access MaxCompute only by using VPC endpoints. For more information, see Endpoints.
`accessid`	Yes	The AccessKey ID of an Alibaba Cloud account or a Resource Access Management (RAM) user that is used 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 is used 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. If the external table is a partitioned table, you must specify this parameter.
`project_name`	Yes	The name of the MaxCompute project.
`table_name`	Yes	The name of the MaxCompute table.

Examples

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

References

OSS external tables: Use external tables to import data to Data Lakehouse Edition
ApsaraDB RDS for MySQL external tables: Use external tables to import data to Data Lakehouse Edition
ApsaraDB for MongoDB external tables: Import data from ApsaraDB for MongoDB to AnalyticDB for MySQL
Tablestore external tables: Import data from Tablestore
MaxCompute external tables: Use external tables to import data to Data Lakehouse Edition