CREATE LOCATION

Purpose

This statement is used to create a Location object. A Location object stores the path and authorization information for accessing external tables and temporary external 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 OceanBase Database privileges, see Privilege classification in Oracle-compatible mode.

• Credential privilege:

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

• Local path: URL = 'file://local_file_path', where 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'.

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 user authentication mode.

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

  Note

  This applies to clusters without Kerberos authentication enabled, but where specific HDFS paths need to be accessed by specific users.

  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 mode).

  Note

  The value of dfs.data.transfer.protection in CONFIGS must be consistent with the configuration of the HDFS cluster.

  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

  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 more information about viewing files under a Location object, see SHOW.
• For more information about modifying a Location object, see ALTER LOCATION.
• For more information about deleting files under a Location object, see REMOVE.
• For more information about deleting a Location object, see DROP LOCATION.