File external tables

This topic describes how to use external tables to access external files in CSV, Parquet, or ORC format by configuring LOCATION and FORMAT. It does not cover ODPS API-based external tables (see ODPS external tables) or access through an External Catalog.

Data in regular database tables is typically stored in the database's own storage space. In contrast, data in a file external table is stored in an external storage service, such as a local file system, HDFS, OSS, S3, or an object storage service compatible with the S3 protocol. When you create an external table, you must explicitly define the data file path and file format. You can then query the external data by using standard SQL statements in the same way that you query a regular table.

Limits

• External tables are read-only. They support SELECT but do not support DML operations such as INSERT, UPDATE, or DELETE.
• External tables do not support constraints such as DEFAULT, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, or FOREIGN KEY.
• External tables do not support indexes.
• Because external-table access involves remote I/O and parsing overhead, it is generally slower than regular-table access.

Create a file external table

Syntax

CREATE EXTERNAL TABLE <table_name>
(
    [ <col_name> <col_type> [AS <expr>] ]
    [ , <col_name> <col_type> [AS <expr>] ]
    [ , ... ]
)
[PARTITION_TYPE = USER_SPECIFIED]
LOCATION = '<string>'
[AUTO_REFRESH = { IMMEDIATE | OFF | INTERVAL }]
FORMAT = (
    TYPE = '<string>',
    LINE_DELIMITER = '<string>' | <expr>,
    FIELD_DELIMITER = '<string>' | <expr>,
    ESCAPE = '<character>' | <expr>,
    FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
    ENCODING = 'UTF8MB4|GBK|GB18030|BINARY',
    NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
    SKIP_HEADER = <int>,
    SKIP_BLANK_LINES = { TRUE | FALSE },
    TRIM_SPACE = { TRUE | FALSE },
    EMPTY_FIELD_AS_NULL = { TRUE | FALSE },
    COMPRESSION = AUTO | GZIP | ZSTD | DEFLATE | NONE
)
[ PATTERN = '<regex_pattern>' ]
[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]

Column definitions

Each column in an external table must have a data type, but it cannot have a constraint such as DEFAULT, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, or FOREIGN KEY.

Default column mapping

By default, data columns in a file are mapped to the columns defined in the external table in order:

• Column 1 of the external table → Column 1 of the file
• Column 2 of the external table → Column 2 of the file
• And so on

Example:

CREATE EXTERNAL TABLE ext_t1 (
    c1 INT,
    c2 INT
)
...

For a CSV file, c1 is automatically mapped to the first column in the file, and c2 is mapped to the second column.

Manual column mapping

When the column order in a file differs from the table definition, use the metadata$filecol{N} pseudocolumn to explicitly define the mapping. N starts from 1.

CREATE EXTERNAL TABLE ext_t1 (
    c1 INT AS (metadata$filecol2),  -- c1 in ext_t1 maps to the second column in the file.
    c2 INT AS (metadata$filecol4)   -- c2 in ext_t1 maps to the fourth column in the file.
)
...

LOCATION

LOCATION specifies the storage path of the external data files. The system recursively scans all files in the specified directory and its subdirectories.

Local LOCATION format

LOCATION = '[file://] local_file_path'

• local_file_path can be a relative or absolute path.

  • A relative path is resolved against the OBServer installation directory.

• You must specify a directory instead of a single file. To read one file, specify its parent directory and use PATTERN to filter the files.

• local_file_path is subject to secure_file_priv and must be a subdirectory of the path specified by secure_file_priv.

Remote LOCATION format

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

• $ACCESS_ID, $ACCESS_KEY, and $HOST are the credentials required to access the cloud storage service.
• OceanBase encrypts sensitive information such as AccessKeys and stores it in system tables, so it does not need to be exposed in plaintext.

FORMAT

For the complete set of FORMAT parameters, including CSV COMPRESSION, see CREATE EXTERNAL TABLE. The following sections provide common examples.

CSV format

FORMAT = (
    TYPE = 'CSV',
    LINE_DELIMITER = '<string>' | <expr>,
    FIELD_DELIMITER = '<string>' | <expr>,
    ESCAPE = '<character>' | <expr>,
    FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
    ENCODING = 'UTF8MB4|GBK|GB18030|BINARY',
    NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
    SKIP_HEADER = <int>,
    SKIP_BLANK_LINES = { TRUE | FALSE },
    TRIM_SPACE = { TRUE | FALSE },
    EMPTY_FIELD_AS_NULL = { TRUE | FALSE },
    COMPRESSION = AUTO | GZIP | ZSTD | DEFLATE | NONE
)

