Tutorial: Carga de datos desde Amazon S3

En este tutorial, siga el proceso de creación de datos en las tablas de bases de datos de Amazon Redshift desde archivos de datos en un bucket de Amazon S3 de principio a fin.

En este tutorial, aprenderá a hacer lo siguiente:

Tiempo estimado: 60 minutos

Costo estimado: 1,00 USD por hora del clúster

Requisitos previos

Necesita los siguientes requisitos previos:

Este tutorial está diseñado para que pueda realizarlo por sí mismo. Además de este tutorial, recomendamos completar los siguientes tutoriales para obtener un conocimiento más completo sobre cómo diseñar y utilizar las bases de datos de Amazon Redshift:

Información general

Puede agregar datos a las tablas de Amazon Redshift mediante el uso de un comando INSERT o uno COPY. En la escala y la velocidad de un almacenamiento de datos de Amazon Redshift, el comando COPY es muchas veces más rápido y eficiente que los comandos INSERT.

El comando COPY utiliza la arquitectura de procesamiento masivo en paralelo (MPP) de Amazon Redshift para leer y cargar datos en paralelo desde varios orígenes de datos. Puede cargar datos desde archivos de datos en Amazon S3, Amazon EMR o cualquier alojamiento remoto que sea accesible a través de una conexión Secure Shell (SSH). También puede cargarlos directamente desde una tabla de Amazon DynamoDB.

En este tutorial, se utiliza el comando COPY para cargar datos desde Amazon S3. Muchos de los principios presentados aquí también aplican para la carga desde otros orígenes de datos.

Para obtener más información acerca del uso del comando COPY, consulte los siguientes recursos:

Paso 1: creación de un clúster

Puede omitir este paso si ya dispone del clúster que desea utilizar.

Para los ejercicios de este tutorial, use un clúster de cuatro nodos.

Siga los pasos de la Guía de introducción a Amazon Redshift para conectarse al clúster desde un cliente SQL y probar una conexión. No necesita completar los pasos de introducción restantes para crear tablas, cargar datos y probar consultas de ejemplo.

Paso 2: Descargar los archivos de datos

En este paso, se descarga un conjunto de archivos de datos de muestra en su equipo. En el paso siguiente, se cargan los archivos a un bucket de Amazon S3.

Paso 3: Cargar los archivos en un bucket de Amazon S3

En este paso, cree un bucket de Amazon S3 y cargue los archivos de datos en él.

de usuario autentic.

El comando COPY de Amazon Redshift debe tener acceso para leer los objetos de los archivos del bucket de Amazon S3. Si utiliza las mismas credenciales de usuario para crear el bucket de Amazon S3 y ejecutar el comando COPY de Amazon Redshift, el comando COPY tiene todos los permisos necesarios. Si desea utilizar credenciales de usuario diferentes, puede conceder acceso mediante los controles de acceso de Amazon S3. El comando COPY de Amazon Redshift requiere como mínimo los permisos de ListBucket y GetObject para obtener acceso a los objetos de los archivos del bucket de Amazon S3. Para obtener más información acerca del control del acceso a los recursos de Amazon S3, visite Administración de permisos de acceso para los recursos de Amazon S3.

Paso 4: Crear las tablas de muestra

En este tutorial, se utiliza un conjunto de cinco tablas basadas en el esquema Star Schema Benchmark (SSB). En el siguiente diagrama se muestra el modelo de datos en SSB.

Puede que las tablas de SSB ya existan en la base de datos actual. Si es el caso, deberá eliminarlas para quitarlas de la base de datos antes de crearlas con los comandos CREATE TABLE en el siguiente paso. Las tablas utilizadas en este tutorial pueden tener atributos diferentes a las tablas existentes.

Paso 5: Ejecutar los comandos COPY

