Purpose
This statement is used to create an external data catalog in the database for connecting to external data sources and obtaining metadata of external data. You can use it to directly query external data without performing data import or migration.
Limitations and considerations
- Currently, you can create
ODPSandHMStypes of external catalogs. - Before creating an HMS Catalog, if the file data expected to be accessed by HMS from the underlying storage is on HDFS, you must deploy the Java SDK environment. For detailed information about configuring the JAVA SDK environment, see Deploy Java SDK.
Privilege requirements
To execute the CREATE EXTERNAL CATALOG statement, the current user must have the CREATE CATALOG privilege. For detailed information about OceanBase AI Database privileges, see Permission classification.
Syntax
CREATE EXTERNAL CATALOG [IF NOT EXISTS] external_catalog_name
PROPERTIES [=] (properties_type_options);
properties_type_options:
odps_type_list
| hms_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'
hms_type_list:
TYPE = 'HMS',
URI = "string"
[, KRB5CONF = "string"]
[, KEYTAB = "string"]
[, PRINCIPAL = "string"]
[, MAX_CLIENT_POOL_SIZE = 20]
[, SOCKET_TIMEOUT = 10000000]
accesstype_string:
aliyun
| sts
| app
compression_string:
zlib
| zstd
| lz4
| odps_lz4
Parameters
Parameters |
Description |
|---|---|
| IF NOT EXISTS | Optional. If this clause is specified, the system will issue a warning message even if the external data directory to be created already exists in the current tenant. If this clause is not specified, an error will be returned. |
| external_catalog_name | The name of the external data directory to be created. |
| PROPERTIES [=] (properties_type_options) | Used to specify the attributes of the external data directory type. For details, see properties_type_options. |
properties_type_options
odps_type_list: A list of attributes for the ODPS type external data catalog (ODPS Catalog). For details, see odps_type_list.hms_type_list: A list of attributes for the HMS type external data catalog (HMS Catalog). For details, see hms_type_list.
odps_type_list
TYPE: Specifies the type of the external data catalog. The value isODPS, which is used when reading from or writing to a MaxCompute data source.ACCESSTYPE: Optional. Specifies the account type for MaxCompute. Default value:aliyun. Account type values (case-insensitive) are as follows:aliyunstsapp
ACCESSID: Specifies the AccessKey ID. When the value ofACCESSTYPEisaliyun,app, or empty, this parameter specifies the AccessKey ID of analiyunorappaccount, or a RAM user with access permissions to MaxCompute.ACCESSKEY: Specifies the AccessKey Secret. When the value ofACCESSTYPEisaliyun,app, or empty, this parameter specifies the AccessKey Secret of analiyunorappaccount, or a RAM user with access permissions to MaxCompute.STSTOKEN: Specifies the token. When the value ofACCESSTYPEissts, this parameter specifies the token for accessing the MaxCompute service.ENDPOINT: Specifies the MaxCompute endpoint (domain name node).TUNNEL_ENDPOINT: Specifies the tunnel endpoint. The MaxCompute Catalog uses the Tunnel SDK to obtain data.PROJECT_NAME: Specifies the project space name in MaxCompute. A project space is the basic organizational unit in MaxCompute, similar to the concept of a database or schema in traditional databases.QUOTA_NAME: Optional. Specifies the quota. In MaxCompute, a quota represents a computing resource pool (computing, access, and write). If a user has configured a corresponding quota, specify the specific quota through this parameter.COMPRESSION: Optional. Specifies the compression format of the data source. If not set, compression is disabled. Values (case-insensitive) are as follows:zlibzstdlz4odps_lz4
REGION: Specifies the region where MaxCompute is enabled.
hms_type_list
TYPE: Specifies the type of external data directory. The valueHMSindicates that external table data is read from data organized and managed by Hive.URI: ThethriftURI for accessing the specifiedHMSservice, in the formatthrift://$host:$port, where:$host: The IP address of the Thrift client.$port: thethriftport. The defaultthriftport forHMSis 9083.
KRB5CONF: Optional. The path to the Kerberos configuration file required when accessing theHMSservice with Kerberos authentication enabled.Notice
The
KRB5CONFparameter is required only when Kerberos authentication is enabled forHMS.KEYTAB: Optional. The path to theKEYTABkey file required when accessing anHMSservice with Kerberos authentication enabled. If the OceanBase AI Database cluster is deployed in a distributed manner, this file must exist on the corresponding server node for each related OBServer.Notice
The
KEYTABparameter is required only when Kerberos authentication is enabled forHMS.PRINCIPAL: Optional. The Kerberos principal name required for accessing theHMSservice with Kerberos authentication enabled. It typically follows the formatservice/HOST@REGION.com, for example,hive/hadoop@QA.COM.Notice
The
PRINCIPALparameter is required only when Kerberos authentication is enabled forHMS.MAX_CLIENT_POOL_SIZE: Optional. Specifies the maximum size of the client queue available for the current catalog. The default value is 20, which means the current HMS catalog can start at most 20 clients that connect to theHMSservice.SOCKET_TIMEOUT: Optional. Specifies the timeout period for client access to the current catalog. The default value is 10000000 (10 seconds).
Examples
Create an External Catalog of the
ODPStype.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 = '' );Create an External Catalog of the
HMStype.Create an HMS Catalog with SIMPLE authentication in normal authentication mode. No Location authentication is required.
obclient> CREATE EXTERNAL CATALOG test_hms_catalog PROPERTIES = ( TYPE = 'HMS', URI = "thrift://xxx.xxx.xxx.xxx:xxxx" );Create a Kerberos-authenticated HMS Catalog.
obclient> CREATE EXTERNAL CATALOG test_hms_catalog_kerberos PROPERTIES = ( TYPE = 'HMS', URI = "thrift://xxx.xxx.xxx.xxx:xxxx", PRINCIPAL = "hive/xxx@xxx.COM", KEYTAB = "/xxx/xxx/xxx/hadoop.keytab", KRB5CONF = "/etc/xxx.conf" );
Location Authentication
OceanBase AI Database's HMS Catalog supports key management through External Locations, enabling secure access to external storage. When accessing tables in HMS, the system automatically matches the corresponding External Location based on the table path. If a match is found, access is authenticated using its key.
Location Authentication Scenarios
Based on the storage type and security requirements, Location authentication is divided into the following scenarios:
Storage type |
Authentication method |
|---|---|
| S3 (including object storage compatible with the S3 protocol) | AK/SK authentication. |
| HDFS | HDFS supports the following authentication methods:
|
Procedure
Determine the Location authentication scenario.
Select the corresponding authentication scenario based on the storage type and security requirements.
Create a Location object.
Use the
CREATE LOCATIONstatement to create a Location object for the corresponding scenario. For detailed information on creating a Location, see CREATE LOCATION.Associate with a Catalog.
No additional configuration is required when creating a Catalog. The system automatically matches the Location based on the storage path of the corresponding table.
Configuration Example for Location Authentication Scenarios
HDFS Storage Authentication
Scenario 1: No authentication mode.
No Location needs to be created. This applies to development or test environments where Kerberos authentication is not enabled in the HDFS cluster (i.e.,
hadoop.security.authentication=simple).Scenario 2: User authentication mode.
No Kerberos is required, but access must be made as a specific HDFS user.
Note
Applicable to clusters where Kerberos authentication is not enabled, but the related HDFS paths require access by a specific user.
Create a Location: Specify the HDFS user.
CREATE LOCATION hdfs_user URL = 'hdfs://namenode:8020/' CREDENTIAL ( USER = 'hdfs_user_name' );Scenario 3: Enable Kerberos, HDFS is single NameNode (non-HA mode).
Note
The
dfs.data.transfer.protectionparameter in theCONFIGSfile must be consistent with the HDFS cluster configuration.Create Location: Kerberos authentication + single-node HDFS.
CREATE LOCATION hdfs_kerberos_single URL = 'hdfs://namenode.example.com:8020/' CREDENTIAL ( PRINCIPAL = "hdfs/xxx@xxx.COM", KEYTAB = "/data/hdfs.keytab", KRB5CONF = "/data/krb5.conf", CONFIGS = 'dfs.data.transfer.protection=integrity' );Scenario 4: Kerberos is enabled, and HDFS is in high-availability (HA) mode.
CREATE LOCATION hdfs_kerberos_ha URL = 'hdfs://${nameservice id}' CREDENTIAL ( PRINCIPAL = "hdfs/xxx@xxx.COM", KEYTAB = "/etc/ob/hdfs.keytab", KRB5CONF = "/etc/krb5.conf", CONFIGS = 'dfs.data.transfer.protection=${string}#dfs.nameservices=${nameservice id}#dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}#dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}#dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}#dfs.ha.automatic-failover.enabled.${nameservice id}=true#dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider' );Scenario 5: Kerberos is not enabled, but HDFS is in high-availability (HA) mode.
Note
Kerberos is not enabled, but HDFS is in high-availability (HA) mode. Therefore, you do not need to set the
PRINCIPAL,KEYTAB, andKRB5CONFparameters.CREATE LOCATION hdfs_location_ha URL = 'hdfs://${nameservice id}' CREDENTIAL ( CONFIGS = 'dfs.nameservices=${nameservice id}#dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}#dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}#dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}#dfs.ha.automatic-failover.enabled.${nameservice id}=true#dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider' );