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

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

• Una instancia de RDS que ejecute PostgreSQL 10 o posterior

• Si su instancia ejecuta PostgreSQL 14: una versión menor del motor 20220830 o posterior. Para actualizar, consulte Update the minor engine version

• Un bucket de OSS en la misma región que su instancia de RDS (recomendado por rendimiento). Consulte Activate OSS y Create a bucket

• Un AccessKey ID y un AccessKey Secret con acceso de lectura y escritura al bucket. Consulte Obtain an AccessKey pair

Funcionamiento

oss_fdw asigna uno o más objetos de OSS a una tabla externa de PostgreSQL. Las consultas sobre la tabla externa desencadenan un escaneo de los objetos coincidentes en OSS; no se copian datos a su instancia de RDS hasta que los inserta explícitamente. Todos los objetos de OSS deben estar en formato CSV, con compresión gzip opcional.

Para usar oss_fdw:

1. Instale la extensión.

2. Cree un servidor que almacene las credenciales de conexión a OSS.

3. Cree una tabla externa que se asigne a los objetos de OSS que desea leer o escribir.

4. Consulte o escriba datos a través de la tabla externa.

Configuración de 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 datos desde OSS

Lea directamente desde OSS sin cargar datos en su instancia de RDS:

SELECT * FROM ossexample;

Cargue datos en una tabla local para acelerar las consultas posteriores:

1. Cree una tabla local con el mismo esquema:

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

2. Inserte datos desde la tabla externa:

    INSERT INTO example SELECT * FROM ossexample;

Antes de importar, ejecute EXPLAIN para estimar la cantidad de objetos de OSS coincidentes y obtener el plan de consulta:

EXPLAIN INSERT INTO example SELECT * FROM ossexample;

Salida 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 datos a OSS

Escriba datos de una tabla local hacia OSS:

INSERT INTO ossexample SELECT * FROM example;

Para la exportación, la tabla externa debe usar la opción dir (no filepath). Cada exportación escribe objetos en la carpeta de OSS especificada.

Para controlar el tamaño del archivo de salida y el paralelismo, utilice los parámetros específicos de escritura en Parámetros de la tabla externa.

Parámetros del servidor

Utilice estos parámetros en la cláusula OPTIONS de CREATE SERVER.

Parámetros de la tabla externa

Utilice estos parámetros en la cláusula OPTIONS de CREATE FOREIGN TABLE.

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

Proteja sus credenciales

Si los valores de id y key en CREATE SERVER se almacenan en texto plano, cualquier usuario de la base de datos con acceso a pg_foreign_server podrá leer su par de AccessKey:

SELECT * FROM pg_foreign_server;

Para proteger sus credenciales, utilice cifrado simétrico en los valores de id y key al crear el servidor. Utilice una clave de cifrado diferente para cada instancia de RDS. Cuando están cifrados, los valores aparecen como MD5**** en pg_foreign_server:

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

Cada valor cifrado comienza con MD5, y la longitud total dividida entre 8 es igual a 3. Una vez exportados los valores cifrados, no se vuelven a cifrar. No se pueden crear AccessKey IDs ni secrets que comiencen con MD5, ya que el prefijo de cifrado está reservado.

A diferencia de Greenplum, oss_fdw no admite agregar tipos de datos durante el cifrado, lo cual preserva la compatibilidad con versiones anteriores de PostgreSQL.

Notas de uso

• Ubique su instancia de RDS y su bucket de OSS en la misma región para maximizar el rendimiento de importación y exportación. Consulte OSS domain names para obtener detalles sobre los endpoints.

• oss_fdw lee y escribe únicamente en formato CSV, incluido CSV comprimido con gzip.

• El rendimiento de la importación depende de la CPU, la E/S y la memoria disponibles en su instancia de RDS.

• parse_errors solo es compatible con operaciones de lectura. No se permite configurarlo en una tabla externa de exportación.

• filepath es solo para importación. Para exportación, utilice dir.

• Las opciones filepath y dir son mutuamente excluyentes. Especifique exactamente una.

Solución de problemas