CREATE LOCATION

Purpose

This statement is used to create a Location object. A Location object stores the access path and authentication information for external tables and temporary external tables, enabling 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 Oracle 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 the storage location set to local.

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

• Create a Location object with the storage location set to OSS.

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

• Create a Location object with the authentication mode set to user authentication.

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

  Note

  This applies to clusters without Kerberos authentication enabled, but where specific 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 in single NameNode mode (non-HA).

  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 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'
          );
  

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

  Note

  If Kerberos is not enabled, but HDFS is in high availability (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'
          );
  

References

• For information about viewing files in a Location object, see SHOW.
• For information about modifying a Location object, see ALTER LOCATION.
• For information about deleting files in a Location object, see REMOVE.
• For information about deleting a Location object, see DROP LOCATION.