URL 外部表

URL 外部表

更新时间：2026-06-24 17:02:52

OceanBase 自 V4.3.5 BP1 起（MySQL / Oracle 模式）支持通过 SELECT 或 LOAD DATA 直接读取 CSV、Parquet、ORC 格式文件；V4.3.5 BP1 起支持 ODPS 数据源（SOURCE() / LOAD DATA）。该能力适用于以下典型 AP 场景：

临时数据探查
快速数据加载

URL 外表是 OceanBase 提供的一种 无元数据注册式 的外部数据访问能力。用户通过 SELECT 或 LOAD DATA 语句直接指定数据文件路径与格式，系统动态解析并返回结果，无需预先创建外部表。

支持的数据源与格式

数据源类型	访问方式	支持格式	说明
本地 / 对象存储文件	`FILES()`	CSV / Parquet / ORC	支持 HDFS、OSS、S3 及兼容 S3 协议的对象存储、本地文件
MaxCompute (ODPS)	`SOURCE()`	ODPS 表	直连 ODPS Tunnel，无需导出

注意

CSV 查询结果默认为 VARCHAR 类型（可通过后续 CAST 转换）。
Parquet / ORC 自动推断 Schema（含嵌套结构）。
单次读取受集群资源、外部存储与网络条件限制；文档未定义固定文件数或大小上限。
ODPS 不支持 FILES()，须使用 SOURCE()（当前以 MySQL 模式 LOAD DATA 语法为主）。
URL 外表（FILES() / Location URL）在 MySQL 与 Oracle 模式均支持；具体语法见各模式 SQL 参考。

查询外部数据

通用语法

方式一：Location URL 形式（简洁）

SELECT * FROM 'outfiles/'
(
    FORMAT (TYPE = 'format_type', FIELD_DELIMITER = ','),
    PATTERN = 'regex'
);

方式二：Table Function 形式（灵活，推荐）

SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (TYPE = 'format_type', FIELD_DELIMITER = ','),
    PATTERN = 'regex'
);

'outfiles/'：指定外表文件存放的路径。支持子目录递归扫描。
FORMAT：用于指定外部文件的格式及解析选项。
PATTERN：指定正则模式串，用于过滤 LOCATION 目录下的文件。若不指定，则默认读取所有文件。

各格式查询示例

CSV 文件

通用语法

URL 外表支持两种等效语法形式读取 CSV 文件，需指定 TYPE = 'CSV'：

Location URL 形式（简洁）

SELECT * FROM 'outfiles/'
(
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ','
    ),
    PATTERN = 'data$'
);

Table Function 形式（灵活，推荐）

SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ','
    ),
    PATTERN = 'data$'
);

说明

outfiles/ 指定外表文件存放的路径，支持子目录递归扫描。

FORMAT 用于指定外部文件格式为 CSV 及相关解析选项。

PATTERN 指定正则模式串，用于过滤 LOCATION 目录下的文件；若不指定，则读取所有文件。

重要：使用 URL 外表读取 CSV 文件时，所有列的数据类型默认为 VARCHAR（可通过后续 CAST 转换为其他类型）。

LOCATION 参数既可指定字符串形式的路径（如 'oss://bucket/path/'、'outfiles/'），也可指定已创建的 Location 对象名称（使用 @location_name 引用，语法以当前版本 SQL 参考为准）。

示例 1：创建持久化外部表并引用 Location 对象

适用于需要长期使用的外部表场景，表元数据会保存在数据库中。

-- 先定义一个名为 my_hdfs_loc 的 Location 对象
CREATE LOCATION my_hdfs_loc URL = 'hdfs://namenode:8020/data/';

-- 创建外部表时引用该 Location
CREATE EXTERNAL TABLE ex_t1 (
    c1 INT,
    c2 INT,
    c3 INT
)
LOCATION = @my_hdfs_loc
FORMAT (
    TYPE = 'csv',
    FIELD_DELIMITER = ',',
    LINE_DELIMITER = '\n'
);

示例 2：在临时查询中通过 FILES() 引用 Location 对象

适用于一次性分析外部文件，无需创建持久化表。

-- 假设已存在 Location 对象 my_hdfs_loc
CREATE LOCATION my_hdfs_loc URL = 'hdfs://namenode:8020/data/';

-- 直接在 FILES 表函数中引用
SELECT * FROM FILES(
    LOCATION = @my_hdfs_loc,      -- 使用 @ 前缀引用已定义的 Location
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = '.*\\.parquet$'     -- 匹配所有 .parquet 文件
);

示例 3：不使用 Location 对象，直接指定路径

适用于简单或临时场景，当无需复用路径时，可直接在查询中写入 URL 或路径，支持两种等效语法。

