文档

oss_fdw

更新时间:

本文介绍了oss_fdw插件的概述、使用限制以及使用方法等内容。

概述

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

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

前提条件

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

  • 支持的PolarDB PostgreSQL版的版本如下:

    • PostgreSQL 14(内核小版本14.5.3.0及以上)

    • PostgreSQL 11(内核小版本1.1.1及以上)

    说明

    您可通过如下语句查看PolarDB PostgreSQL版的内核小版本的版本号:

    • PostgreSQL 14

      select version();
    • PostgreSQL 11

      show polar_version;

使用限制

oss_fdw外部表仅支持SELECTINSERTTRUNCATE操作,不支持UPDATEDELETE操作。因此,只适用于数据归档场景:数据被写入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'
);
说明

其中:

  • host:OSS的访问域名(EndPoint)。

  • bucket:OSS的存储空间 (Bucket)名称。

  • id/key:阿里云账号Access Key的ID和Secret。

将外部表映射到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');

    此时,插入该表的数据已经写入到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
             Total size of OSS file: 1292 bytes
    (5 rows)
    
    SELECT COUNT(*) FROM t1_oss;
     count
    -------
       100
    (1 row)

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

    INSERT INTO t1_oss VALUES (generate_series(1,100), 0.1, 'hello');
    INSERT 0 100
    
    EXPLAIN SELECT COUNT(*) FROM t1_oss;
                                QUERY PLAN
    -------------------------------------------------------------------
     Aggregate  (cost=12.07..12.08 rows=1 width=8)
       ->  Foreign Scan on t1_oss  (cost=0.00..11.80 rows=108 width=0)
             Directory on OSS: archive/
             Number Of OSS file: 2
             Total size of OSS file: 2584 bytes
    (5 rows)
    
    SELECT COUNT(*) FROM t1_oss;
     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');
    INSERT 0 100
    
    INSERT INTO t2_oss VALUES (generate_series(1,100), 0.1, 'hello');
    INSERT 0 100
    
    EXPLAIN SELECT COUNT(*) FROM t2_oss;
                                QUERY PLAN
    -------------------------------------------------------------------
     Aggregate  (cost=12.07..12.08 rows=1 width=8)
       ->  Foreign Scan on t2_oss  (cost=0.00..11.80 rows=108 width=0)
             Directory on OSS: prefix/file_
             Number Of OSS file: 2
             Total size of OSS file: 2584 bytes
    (5 rows)
    
    SELECT COUNT(*) FROM t2_oss;
     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或zstd。

通过参数compressionlevel可以选择压缩等级,压缩等级越高,压缩及解压缩时占用的CPU越多;同时,网络传输字节数与外表数据在OSS上使用的存储空间越小

Gzip压缩

Gzip压缩算法的压缩等级取值范围:1~9默认值为6。

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外表对应的每个文件,可以看到Gzip压缩后的外部表文件大小显著低于未压缩的外部表

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)

Zstandard压缩

说明

Zstandard压缩算法仅PostgreSQL 14(内核小版本14.9.13.0及以上)支持。

Zstandard压缩算法的压缩等级取值范围为-7~22,默认值为6。

CREATE FOREIGN TABLE t6_oss (
    id INT,
    f FLOAT,
    txt TEXT
)
SERVER ossserver
OPTIONS (
    dir 'archive_file_zstd/',
    compressiontype 'zstd',
    compressionlevel '9'
);

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

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

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('t6_oss');
                     name                      | size
-----------------------------------------------+------
 archive_file_zstd/_t6_oss_748106174612293.zst | 6710
 archive_file_zstd/_t6_oss_748106174700206.zst | 6710
 archive_file_zstd/_t6_oss_748106174866829.zst | 6710
(3 rows)

卸载插件

DROP EXTENSION oss_fdw;