CREATE EXTERNAL TABLE

Purpose

This statement is used to create a new external table in the database.

External tables are a key feature of a database management system. Typically, tables in a database are stored in the database's storage space, while the data of an external table is stored in an external storage service.

When creating an external table, you need to define the file path and file format of the data. After that, users can read data from the external storage service through the external table. External tables are read-only and can be used in query statements, but DML operations cannot be performed on them. External tables do not support defining constraints or creating indexes.

Privilege requirements

• The current user must have the CREATE privilege to execute CREATE EXTERNAL TABLE. For more information, see Privilege types in MySQL-compatible mode.

• When creating an external table by referencing a Location object, you must have the READ privilege on that Location.

  GRANT READ ON LOCATION test_location_local TO user001 WITH GRANT OPTION;
  

Version information

Syntax

CREATE EXTERNAL TABLE table_name (column_definition [, column_definition ...])
    LOCATION = 'file_name'
    {FORMAT = (format_type_options)
     | PROPERTIES = (properties_type_options)}
    [PARTITION BY (column_name [, column_name ...])]
    [PARTITION_TYPE = USER_SPECIFIED]
    [pattern_expr]
    [AUTO_REFRESH = 'xxx'];

column_definition:
    column_name column_type [AS expr]

format_type_options:
    type_csv_option
    | type_parquet_option
    | type_orc_option

type_csv_option:
    TYPE = 'CSV'
    LINE_DELIMITER = '<string>' | <expr>
    FIELD_DELIMITER = '<string>' | <expr>
    ESCAPE = '<character>' | <expr>
    FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
    ENCODING = 'charset'
    NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...)
    SKIP_HEADER = <int>
    SKIP_BLANK_LINES = {TRUE | FALSE}
    TRIM_SPACE = {TRUE | FALSE}
    EMPTY_FIELD_AS_NULL = {TRUE | FALSE}
    IGNORE_LAST_EMPTY_COLUMN = {TRUE | FALSE}

type_parquet_option:
    TYPE = 'PARQUET'

type_orc_option:
    TYPE = 'ORC'

properties_type_options:
    type_odps_option

type_odps_option:
    TYPE = 'ODPS'
    ACCESSID = '<string>'
    ACCESSKEY = '<string>'
    ENDPOINT = '<string>',
    PROJECT_NAME = '<string>',
    SCHEMA_NAME = '<string>',
    TABLE_NAME = '<string>',
    QUOTA_NAME = '<string>',
    COMPRESSION_CODE = '<string>',
    API_MODE = {"tunnel_api" | "storage_api"},
    SPLIT = {"byte" | "row"}

Parameters

file_path

The file path for external tables can be in the following formats:

• If the file is stored locally, the LOCATION format is: LOCATION = '[file://] local_file_path', where local_file_path can be either a relative or absolute path. If a relative path is specified, the current directory must be the installation directory of OceanBase Database. The secure_file_priv parameter specifies the file paths that OBServer nodes are allowed to access. local_file_path must be a subpath of the secure_file_priv path.

• If the file is stored remotely, the LOCATION format is:

  Notice

  When using an object storage path, the parameters of the object storage path are separated by the & symbol. Ensure that the parameter values you enter contain only uppercase and lowercase letters, numbers, /-_$+=, and wildcards. If you enter any other characters, the configuration may fail.

  • If the file is stored on Alibaba Cloud OSS or AWS S3, the format is: LOCATION = '{oss\|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path', where $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required to access Alibaba Cloud OSS, AWS S3, and object storage compatible with the S3 protocol, respectively. s3_region specifies the region selected when using S3. These sensitive access information are stored in the system tables of the database in an encrypted manner.

  • If the file is stored on HDFS, the format is as follows:

    • When accessing the cluster based on the address of a single-node NameNode (NN), the format is: LOCATION = hdfs://localhost:port/PATH, where localhost specifies the address of HDFS, port specifies the port number of HDFS, and PATH specifies the file path in HDFS.

      • When kerberos authentication is used, the format is: LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx', where:

        • principal: specifies the user for login and authentication.
        • keytab: specifies the path of the key file for user authentication.
        • krb5conf: specifies the path of the description file for the kerberos environment.
        • configs: specifies additional HDFS configuration items. By default, this parameter is empty. However, in a kerberos environment, this parameter typically has a value and needs to be configured, for example: dfs.data.transfer.protection=authentication,privacy, which specifies the data transfer protection level as authentication and privacy.

    • When accessing the cluster based on Hadoop High Availability (HA), the format is: LOCATION = hdfs://nameserviceID/PATH, where nameserviceID specifies the logical naming service ID of the Hadoop HA cluster, and PATH specifies the file path.

  * When kerberos authentication is used, the format is: `LOCATION = 'hdfs://nameserviceID/PATH?principal=xxx&keytab=xxx&krb5conf=xxx&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'`, where:

    * `principal`: specifies the user for login and authentication, and it should be set to the `pricipal` of a non-primary NN.
    * `keytab` and `krb5conf`: are set in the same way as for a single-node NN.
    * `configs`: specifies additional HDFS configuration items. If multiple configuration items need to be set, they are related to HA and security configurations:

      * `dfs.data.transfer.protection=${string}`: aligns with the `dfs.data.transfer.protection` configuration of the cluster.
      * `dfs.nameservices=${nameservice id}`: specifies the `namesevice` (alias) of the current HA cluster.
      * `dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}`: specifies the list of namenode backup IDs in the HA cluster.
      * `dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}`: specifies the specific namenode for `namenode1` to facilitate client routing.
      * `dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}`: specifies the specific namenode for `namenode2` to facilitate client routing.
      * `dfs.ha.automatic-failover.enabled.${nameservice id}=true`: enables the HA cluster to automatically obtain an available namenode to respond to requests.
      * `dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider`: specifies the logical tool class for switching between primary and backup nodes in the HA cluster. You can also customize and upload the logic required by the HA cluster.

  <main id="notice" type='notice'>
    <h4>Notice</h4>
    <p>Some HA-related configuration items are bound to the <code>namespace</code>. For example, in the following sample three, <code>mycluster</code> is the <code>namespace</code>. Make sure to set the related configuration items accordingly.</p>
  </main>

