Create a catalog

This topic describes how to create an external catalog by using SQL statements.

Privilege requirements

To create an external catalog, you must have the CREATE CATALOG privilege. For more information about the privileges of OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

The syntax of the SQL statement for creating an external catalog is as follows:

CREATE EXTERNAL CATALOG [IF NOT EXISTS] external_catalog_name
    PROPERTIES [=] (properties_type_options);

properties_type_options:
    odps_type_list

odps_type_list:
    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:

• IF NOT EXISTS: optional. If you specify this clause, the system does not return an error when the external catalog to be created already exists in the current tenant. Instead, it returns a warning. If you do not specify this clause, an error is returned.
• external_catalog_name: the name of the external catalog to be created.
• PROPERTIES [=] (properties_type_options): specifies the properties of the external catalog.
• odps_type_list: specifies the properties of an ODPS external catalog.

For detailed information about the parameters used in the syntax for creating an external catalog, see CREATE EXTERNAL CATALOG.

Create an ODPS external catalog

When you read from or write to a MaxCompute table, set the value of this parameter to ODPS.

You can run the CREATE EXTERNAL CATALOG statement to create a connection to MaxCompute (ODPS), and directly query and write data in ODPS tables without manually creating mapping tables.

Here is an example:

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

References

• Overview of catalogs
• View a catalog
• Delete a catalog
• CREATE EXTERNAL CATALOG