ALTER LOCATION

Purpose

This statement is used to modify the properties of a Location object.

Privilege requirements

• Privilege:

  To execute the ALTER LOCATION statement, the current user must have the CREATE LOCATION privilege. For more information about the privileges of OceanBase Database, see Privilege classification in Oracle-compatible mode.

• Credential:

  The provided ACCESSID and ACCESSKEY must have the necessary permissions to access the target storage service.

Syntax

ALTER LOCATION location_name SET 
    {URL = 'file_path'
    | CREDENTIAL = (credential_list)
    };

credential_list:
    ACCESSID = 'string'
    ACCESSKEY = 'string'
    HOST = 'string'
    [S3_REGION = 'string']
    [PRINCIPAL = 'string']
    [KEYTAB = 'string']
    [KRB5CONF = 'string']
    [CONFIGS = 'string']
    [USERNAME = 'string']

Parameters

file_path

The path for storing external table files can be in the following formats:

• Local path: URL = '[file:// | sfile://] local_file_path'

  • file://: This protocol header indicates that when encountering files with the same logical path across multiple OBServer nodes, they are treated as different files and processed separately. Each node independently reads the file from its local path.

  • sfile://: This protocol header indicates that when encountering files with the same logical path across multiple OBServer nodes, the system performs deduplication based on the logical path to ensure only one copy of data is read (supporting multi-server load balancing).

    Note

    • When using sfile://, the system performs consistency verification on file sizes. If files under the same logical path have inconsistent sizes across different OBServer nodes, the system reports an error OB_INVALID_EXTERNAL_FILE to avoid result errors caused by data inconsistency.
    • For version V4.4.2, starting from V4.4.2 BP2, support for the sfile:// protocol header was added to the local URL path format in the ALTER LOCATION syntax.

  • local_file_path: Can be a relative or absolute path.

• HDFS path: URL = 'hdfs://localhost:port/PATH', where localhost is the HDFS address, port is the HDFS port number, and PATH is the directory path in HDFS.

• Object storage (OSS/S3) path: URL = '{oss | s3}://bucket/remote_file_path'.

credential_list

• ACCESSID: The AccessKey ID for accessing object storage.
• ACCESSKEY: The AccessKey secret for accessing object storage.
• HOST: The host address for the object storage service.
• S3_REGION: Optional. The service region for S3 object storage.
• PRINCIPAL: Optional. The login authentication user for the target HDFS cluster with Kerberos environment.
• KEYTAB: Optional. The authentication key file path for the target HDFS cluster with Kerberos environment.
• KRB5CONF: Optional. The file path for using Kerberos environment for the target HDFS cluster with Kerberos environment.
• CONFIGS: Optional. Additional cluster configuration items for the target HDFS cluster with Kerberos environment.
• USERNAME: Optional. The unique identifier for the user's identity in the target HDFS cluster with Kerberos environment.

Examples

Modify the CREDENTIAL information of the test_location_oss Location object.

obclient> ALTER LOCATION test_location_oss SET
    CREDENTIAL = (
        ACCESSID = '******'
        ACCESSKEY = '******'
        HOST = '******'
        );

References

For information about viewing files under a Location object, see SHOW.