-- 方式一：路径作为表名（简洁写法）
SELECT * FROM '/data/'
(
    FORMAT (TYPE = 'CSV', FIELD_DELIMITER = ',', SKIP_BLANK_LINES = TRUE),
    PATTERN = '^datafiles.*\\.csv$'  -- 注意：PATTERN 是正则表达式
);

-- 方式二：使用 FILES 表函数（显式、推荐用于复杂配置）
SELECT * FROM FILES(
    LOCATION = '/data/',
    FORMAT (TYPE = 'CSV', FIELD_DELIMITER = ',', SKIP_BLANK_LINES = TRUE),
    PATTERN = '^datafiles.*\\.csv$'
);

支持 Header 解析

当通过 FILES() 函数访问 CSV 文件时，若未显式定义表结构（列名和列数），系统将自动推断表结构：

列数：采样指定目录下 CSV 文件的第一行。
列名：默认按顺序命名为 'c1', 'c2', ...

由于许多 CSV 文件的首行是列名（Header），而后续行才是数据，OceanBase 提供了 PARSE_HEADER 配置参数，用于控制是否将第一行解析为列名。

若 CSV 文件首行为列名：应设置 PARSE_HEADER = TRUE。
若 CSV 文件首行为实际数据：应保持 PARSE_HEADER = FALSE（默认）。

-- 基础查询，CSV 首行为数据（无 Header），使用默认列名 c1, c2...
SELECT * FROM FILES(
    LOCATION = 'oss://my-bucket/logs/',
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ',',
        SKIP_BLANK_LINES = TRUE
    ),
    PATTERN = 'user_log_202504.*\\.csv$'
);

-- CSV 首行为列名（如 name,age,city），启用 PARSE_HEADER
SELECT * FROM FILES(
    LOCATION = '/data/sales.csv',
    FORMAT (
        TYPE = 'CSV',
        FIELD_DELIMITER = ',',
        PARSE_HEADER = TRUE  -- 将第一行作为列名
    )
);

使用限制：PARSE_HEADER 与 SKIP_HEADER 互斥，不可同时使用。

Parquet 文件（自动推断 Schema）

-- Location URL 形式
SELECT * FROM 'outfiles/'
(
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = 'data$'
);

-- Table Function 形式
SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = 'data$'
);

'outfiles/' 指定外表文件存放的路径，支持子目录递归扫描。
FORMAT 指定文件格式为 PARQUET。
PATTERN 用于文件过滤（正则匹配）。

-- 实际示例：读取 S3 上的 Parquet 分区文件
SELECT * FROM FILES(
    LOCATION = 's3://analytics-bucket/events/',
    FORMAT (TYPE = 'PARQUET'),
    PATTERN = 'part-.*\\.parquet$'
);

-- 读取 '/data/' 路径下所有以 "datafiles" 开头的 Parquet 文件
SELECT * FROM '/data/' (
    FORMAT = (TYPE = 'PARQUET'),
    PATTERN = 'datafiles$'
);

SELECT * FROM FILES(
    LOCATION = '/data/',
    FORMAT = (TYPE = 'PARQUET'),
    PATTERN = 'datafiles$'
);

ORC 文件

-- URL Location 形式
SELECT * FROM 'outfiles/'
(
    FORMAT (TYPE = 'ORC'),
    PATTERN = 'data$'
);

-- Table Function 形式
SELECT * FROM FILES(
    LOCATION = 'outfiles/',
    FORMAT (TYPE = 'ORC'),
    PATTERN = 'data$'
);

'outfiles/' 指定路径，支持子目录。
FORMAT 指定格式为 ORC。
PATTERN 用于文件过滤。

-- 示例：读取 HDFS 上的 ORC 文件
SELECT * FROM 'hdfs://mycluster/data/orc/'
(
    FORMAT (TYPE = 'ORC'),
    PATTERN = 'clicks_202504.*'
);

ODPS 表（直连 MaxCompute）

ODPS 数据源非文件形式，不支持 FILES()，仅支持 SOURCE()：

SELECT * FROM SOURCE(
    TYPE = 'ODPS',
    ACCESSID = 'LTAI5tXXXXXX',
    ACCESSKEY = 'xxxxxxxxxxxxxx',
    ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
    TUNNEL_ENDPOINT = 'http://dt.cn-hangzhou.maxcompute.aliyun.com',
    PROJECT_NAME = 'sales_analytics',
    TABLE_NAME = 'user_behavior'
);

导入外部数据（LOAD DATA）

将外部数据高效导入 OceanBase 内部表，支持并行、直写等优化。

语法结构

LOAD DATA
    [/*+ INSERT HINT */]
    [REPLACE | IGNORE]
    FROM {
        <url_table_function_expr> |
        ( SELECT expression_list FROM <url_table_function_expr> )
    }
    INTO TABLE table_name
    [PARTITION (partition_name1, [partition_name2 ...])]
    [(column_name_var [, column_name_var] ...)]

