文档

使用OSS Foreign Table进行数据湖分析

更新时间:

本文介绍如何使用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

字符串

声明匹配quote参数的字符串,只允许为单字节的字符。默认为双引号(")。

说明

该参数仅适用于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数据

查询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');

常见问题

Q:删除OSS FDW上的数据时,能否同时删除OSS上的数据。

A:删除OSS FDW上的数据时,无法删除OSS上的数据。

相关文档