PolarDB:Read and write external data files by using oss_fdw

Pré-requisitos

• Ative o OSS e crie um bucket. O que é o OSS?

• O cluster do PolarDB for PostgreSQL deve executar uma das seguintes versões do mecanismo:

  • PostgreSQL 16 (versão de revisão 2.0.16.6.2.0 ou posterior)

  • PostgreSQL 14 (versão de revisão 2.0.14.5.3.0 ou posterior)

  • PostgreSQL 11 (versão de revisão 2.0.11.2.1.0 ou posterior)

Limitações

As tabelas externas da oss_fdw suportam apenas SELECT, INSERT e TRUNCATE. Não há suporte para UPDATE e DELETE. Após a gravação no OSS, os dados podem ser lidos, mas não modificados no local.

Referência de opções de tabela externa

Use estas opções ao criar uma tabela externa do OSS:

Instale a extensão

CREATE EXTENSION oss_fdw;

Crie um servidor de dados externo

Defina a conexão entre o cluster do PolarDB e um bucket do OSS.

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

As opções do servidor são:

Mapeie uma tabela externa para um diretório do OSS

1. Crie uma tabela externa do OSS mapeada para um diretório.

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

2. Insira dados. A gravação ocorre no diretório archive/. Consulte a tabela para verificar:

      INSERT INTO t1_oss VALUES (generate_series(1,100), 0.1, 'hello');

      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)

3. Cada comando INSERT subsequente cria um novo arquivo no mesmo diretório do 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)

4. Execute TRUNCATE para remover todos os arquivos do OSS mapeados para a tabela externa.

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

Mapeie uma tabela externa para um prefixo de diretório

1. Crie uma tabela externa com a opção prefix.

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

2. Cada comando INSERT gera um novo arquivo com o prefixo especificado.

      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)

Especifique o formato de armazenamento

Por padrão, a oss_fdw armazena dados no formato CSV. Use a opção format para definir outro formato. Cada comando INSERT grava os dados no formato especificado em um arquivo do OSS.

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

Liste os arquivos de uma tabela externa do OSS

1. Crie uma tabela externa e execute três comandos INSERT para gravar dados em três arquivos do 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. Chame oss_fdw_list_file() para listar os arquivos associados a uma tabela externa. Opcionalmente, passe o nome do schema (o padrão é public).

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

Compacte dados com gzip ou Zstandard

A opção compressiontype define o algoritmo de compactação. Padrão: nenhum. Valores válidos: gzip e zstd.

A opção compressionlevel controla o equilíbrio entre tamanho do arquivo e uso da CPU. Níveis mais altos produzem arquivos menores e reduzem o volume de transferência de rede, mas exigem mais processamento.

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

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)

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

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)

Remova a extensão

DROP EXTENSION oss_fdw;