CREATE LOCATION

Purpose

This statement is used to create a Location object. A Location object stores the paths and authentication information for accessing external tables and temporary tables, which are used by the database to read or write external data.

Privilege requirements

• Execution privilege:

  To execute the CREATE LOCATION statement, the current user must have the CREATE LOCATION privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

• Credential privilege:

  The provided ACCESSID and ACCESSKEY must have valid 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

file_path

The path for storing external table files has the following formats:

• For a local path: URL = 'file://local_file_path', where local_file_path can be a relative or absolute path.

• For an 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.

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

configs_option

Examples

• Create a Location object with a local storage path.

  obclient> CREATE LOCATION test_location_local
      URL = 'file://home/admin/test_csv';
  

• Create a Location object with an OSS storage path.

  obclient> CREATE LOCATION test_location_oss
      URL = 'oss://dir'
      CREDENTIAL = (
          ACCESSID = '******',
          ACCESSKEY = '******',
          HOST = '****'
      );
  

• Create a Location object with a user authentication mode.

  Kerberos is not required, but you must access it as a specific HDFS user.

  Note

  This applies to clusters that do not use Kerberos authentication, but where specific HDFS paths require a specific user to access.

  CREATE LOCATION hdfs_user
      URL = 'hdfs://namenode:8020/'
      CREDENTIAL (
          USER = 'hdfs_user_name'
      );
  

• Create a Location object with Kerberos enabled and HDFS in a single NameNode (non-HA mode).

  Note

  The dfs.data.transfer.protection parameter in CONFIGS 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 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'
          );
  

• Create a Location object without Kerberos enabled, but with HDFS in HA mode.

  Note

  If Kerberos is not enabled, but HDFS is in HA mode, you do not need to set the PRINCIPAL, KEYTAB, and KRB5CONF parameters.

  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 you create a Location object, you can use the LOAD_FILE function to read external files from the specified path into the database (as BLOBs) for document processing in OceanBase AI. For more information about the syntax, parameters, and constraints of LOAD_FILE, see LOAD_FILE.

References

• For information about the LOAD_FILE function that reads files under a Location object as BLOBs, see LOAD_FILE.
• For information about viewing files under a Location object, see SHOW.