CREATE EXTERNAL DATABASE - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL allows you to execute the CREATE EXTERNAL DATABASE statement to create external databases. This topic describes the syntax, usage notes, and examples of the CREATE EXTERNAL DATABASE statement.

Prerequisites

An AnalyticDB for MySQL Data Lakehouse Edition cluster is created.
Before you create an Object Storage Service (OSS) external database across Alibaba Cloud accounts, the following requirements are met: A Resource Access Management (RAM) role is created and the AliyunOSSReadOnlyAccess policy is attached to the RAM role. For more information, see Perform authorization.
Important
To perform data update operations such as INSERT on OSS external tables in an OSS external database, you must attach the AliyunOSSFullAccess policy to the RAM role.

Usage notes

The Spark engine does not allow you to execute the CREATE EXTERNAL DATABASE statement to create external databases. Use the XIHE engine to perform this operation.
You can use only the CREATE EXTERNAL TABLE statement to create external tables in the external databases that are created by executing the CREATE EXTERNAL DATABASE statement. For more information, see CREATE EXTERNAL TABLE.
When you create external databases and external tables across Alibaba Cloud accounts, only OSS external databases and external tables can be created. Other types of external databases and external tables, such as ApsaraDB RDS for MySQL external tables and ApsaraDB for MongoDB external tables, cannot be created.

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 The parameters are required only when you create an OSS external database across Alibaba Cloud accounts.	The database engine. Set the value to OSS. Important When you create external databases across Alibaba Cloud accounts, only OSS external databases can be created.
location		The path of the OSS object or directory. Important After you specify a path, AnalyticDB for MySQL can access files only in this path. Assume that the `test` and `person` directories exist in the `testBucketname` bucket. If you set the OSS path to `oss://testBucketname/test/`, files only in the `test` directory can be accessed. Files in the `person` directory cannot be accessed.
cross_account_accessing_arn		The Alibaba Cloud Resource Name (ARN) of a RAM role. For information about how to view the ARN of a RAM role, see View the information about a RAM role.

Examples

Create an external database within the same Alibaba Cloud account.
```
CREATE EXTERNAL DATABASE IF NOT EXISTS adb_demo;     
```

Create an external database across Alibaba Cloud 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');