File external 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, whereas file external table data is stored in external storage services (such as local file systems, HDFS, OSS, S3, or S3-compatible object storage). When creating an external table, you must explicitly define the data file path and file format. Afterward, users can access 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 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 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 is automatically mapped to the first column of the file, and c2 is mapped 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:// | sfile://] local_file_path'

• file://: This protocol header indicates that when encountering a file with the same logical path across multiple OBServer nodes, it is 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 a file with the same logical path across multiple OBServer nodes, the system performs deduplication based on the logical path to ensure only one copy of data is read (supports multi-server load balancing).

  Notice

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

• 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 specified directly. To read a single file, specify its parent directory and filter it 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 and stored in system tables by OceanBase, eliminating the need to expose them in plaintext.

FORMAT

For the complete FORMAT parameter (including CSV COMPRESSION, etc.), 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 considered of the STRING type and are converted according to the column types defined in the external table during queries.

Parquet Format/ORC Format

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

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

• You only need to specify TYPE = 'PARQUET' or TYPE = 'ORC'.
• The schema is automatically inferred from the Parquet file metadata.

Type mapping (Parquet/ORC → OceanBase)

Special notes for 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 to filter 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: 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);

• After a new file is added, queries will 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 discover partitions.
• You must explicitly manage partitions using the ALTER EXTERNAL TABLE ... ADD/DROP PARTITION LOCATION statement.

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 pseudo-column for the partitioning key.
• Initially, an external table has no file metadata, and partitions must be added manually.

Add a partition:

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

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

Drop a partition:

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

• Drop all associated partitions and their file metadata from this path.

Pseudo-columns

External tables support the following three types of pseudo-columns:

External file management

External tables store a list of files that meet the PATTERN criterion under a LOCATION in a system table. Queries access external files based on this list. 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 mappings in MySQL-compatible mode
• Data type mappings in Oracle-compatible mode