CREATE EXTERNAL CATALOG

Description

This statement is used to create an external catalog in a database. It is used to connect to external data sources and retrieve metadata of external data. You can directly query external data without importing or migrating it.

Limitations and considerations

Currently, you can only create an external catalog of the ODPS type.

Permissions

To execute the CREATE EXTERNAL CATALOG statement, you must have the CREATE CATALOG privilege. For more information about the privileges of seekdb, see Privilege types.

Syntax

CREATE EXTERNAL CATALOG [IF NOT EXISTS] external_catalog_name
    PROPERTIES [=] (
        TYPE = 'ODPS',
        [ACCESSTYPE = 'accesstype_string',]
        ACCESSID = 'string',
        ACCESSKEY = 'string',
        STSTOKEN = 'string',
        ENDPOINT = 'string',
        TUNNEL_ENDPOINT = 'string',
        PROJECT_NAME = 'string',
        [QUOTA_NAME = 'string',]
        [COMPRESSION = 'compression_string',]
        REGION = 'string'
    );

accesstype_string:
    aliyun
    | sts
    | app

compression_string:
    zlib
    | zstd
    | lz4
    | odps_lz4

Parameters

Parameter	Description
IF NOT EXISTS	Optional. If you specify this clause, the system will not return an error when the external catalog already exists in the current tenant. Instead, it will return a warning. If you do not specify this clause, an error will be returned.
external_catalog_name	The name of the external catalog to be created.
TYPE	The type of the external catalog. The value is ODPS in the current version. When reading and writing data from and to MaxCompute, the value is ODPS.
ACCESSTYPE	Optional. The type of the MaxCompute account. The default value is aliyun. Valid values (case-insensitive): aliyun sts app
ACCESSID	The AccessKey ID of the aliyun or app account, or a RAM user with MaxCompute access permissions. This parameter is required when the value of ACCESSTYPE is aliyun or app.
ACCESSKEY	The AccessKey secret of the aliyun or app account, or a RAM user with MaxCompute access permissions. This parameter is required when the value of ACCESSTYPE is aliyun or app.
STSTOKEN	The token for accessing MaxCompute services. This parameter is required when the value of ACCESSTYPE is sts.
ENDPOINT	The endpoint (domain name) of MaxCompute.
TUNNEL_ENDPOINT	The tunnel endpoint. MaxCompute catalogs use the Tunnel SDK to obtain data.
PROJECT_NAME	The name of the project space in MaxCompute. A project space is the basic organizational unit of MaxCompute, similar to the concept of a database or schema in traditional databases.
QUOTA_NAME	Optional. The quota in MaxCompute represents a resource pool (computing, access, and write). If you have configured a corresponding quota, you can specify it using this parameter.
COMPRESSION	Optional. The compression format of the data source. If this parameter is not specified, compression is not enabled. Valid values (case-insensitive): zlib zstd lz4 odps_lz4
REGION	The region where MaxCompute is enabled.

Examples

CREATE EXTERNAL CATALOG test_odps_catalog
    PROPERTIES = (
        TYPE = 'ODPS',
        ACCESSID = '$odps_accessid',
        ACCESSKEY = '$odps_accesskey',
        ENDPOINT = '$odps_endpoint',
        TUNNEL_ENDPOINT = 'http://dt.cn-hangzhou.maxcompute.aliyun.com',
        PROJECT_NAME = 'mysqltest_regression_sqlqa',
        QUOTA_NAME = '',
        COMPRESSION_CODE = ''
    );