本文介绍如何使用OSS Foreign Table(OSS外表,简称OSS FDW)导入OSS中的数据并进行数据分析。

功能介绍

OSS FDW基于PG FDW(PostgreSQL Foreign Data Wrapper)框架进行开发,通过OSS FDW,您可以进行如下操作:

  • 将OSS数据导入本地表(行存表或列存表)进行分析加速。
  • 直接查询分析OSS的海量数据。
  • OSS外表与本地表关联分析。

OSS FDW支持多种格式的数据文件,适用不同的业务场景,具体文件格式如下:

  • 支持访问CSV、TEXT、JSON、JSONLINE格式的非压缩文本文件。
  • 支持访问CSV、TEXT格式的GZIP压缩、标准SNAPPY压缩文本文件。
  • 支持访问JSON、JSONLINE格式的GZIP压缩文本文件。
  • 支持访问ORC格式的二进制文件。ORC数据类型与AnalyticDB PostgreSQL版数据类型的映射关系,请参见ORC文件数据类型对照表
  • 支持访问PARQUET格式的二进制文件。PARQUET数据类型与AnalyticDB PostgreSQL版数据类型的映射关系,请参见Parquet文件数据类型对照表
  • 支持访问AVRO格式的二进制文件。AVRO数据类型与AnalyticDB PostgreSQL版数据类型的映射关系,请参见Avro文件数据类型对照表

准备工作

准备OSS数据

准备示例文件example.csv

获取OSS Bucket信息

以下内容将指导您获取Bucket名称文件的路径Endpoint(地域节点)Bucket域名

  1. 登录OSS管理控制台
  2. 在左侧导航栏中,单击目标Bucket列表
  3. Bucket列表,单击目标Bucket。
    您可以在Bucket列表页面,您可以获取Bucket名称
  4. 文件管理页面,您可以获取的Bucket中文件的路径。
  5. 单击左侧概览
  6. 概览页面的访问域名区域,您可以获取Endpoint(地域节点)Bucket域名
    建议使用ECS的VPC网络访问(内网)的访问域名进行访问。

获取AccessKey ID和AccessKey Secret

获取AccessKey ID和AccessKey Secret的具体操作,请参见获取AccessKey

创建OSS Server

使用CREATE SERVER语句创建OSS Server,指定需要访问的OSS服务端。更多关于CREATE SERVER的介绍,请参见CREATE SERVER

语法

CREATE SERVER server_name
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

参数选项

参数 类型 是否必填 说明
server_name 字符串 OSS Server的名称。
fdw_name 字符串 管理服务器的外部数据容器的名称,固定为oss_fdw。

OPTIONS参数选项请参见下表:

参数 类型 是否必填 说明
endpoint 字符串 Bucket域名,获取方法请参见准备工作
bucket 字符串 数据文件所属的Bucket的名称,获取方法请参见准备工作
说明
  • OSS Server和OSS FDW中必须有一个设置了Bucket。关于OSS FDW的Bucket相关信息,请参见创建OSS FDW
  • 如果OSS Server和OSS FDW都设置了Bucket,则OSS FDW中的Bucket生效。
speed_limit 数值 触发超时的数据量。单位为字节,默认值为1024字节。

需要与speed_time参数配合使用。

说明 默认情况下,如果连续90秒传输的数据量少于1024字节,则会触发超时。具体信息,请参见OSS SDK 错误处理
speed_time 数值 触发超时的时间。单位为秒,默认值为90秒。

需要与speed_limit参数配合使用。

说明 默认情况下,如果连续90秒传输的数据量少于1024字节,则会触发超时。具体信息,请参见OSS SDK 错误处理
connect_timeout 数值 连接超时时间。单位为秒,默认值为10秒。
dns_cache_timeout 数值 DNS超时时间。单位为秒,默认值为60秒。

示例

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
  );

您也可以通过ALTER SERVER语句修改OSS Server的配置,使用方法,请参见ALTER SERVER

修改OSS Server配置示例如下:

  • 修改OSS Server中的某个参数:
    ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com');
  • 添加OSS Server中的某个参数:
    ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20');
  • 删除OSS Server中的某个参数:
    ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);

您也可以通过DROP SERVER语句删除该OSS Server,使用方法,请参见DROP SERVER

创建OSS User Mapping

创建OSS Server后,您还需要创建一个访问OSS Server的用户。您可以使用CREATE USER MAPPING语句创建OSS User Mapping,用于定义AnalyticDB PostgreSQL版数据库用户与访问OSS Server用户的映射关系。更多介绍,请参见CREATE USER MAPPING

语法

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
    SERVER servername
    [ OPTIONS ( option 'value' [, ... ] ) ]

参数选项

参数 类型 是否必填 说明
username 字符串 是,四选一 指定映射的AnalyticDB PostgreSQL版实例的用户名。
USER 字符串 映射当前的AnalyticDB PostgreSQL版实例的用户名。
CURRENT_USER 字符串
PUBLIC 字符串 匹配所有AnalyticDB PostgreSQL版实例的用户名,包括以后创建的用户。
servername 字符串 OSS Server的名称。

