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.4.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.

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.
When you create a Paimon external database, the database name must be in lowercase and must be the same as the Paimon database file name (*.db).

Syntax

CREATE EXTERNAL DATABASE [IF NOT EXISTS] <db_name> 
[WITH DBPROPERTIES(
catalog='oss',
adb.paimon.warehouse='<oss_location>',
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.
adb.paimon.warehouse	No Note This parameter is required only when you create a Paimon external database.	The root path where Paimon-formatted files are stored. This is the path of the parent folder of the `*.db` file. For example, if the storage path of the Paimon file is `oss://testBucketName/OSS/Paimon/paimon.db/test`, set the root path to `oss://testBucketName/OSS/Paimon/`.
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(
adb.paimon.warehouse='oss://testBucketName/paimon/',
location = 'oss://testBucketName/paimon/paimon_complex.db/')