ApsaraDB RDS:Use the oss_fdw extension to read and write foreign data text files

Pré-requisitos

Antes de começar, verifique se você tem:

• Uma instância RDS com PostgreSQL 10 ou superior

• Se a instância executar o PostgreSQL 14: versão secundária do mecanismo 20220830 ou posterior. Para atualize, consulte Atualizar a versão secundária do mecanismo

• Um bucket do OSS na mesma região da instância RDS (recomendado para melhor desempenho). Consulte Ativar o OSS e Crie um bucket

• Um AccessKey ID e AccessKey Secret com permissões de leitura e gravação no bucket. Consulte Obter um par de AccessKey

Como funciona

O oss_fdw mapeia um ou mais objetos do OSS a uma tabela externa do PostgreSQL. Consultas nessa tabela externa acionam uma varredura nos objetos correspondentes no OSS. Nenhum dado é copiado para a instância RDS até que você o insira explicitamente. Todos os objetos do OSS devem estar no formato CSV, com compressão gzip opcional.

Para usar o oss_fdw:

1. Instale a extensão.

2. Crie um servidor para armazenar as credenciais de conexão do OSS.

3. Crie uma tabela externa mapeada para os objetos do OSS que deseja ler ou gravar.

4. Consulte ou grave dados pela tabela externa.

Configurar o oss_fdw

CREATE EXTENSION oss_fdw;

CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS (
    host   'oss-cn-hangzhou-internal.aliyuncs.com',
    id     '<your-access-key-id>',
    key    '<your-access-key-secret>',
    bucket '<your-bucket-name>'
);

CREATE FOREIGN TABLE ossexample (
    date   text,
    time   text,
    open   float,
    high   float,
    low    float,
    volume int
)
SERVER ossserver
OPTIONS (
    dir       'osstest/',
    delimiter ',',
    format    'csv',
    encoding  'utf8'
);

Importar dados do OSS

Leia diretamente do OSS sem carregar dados na instância RDS:

SELECT * FROM ossexample;

Carregue dados em uma tabela local para acelerar consultas subsequentes:

1. Crie uma tabela local com o mesmo esquema:

    CREATE TABLE example (
        date   text,
        time   text,
        open   float,
        high   float,
        low    float,
        volume int
    );

2. Insira os dados da tabela externa:

    INSERT INTO example SELECT * FROM ossexample;

Antes de importar, execute EXPLAIN para estimar a quantidade de objetos do OSS correspondentes e obter o plano de consulta:

EXPLAIN INSERT INTO example SELECT * FROM ossexample;

Saída esperada:

                                      QUERY PLAN
----------------------------------------------------------------------
 Insert on example  (cost=0.00..1.10 rows=0 width=0)
   ->  Foreign Scan on ossexample  (cost=0.00..1.10 rows=1 width=998)
         Foreign OssDir: osstest/
         Number Of Ossfile: 2

Exportar dados para o OSS

Grave dados de uma tabela local no OSS:

INSERT INTO ossexample SELECT * FROM example;

Na exportação, a tabela externa deve usar a opção dir (não filepath). Cada operação de exportação grava objetos na pasta especificada do OSS.

Para controlar o tamanho do arquivo de saída e o paralelismo, use os parâmetros específicos de gravação em Parâmetros da tabela externa.

Parâmetros do servidor

Use estes parâmetros na cláusula OPTIONS do comando CREATE SERVER.

Parâmetros da tabela externa

Use estes parâmetros na cláusula OPTIONS do comando CREATE FOREIGN TABLE.

Ferramentas auxiliares

oss_fdw_list_file(relname text, schema text DEFAULT 'public')

SELECT * FROM oss_fdw_list_file('ossexample');

              name              |   size
--------------------------------+-----------
 osstest/test.gz.1              | 739698350
 osstest/test.gz.2              | 739413041
 osstest/test.gz.3              | 739562048
(3 rows)

SET oss_fdw.rds_read_one_file = 'osstest/test.gz.2';
SELECT * FROM oss_fdw_list_file('ossexample');

              name              |   size
--------------------------------+-----------
 oss_test/test.gz.2             | 739413041
(1 rows)

Proteger suas credenciais

Se os valores de id e key em CREATE SERVER forem armazenados em texto simples, qualquer usuário de banco de dados com acesso a pg_foreign_server poderá ler seu par de AccessKey:

SELECT * FROM pg_foreign_server;

Para proteger suas credenciais, use criptografia simétrica nos valores de id e key ao criar o servidor. Use uma chave de criptografia diferente para cada instância RDS. Quando criptografados, os valores aparecem como MD5**** em pg_foreign_server:

 ossserver | 10 | 16390 | | | | {host=oss-cn-hangzhou-zmf.aliyuncs.com,id=MD5****,key=MD5****,bucket=067862}

Cada valor criptografado começa com MD5, e o comprimento total dividido por 8 resulta em 3. Após a exportação, os valores criptografados não são recriptografados. Não é possível criar AccessKey IDs e secrets que comecem com MD5 — o prefixo de criptografia é reservado.

Diferentemente do Greenplum, o oss_fdw não suporta a adição de tipos de dados durante a criptografia, o que preserva a compatibilidade com versões anteriores do PostgreSQL.

Observações de uso

• Mantenha a instância RDS e o bucket do OSS na mesma região para maximizar o throughput de importação e exportação. Consulte Nomes de domínio do OSS para detalhes sobre endpoints.

• O oss_fdw lê e grava apenas no formato CSV, incluindo CSV comprimido com gzip.

• O desempenho da importação depende da CPU, I/O e memória disponíveis na instância RDS.

• O parâmetro parse_errors é suportado apenas para leituras. Sua definição em uma tabela externa de exportação não é permitida.

• O parâmetro filepath destina-se apenas à importação. Para exportação, use dir.

• As opções filepath e dir são mutuamente exclusivas. Especifique exatamente uma.

Solução de problemas