---
title: "文件外部表 - OceanBase 数据库 V4.6.0 | OceanBase 文档中心"
description: 文件外部表 本文介绍通过 LOCATION + FORMAT 访问外部 文件 的外表（CSV / Parquet / ORC）。 不包含 ODPS API 外表（见 ODPS 外部表 ）及 External Catalog 接入方式。 通常，数据库中的表数据存放于数据库自身的存储空间中，而文件外部表的数据则存储在外部…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.6.0

# 文件外部表

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

[编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.6.0/zh-CN/620.obap/400.data-lake/200.data-lake-integration/500.file-external-table.md)  

本文介绍通过 `LOCATION` + `FORMAT` 访问外部**文件**的外表（CSV / Parquet / ORC）。**不包含** ODPS API 外表（见 [ODPS 外部表](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000006326203)）及 External Catalog 接入方式。

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

## 使用限制

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

## 创建文件外部表

### 语法

```sql
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 列
 - 依此类推

**示例：**

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

```

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

### 手动列映射

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

```sql
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 格式

```sql
LOCATION = '[file://] local_file_path'

```

- `local_file_path` 可为相对路径或绝对路径。

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

### 远程 LOCATION 格式

```sql
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 参考](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000005686072) 为准。以下为常用示例。

### CSV Format

```sql
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

```sql
-- 格式为 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 模式下 不支持。

更多信息，参见 [MySQL 模式下的数据类型映射](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000005685878) 和 [Oracle 模式下的数据类型映射](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000005685843)。

## PATTERN

```sql
[ PATTERN = '<regex_pattern>' ]

```

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

## 分区

### 自动分区

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

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

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

```sql
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` 显式管理分区。

**建表示例：**

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

**添加分区：**

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

```

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

**删除分区：**

```sql
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 租户：

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

```

- Oracle 模式租户：

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

```

- 系统租户：

```sql
SELECT * FROM oceanbase.CDB_OB_EXTERNAL_TABLE_FILES;

```

### 手动更新文件列表

```sql
ALTER EXTERNAL TABLE <table_name> REFRESH;

```

### 自动更新策略（AUTO_REFRESH）

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

## 相关文档

- [MySQL 模式下的数据类型映射](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000005685878)
 - [Oracle 模式下的数据类型映射](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000005685843)

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线