其中 <url_table_function_expr> 为：

FILES (
    LOCATION = '<string>',
    {
        FORMAT = (
            TYPE = 'CSV',
            LINE_DELIMITER = '<string>' | <expr>,
            FIELD_DELIMITER = '<string>' | <expr>,
            ESCAPE = '<character>' | <expr>,
            FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
            ENCODING = 'charset',
            NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
            SKIP_HEADER = <int>,
            SKIP_BLANK_LINES = { TRUE | FALSE },
            TRIM_SPACE = { TRUE | FALSE },
            EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
        )
        | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
    },
    [PATTERN = '<regex_pattern>']
)
| SOURCE (
    TYPE = 'ODPS',
    ACCESSID = '<string>',
    ACCESSKEY = '<string>',
    ENDPOINT = '<string>',
    TUNNEL_ENDPOINT = '<string>',
    PROJECT_NAME = '<string>',
    SCHEMA_NAME = '<string>',
    TABLE_NAME = '<string>',
    QUOTA_NAME = '<string>',
    COMPRESSION_CODE = '<string>'
)

LOCATION 指定文件路径，支持子目录递归扫描。
本地路径：格式为 file://path/ 或 /path/，必须是 secure_file_priv 配置路径的子目录。
远程路径：如 oss://bucket/path/，敏感信息（如 AccessKey）由 OceanBase 加密存储于系统表。

说明

敏感凭证（如 AccessKey）由 OceanBase 加密存储于系统表；可通过预配置 Location 对象复用，避免在 SQL 中重复明文填写（Location 语法以 SQL 参考为准）。

导入示例

导入 CSV（全列或指定列）

-- 导入 data1.csv 全部列
LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM FILES(
    LOCATION = 'data/csv',
    FORMAT = (
        TYPE = 'CSV',
        FIELD_DELIMITER = ',',
        SKIP_BLANK_LINES = TRUE
    ),
    PATTERN = 'data1.csv'
)
INTO TABLE csv_ex_t1;

-- 仅导入 c1, c2 列到目标表 col1, col2
LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM (
    SELECT c1, c2 FROM FILES(
        LOCATION = 'data/csv',
        FORMAT = (
            TYPE = 'CSV',
            FIELD_DELIMITER = ',',
            SKIP_BLANK_LINES = TRUE
        ),
        PATTERN = 'data1.csv'
    )
)
INTO TABLE csv_ex_t1 (col1, col2);

导入 Parquet

LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM FILES(
    LOCATION = 'data/parquet',
    FORMAT = (TYPE = 'PARQUET'),
    PATTERN = 'data1.parquet'
)
INTO TABLE parquet_ex_t1;

导入 ORC

LOAD DATA /*+ DIRECT(TRUE) PARALLEL(2) */
FROM FILES(
    LOCATION = 'data/orc',
    FORMAT = (TYPE = 'ORC'),
    PATTERN = 'data1.orc'
)
INTO TABLE orc_ex_t1;

从 ODPS 导入

LOAD DATA
FROM SOURCE (
    TYPE = 'ODPS',
    ACCESSID = '$ODPS_ACCESSID',
    ACCESSKEY = '$ODPS_ACCESSKEY',
    ENDPOINT = '$ODPS_ENDPOINT',
    PROJECT_NAME = 'example_project',
    SCHEMA_NAME = '',
    TABLE_NAME = 'example_table',
    QUOTA_NAME = '',
    COMPRESSION_CODE = ''
)
INTO TABLE odps_ex_t1;

参数说明

LOCATION 路径格式

类型	格式示例	说明
本地文件	`file://data/` 或 `/data/`	需配置 `secure_file_priv`，路径为其子目录
OSS	`oss://bucket/path/`	需在 OBServer 配置 AccessKey
S3	`s3://bucket/path/`	支持 IAM Role / AccessKey
HDFS	`hdfs://namenode:8020/path/`	需部署 Java SDK + Kerberos（如启用）

CSV 格式选项（常用）

参数	默认值	说明
`FIELD_DELIMITER`	`,`	字段分隔符
`PARSE_HEADER`	`FALSE`	是否将首行作为列名
`SKIP_BLANK_LINES`	`FALSE`	跳过空行
`EMPTY_FIELD_AS_NULL`	`FALSE`	空字段转为 NULL
`ENCODING`	`utf8`	文件编码

性能 Hint（LOAD DATA 专用）

/*+ DIRECT(TRUE) PARALLEL(8) */

DIRECT(TRUE)：绕过事务层，直写存储引擎（高速但不可回滚）。
PARALLEL(N)：并行读取文件（N ≤ 文件数）。