文件外部表

文件外部表

更新时间：2026-07-05 20:36:18

本文介绍通过 LOCATION + FORMAT 访问外部文件的外表（CSV / Parquet / ORC）。不包含 ODPS API 外表（见 ODPS 外部表）及 External Catalog 接入方式。

通常，数据库中的表数据存放于数据库自身的存储空间中，而文件外部表的数据则存储在外部存储服务（本地文件系统、HDFS、OSS、S3 或兼容 S3 协议的对象存储等）中。创建外部表时，需明确定义数据文件路径和文件格式。之后，用户可通过标准 SQL 查询语句访问外部数据，如同查询普通表。

使用限制

外部表是只读的，支持 SELECT，但不支持 DML 操作（如 INSERT / UPDATE / DELETE）。
不支持定义约束（如 DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY）。
不支持创建索引。
由于涉及远程 I/O 和解析开销，外部表的访问速度通常慢于普通表。

创建文件外部表

语法

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> ... ] ) ]

列定义

外部表的列必须定义数据类型，但不能定义任何约束（如 DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY 、FOREIGN KEY 等）。

默认列映射

默认情况下，文件中的数据列与外表定义的列按顺序自动对应：

外表第 1 列 → 文件第 1 列
外表第 2 列 → 文件第 2 列
依此类推

示例：

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

对于 CSV 文件，c1 自动映射文件第一列，c2 映射第二列。

手动列映射

当文件列顺序与表定义不一致时，可通过伪列 metadata$filecol{N} 显式指定映射关系（N 从 1 开始）：

CREATE EXTERNAL TABLE ext_t1 (
    c1 INT AS (metadata$filecol2),  -- 外表 ext_t1 的 c1 列对应文件中的第2列
    c2 INT AS (metadata$filecol4)   -- 外表 ext_t1 的 c2 列对应文件中的第4列
)
...

注意

一旦使用手动映射，所有列都必须显式定义映射，默认顺序映射将失效。

LOCATION

LOCATION 用于指定外部数据文件的存储路径。系统会自动递归扫描该目录及其子目录下的所有文件。

本地 LOCATION 格式

LOCATION = '[file://] local_file_path'

local_file_path 可为相对路径或绝对路径。
- 相对路径的基准目录为 OBServer 的安装目录。
必须指定目录，不能直接指定单个文件。若需读取单个文件，应指定其父目录，并通过 PATTERN 过滤。
受 secure_file_priv 限制：local_file_path 必须是 secure_file_priv 配置路径的子目录。

远程 LOCATION 格式

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

$ACCESS_ID、$ACCESS_KEY、$HOST 为访问云存储所需的凭证信息。
敏感信息（如 AccessKey）由 OceanBase 加密存储于系统表，无需明文暴露。

FORMAT

完整 FORMAT 参数（含 CSV COMPRESSION 等）以 CREATE EXTERNAL TABLE SQL 参考为准。以下为常用示例。

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 类型说明：文件中所有列均被视为 STRING 类型，查询时按外表定义的列类型进行转换。

Parquet Format/ORC Format

-- 格式为 Parquet Format
FORMAT = ( TYPE = 'PARQUET' )

-- 格式为 ORC Format
FORMAT = ( TYPE = 'ORC' )

仅需指定 TYPE = 'PARQUET'或 TYPE = 'ORC'。
Schema 由 Parquet 文件元数据自动推断。

说明

在 OceanBase 的 MySQL 模式下，外部表不支持使用 ZEROFILL 列属性。

类型映射（Parquet/ORC → OceanBase）

Hive Parquet 特殊说明：

BOOL 类型不支持。
BINARY 类型在 Oracle 模式下不支持。

PATTERN

[ PATTERN = '<regex_pattern>' ]

指定正则表达式，用于过滤 LOCATION 目录下的文件。
仅匹配成功的文件会被读取；未匹配的文件将被跳过。
若未指定 PATTERN，则默认读取目录下所有文件。

分区

自动分区

通过 PARTITION BY 子句结合 metadata$fileurl 实现自动分区。

metadata$fileurl：表示当前行所属文件的相对路径（以 LOCATION 为根）。
分区表达式必须包含 metadata$fileurl，系统根据其值动态计算分区。

示例：按日志路径中的日期字符串分区

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);

新文件加入后，查询时会自动根据 date_part 表达式归入对应分区。
若表达式计算失败（如类型转换错误），将返回错误。

手动分区

通过 PARTITION_TYPE = USER_SPECIFIED 启用手动分区模式。此时：

外表不会自动发现分区。
用户需通过 ALTER EXTERNAL TABLE ... ADD/DROP PARTITION LOCATION 显式管理分区。

建表示例：

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 是分区列的占位伪列。
初始外表无文件元数据，需手动添加分区。

添加分区：

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

将 ~/log/2024/06 下所有文件纳入 date_part = '2024-06' 分区。
一个分区只能绑定一个 LOCATION；但一个 LOCATION 可绑定多个分区（导致数据重复）。

删除分区：

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

删除该路径下所有关联分区及其文件元数据。

伪列

外部表支持以下三种伪列：

伪列	说明
`METADATA$FILECOL{N}`	映射文件第 N 列（N ≥ 1）
`METADATA$FILEURL`	当前行所属文件的相对路径（以建表语句中location为 root 的相对路径）
`METADATA$PARTITION_LIST_COL{N}`	手动分区模式下，表示第 N 个分区键的占位符

外部文件管理

外部表将 LOCATION 下满足 PATTERN 的文件列表存储于系统表中，查询时据此访问外部文件。文件列表支持自动或手动更新。

查看外部表文件

MySQL 租户：

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

Oracle 模式租户：

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

系统租户：

SELECT * FROM oceanbase.CDB_OB_EXTERNAL_TABLE_FILES;

手动更新文件列表

ALTER EXTERNAL TABLE <table_name> REFRESH;

自动更新策略（AUTO_REFRESH）

策略	说明
`IMMEDIATE`	每次查询前自动刷新文件列表
`OFF`	永不自动刷新，仅支持手动刷新
`INTERVAL`	通过 `DBMS_EXTERNAL_TABLE.AUTO_REFRESH_EXTERNAL_TABLE(x)` 设置定时任务（单位：秒）