format_type_options

• TYPE = 'CSV': specifies the format of the external file as CSV. It also includes the following fields:

  • LINE_DELIMITER: specifies the line delimiter for the CSV file. The default value is LINE_DELIMITER='\n'.

  • FIELD_DELIMITER: specifies the field delimiter for the CSV file. The default value is FIELD_DELIMITER='\t'.

  • ESCAPE: specifies the escape character for the CSV file. It must be a single byte. The default value is ESCAPE ='\'.

  • FIELD_OPTIONALLY_ENCLOSED_BY: specifies the character used to enclose field values in the CSV file. The default value is empty.

    Notice

    When the external table data file contains NULL values (non-string NULL, i.e., not "NULL"), you must explicitly configure the FIELD_OPTIONALLY_ENCLOSED_BY parameter, and the value of this parameter cannot be empty.

TYPE = 'PARQUET'

Specifies the external file format as Parquet.

TYPE = 'ORC'

Specifies the external file format as ORC.

properties_type_options

• TYPE = 'ODPS': specifies the format of the external file as ODPS. It also includes the following fields:

  • ACCESSID: specifies the AccessKey ID of the Alibaba Cloud account for identity authentication.

  • ACCESSKEY: specifies the AccessKey secret corresponding to the AccessKey ID for identity verification.

  • ENDPOINT: specifies the endpoint of the ODPS service.

  • PROJECT_NAME: specifies the name of the target ODPS project.

  • SCHEMA_NAME: optional. Specifies the schema name in ODPS.

  • TABLE_NAME: specifies the name of the target table in ODPS.

  • QUOTA_NAME: optional. Specifies the quota to use.

  • COMPRESSION_CODE: optional. Specifies the compression format of the data source. Valid values: ZLIB, ZSTD, LZ4, and ODPS_LZ4. If this parameter is not specified, compression is not enabled.

  • API_MODE: specifies the API mode for calling ODPS. Valid values:

• tunnel_api (default):

  * No special network configuration is required: applicable to all deployment scenarios, without the need for OceanBase Database and MaxCompute to be in the same VPC (Virtual Private Cloud).
  
  * No additional MaxCompute permissions are required: authentication can be completed by providing the AccessID and AccessKey, without the need to enable the MaxCompute Storage API permission.
  

  • Applicable environments:

    • OceanBase Database and MaxCompute are not deployed in the same VPC.
    • The MaxCompute Storage API is not enabled.
    • Low latency requirements for data transmission.

• storage_api:

  • Network dependency: OceanBase Database and MaxCompute must be deployed in the same VPC to achieve low-latency, high-throughput data transmission.

  • Permission dependency: You must enable the Storage API permission in MaxCompute and ensure that the AccessKey has the corresponding permissions.

  • Application environment:

    * OceanBase Database and MaxCompute are in the same VPC.
    * The MaxCompute Storage API is enabled.
    * The data volume is extremely large or real-time requirements are high.
    

  • SPLIT: specifies whether to split the task by byte or row when using storage_api and assign it to each thread. If the data size of each row in a table varies significantly, set SPLIT to byte. Otherwise, set it to row.

Considerations

• When an external file is deleted, the external table ignores the file if it no longer exists in the file list.
• When an external file is modified, the external table accesses the latest data of the external file. If the modification of the external file and the query of the external table are concurrent, unexpected results may occur. Therefore, avoid modifying the external file while querying the external table.
• When a new file is added to the external directory, the external table only accesses the files in the file list. If you want to add the new file to the file list of the external table, you need to execute the update external table file operation.

Examples

Example 1: Local CSV external table

1. Prepare the data. First, set the secure_file_priv parameter to /home/admin/, and place the CSV file extdata.csv corresponding to the external table data in the /home/admin/test directory of the current OBServer node.

   Here is an example of setting the global secure file path.

   obclient> SET GLOBAL secure_file_priv = "/home/admin/";
   Query OK, 0 rows affected
   obclinet> \q
   Bye
   

   Note

   Since secure_file_priv is a GLOBAL variable, you must execute \q to make it take effect.

   The content of the CSV file is as follows:

   1,'Dave','Smith','dsmith@outlook.com','friend',32
   2,'Xena','Johnson','xjonson@outlook.com','contact',45
   3,'Fred','Jackon','fjackson@outlook.com','co-worker',19
   4,'Alma','Tyler','atyler@outlook.com','friend',53
   