Ejecute los comandos COPY para cargar cada una de las tablas en el esquema SSB. Los ejemplos del comando COPY muestran cómo cargar desde diferentes formatos de archivo, utilizar varias opciones de comando COPY y solucionar los errores de carga.

Sintaxis del comando COPY

La sintaxis básica del comando COPY es la siguiente.

COPY table_name [ column_list ] FROM data_source CREDENTIALS access_credentials [options] 

Para ejecutar un comando COPY, proporcione los siguientes valores.

Nombre de la tabla

La tabla de destino para el comando COPY. La tabla ya debe existir en la base de datos. La tabla puede ser temporal o persistente. El comando COPY adjunta los nuevos datos de entrada a cualquier fila existente en la tabla.

Lista de columnas

De forma predeterminada, COPY carga los campos desde los datos de origen a las columnas de la tabla en orden. Si lo desea, también puede especificar una lista de columnas, que es una lista de nombres de columnas separadas por una coma, para asignar los campos de datos a columnas específicas. No utiliza listas de columnas en este tutorial. Para obtener más información, consulte Column List en COPY command reference.

Origen de datos

Puede utilizar el comando COPY para cargar datos desde un bucket de Amazon S3, un clúster de Amazon EMR, un alojamiento remoto que utilice una conexión SSH o una tabla de Amazon DynamoDB. Para este tutorial, realice la carga a partir de archivos de datos en un bucket de Amazon S3. Cuando realice cargas desde Amazon S3, debe proporcionar el nombre del bucket y la ubicación de los archivos de datos. Para ello, proporcione o bien una ruta de objecto para los archivos de datos o bien la ubicación de un archivo de manifiesto que enumere explícitamente cada archivo de datos y su ubicación.

Credenciales

Para obtener acceso a los recursos de AWS que contienen los datos que se van a cargar, debe proporcionar las credenciales de acceso de AWS de un usuario con privilegios suficientes. Estas credenciales incluyen el Nombre de recurso de Amazon (ARN) de un rol de IAM. Para cargar datos desde Amazon S3, las credenciales deben incluir los permisos de ListBucket y GetObject. Se requerirán credenciales adicionales si los datos están cifrados. Para obtener más información, consulte Parámetros de autorización en COPY command reference. Para obtener más información acerca de la administración del acceso, visite Administración de permisos de acceso a los recursos de Amazon S3.

Opciones

Puede especificar un número de parámetros con el comando COPY para especificar los formatos de archivo, administrar los formatos de datos, administrar errores y controlar otras características. En este tutorial, utilice las siguientes opciones y características del comando COPY:

Carga de las tablas SSB

Utilice los siguientes comandos COPY para cargar cada una de las tablas en el esquema SSB. El comando de cada tabla muestra diferentes técnicas de solución de problemas y opciones de COPY.

Siga estos pasos para cargar las tablas SSB:

Sustituya el nombre del bucket y las credenciales de AWS.

En este tutorial, los comandos COPY se presentan en el siguiente formato.

copy table from 's3://<your-bucket-name>/load/key_prefix' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>'
options;

Para cada comando COPY, haga lo siguiente:

Carga de tabla PART con NULL AS

En este paso, utilice las opciones CSV y NULL AS para cargar la tabla PART.

El comando COPY puede cargar datos desde varios archivos en paralelo, lo que es mucho más rápido que cargar desde un archivo individual. Para demostrar este principio, los datos de cada tabla en este tutorial se dividen en ocho archivos, aunque los archivos sean muy pequeños. En un paso posterior, se compara la diferencia de tiempo entre cargar desde un archivo individual y cargar desde varios archivos. Para obtener más información, consulte Dividir los datos de carga.

Prefijo de clave

Puede cargar desde varios archivos especificando un prefijo de clave para el conjunto de archivos o enumerando explícitamente los archivos en un archivo de manifiesto. En este paso, se utiliza un prefijo de clave. En un paso posterior, se utiliza un archivo de manifiesto. El prefijo de clave 's3://amzn-s3-demo-bucket/load/part-csv.tbl' carga el siguiente conjunto de los archivos en la carpeta load.

