oss_fdw

概述

oss_fdw插件是PolarDB PostgreSQL版（兼容Oracle）的一个外部数据包装器（FDW，Foreign Data Wrapper），能够将阿里云对象存储OSS （Object Storage Service）上的数据与数据库中的外部表定义关联，使用户能够将数据库表的数据存放到OSS上的同时，而依旧能够通过标准SQL来读写这些数据。

阿里云对象存储OSS是一款海量、安全、低成本、高可靠的云存储服务，提供最高可达99.995%的服务可用性，具备多种存储类型供选择，能够全面优化存储成本。数据库中的历史数据、只读归档数据、冷数据非常适合存放到OSS上，以节约数据库存储成本。

前提条件

已开通阿里云对象存储OSS并创建Bucket，具体操作请参见阿里云对象存储OSS。

使用限制

oss_fdw外部表仅支持SELECT、INSERT和TRUNCATE操作，不支持UPDATE和DELETE操作。因此，只适用于数据归档场景：数据被写入OSS后，只会被读取，不再被更新。

使用方法

安装插件

CREATE EXTENSION oss_fdw;

创建外部数据服务器

配置OSS连接信息，创建PolarDB到OSS Bucket的映射。

示例如下：

CREATE SERVER ossserver
FOREIGN DATA WRAPPER oss_fdw
OPTIONS (
    host 'oss-cn-xxx.aliyuncs.com',
    bucket 'mybucket',
    id 'xxx',
    key 'xxx'
);

将外部表映射到OSS目录

1. 在PolarDB中创建一张OSS外部表，映射到创建外部数据服务器中配置的OSS外部数据源的某个目录下。

   CREATE FOREIGN TABLE t1_oss (
       id INT,
       f FLOAT,
       txt TEXT
   )
   SERVER ossserver
   OPTIONS (dir 'archive/');

2. 向OSS外部表中导入数据。

   INSERT INTO t1_oss VALUES (generate_series(1,100), 0.1, 'hello');
   NOTICE:  begin writing data to oss location archive/, with block size 10 MB and oss file size 1024 MB
   INSERT 0 100

   此时，插入该表的数据已经写入到 OSS的archive/路径下的文件中。使用如下方法可以查询该外部表。

   EXPLAIN SELECT COUNT(*) FROM t1_oss;
                              QUERY PLAN
   -----------------------------------------------------------------
    Aggregate  (cost=6.54..6.54 rows=1 width=8)
      ->  Foreign Scan on t1_oss  (cost=0.00..6.40 rows=54 width=0)
            Directory on OSS: archive/
            Number Of OSS file: 1
   (4 rows)
   
   SELECT COUNT(*) FROM t1_oss;
   NOTICE:  a total of 1 files will be loaded, begin archive/_t1_oss_741147075942690, end archive/_t1_oss_741147075942690, compression: none
    count
   -------
      100
   (1 row)

   再次对该表执行INSERT时，将会写入OSS该路径下的另一个新文件。

   INSERT INTO t1_oss VALUES (generate_series(1,100), 0.1, 'hello');
   NOTICE:  begin writing data to oss location archive/, with block size 10 MB and oss file size 1024 MB
   INSERT 0 100
   
   SELECT COUNT(*) FROM t1_oss;
   NOTICE:  a total of 2 files will be loaded, begin archive/_t1_oss_741147075942690, end archive/_t1_oss_741147153235165, compression: none
    count
   -------
      200
   (1 row)

   对该表执行TRUNCATE命令，将会移除该外部表在OSS上的所有映射文件。

   TRUNCATE t1_oss;
   TRUNCATE TABLE
   
   SELECT COUNT(*) FROM t1_oss;
   WARNING:  does not match any file in oss
    count
   -------
        0
   (1 row)

将外部表映射到某个路径前缀

1. 使用prefix选项创建外部表。

   CREATE FOREIGN TABLE t2_oss (
       id INT,
       f FLOAT,
       txt TEXT
   )
   SERVER ossserver
   OPTIONS (prefix 'prefix/file_');