OPTIONS参数选项请参见下表:

参数 类型 是否必填 说明
id 字符串 AccessKey ID,获取方法,请参见获取AccessKey
key 字符串 AccessKey Secret,获取方法,请参见获取AccessKey

示例

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI5t7Ge***************',
        key 'FikziJd2La*******************'
    );

您也可以通过DROP USER MAPPING语句删除该用户,使用方法,请参见DROP USER MAPPING

创建OSS FDW

拥有OSS Server和访问OSS Server的用户后,您可以开始创建OSS FDW。您可以使用CREATE FOREIGN TABLE语句创建OSS FDW,更多信息,请参见CREATE FOREIGN TABLE

语法

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

参数选项

参数 类型 是否必填 说明
table_name 字符串 OSS FDW名称。
column_name 字符串 列名。
data_type 字符串 该列的数据类型。

OPTIONS参数选项请参见下表:

参数 类型 是否必填 说明
filepath 字符串 是,三选一 OSS中包含路径的文件名称。

如果使用filepath参数,则仅选择指定文件。

prefix 字符串 指定数据文件对应路径名的前缀,不支持正则表达式,仅是匹配前缀。

如果使用prefix参数,则会选择含有这一前缀的所有OSS文件,例如:

  • 如果指定prefix=test/filename,以下文件都会被导入:
    • test/filename
    • test/filenamexxx
    • test/filename/aa
    • test/filenameyyy/aa
    • test/filenameyyy/bb/aa
  • 如果指定prefix=test/filename/,只有以下文件会被导入(上面列的其他文件不会被导入):
    • test/filename/aa
dir 字符串 OSS中的目录路径。目录路径需要以/结尾,例如test/mydir/

如果使用dir参数,会选择虚拟目录下的所有文件,但不包括它的子目录和子目录下的文件。

bucket 字符串 数据文件所属的Bucket的名称,获取方法请参见准备工作
说明
  • OSS Server和OSS FDW中必须有一个设置了Bucket。
  • 如果OSS Server和OSS FDW都设置了Bucket,则OSS FDW中的Bucket生效。
format 字符串 文件格式,取值范围如下:
  • csv
  • text
  • orc
  • avro
  • parquet
  • json

    关于JSON的使用规范,请参见JSON规范

  • jsonline

    以换行符分隔的JSON,所有能被JSONLINE读取的数据一定可以用JSON读取,反之则不一定。在可行的情况下,推荐使用JSONLINE。更多JSONLINE规范,请参见JSONLINE规范

filetype 字符串 文件类型,取值范围如下:
  • plain(默认):按字节二进制读取,不做额外处理。
  • gzip:读取原始二进制数据并使用GZIP解压缩。
  • snappy:读取原始二进制数据并使用SNAPPY解压缩。

    仅支持标准格式的SNAPPY压缩,暂不支持HADOOP-SNAPPY压缩文件。

说明
  • filetype参数仅支持CSV、TEXT、JSON和JSONLINE格式的文件。
  • 其中snappy选项不支持JSON和JSONLINE格式的文件。
log_errors 布尔型 是否将错误记录到日志文件。默认值为false。更多信息,请参见容错机制
说明 该参数仅适用于CSV和TEXT格式的文件。
segment_reject_limit 数值 异常中止任务(error abort)的数量。

包含%时表示错误行百分比,不包含%时表示错误行数。例如:

  • segment_reject_limit = '10':表示错误的行数超过10行时,任务停止并报错退出。
  • segment_reject_limit = '10%':表示错误的行数超过已处理行数的10%时,任务停止并报错退出。
说明 该参数仅适用于CSV和TEXT格式的文件。
header 布尔型 源文件中字段名是否包含header行,取值如下:
  • true:包含header行。
  • false(默认):不包含header行。
说明 该参数仅适用于CSV格式的文件。
delimiter 字符串 字段分隔符,仅允许设置为单字节字符。
  • CSV文件:默认为逗号(,)。
  • TEXT文件:默认为tab键。