part-csv.tbl-000
part-csv.tbl-001
part-csv.tbl-002
part-csv.tbl-003
part-csv.tbl-004
part-csv.tbl-005
part-csv.tbl-006
part-csv.tbl-007

Formato CSV

CSV, que significa valores separados por comas, es un formato común utilizado para importar y exportar datos de hojas de cálculo. CSV es más flexible que el formato delimitado por comas porque le permite incluir cadenas entre comillas dentro de los campos. El carácter de comilla predeterminado para COPY desde un formato CSV es una comilla recta doble ( " ), pero puede especificar otro con la opción QUOTE AS. Cuando utilice el carácter de comilla dentro del campo, encierre el carácter con otro carácter de comilla.

El siguiente fragmento de una archivo de datos con formato CSV para la tabla PART muestra cadenas encerradas entre comillas dobles ("LARGE ANODIZED BRASS"). También muestra un string comprendido entre dos comillas dobles dentro de un string encomillado ("MEDIUM ""BURNISHED"" TIN").

15,dark sky,MFGR#3,MFGR#47,MFGR#3438,indigo,"LARGE ANODIZED BRASS",45,LG CASE
22,floral beige,MFGR#4,MFGR#44,MFGR#4421,medium,"PROMO, POLISHED BRASS",19,LG DRUM
23,bisque slate,MFGR#4,MFGR#41,MFGR#4137,firebrick,"MEDIUM ""BURNISHED"" TIN",42,JUMBO JAR

Los datos de la tabla PART contienen caracteres que pueden provocar que COPY falle. En este ejercicio, se identifican los errores y se corrigen.

Para cargar datos que estén en formato CSV, agregue csv al comando COPY. Ejecute el siguiente comando para cargar la tabla PART.

copy part from 's3://<your-bucket-name>/load/part-csv.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>'
csv;

Podría obtener un mensaje de error similar al siguiente.

An error occurred when executing the SQL command:
copy part from 's3://amzn-s3-demo-bucket/load/part-csv.tbl' 
credentials' ...

ERROR: Load into table 'part' failed.  Check 'stl_load_errors' system table for details. [SQL State=XX000] 

Execution time: 1.46s

1 statement(s) failed.
1 statement(s) failed.

Para obtener más información acerca del error, consulte la tabla STL_LOAD_ERRORS. La siguiente consulta utiliza la función SUBSTRING para acortar columnas y facilitar su legibilidad y utiliza LIMIT 10 para reducir el número de filas que se devuelven. Puede ajustar los valores en substring(filename,22,25) para permitir la longitud del nombre de bucket.

select query, substring(filename,22,25) as filename,line_number as line, 
substring(colname,0,12) as column, type, position as pos, substring(raw_line,0,30) as line_text,
substring(raw_field_value,0,15) as field_text, 
substring(err_reason,0,45) as reason
from stl_load_errors 
order by query desc
limit 10;

 query  |    filename      | line |  column   |    type    | pos |      
--------+-------------------------+-----------+------------+------------+-----+----
 333765 | part-csv.tbl-000 |    1 |           |            |   0 |

 line_text        | field_text |                    reason
------------------+------------+----------------------------------------------
 15,NUL next,     |            | Missing newline: Unexpected character 0x2c f

NULL AS

Los archivos de datos part-csv.tbl utilizan el carácter de terminación NUL (\x000 o \x0) para indicar los valores NULL.

De manera predeterminada COPY trata el carácter de terminación NUL como un carácter EOR y termina el registro, lo que suele ocasionar resultados inesperados o un error. No hay un método estándar único para indicar NULL en datos de texto. Por lo tanto, la opción de comando NULL AS COPY le permite especificar qué carácter sustituir por NULL cuando carga la tabla. En este ejemplo, desea que COPY trate el carácter de terminación NUL como un valor NULL.

Para cargar PART por medio de la opción NULL AS, ejecute el siguiente comando COPY.

copy part from 's3://<your-bucket-name>/load/part-csv.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
csv
null as '\000';

Para verificar que COPY cargó los valores NULL, ejecute el siguiente comando para seleccionar solo las filas que contienen NULL.

select p_partkey, p_name, p_mfgr, p_category from part where p_mfgr is null;

 p_partkey |  p_name  | p_mfgr | p_category
-----------+----------+--------+------------
        15 | NUL next |        | MFGR#47
        81 | NUL next |        | MFGR#23
       133 | NUL next |        | MFGR#44 
(2 rows)

Carga de tabla SUPPLIER con REGION

En este paso se utilizan las opciones DELIMITER y REGION para cargar la tabla SUPPLIER.

Formato delimitado por caracteres

Los campos en un archivo delimitado por caracteres están separados por un carácter específico, como un carácter de barra vertical ( | ), una coma ( , ) o una pestaña ( \t ). Los archivos delimitados por caracteres pueden utilizar cualquier carácter único ASCII, incluido uno de los caracteres ASCII no imprimibles, como el delimitador. El carácter delimitador se especifica mediante la opción DELIMITER. El delimitador predeterminado es el carácter de barra vertical ( | ).

El siguiente fragmento de los datos para la tabla SUPPLIER utiliza el formato delimitado por la barra vertical.

1|1|257368|465569|41365|19950218|2-HIGH|0|17|2608718|9783671|4|2504369|92072|2|19950331|TRUCK
1|2|257368|201928|8146|19950218|2-HIGH|0|36|6587676|9783671|9|5994785|109794|6|19950416|MAIL

REGION

Siempre que sea posible, debe ubicar los datos de carga en la misma región de AWS que el clúster de Amazon Redshift. Si los datos y el clúster se encuentran en la misma región, se reduce la latencia y se evitan los costos de la transferencia de datos entre regiones. Para obtener más información, consulte Prácticas recomendadas de Amazon Redshift para la carga de datos

Si debe cargar datos de una región de AWS diferente, utilice la opción REGION para especificar la región de AWS en la que se ubican los datos de carga. Si especifica una región, todos los datos de carga, incluidos los archivos de manifiesto, deben encontrarse en la región mencionada. Para obtener más información, consulte REGION.

Si el clúster se encuentra en la región Este de EE. UU. (Norte de Virginia), ejecute el siguiente comando para cargar la tabla SUPPLIER desde los datos delimitados por la barra vertical en un bucket de Amazon S3 ubicado en la región Oeste de EE. UU. (Oregón). En este ejemplo, no cambie el nombre del bucket.

copy supplier from 's3://awssampledbuswest2/ssbgz/supplier.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '|' 
gzip
region 'us-west-2';

Si el clúster no se encuentra en la región Este de EE. UU. (Norte de Virginia), ejecute el siguiente comando para cargar la tabla SUPPLIER desde los datos delimitados por la barra vertical en un bucket de Amazon S3 ubicado en la región Este de EE. UU. (Norte de Virginia). En este ejemplo, no cambie el nombre del bucket.

copy supplier from 's3://awssampledb/ssbgz/supplier.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '|' 
gzip
region 'us-east-1';

Carga de tabla CUSTOMER con MANIFEST

En este paso, se utilizan las opciones FIXEDWIDTH, MAXERROR, ACCEPTINVCHARS Y MANIFEST para cargar la tabla CUSTOMER.

Los datos de muestra para este ejercicio contienen caracteres que provocan errores cuando COPY trata de cargarlos. Utilice la opción MAXERRORS y la tabla de sistema STL_LOAD_ERRORS para solucionar los errores de carga y luego utilice las opciones ACCEPTINVCHARS y MANIFEST para eliminar los errores.

Formato de ancho fijo

El formato de ancho fijo define cada campo como un número fijo de caracteres, en lugar de separar los campos con un delimitador. El siguiente fragmento de los datos para la tabla CUSTOMER utiliza el formato de ancho fijo.

1   Customer#000000001   IVhzIApeRb           MOROCCO  0MOROCCO  AFRICA      25-705 
2   Customer#000000002   XSTf4,NCwDVaWNe6tE   JORDAN   6JORDAN   MIDDLE EAST 23-453
3   Customer#000000003   MG9kdTD              ARGENTINA5ARGENTINAAMERICA     11-783

El orden de los pares etiqueta/ancho debe coincidir con el orden de las columnas de la tabla de forma exacta. Para obtener más información, consulte FIXEDWIDTH.

La cadena de especificaciones de ancho fijo para la tabla CUSTOMER es la siguiente.

fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, 
c_region :12, c_phone:15,c_mktsegment:10'

Para cargar la tabla CUSTOMER desde los datos de ancho fijo, ejecute el siguiente comando.

copy customer
from 's3://<your-bucket-name>/load/customer-fw.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10';

Debería obtener un mensaje de error, similar al siguiente.

An error occurred when executing the SQL command:
copy customer
from 's3://amzn-s3-demo-bucket/load/customer-fw.tbl'
credentials'...

ERROR: Load into table 'customer' failed.  Check 'stl_load_errors' system table for details. [SQL State=XX000] 

Execution time: 2.95s

1 statement(s) failed.

MAXERROR

De forma predeterminada, la primera vez que COPY encuentra un error, el comando falla y devuelve un mensaje de error. Para ahorrar tiempo durante las pruebas, puede utilizar la opción MAXERROR para indicar a COPY que omita un número específico de errores antes de fallar. Dado que se esperan errores la primera vez que se prueba la carga de los datos de la tabla CUSTOMER, agregue maxerror 10 al comando COPY.

Para realizar la prueba utilizando las opciones FIXEDWIDTH y MAXERROR, ejecute el siguiente comando.

copy customer
from 's3://<your-bucket-name>/load/customer-fw.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10'
maxerror 10;

Esta vez, en lugar de un mensaje de error, obtendrá un mensaje de advertencia, similar al siguiente.

Warnings:
Load into table 'customer' completed, 112497 record(s) loaded successfully.
Load into table 'customer' completed, 7 record(s) could not be loaded.  Check 'stl_load_errors' system table for details.

La advertencia indica que COPY encontró siete errores. Para comprobar los errores, consulte la tabla STL_LOAD_ERRORS, como se muestra en el siguiente ejemplo.

select query, substring(filename,22,25) as filename,line_number as line, 
substring(colname,0,12) as column, type, position as pos, substring(raw_line,0,30) as line_text,
substring(raw_field_value,0,15) as field_text, 
substring(err_reason,0,45) as error_reason
from stl_load_errors 
order by query desc, filename 
limit 7;

Los resultados de la consulta a STL_LOAD_ERRORS deben ser similares a los siguientes.

 query  |         filename          | line |  column   |    type    | pos |           line_text           | field_text |              error_reason
--------+---------------------------+------+-----------+------------+-----+-------------------------------+------------+----------------------------------------------
 334489 | customer-fw.tbl.log       |    2 | c_custkey | int4       |  -1 | customer-fw.tbl               | customer-f | Invalid digit, Value 'c', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    6 | c_custkey | int4       |  -1 | Complete                      | Complete   | Invalid digit, Value 'C', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    3 | c_custkey | int4       |  -1 | #Total rows                   | #Total row | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    5 | c_custkey | int4       |  -1 | #Status                       | #Status    | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    1 | c_custkey | int4       |  -1 | #Load file                    | #Load file | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl000        |    1 | c_address | varchar    |  34 | 1         Customer#000000001  | .Mayag.ezR | String contains invalid or unsupported UTF8
 334489 | customer-fw.tbl000        |    1 | c_address | varchar    |  34 | 1         Customer#000000001  | .Mayag.ezR | String contains invalid or unsupported UTF8
(7 rows)

Al examinar los resultados, podrá ver que hay dos mensajes en la columna error_reasons:

Otro problema con la carga que es más difícil de detectar es cuando la carga produjo resultados inesperados. Para investigar este problema, ejecute el siguiente comando para consultar la tabla CUSTOMER.

select c_custkey, c_name, c_address        
from customer
order by c_custkey
limit 10;

 c_custkey |          c_name           |         c_address
-----------+---------------------------+---------------------------
         2 | Customer#000000002        | XSTf4,NCwDVaWNe6tE
         2 | Customer#000000002        | XSTf4,NCwDVaWNe6tE
         3 | Customer#000000003        | MG9kdTD
         3 | Customer#000000003        | MG9kdTD
         4 | Customer#000000004        | XxVSJsL
         4 | Customer#000000004        | XxVSJsL
         5 | Customer#000000005        | KvpyuHCplrB84WgAi
         5 | Customer#000000005        | KvpyuHCplrB84WgAi
         6 | Customer#000000006        | sKZz0CsnMD7mp4Xd0YrBvx
         6 | Customer#000000006        | sKZz0CsnMD7mp4Xd0YrBvx
(10 rows)

Las filas deben ser únicas, pero están duplicadas.

Otra forma de comprobar los resultados inesperados es verificar el número de filas que se cargaron. En nuestro caso, deberían haberse cargado 100 000 filas, pero el mensaje de carga notificó que se cargaron 112 497 registros. Las filas adicionales se cargaron porque COPY cargo un archivo extraño, customer-fw.tbl0000.bak.

En este ejercicio, se utiliza un archivo de manifiesto para evitar la carga de los archivos incorrectos.

ACCEPTINVCHARS

De forma predeterminada, cuando COPY encuentra un carácter que no es compatible con los tipos de datos de la columna, omite la fila y devuelve un error. Para obtener más información acerca de los caracteres UTF-8 no válidos, consulte Errores de carga de caracteres multibyte.

Puede utilizar la opción MAXERRORS para ignorar los errores y continuar la carga, luego consulte STL_LOAD_ERRORS para ubicar los caracteres no válidos y después corrija los archivos de datos. No obstante, MAXERRORS es más adecuado para solucionar problemas de carga y en general no debe utilizarse en un entorno de producción.

La opción ACCEPTINVCHARS suele ser una mejor elección para administrar caracteres no válidos. ACCEPTINVCHARS le indica a COPY reemplazar cada carácter no válido por un carácter válido especificado y continuar con la operación de carga. Puede especificar cualquier carácter ASCII válido, excepto NULL, como el carácter de sustitución. El carácter de sustitución predeterminado es un signo de interrogación (?). COPY reemplaza los caracteres multibyte con una cadena de sustitución de igual longitud. Por ejemplo, un carácter de 4 bytes se reemplazaría con '????'.

COPY devuelve el número de filas que contenían caracteres UTF-8 no válidos. Además agrega una entrada a la tabla de sistema STL_REPLACEMENTS para cada fila afectada, hasta un máximo de 100 filas por sector del nodo. También se sustituyen los caracteres UTF-8 no válidos adicionales, pero esos eventos de sustitución no se registran.

ACCEPTINVCHARS es válido solo para las columnas VARCHAR.

En este paso, se agrega el ACCEPTINVCHARS con el carácter de reemplazo '^'.

MANIFEST

Cuando utiliza el comando COPY desde Amazon S3 mediante un prefijo de clave, existe el riesgo de cargar tablas no deseadas. Por ejemplo, la carpeta 's3://amzn-s3-demo-bucket/load/ contiene ocho archivos de datos que comparten el prefijo de clave customer-fw.tbl, customer-fw.tbl0000, customer-fw.tbl0001 y así sucesivamente. No obstante, la misma carpeta también contiene los archivos extraños customer-fw.tbl.log y customer-fw.tbl-0001.bak.

Para garantizar que carga todos los archivos correctos, y únicamente los archivos correctos, utilice un archivo de manifiesto. El manifiesto es un archivo de texto en formato JSON que muestra explícitamente la clave de objeto única para cada archivo de origen a cargar. Los objetos de archivos pueden encontrarse en diferentes carpetas o diferentes buckets, pero deben estar en la misma región. Para obtener más información, consulte MANIFEST.

A continuación se muestra el texto customer-fw-manifest.

{
  "entries": [
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-000"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-001"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-002"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-003"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-004"},    
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-005"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-006"}, 
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-007"} 
    ]
}