CSV type description: All columns in a file are treated as strings and are converted to the column types defined in the external table during queries.

Parquet and ORC formats

-- Parquet format
FORMAT = ( TYPE = 'PARQUET' )

-- ORC format
FORMAT = ( TYPE = 'ORC' )

• Specify only TYPE = 'PARQUET' or TYPE = 'ORC'.
• The schema is automatically inferred from the Parquet file metadata.

Type mappings from Parquet/ORC to OceanBase

Special considerations for Hive Parquet files:

• The BOOL type is not supported.
• The BINARY type is not supported in Oracle-compatible mode.

For more information, see Data type mappings in MySQL-compatible mode and Data type mappings in Oracle-compatible mode.

PATTERN

[ PATTERN = '<regex_pattern>' ]

• Specifies a regular expression used to filter files in the LOCATION directory.
• Only files that match the pattern are read. Files that do not match are skipped.
• If PATTERN is not specified, all files in the directory are read by default.

Partitioning

Automatic partitioning

Automatic partitioning uses the PARTITION BY clause together with metadata$fileurl.

• metadata$fileurl is the path of the file that contains the current row, relative to the LOCATION root.
• The partitioning expression must include metadata$fileurl. The system dynamically calculates the partition based on its value.

Example: Partition by the date string in the log path

CREATE EXTERNAL TABLE t0 (
    c0 VARCHAR(500) AS (NVL(METADATA$FILECOL1, '')),
    c1 FLOAT(5,4) AS (METADATA$FILECOL2),
    date_part VARCHAR(100) AS (SUBSTR(METADATA$fileurl, INSTR(METADATA$fileurl, '2024'), 10)),
    path VARCHAR(100) AS (METADATA$fileurl)
)
LOCATION = '~/log'
FORMAT = (TYPE = 'CSV')
PARTITION BY (date_part);

• When a new file is added, queries automatically assign it to the corresponding partition based on the date_part expression.
• If expression evaluation fails, for example, because of a type conversion error, the system returns an error.

Manual partitioning

Set PARTITION_TYPE = USER_SPECIFIED to enable manual partitioning. In this mode:

• The external table does not automatically discover partitions.
• You must explicitly manage partitions by using ALTER EXTERNAL TABLE ... ADD/DROP PARTITION LOCATION.

Table creation example:

CREATE EXTERNAL TABLE table_name (
    date_part VARCHAR(100) AS (METADATA$PARTITION_LIST_COL1),
    col2 INT AS (METADATA$filecol2)
)
PARTITION_TYPE = USER_SPECIFIED
AUTO_REFRESH = OFF
LOCATION = '~/log'
FORMAT = (TYPE = 'CSV')
PARTITION BY (date_part);

• METADATA$PARTITION_LIST_COL1 is a placeholder pseudocolumn for the partitioning column.
• A newly created external table has no file metadata. You must add partitions manually.

Add a partition:

ALTER EXTERNAL TABLE table_name
ADD PARTITION (date_part = '2024-06')
LOCATION '2024/06';

• Adds all files under ~/log/2024/06 to the date_part = '2024-06' partition.
• A partition can be associated with only one LOCATION. However, one LOCATION can be associated with multiple partitions, which results in duplicate data.

Drop a partition:

ALTER EXTERNAL TABLE table_name DROP PARTITION LOCATION '2022/02';

• Drops all associated partitions and their file metadata under the specified path.

Pseudocolumns

External tables support the following three pseudocolumns:

External file management

An external table stores the list of files under LOCATION that match PATTERN in a system table and uses this list to access external files during queries. The file list can be refreshed automatically or manually.

View external-table files

• MySQL-compatible tenant:

SELECT * FROM oceanbase.DBA_OB_EXTERNAL_TABLE_FILES
WHERE table_schema = 'DATABASE4' AND table_name = 'T0';

• Oracle-compatible tenant:

SELECT * FROM DBA_OB_EXTERNAL_TABLE_FILES
WHERE owner = 'DATABASE4' AND table_name = 'T0';

• System tenant:

SELECT * FROM oceanbase.CDB_OB_EXTERNAL_TABLE_FILES;

Manually refresh the file list

ALTER EXTERNAL TABLE <table_name> REFRESH;

Automatic refresh policies (AUTO_REFRESH)

References

• Data type mappings in MySQL-compatible mode
• Data type mappings in Oracle-compatible mode