全部產品
Search
文件中心

PolarDB:oss_fdw(Object Storage Service外部表格)

更新時間:Oct 26, 2024

本文介紹了oss_fdw外掛程式的概述、使用限制以及使用方法等內容。

概述

oss_fdw外掛程式是PolarDB PostgreSQL版的一個外部資料封裝器(FDW,Foreign Data Wrapper),能夠將阿里雲Object Storage Service(Object Storage Service)上的資料與資料庫中的外部表格定義關聯,使使用者能夠將資料庫表的資料存放到OSS上的同時,而依舊能夠通過標準SQL來讀寫這些資料。

阿里雲Object Storage Service是一款海量、安全、低成本、高可靠的雲端儲存體服務,提供最高可達99.995%的服務可用性,具備多種儲存類型供選擇,能夠全面最佳化儲存成本。資料庫中的歷史資料、唯讀歸檔資料、冷資料非常適合存放到OSS上,以節約資料庫儲存成本。

前提條件

  • 已開通阿里雲Object Storage Service並建立Bucket,具體操作請參見阿里雲Object Storage Service

  • 支援的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');
    • 查詢表格式資料:

      • 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;

    移除後查詢表格式資料。

    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 INTO t2_oss VALUES (generate_series(1,100), 0.1, 'hello');
    
    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外部表格對應的檔案。

    • 使用預設Schema名查詢OSS外部表格對應的檔案。

      SELECT * FROM oss_fdw_list_file('t4_oss');

      返回結果如下:

                         name                    |  size  
      -------------------------------------------+--------
       archive_file_list/_t4_oss_783053364762580 | 148894
       archive_file_list/_t4_oss_783053364849053 | 148894
       archive_file_list/_t4_oss_783053366496328 | 148894
      (3 rows)
    • 指定Schema名查詢OSS外部表格對應的檔案。

      SELECT * FROM oss_fdw_list_file('t4_oss', 'public');

      返回結果如下:

                         name                    |  size  
      -------------------------------------------+--------
       archive_file_list/_t4_oss_783053364762580 | 148894
       archive_file_list/_t4_oss_783053364849053 | 148894
       archive_file_list/_t4_oss_783053366496328 | 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壓縮後的外部表格檔案大小顯著低於未壓縮的外部表格

  • 未進行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)
  • 進行Gzip壓縮後的外部表格空間大小:

    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壓縮後的外部表格檔案大小顯著低於未壓縮的外部表格。

  • 未進行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)
  • 進行Zstandard壓縮後的外部表格空間大小:

    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;