Carga de la tabla DWDATE con DATEFORMAT

En este paso, se utilizan las opciones DELIMITER y DATEFORMAT para cargar la tabla DWDATE .

Cuando se cargan las columnas DATE y TIMESTAMP, COPY espera el formato predeterminado, que es AAAA-MM-DD para fechas y AAAA-MM-DD HH:MM:SS para marcas temporales. Si los datos de carga no utilizan un formato predeterminado, puede utilizar DATEFORMAT y TIMEFORMAT para especificar el formato.

El siguiente fragmento muestra formatos de fecha en la tabla DWDATE. Observe que los formatos de fecha en la columna dos son inconsistentes.

19920104	1992-01-04          Sunday		January	1992	199201	Jan1992	1	4	4	1...
19920112	January 12, 1992	Monday		January	1992	199201	Jan1992	2	12	12	1...
19920120	January 20, 1992	Tuesday	    January	1992	199201	Jan1992	3	20	20	1...

DATEFORMAT

Puede especificar solo un formato de fecha. Si los datos de carga contienen formatos inconsistentes, posiblemente en diferentes columnas, o si en el tiempo de carga no se conoce el formato, utilice DATEFORMAT con el argumento 'auto'. Cuando se especifica 'auto', COPY reconoce cualquier formato válido de fecha u hora y lo convierte al formato predeterminado. La opción 'auto' reconoce varios formatos que no son compatibles cuando se utiliza una cadena de DATEFORMAT y TIMEFORMAT. Para obtener más información, consulte Utilización del reconocimiento automático con DATEFORMAT y TIMEFORMAT.

