CREATE EXTERNAL TABLE

Purpose

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

The data of an external table is stored in an external storage service (such as the local file system, object storage, HDFS, or MaxCompute). OceanBase Database stores the table definition and access path. An external table is a read-only object that can be used for queries. It does not support DML operations, nor does it support defining constraints or creating indexes.

Privilege requirements

• To execute CREATE EXTERNAL TABLE, the current user must have the CREATE privilege. 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 = 'regex_pattern']
    [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"}

Usage

When creating an external table, you need to specify LOCATION and choose either FORMAT or PROPERTIES based on the external table type (the two are mutually exclusive and cannot be used together):

Other common clauses:

Parameters

Supported path formats for LOCATION

Local path

• When the file is local, the local LOCATION format is: LOCATION = '[file:// | sfile://] local_file_path'

  • file://: This protocol header indicates that when encountering files with the same logical path across multiple OBServer nodes, they are treated as different files and processed separately. Each node independently reads the file from its local path.

  • sfile://: This protocol header indicates that when encountering files with the same logical path across multiple OBServer nodes, the system deduplicates them based on the logical path to ensure only one copy of the data is read (supports multi-server load balancing).

    Note

    • When using sfile://, the system performs consistency verification of file sizes. If files under the same logical path have inconsistent sizes across different OBServer nodes, the system reports the OB_INVALID_EXTERNAL_FILE error to avoid result errors caused by data inconsistency.
    • For version V4.4.2, starting from V4.4.2 BP2, the sfile:// protocol header is supported in the local Location format of the CREATE EXTERNAL TABLE syntax.

  • local_file_path: Can be a relative path or an absolute path. If a relative path is provided, the current directory must be the installation directory of OceanBase Database. secure_file_priv is used to configure the file paths that OBServer nodes are authorized to access. local_file_path can only be a subpath of the secure_file_priv path.

Object storage (OSS / S3)

LOCATION = '{oss|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path'

• $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.
• s3_region: The S3 region. The credentials are stored in an encrypted format in the system table.

HDFS Path

Single NameNode:

LOCATION = 'hdfs://host:port/path'

Hadoop HA (logical nameservice):

LOCATION = 'hdfs://nameserviceID/path'

The client OBServer side must include the nameservice definition and failover strategy for the HA cluster.

Kerberos authentication:

Append query parameters to the URL: ?principal=...&keytab=...&krb5conf=...&configs=...

In a HA + Kerberos environment, common configs parameters (multiple parameters are separated by #):

The example format is as follows:

dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#...

File format and attribute description (format_type_options)

CSV format parameters (Type = 'CSV')

format_type_options

TYPE = 'CSV'

The default values for each parameter are shown in the table above. The following are the constraints and considerations:

• ESCAPE: Specifies the escape character for the CSV file, which must be 1 byte. Default value is ESCAPE ='\'.

• ENCODING: Supported character sets are listed in Character sets. Default is UTF8MB4.

• FIELD_OPTIONALLY_ENCLOSED_BY: When the file contains NULL values (not the string "NULL"), this parameter must be explicitly configured and cannot be empty.

  Notice

  When an external table data file contains NULL values (not the string NULL, i.e., not "NULL"), the FIELD_OPTIONALLY_ENCLOSED_BY parameter must be explicitly configured and its value 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'

Used to create a MaxCompute (ODPS) external table, accessing a remote table via API (not a file path external table).

API_MODE: Specifies the API mode for calling ODPS. Valid values:

• tunnel_api (default):

  • No special network configuration required: Suitable for all deployment scenarios, as OceanBase Database and MaxCompute do not need to reside in the same Virtual Private Cloud (VPC).

  • No additional MaxCompute permissions required: Authentication can be completed with only the AccessID and AccessKey; there is no need to enable MaxCompute Storage API permissions.

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

  • Applicable environments:

    • 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: When using storage_api, specifies whether to split tasks by byte or row for allocation to threads. Set this parameter to byte when the byte size of data in rows of a table varies greatly, and set it to row in other cases.

For details on scenario implementation and partition mapping, see ODPS external tables.

Usage instructions

• External tables are read-only objects and do not support DML, constraints, or indexes.
• The file list is collected based on the LOCATION and PATTERN parameters during creation. To add files to the directory, you must execute ALTER EXTERNAL TABLE ... REFRESH. For more information, see Update the file list of an external table.
• For more information about external file deletion, modification, concurrent access, secure_file_priv, and HDFS/ODPS environments, see About external tables and external files and Manage external files.
• When you do not need to persist table objects and only temporarily read from external files, you can use the FILES table function.

Examples

Example 1: Local CSV external table

1. Prepare the data. First, set the path of secure_file_priv to /home/admin/. Place the CSV file extdata.csv corresponding to the external table data to be imported in the /home/admin/test directory of the current local connection OBServer node.

   The following example shows how to set the global security path.

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

   Note

   Because secure_file_priv is a GLOBAL variable, you must execute \q to exit and make it effective.

   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 tenant, create an external table named 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 data from 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
   

Example 2: HDFS CSV external table (without Kerberos)

Prerequisite: You have completed Java SDK environment deployment, which is required when accessing HDFS.

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;

Example 3: HDFS HA + Kerberos

Prerequisites:

• You have completed the Java SDK environment deployment.
• The Kerberos credential files, such as the keytab and krb5.conf files, have been deployed on all OBServer nodes.
• The HA parameters in configs are consistent with the cluster's nameservice configuration.

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

• About external tables and external files
• Create and query external tables
• ODPS external tables
• FILES table function
• Manage external files
• Update external table file list
• Deploy the OceanBase Java SDK environment