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, 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-compatible 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:

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

• HDFS path format: 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 format: 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.

  You do not need to use Kerberos, but you must access it with a specific HDFS user.

  Note

  This example is applicable to a cluster that does not use Kerberos authentication, but where specific HDFS users are required to access the HDFS paths.

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

• Kerberos is not enabled, but HDFS is 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'
          );
  

References

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