Para cargar la tabla DWDATE, ejecute el siguiente comando COPY.

copy dwdate from 's3://<your-bucket-name>/load/dwdate-tab.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '\t' 
dateformat 'auto';

Carga de la tabla LINEORDER utilizando varios archivos

En este paso, se utilizan las opciones GZIP y COMPUPDATE para cargar la tabla LINEORDER.

En este ejercicio, se carga la tabla LINEORDER desde un solo archivo de datos y luego se vuelve a cargar desde varios archivos. Hacer esto le permite comparar los tiempos de carga de ambos métodos.

GZIP, LZOP y BZIP2

Puede comprimir los archivos utilizando los formatos de compresión gzip, lzop o bzip2. Cuando se carga desde archivos comprimidos, COPY descomprime los archivos durante el proceso de carga. La compresión de los archivos ahorra espacio de almacenamiento y reduce los tiempos de carga.

COMPUPDATE

Cuando COPY carga una tabla vacía sin codificaciones de compresión, analiza los datos de carga para determinar las codificaciones óptimas. Luego modifica la tabla para utilizar esas codificaciones antes de comenzar la carga. Este proceso de análisis toma tiempo, pero ocurre una sola vez por tabla, como máximo. Para ahorrar tiempo, puede omitir este paso desactivando COMPUPDATE. Para habilitar una evaluación precisa de los tiempos de COPY, se desactiva COMPUPDATE en este paso.