2. After logging in to the database as a user in the user tenant, create the external table contacts.

   obclient> CREATE EXTERNAL TABLE contacts (
       id    INT,
       firstname  VARCHAR(100),
       lastname   VARCHAR(100),
       email      VARCHAR(255),
       category   CHAR(30),
       age        NUMBER
      )
      LOCATION = '/home/admin/test'
      FORMAT = (
        TYPE = 'CSV'
        FIELD_DELIMITER = ','
        FIELD_OPTIONALLY_ENCLOSED_BY ='\''
       )
     PATTERN = 'extdata.csv';
   

3. Query the data in the external table contacts.

   obclient> SELECT * FROM contacts;
   +----+-----------+----------+----------------------+-----------+------+
   | id | firstname | lastname | email                | category  | age  |
   +----+-----------+----------+----------------------+-----------+------+
   |  1 | Dave      | Smith    | dsmith@outlook.com   | friend    |   32 |
   |  2 | Xena      | Johnson  | xjonson@outlook.com  | contact   |   45 |
   |  3 | Fred      | Jackon   | fjackson@outlook.com | co-worker |   19 |
   |  4 | Alma      | Tyler    | atyler@outlook.com   | friend    |   53 |
   +----+-----------+----------+----------------------+-----------+------+
   4 rows in set
   

4. Create an external table test_tbl1_csv that specifies the COMPRESSION, PARALLEL_PARSE_ON_SINGLE_FILE, PARALLEL_PARSE_FILE_SIZE_THRESHOLD, and MAX_ROW_LENGTH parameters in the FORMAT clause.

   obclient> CREATE EXTERNAL TABLE test_tbl1_csv (
       col1 INT,
       col2 VARCHAR(100),
       col3 VARCHAR(100),
       col4 VARCHAR(255),
       col5 CHAR(30),
       col6 NUMBER)
       LOCATION = '/home/admin/test'
       FORMAT = (
           TYPE = 'CSV'
           FIELD_DELIMITER = ','
           FIELD_OPTIONALLY_ENCLOSED_BY ='\''
           COMPRESSION = SNAPPY_BLOCK
           PARALLEL_PARSE_ON_SINGLE_FILE = TRUE
           PARALLEL_PARSE_FILE_SIZE_THRESHOLD = 1024
           MAX_ROW_LENGTH = 1024)
       PATTERN = 'extdata.csv';
   

Example 2

1. Assume that a CSV file exists in a specified file path on HDFS, and the file content is as follows:

CREATE EXTERNAL TABLE test_tbl1_csv_mysql (
    id INT,
    name VARCHAR(50),
    c_date DATE
)
LOCATION = 'hdfs://${hadoop_namenode_host}:${hadoop_namenode_port}/user'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'test_tbl1.csv';

SELECT * FROM test_tbl1_csv_mysql;

If Kerberos authentication is enabled in the target HDFS environment

1. Prepare the environment Since the current HDFS integration uses the JNI framework, the environment must support the deployment of a Java environment. If OBServer is deployed across multiple nodes, the corresponding OBServer nodes must also be configured as described, and not just a single node.

   Step 1: Deploy and configure the Java environment

   You can download the latest version of OpenJDK 8 from openjdk.

   Step 2: Decompress the installation package

   After decompression, you will see the following files:

CREATE EXTERNAL TABLE test_ha (
    id INT,
    r_name VARCHAR(100),
    age INT
)
LOCATION = 'hdfs://mycluster/hadoop_ha_test?principal=ha/xxx@xxx.com&keytab=/path/to/ha.keytab&krb5conf=/path/to/krb5conf_file&configs=dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#dfs.namenode.rpc-address.mycluster.nn1=localhost1:port#dfs.namenode.rpc-address.mycluster.nn2=localhost2:port#dfs.ha.automatic-failover.enabled.mycluster=true#dfs.client.failover.proxy.provider.mycluster=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'test_simple.csv';

SELECT * FROM test_ha;

Example 4: HDFS Kerberos single-node

The prerequisites are the same as those in Example 3. In a single-NameNode environment, LOCATION can be simplified to:

CREATE EXTERNAL TABLE partsupp (
    PS_PARTKEY    INTEGER,
    PS_SUPPKEY    INTEGER,
    PS_AVAILQTY   INTEGER,
    PS_SUPPLYCOST DECIMAL(15,2),
    PS_COMMENT    VARCHAR(199)
)
LOCATION = 'hdfs://localhost:8020/tpch_csv?principal=principal_str&keytab=/path/to/keytab&krb5conf=/path/to/krb5conf_file&configs=xxx=xxx#xxx=xxx'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
PATTERN = 'partsupp.tbl';

References

Create an external table

Manage external files

Update the list of external table files