CREATE EXTERNAL TABLE

Purpose

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

Data for external tables is stored in external storage services (such as local file systems, object storage, HDFS, and MaxCompute). OceanBase Database stores the table definition and access path. External tables are read-only objects that can be used for queries but do not support DML operations, constraints, or index creation.

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 = '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 instructions

When creating an external table, you must specify LOCATION and choose either FORMAT or PROPERTIES based on the external table type. These two options are mutually exclusive and cannot be used together:

Other common clauses:

Parameters

Supported path formats for LOCATION

Local path

LOCATION = '[file://]local_file_path'

local_file_path can be a relative or absolute path. If it is a relative path, the current directory must be the OceanBase installation directory. secure_file_priv specifies the accessible paths for OBServer nodes, and local_file_path must be a subpath of secure_file_priv.

Object Storage Service (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 high-availability + 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 of the parameters 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: ESCAPE ='\'.

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

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

  Notice

  When an external table data file contains NULL values (other than the string NULL, that is, not "NULL"), you must explicitly configure the FIELD_OPTIONALLY_ENCLOSED_BY parameter, 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'

Creates a MaxCompute (ODPS) external table to access 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, where OceanBase Database and MaxCompute do not need to reside in the same VPC (Virtual Private Cloud).

  • No additional MaxCompute permissions required: Authentication can be completed with only an AccessID and AccessKey; there is no 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.

  • Applicable environments:

    • OceanBase Database and MaxCompute are in the same VPC.
    • The MaxCompute Storage API has been 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; otherwise, set it to row.

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, execute ALTER EXTERNAL TABLE ... REFRESH. For more information, see Update the file list of an external table.
• For 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 read external files temporarily, you can use the FILES table function.

Examples

Example 1: Local CSV external table

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

   The following is an example of setting the global security 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 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 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 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 the 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.
• Kerberos credential files such as keytab and krb5.conf have been deployed on each OBServer node.
• 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