Exportación de datos de consulta mediante la función aws_s3.query_export_to_s3

Exporte sus datos de PostgreSQL a Amazon S3 llamando a la función aws_s3.query_export_to_s3.

Requisitos previos

Antes de utilizar la función aws_s3.query_export_to_s3, asegúrese de completar los siguientes requisitos previos:

• Instale las extensiones de PostgreSQL necesarias como se describe en Información general de la exportación de datos a Amazon S3.

• Determine a dónde exportar los datos en Amazon S3 como se describe en Especificación de la ruta del archivo de Amazon S3 a exportar.

• Tenga cuidado de que la del clúster de base de datos tenga acceso de exportación a Amazon S3 según se describe en Configuración del acceso a un bucket de Amazon S3.

Los ejemplos siguientes utilizan una tabla de base de datos llamada sample_table. Estos ejemplos exportan los datos a un bucket llamado amzn-s3-demo-bucket. La tabla y los datos de ejemplo se crean con las siguientes instrucciones SQL en psql.

psql=> CREATE TABLE sample_table (bid bigint PRIMARY KEY, name varchar(80));
psql=> INSERT INTO sample_table (bid,name) VALUES (1, 'Monday'), (2,'Tuesday'), (3, 'Wednesday');

Llamar a aws_s3.query_export_to_s3

A continuación, se muestran las formas básicas de llamar a la función aws_s3.query_export_to_s3.

En estos ejemplos se utiliza la variable s3_uri_1 para identificar una estructura que contiene la información que identifica el archivo de Amazon S3. Utilice la función aws_commons.create_s3_uri para crear la estructura.

psql=> SELECT aws_commons.create_s3_uri(
   'amzn-s3-demo-bucket',
   'sample-filepath',
   'us-west-2'
) AS s3_uri_1 \gset

Aunque los parámetros varían para las dos llamadas a funciones siguientes aws_s3.query_export_to_s3, los resultados son los mismos para estos ejemplos. Todas las filas de la tabla sample_table se exportan a un bucket llamado amzn-s3-demo-bucket.

psql=> SELECT * FROM aws_s3.query_export_to_s3('SELECT * FROM sample_table', :'s3_uri_1');

psql=> SELECT * FROM aws_s3.query_export_to_s3('SELECT * FROM sample_table', :'s3_uri_1', options :='format text');

Los parámetros se describen de la siguiente manera:

• 'SELECT * FROM sample_table': el primer parámetro es una cadena de texto requerida que contiene una consulta SQL. El motor de PostgreSQL ejecuta esta consulta. Los resultados de la consulta se copian en el bucket de S3 identificado en otros parámetros.

• :'s3_uri_1': este parámetro es una estructura que identifica el archivo de Amazon S3. En este ejemplo se utiliza una variable para identificar la estructura creada anteriormente. En su lugar, puede crear la estructura incluyendo la llamada a la función aws_commons.create_s3_uri insertada dentro de la llamada a la función aws_s3.query_export_to_s3 de la siguiente manera.

  SELECT * from aws_s3.query_export_to_s3('select * from sample_table', 
     aws_commons.create_s3_uri('amzn-s3-demo-bucket', 'sample-filepath', 'us-west-2') 
  );

• options :='format text': el parámetro options es una cadena de texto opcional que contiene argumentos COPY de PostgreSQL. El proceso de copia utiliza los argumentos y el formato del comando COPY de PostgreSQL.

Si el archivo especificado no existe en el bucket de Amazon S3, se crea. Si el archivo ya existe, se sobrescribe. La sintaxis para acceder a los datos exportados en Amazon S3 es la siguiente.

s3-region://bucket-name[/path-prefix]/file-prefix

Las exportaciones más grandes se almacenan en varios archivos, cada uno con un tamaño máximo de aproximadamente 6 GB. Los nombres de archivo adicionales tienen el mismo prefijo de archivo, pero con _partXX anexado. XX representa 2, luego 3, y así sucesivamente. Por ejemplo, supongamos que especifica la ruta donde almacena los archivos de datos como sigue.

s3-us-west-2://amzn-s3-demo-bucket/my-prefix

Si la exportación tiene que crear tres archivos de datos, el bucket de Amazon S3 contiene los siguientes archivos de datos.

s3-us-west-2://amzn-s3-demo-bucket/my-prefix
s3-us-west-2://amzn-s3-demo-bucket/my-prefix_part2
s3-us-west-2://amzn-s3-demo-bucket/my-prefix_part3

Para obtener la referencia completa de esta función y formas adicionales de llamarla, consulte aws_s3.query_export_to_s3. Para obtener más información sobre el acceso a archivos en Amazon S3, consulte Ver un objeto en la Guía del usuario de Amazon Simple Storage Service.

Exportación a un archivo CSV que utiliza un delimitador personalizado

En el ejemplo siguiente se muestra cómo llamar a la función aws_s3.query_export_to_s3 para exportar datos a un archivo que utiliza un delimitador personalizado. En el ejemplo se utilizan argumentos del comando COPY de PostgreSQL para especificar el formato de valor separado por comas (CSV) y un delimitador de dos puntos (:).

SELECT * from aws_s3.query_export_to_s3('select * from basic_test', :'s3_uri_1', options :='format csv, delimiter $$:$$');

Exportación a un archivo binario con codificación

En el ejemplo siguiente se muestra cómo llamar a la función aws_s3.query_export_to_s3 para exportar datos a un archivo binario que tiene codificación Windows-1253.

SELECT * from aws_s3.query_export_to_s3('select * from basic_test', :'s3_uri_1', options :='format binary, encoding WIN1253');