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 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 Oracle-compatible mode.
Syntax
CREATE EXTERNAL CATALOG 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 |
|---|---|
| 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 used when you read from or write to a MaxCompute data source. |
| ACCESSTYPE | Optional. The type of the MaxCompute account. The default value is aliyun. Valid values (case-insensitive):
|
| ACCESSID | When the value of ACCESSTYPE is an aliyun/app account or empty, this is 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 empty, this is 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 is the token for accessing the MaxCompute service. |
| ENDPOINT | The endpoint (domain name) of MaxCompute. |
| TUNNEL_ENDPOINT | The Tunnel endpoint. MaxCompute Catalog uses the Tunnel SDK to obtain data. |
| 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 a specific quota by setting this parameter. |
| COMPRESSION | Optional. The compression format of the data source. If this parameter is not specified, data compression is not enabled. Valid values (case-insensitive):
|
| REGION | The region in which the MaxCompute resource 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 = ''
);