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

功能介绍

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' [, ... ] ) ]

参数选项

OPTIONS参数选项请参见下表：

示例

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' [, ... ] ) ]

参数选项

OPTIONS参数选项请参见下表：

示例

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' [, ... ] ) ]

参数选项

OPTIONS参数选项请参见下表：

示例

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

您也可以通过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_errors和segment_reject_limit参数提供容错功能，原始数据中的错误数据不会导致OSS外表扫描停止。

关于log_errors和segment_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上的数据。

相关文档

• 使用OSS Foreign Table分区表

• 使用OSS Foreign Table导出数据

• OSS Foreign Table使用技巧

• OSS Foreign Table数据类型对照表