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

使用限制

云原生数据仓库 AnalyticDB PostgreSQL 版实例和OSS存储空间Bucket必须位于同一地域。

功能介绍

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 <server_name>
    [ OPTIONS ( option 'value' [, ... ] ) ]

参数选项

OPTIONS参数选项请参见下表。

示例

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

您也可以通过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数据

查询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数据类型对照表