All Products
Search
Document Center

AnalyticDB for MySQL:CREATE EXTERNAL TABLE

Last Updated:Nov 29, 2023

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, Tablestore, and MaxCompute.

Prerequisites

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.

    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.

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.

STORED AS {TEXTFILE|ORC|PARQUET|JSON|RCFILE|HUDI}

Yes

The storage format of the file.

For TXT and CSV files, specify STORED AS TEXTFILE as the parameter.

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 specify LOCATION as the upper-level directory of partitions. For example, assume that 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.

Note
  • When you create a Hudi external table, make sure that the Hudi metadata file named .hoodies exists in the OSS path.

  • If the OSS path or directory specified by LOCATION does not exist when you create a partitioned external table, you can specify auto.create.location=true for the TABLE_PROPERTIES parameter to make an OSS path or directory 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

When the STORED AS parameter is set to HUDI, you must specify this parameter.

auto.create.location

No

Specifies whether to make an OSS path or directory automatically created. Valid values:

  • true

  • false (default)

Examples

Example 1: Create a non-partitioned external table

  • Set the file storage format to TEXTFILE.

    CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_db.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_db.osstest2
    (id int,
    name string,
    age int,
    city string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
    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.

Example 2: Create a partitioned external table

CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_db.osstest3
(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_db.osstest4
(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 account password of the ApsaraDB RDS for MySQL instance.

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_db.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"
}';

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_db.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_db.mctest (
	id int,
	name varchar(1023),
	age int,
	dt sting
) 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