Múltiples archivos

El comando COPY puede cargar datos desde varios archivos en paralelo de manera muy eficiente, en lugar de desde un archivo individual. Puede dividir los datos en archivos de manera tal que la cantidad de archivos sea múltiplo de la cantidad de sectores en su clúster. Si lo hace, Amazon Redshift divide la carga de trabajo y distribuye los datos de manera uniforme entre los sectores. El número de sectores por nodo depende del tamaño de nodo del clúster. Para obtener más información acerca de la cantidad de sectores que tiene cada tamaño de nodo, consulte Acerca de clústeres y nodos en la Guía de administración de Amazon Redshift.

Por ejemplo, los nodos de computación dc2.large que se utilizan en este tutorial tienen dos sectores cada uno, por lo que el clúster de cuatro nodos tendrá ocho sectores. En los pasos anteriores, los datos de carga estaban contenidos en ocho archivos, aunque los archivos sean muy pequeños. En este paso, se compara la diferencia de tiempo entre cargar desde un solo archivo grande y cargar desde varios archivos.

Los archivos que se utilizan para este tutorial contienen alrededor de 15 millones de registros y ocupan cerca de 1,2 GB. Estos archivos son muy pequeños en la escala de Amazon Redshift, pero suficientes para demostrar la ventaja en el rendimiento de la carga desde varios archivos. Los archivos son tan grandes que el tiempo necesario para descargarlos y cargarlos después en Amazon S3 es excesivo para este tutorial. Por lo tanto, se cargan los archivos directamente desde un bucket de muestra de AWS.

