产品指南
表设计
外部表
配置基于文件的外部表

基于文件的外表配置

云平台普遍支持通用的对象存储系统，提供对文件的可扩展读写能力，是广泛使用的一类面向湖场景的数据存储平台，本文介绍基于文件的外部表配置方法。

安装扩展

安装 extension，仅需要安装一次，如果已经安装过，可以忽略。

注意：安装扩展（extension）通常需要超级用户权限。只有超级用户可以执行 CREATE EXTENSION，除非扩展被标记为 trusted，此时拥有数据库 CREATE 权限的用户也可安装。创建外部表时如依赖扩展提供的数据类型或函数，需确保已正确安装相关扩展且具备相应权限。

CREATE EXTENSION file_fdw;

创建文件存储外部服务器

创建阿里云 OSS 的外部服务器

CREATE SERVER <server_name> FOREIGN DATA WRAPPER file_fdw
OPTIONS (
 OSS_ENDPOINT 'endopint-url'
);
-- 参考示例
CREATE SERVER oss_server FOREIGN DATA WRAPPER file_fdw
OPTIONS (
 OSS_ENDPOINT 'https://oss-cn-beijing-internal.aliyuncs.com/' -- 同一区域建议使用 oss 内网地址
);

创建 AWS S3 的外部服务器

CREATE SERVER <server_name> FOREIGN DATA WRAPPER file_fdw
OPTIONS (
 AWS_REGION 'region-id'
);
-- 参考示例
CREATE SERVER s3_server FOREIGN DATA WRAPPER file_fdw
OPTIONS (
 AWS_REGION 'cn-north-1'
);

创建用户映射

创建 Aliyun OSS 的用户映射

为本地用户账号授予对应的对象存储的访问权限，通过 OSS_ACCESS_ID 和 OSS_ACCESS_KEY 授权。

CREATE USER MAPPING FOR <local_user> SERVER oss_server
OPTIONS (
 OSS_ACCESS_ID 'access-id',
 OSS_ACCESS_KEY 'access-key'
);

如无特别的权限设置，可考虑对当前所有系统用户都开放访问权限，具体参考如下的使用示例：

CREATE USER MAPPING FOR public SERVER oss_server
OPTIONS (
 OSS_ACCESS_ID 'access-id',
 OSS_ACCESS_KEY 'access-key'
);

创建 AWS S3 的用户映射

为本地用户账号授予对应的对象存储的访问权限，通过 AWS_ACCESS_ID 和 AWS_ACCESS_KEY 授权。

CREATE USER MAPPING FOR <local_user> SERVER oss_server
OPTIONS (
 AWS_ACCESS_ID 'access-id',
 AWS_ACCESS_KEY 'access-key'
);

创建外表

CREATE FOREIGN TABLE <table_name> (
    <column_name> <data_type>,
     ...
    )
SERVER <server_name>
OPTIONS (
   DIR '<path_to_oss>',
   FORMAT '<parquet|orc>'
);
  • server_name：之前创建的外部服务器名称
  • DIR：指定外部对象存储的目录绝对路径
  • FORMAT：指定文件格式，可以是 parquet，orc
-- 创建 OSS 上的 Parquet 表
CREATE FOREIGN TABLE f_t1(
  col1 INT,
  col2 TEXT  -- 手动定义 schema
)
SERVER oss_server
OPTIONS
(
 FORMAT 'parquet',  -- 或者 orc
 DIR 'oss://demo-hz/data/'
);
 
-- 创建 S3 上的 Parquet 表
CREATE FOREIGN TABLE f_t2(
  col1 INT,
  col2 TEXT  -- 手动定义 schema
)
SERVER s3_server
OPTIONS
(
 FORMAT 'parquet',  -- 或者 orc
 DIR 's3://demo-bj/data/'
);
 
-- 从外表导入到 ProtonBase 内表
INSERT INTO local_t1 SELECT * FROM f_t1;
 
-- 从 ProtonBase 内表导出到外表
INSERT INTO f_t1 SELECT * FROM local_t1;

批量创建外表（IMPORT FOREIGN SCHEMA）

file_fdw 扩展支持使用 IMPORT FOREIGN SCHEMA 命令批量创建外表，简化了对外部文件的管理。

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

支持的参数说明

  • remote_schema：在 file_fdw 中，我们扩展了这个参数的语义，目前支持两个内置值：
    • parquet：用于批量创建 Parquet 格式的外表
    • orc：用于批量创建 ORC 格式的外表
  • LIMIT TO：限制只导入指定的表
  • EXCEPT：排除指定的表不导入
  • server_name：之前创建的外部服务器名称
  • local_schema：外表元数据保存在本地对应的 schema
  • OPTIONS：可选参数，用于指定额外的配置选项

使用示例

-- 批量创建 OSS 上的 Parquet 文件外表
IMPORT FOREIGN SCHEMA parquet
    FROM SERVER oss_server
    INTO public
    OPTIONS (
        DIR 'oss://demo-hz/data/'
    );
 
-- 批量创建 S3 上的 ORC 文件外表，只导入指定的表
IMPORT FOREIGN SCHEMA orc
    LIMIT TO (table1, table2)
    FROM SERVER s3_server
    INTO public
    OPTIONS (
        DIR 's3://demo-bj/data/'
    );

注意事项

  • 目前仅支持 Parquet 和 ORC 两种文件格式
  • 不支持分区表
  • 不支持其他文件格式（如 CSV、JSON 等）
  • 外表的名称默认与文件夹同名，不可修改

导入外表数据

为进一步提升用户要做数据分析时的体验，建议可以把数据加载到 ProtonBase 内部，利用 ProtonBase 更为高效的存储格式与能力，来加速数据查询的性能效率。只需要一行简单的 CREATE 命令即能完成，参考如下语法说明：

CREATE TABLE (table_name) 
USING [ { row | column | hybrid }]
AS
SELECT * FROM (foreign_table_name);
外表数据类型映射配置阿里云 MaxCompute 外部表