2. 对该外部表的多次插入将会产生相同前缀名的多个文件。

   INSERT INTO t2_oss VALUES (generate_series(1,100), 0.1, 'hello');
   NOTICE:  begin writing data to oss location archive/file_, with block size 10 MB and oss file size 1024 MB
   INSERT 0 100
   
   INSERT INTO t2_oss VALUES (generate_series(1,100), 0.1, 'hello');
   NOTICE:  begin writing data to oss location archive/file_, with block size 10 MB and oss file size 1024 MB
   INSERT 0 100
   
   SELECT COUNT(*) FROM t2_oss;
   NOTICE:  a total of 2 files will be loaded, begin prefix/file__t2_oss_741147242967198, end prefix/file__t2_oss_741147243797884, compression: none
    count
   -------
      200
   (1 row)

OSS文件存储格式

oss_fdw支持设置在OSS上存储数据的格式，默认值为CSV，也可以显式声明。对OSS外部表进行INSERT时，将会以CSV格式写入OSS文件。

CREATE FOREIGN TABLE t3_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (dir 'archive_csv/', format 'csv');

查看OSS外部表对应的文件

1. 创建一张OSS外部表，并执行三次INSERT，将会写入三个OSS文件。

   CREATE FOREIGN TABLE t4_oss (
       id INT,
       f FLOAT,
       txt TEXT
   )
   SERVER ossserver
   OPTIONS (dir 'archive_file_list/');
   
   INSERT INTO t4_oss VALUES (generate_series(1,10000), 0.1, 'hello');
   INSERT INTO t4_oss VALUES (generate_series(1,10000), 0.1, 'hello');
   INSERT INTO t4_oss VALUES (generate_series(1,10000), 0.1, 'hello');

2. 使用如下函数，指定参数为OSS外部表的表名和schema名（可选，默认为public），可以查询OSS外部表对应的文件。

   SELECT * FROM oss_fdw_list_file('t4_oss');
                      name                    |  size
   -------------------------------------------+--------
    archive_file_list/_t4_oss_741147680906121 | 148894
    archive_file_list/_t4_oss_741147680965631 | 148894
    archive_file_list/_t4_oss_741147681201236 | 148894
   (3 rows)
   
   SELECT * FROM oss_fdw_list_file('t4_oss', 'public');
                      name                    |  size
   -------------------------------------------+--------
    archive_file_list/_t4_oss_741147680906121 | 148894
    archive_file_list/_t4_oss_741147680965631 | 148894
    archive_file_list/_t4_oss_741147681201236 | 148894
   (3 rows)

OSS存储压缩

通过参数compressiontype可以指定写入OSS文件时的压缩方式，目前仅支持gzip。

通过参数compressionlevel可以选择压缩等级，取值范围：1~9，默认值为6。压缩等级越高，压缩及解压缩时占用的CPU越多，OSS上的文件越小。

CREATE FOREIGN TABLE t5_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (
    dir 'archive_file_compression/',
    compressiontype 'gzip',
    compressionlevel '9'
);

INSERT INTO t5_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t5_oss VALUES (generate_series(1,10000), 0.1, 'hello');
INSERT INTO t5_oss VALUES (generate_series(1,10000), 0.1, 'hello');

查看OSS外表对应的每个文件，可以看到文件大小显著低于未压缩的外部表。

SELECT * FROM oss_fdw_list_file('t4_oss');
                   name                    |  size
-------------------------------------------+--------
 archive_file_list/_t4_oss_741147680906121 | 148894
 archive_file_list/_t4_oss_741147680965631 | 148894
 archive_file_list/_t4_oss_741147681201236 | 148894
(3 rows)

SELECT * FROM oss_fdw_list_file('t5_oss');
                        name                         | size
-----------------------------------------------------+-------
 archive_file_compression/_t5_oss_741147752563794.gz | 23654
 archive_file_compression/_t5_oss_741147752633713.gz | 23654
 archive_file_compression/_t5_oss_741147752828680.gz | 23654
(3 rows)

卸载插件

DROP EXTENSION oss_fdw;