Purpose
You can execute this statement to create an external catalog in a database to connect to an external data source and obtain the metadata of external data. You can directly query the external data without importing or migrating the data.
Note
For OceanBase Database V4.3.5, the CREATE EXTERNAL CATALOG statement is supported starting from V4.3.5 BP2.
Limitations and considerations
Currently, only ODPS external catalogs can be created.
Privilege requirements
To execute the CREATE EXTERNAL CATALOG statement, the current user must have the CREATE CATALOG privilege. For more information about the privileges in OceanBase Database, see Privilege types in MySQL-compatible mode.
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 specified, no error is returned when the external data directory to be created already exists in the current tenant. Instead, a warning is returned. If this parameter is not specified, an error is returned. |
| external_catalog_name | The name of the external data directory to be created. |
| TYPE | The type of the external data directory. The value of this parameter in the current version is ODPS, which is also used when you read from and write to a MaxCompute data source. |
| ACCESSTYPE | Optional. The account type of MaxCompute. The default value is aliyun. Valid values (case-insensitive):
|
| ACCESSID | When the value of ACCESSTYPE is an aliyun/app account or is empty, it refers to the AccessKey ID of the aliyun/app account or a RAM user with MaxCompute access permissions. |
| ACCESSKEY | When the value of ACCESSTYPE is an aliyun/app account or is empty, it refers to the AccessKey secret of the aliyun/app account or a RAM user with MaxCompute access permissions. |
| STSTOKEN | When the value of ACCESSTYPE is sts account, this refers to the token used to access MaxCompute services. |
| ENDPOINT | The endpoint (domain name) of MaxCompute. |
| TUNNEL_ENDPOINT | The endpoint for tunnel data transmission. The MaxCompute catalog uses the Tunnel SDK to obtain data from MaxCompute. |
| PROJECT_NAME | The name of the project space in MaxCompute. A project is the basic organizational unit in MaxCompute, similar to the concept of a database or schema in conventional databases. |
| QUOTA_NAME | Optional. In MaxCompute, a quota represents a compute resource pool (compute, access, write). If you have configured a quota, you can specify the specific quota by 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):
|
| REGION | The region in which 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 = ''
);