External File Tables

This topic describes how to access external files (CSV, Parquet, or ORC) through LOCATION + FORMAT. It does not cover external tables accessed via the ODPS API (see ODPS external tables or External Catalog.

Typically, table data in a database is stored within the database's own storage space. However, data for an external file 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 creating an external table, you must explicitly define the data file path and file format. Afterward, users can access the external data using standard SQL queries, just like querying a regular table.

Limitations

• External tables are read-only. They support SELECT but do not support DML operations (such as INSERT, UPDATE, or DELETE).
• Constraints (such as DEFAULT, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, or FOREIGN KEY) cannot be defined.
• Indexes cannot be created.
• Due to remote I/O and parsing overhead, access to external tables is generally slower than to regular tables.

Create an external file 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 definition

Columns in an external table must have data types defined, but no constraints (such as DEFAULT, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, or FOREIGN KEY) can be defined.

Default column mapping

By default, data columns in a file are automatically mapped to 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 automatically maps to the first column of the file, and c2 maps to the second column.

Manual column mapping

When the column order in the file does not match the table definition, you can use the pseudo-column metadata$filecol{N} to explicitly specify the mapping relationship (where N starts from 1):

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

LOCATION

LOCATION specifies the storage path for external data files. The system automatically recursively scans all files in this directory and its subdirectories.

Local LOCATION format

LOCATION = '[file://] local_file_path'

• local_file_path can be a relative or absolute path.

  • The base directory for a relative path is the installation directory of the OBServer.

• A directory must be specified; a single file cannot be directly specified. To read a single file, specify its parent directory and filter using PATTERN.

• Restricted by secure_file_priv: local_file_path must be a subdirectory of the path configured 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 cloud storage.
• Sensitive information (such as AccessKeys) is encrypted by OceanBase and stored in system tables, eliminating the need to expose them in plaintext.

FORMAT

For the complete FORMAT parameter (including CSV COMPRESSION), refer to CREATE EXTERNAL TABLE SQL reference. The following are 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 the file are treated as STRING type. During queries, they are converted according to the column types defined in the external table.

Parquet Format/ORC Format

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

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

• Only TYPE = 'PARQUET' or TYPE = 'ORC' needs to be specified.
• The schema is automatically inferred from the Parquet file metadata.

Type mapping (Parquet/ORC → OceanBase)

Special notes on Hive Parquet:

• 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 for filtering files in the LOCATION directory.
• Only files that match the pattern are read; unmatched files are skipped.
• If PATTERN is not specified, all files in the directory are read by default.

Partitioning

Automatic partitioning

Automatic partitioning is implemented using the PARTITION BY clause in combination with metadata$fileurl.

• metadata$fileurl: the relative path of the file to which the current row belongs (rooted at LOCATION).

• The partitioning expression must include metadata$fileurl, and the system dynamically calculates the partition based on its value.

Example: Partitioning 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);

• After a new file is added, queries automatically assign it to the corresponding partition based on the date_part expression.
• If the expression fails to evaluate (for example, due to a type conversion error), an error is returned.

Manual partitioning

Enable manual partitioning mode by setting PARTITION_TYPE = USER_SPECIFIED. In this case:

• External tables do not automatically detect partitions.
• You must explicitly manage partitions using the ALTER EXTERNAL TABLE ... ADD/DROP PARTITION LOCATION statement.

Sample schema:

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 pseudo-column for the partitioning key.
• Initially, the external table has no file metadata, so you need to manually add partitions.

Add a partition:

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

• Incorporates all files under ~/log/2024/06 into the date_part = '2024-06' partition.
• A partition can bind only one LOCATION; however, one LOCATION can bind multiple partitions (resulting in data duplication).

Drop a partition:

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

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

Pseudocolumns

External tables support the following three pseudocolumns:

External file management

External tables store the list of files that meet the PATTERN criterion under the LOCATION in a system table, which is used to access external files during queries. The file list supports automatic or manual updates.

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 update the file list

ALTER EXTERNAL TABLE <table_name> REFRESH;

Automatic update strategy (AUTO_REFRESH)

References

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