En la siguiente captura de pantalla se muestran los archivos de datos para LINEORDER.

Paso 6: Limpiar y analizar la base de datos

Siempre que agregue, elimine o modifique una cantidad importante de filas, debe ejecutar un comando VACUUM y luego un comando ANALYZE. Una limpieza recupera el espacio de las filas eliminadas y restaura el orden. El comando ANALYZE actualiza los metadatos estadísticos, lo que permite al optimizador de consultas generar planes de consultas más precisos. Para obtener más información, consulte Limpieza de tablas.

Si carga los datos en orden de clave de ordenación, la limpieza es rápida. En este tutorial, agregó un número importante de filas, pero lo hizo en tablas vacías. Siendo ese el caso, no hay necesidad de reordenar y no borró ninguna fila. COPY actualiza automáticamente las estadísticas después de cargar una tabla vacía, por lo que las estadísticas deben estar actualizadas. No obstante, por una cuestión de buena organización, se completa este tutorial con la limpieza y el análisis de la base de datos.

Para limpiar y analizar la base de datos, ejecute los siguientes comandos.

vacuum;
analyze;

Paso 7: Eliminar los recursos

El clúster seguirá acumulando cargos mientras esté en ejecución. Una vez que haya completado este tutorial, debe restablecer el entorno al estado anterior si sigue los pasos que se indican en Paso 5: Revocar el acceso y eliminar el clúster de muestra en la Guía de introducción a Amazon Redshift.

Si desea mantener el clúster y recuperar el almacenamiento utilizado por las tablas de SSB, ejecute los siguientes comandos.

drop table part;
drop table supplier;
drop table customer;
drop table dwdate;
drop table lineorder;

Next

Resumen

Resumen

En este tutorial, ha cargado archivos de datos en Amazon S3 y ha utilizado los comandos COPY para cargar los datos de los archivos en tablas de Amazon Redshift.

Cargó datos utilizando los siguientes formatos:

Utilizó la tabla del sistema STL_LOAD_ERRORS para solucionar errores de carga y luego utilizó las opciones REGION, MANIFEST, MAXERROR, ACCEPTINVCHARS, DATEFORMAT y NULL AS para resolver los errores.

Aplicó las siguientes prácticas recomendadas para cargar datos:

Para obtener más información acerca de las prácticas recomendadas de Amazon Redshift, consulte los siguientes enlaces: