CREATE EXTERNAL DATABASE - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL supports creating external databases with the CREATE EXTERNAL DATABASE statement. This topic describes the syntax, usage notes, and examples of the CREATE EXTERNAL DATABASE statement.

Prerequisites

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
To create an OSS external database across accounts, you must first create a RAM role, modify its trust policy, and grant the AliyunOSSReadOnlyAccess permission to the role. For more information, see Cross-account authorization.
Important
For as INSERT, on an OSS external table, grant the AliyunOSSFullAccess permission to the RAM role.
To create a Paimon external database, the cluster version must be 3.2.6.1 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.

Usage notes

The Spark engine does not support the CREATE EXTERNAL DATABASE statement. You must use the XIHE engine.
In an external database created using this command, you can create external tables only using the CREATE EXTERNAL TABLE syntax. For more information, see CREATE EXTERNAL TABLE.
Cross-account creation is supported only for OSS external databases and tables. This operation is not supported for other types, such as external tables for RDS for MySQL or MongoDB.

Syntax

CREATE EXTERNAL DATABASE [IF NOT EXISTS] <db_name> 
[WITH DBPROPERTIES(
catalog='oss',
location = '<oss_location>',
cross_account_accessing_arn= '<ARN>')]

Parameters

Parameter	Required	Description
db_name	Yes	The name of the database.
catalog	No Note This parameter is required only when you create an OSS external database across accounts.	The database engine. Set this parameter to oss when you create an OSS external database across accounts.
location	No	The path of the OSS file or folder. For a Paimon-formatted file, specify the path of the folder that contains the `*.db` file. For example, if the storage path of the Paimon file is `oss://testBucketName/OSS/Paimon/paimon.db/test`, set the file path to `oss://testBucketName/OSS/Paimon/paimon.db/`. After you specify a path, AnalyticDB for MySQL can access only the files in that path. For example, the `testBucketname` bucket contains two folders: `test` and `person`. If you set the OSS path to `oss://testBucketname/test/`, you can access only the files in the `test` folder. You cannot access the files in the `person` folder.
cross_account_accessing_arn	No Note This parameter is required only when you create an OSS external database across accounts.	The Alibaba Cloud Resource Name (ARN) of the RAM role. For more information about how to view the ARN, see View the information of a RAM role.

Examples

Create an external database within the same account.

CREATE EXTERNAL DATABASE IF NOT EXISTS adb_demo;

CREATE EXTERNAL DATABASE IF NOT EXISTS db_external_test
WITH DBPROPERTIES(
location = 'oss://testBucketname/person');

Create an external database across accounts.

CREATE EXTERNAL DATABASE IF NOT EXISTS adb_demo1 
WITH DBPROPERTIES(
catalog='oss',
location = 'oss://testBucketname/test/',
cross_account_accessing_arn= 'acs:ram::16274839*****:role/username');

Create a Paimon external database.

CREATE EXTERNAL DATABASE IF NOT EXISTS paimon_complex
WITH DBPROPERTIES(location = 'oss://testBucketName/paimon/paimon_complex.db/')