Purpose
This statement is used to create a Location object. The Location object stores the paths and authentication information for accessing external tables and temporary external tables, enabling the database to read from or write to external data.
Privilege requirements
Execution permission:
To execute the
CREATE LOCATIONstatement, the current user must have theCREATE LOCATIONprivilege. For more information about OceanBase AI Database privileges, see Permission classification.Authentication credentials:
The provided
ACCESSIDandACCESSKEYmust have legitimate access permissions for the target storage service.
Syntax
CREATE [OR REPLACE] LOCATION location_name
URL = 'file_path'
[CREDENTIAL = (
ACCESSID = 'string'
ACCESSKEY = 'string'
HOST = 'string'
[S3_REGION = 'string',]
[PRINCIPAL = 'string',]
[KEYTAB = 'string',]
[KRB5CONF = 'string',]
[CONFIGS = 'string',]
[USERNAME = 'string']
)
];
Parameters
Parameters |
Description |
|---|---|
| OR REPLACE | Optional. Specifies the OR REPLACE Indicates that if a Location object with the specified key already exists, the existing definition is replaced by the new one. |
| location_name | The name of the Location object to be created. |
| URL = 'file_path' | Specifies the external storage path. Currently supported storage types include: local files, HDFS, and S3 (object storage compatible with the S3 protocol, such as Alibaba Cloud OSS and Huawei Cloud OBS). For more details, see file_path below. |
| CREDENTIAL | Optional. Specifies the information required for the access path. This parameter is not required for local paths. |
| ACCESSID | Specifies the AccessKey ID of the user accessing the object storage. |
| ACCESSKEY | Specifies the user AccessKey Secret for accessing object storage. |
| HOST | Specifies the host address of the object storage service. |
| S3_REGION | Optional. Specifies the service region for the S3 object storage. |
| PRINCIPAL | Optional. When the target HDFS cluster has a Kerberos environment, you need to configure the login authentication user. |
| KEYTAB | Optional. When the target HDFS cluster has a Kerberos environment, you must configure the authentication key file. |
| KRB5CONF | Optional. When the target HDFS cluster has a Kerberos environment, you must specify the user to use in the Kerberos environment. |
| CONFIGS | Optional. When the target HDFS cluster has a Kerberos environment, you need to specify additional Hadoop client configuration parameters. key=value Concatenation, using # Separation. For more information, see configs_option. |
| USERNAME | Optional. When the target HDFS cluster has a Kerberos environment, you must specify a unique identifier for the user. |
file_path
The path where external table files are stored follows this format:
The local path format is:
URL = 'file://local_file_path', wherelocal_file_pathcan be a relative or absolute path.The HDFS path format is
URL = 'hdfs://localhost:port/PATH', wherelocalhostis the HDFS address,portis the HDFS port number, andPATHis the directory path in HDFS.The object storage (S3) path format is:
URL = 's3://bucket/remote_file_path'.The current version is temporarily compatible with existing
oss://paths (addresses in historical metadata can be resolved normally). When creating new object storage locations, uses3://. Compatibility may be removed in future versions. If thehostin existing configurations is still a native OSS endpoint, it needs to be changed to an S3-compatible endpoint.
Notice
The path for creating the Location object should not contain parameters.
configs_option
Parameter |
Function |
|---|---|
| dfs.data.transfer.protection=${string} | Specifies the SASL protection level for secure connections used during data block read and write operations. Valid values:
|
| dfs.nameservices=${nameservice id} | Specifies the current HA cluster's namesevice(aliases). For example, dfs.nameservices=mycluster Indicates defining the logical cluster name as mycluster. |
| dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2} | Specifies the list of standby IDs for the namenodes in the HA cluster. For example, dfs.ha.namenodes.mycluster=nn1,nn2 Indicates that the cluster has two NameNodes: nn1,nn2. |
| dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address} | Parameter description: namenode1 is the specific namenode, which facilitates client routing. For example, dfs.namenode.rpc-address.mycluster.nn1=test1:18020 indicates the RPC address of nn1. |
| dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address} | Parameter description: namenode2 is the specific namenode, which facilitates client routing. For example, dfs.namenode.rpc-address.mycluster.nn2=test2:18020 indicates the RPC address of nn2. |
| dfs.ha.automatic-failover.enabled.${nameservice id}=true | It indicates that automatic failover is enabled. In other words, after the HA cluster receives a request, it automatically locates an available namenode to respond to the service. |
| dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider | A utility tool that instructs a high-availability cluster to perform a primary/standby switchover. It can also be used to customize and package logic for uploading to a high-availability cluster as needed. |
Examples
Create a local Location object.
obclient> CREATE LOCATION test_location_local URL = 'file://home/admin/test_csv';Create a Location object with its storage location set to the object storage service (S3).
obclient> CREATE LOCATION test_location_oss URL = 's3://dir' CREDENTIAL = ( ACCESSID = '******', ACCESSKEY = '******', HOST = '****' );Create a Location object in user authentication mode.
Kerberos is not required, but access must be performed 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 LOCATION hdfs_user URL = 'hdfs://namenode:8020/' CREDENTIAL ( USER = 'hdfs_user_name' );Create a Location object with Kerberos enabled and HDFS set to single NameNode (non-HA mode).
Note
The
dfs.data.transfer.protectionparameter in theCONFIGSfile must be consistent with the HDFS cluster configuration.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' );Create a Location object with Kerberos enabled and HDFS 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' );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' );
After creating a location, you can use the LOAD_FILE function to read external files from a specified path into the database (returning a BLOB), for document processing scenarios in OceanBase AI Database. For details on the syntax, parameters, and constraints of LOAD_FILE, see LOAD_FILE.
References
- For information about the function that reads files under a specified location into BLOBs, see LOAD_FILE.
- For more information about viewing file introduction information under a location object, see SHOW.
- For more information about modifying a location object, see ALTER LOCATION.
- For information about deleting files under a location object, see REMOVE.
- For more information about dropping a location object, see DROP LOCATION.