说明 该参数仅适用于CSV和TEXT格式的文件。
quote 字符串 字段引号,仅允许设置为单字节字符。默认为双引号(")。
说明 该参数仅适用于CSV格式的文件。
escape 字符串 声明。默认为双引号(")。
说明 该参数仅适用于CSV格式的文件。
null 字符串 指定文件中的NULL字符串。
  • CSV格式:默认为\N(backslash)。
  • TEXT格式:默认为未被引号引用的空白字符。
说明 该参数仅适用于CSV和TEXT格式的文件。
encoding 字符串 指定数据文件编码。默认情况下为客户端编码。
说明 该参数仅适用于CSV和TEXT格式的文件。
force_not_null 布尔型 是否声明字段的值不匹配空字符串,取值如下:
  • true:表示字段的值不匹配空字符串。
  • false(默认):表示字段的值匹配空字符串。
说明 该参数仅适用于CSV和TEXT格式的文件。
force_null 布尔型 空字符串处理方法,取值如下:
  • true:空字符串的字段的值无论是否添加了引号均作为NULL返回。
  • false(默认):空字符串的字段的值只有未加引号的情况下作为NULL返回。
说明 该参数仅适用于CSV和TEXT格式的文件。

示例

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');
说明 创建OSS FDW完成后,您可以通过如下方式查看OSS FDW匹配的OSS文件列表是否符合预期:
  • 方法一:
    EXPLAIN VERBOSE SELECT * FROM <OSS FDW表名>;
  • 方法二:
    SELECT * FROM get_oss_table_meta('<OSS FDW表名>');

您也可以通过DROP FOREIGN TABLE语句删除该OSS FDW,使用方法,请参见DROP FOREIGN TABLE

导入OSS数据到本地表

  1. 上传准备工作中的示例文件example.csv到OSS,具体操作,请参见上传文件
    说明
    • 建议数据文件的编码和数据库编码保持一致,减少编码转换,提高效率。数据库编码默认UTF-8。
    • AnalyticDB PostgreSQL版的每个数据分区(Segment)将按轮询方式并行对OSS上的数据文件进行读取。
    • 对于CSV和TEXT文件,AnalyticDB PostgreSQL版支持多文件并行读取,默认并行数为4。文件数量建议为数据节点数(Segment节点数量 X 单个Segment核数)的整倍数,从而提升读取效率。如何拆分源文件,请参见切分大文件
  2. 连接AnalyticDB PostgreSQL版数据库,连接方式,请参见客户端连接
  3. 创建OSS Server,示例如下:
    CREATE SERVER oss_serv
        FOREIGN DATA WRAPPER oss_fdw
        OPTIONS (
            endpoint 'oss-cn-********.aliyuncs.com',
            bucket 'adb-pg'
      );
  4. 创建访问OSS Server的用户,示例如下:
    CREATE USER MAPPING FOR PUBLIC
        SERVER oss_serv
        OPTIONS (
            id 'LTAI5t7Ge***************',
            key 'FikziJd2La*******************'
        );
  5. 创建OSS FDW,示例如下:
    CREATE FOREIGN TABLE ossexample (
        date text,
        time text,
        open float,
        high float,
        low float,
        volume int
    ) SERVER oss_serv OPTIONS (dir 'oss_adb/', format 'csv');
  6. 导入OSS数据,您可以通过INSERT语句或CREATE TABLE AS语句导入数据,具体方法如下:
    • INSERT
      1. 使用INSERT语句导入数据需要在本地创建一张结构一致的表,建表示例如下:
        CREATE TABLE adbexample (
            date text,
            time text,
            open float,
            high float,
            low float,
            volume int
        ) WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5);
      2. 执行INSERT语句将外表ossexample的数据导入到本地表adbexample,示例如下:
        INSERT INTO adbexample SELECT * FROM ossexample;
    • CREATE TABLE AS

      执行CREATE TABLE AS语句新建一张本地表adbexample并导入外表ossexample的数据,示例如下:

      CREATE TABLE adbexample AS SELECT * FROM ossexample DISTRIBUTED BY (volume);

查询分析OSS数据

查询OSS FDW外表的数据与查询本地表数据方法一样,常见查询如下:

  • 键值过滤查询,示例如下:
    SELECT * FROM ossexample WHERE volume = 5;
  • 聚合查询,示例如下:
    SELECT count(*) FROM ossexample WHERE volume = 5;
  • 过滤、分组和LIMIT查询,示例如下:
    SELECT low, sum(volume)
      FROM ossexample
     GROUP BY low
     ORDER BY low
     limit 5;

OSS FDW外表与本地表关联分析

  1. 新建用于关联分析的本地表example,并插入测试数据,示例如下:
    CREATE TABLE example (id int, volume int);
    INSERT INTO example VALUES(1,1), (2,3), (4,5);
  2. 本地表example和OSS FDW外表ossexample执行关联查询,示例如下:
    SELECT example.volume, min(high), max(low)
    FROM
    ossexample,
    example
    WHERE ossexample.volume = example.volume
    GROUP BY(example.volume)
    ORDER BY example.volume;

容错机制

OSS FDW通过log_errorssegment_reject_limit参数提供容错功能,原始数据中的错误数据不会导致OSS外表扫描停止。

关于log_errorssegment_reject_limit参数的信息,请参见创建OSS FDW

  • 创建支持容错的OSS FDW外表,示例如下:
    CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text)
        SERVER oss_serv
        OPTIONS (log_errors 'true',         -- 记录错误行信息
                 segment_reject_limit '10', -- 错误行数不得超过10行,否则会停止扫描。
                 dir 'error_sales/',        -- 指定外表匹配的OSS文件目录
                 format 'csv',              -- 指定按csv格式解析文件
                 encoding 'utf8');          -- 指定文件编码 
  • 查看错误行的日志,查看方式如下:
    SELECT * FROM gp_read_error_log('oss_error_sales');
  • 删除错误行的日志,删除方式如下:
    SELECT gp_truncate_error_log('oss_error_sales');

相关文档