¿Cómo soluciono los errores de carga de datos cuando uso el comando COPY en Amazon Redshift?

Descripción corta

Utiliza la tabla STL_LOAD_ERRORS para identificar los errores de carga de datos que se producen durante la carga de un archivo plano. La tabla STL_LOAD_ERRORS puede ayudarte a realizar un seguimiento del progreso de una carga de datos y registrar cualquier fallo o error. Tras solucionar el problema, utiliza el comando COPY para volver a cargar los datos del archivo plano.

Nota: Si utilizas el comando COPY para cargar un archivo plano en formato Parquet, también puedes utilizar la tabla SVL_S3LOG para identificar errores.

Solución

Nota: En los pasos siguientes se utiliza un conjunto de datos de ejemplo de ciudades y lugares.

Para usar la tabla STL_LOAD_ERRORS para identificar los errores de carga de datos, sigue estos pasos:

1. Comprueba los datos de tu archivo plano de ejemplo y confirma que los datos de origen son válidos:

   7|BMO Field|Toronto|ON|016|TD Garden|Boston|MA|0  
   23|The Palace of Auburn Hills|Auburn Hills|MI|0  
   28|American Airlines Arena|Miami|FL|0  
   37|Staples Center|Los Angeles|CA|0  
   42|FedExForum|Memphis|TN|0  
   52|PNC Arena|Raleigh|NC  ,25   |0  
   59|Scotiabank Saddledome|Calgary|AB|0  
   66|SAP Center|San Jose|CA|0  
   73|Heinz Field|Pittsburgh|PA|65050

   En el archivo demo.txt del ejemplo anterior, un carácter de barra vertical separa los cinco campos que se utilizan. Para obtener más información, consulta Carga de LISTING de un archivo delimitado por la barra vertical (delimitador predeterminado).

2. Abre la consola de Amazon Redshift.

3. Utiliza el siguiente lenguaje de definición de datos (DDL) para crear una tabla de ejemplo:

   CREATE TABLE VENUE1(VENUEID SMALLINT,  
   VENUENAME VARCHAR(100),  
   VENUECITY VARCHAR(30),  
   VENUESTATE CHAR(2),  
   VENUESEATS INTEGER  
   ) DISTSTYLE EVEN;

4. Para identificar la causa del error de carga de datos, crea una vista para obtener una vista previa de las columnas pertinentes de la tabla STL_LOAD_ERRORS:

   create view loadview as(select distinct tbl, trim(name) as table_name, query, starttime,  
   trim(filename) as input, line_number, colname, err_code,  
   trim(err_reason) as reason  
   from stl_load_errors sl, stv_tbl_perm sp  
   where sl.tbl = sp.id);

5. Para cargar los datos, ejecuta el comando COPY:

   copy Demofrom 's3://your_S3_bucket/venue/'  
   iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3'  
   delimiter '|' ;

   Nota: Sustituye your_S3_bucket por el nombre de tu bucket de S3 y arn:aws:iam::123456789012:role/redshiftcopyfroms3 por el ARN de tu rol de AWS Identity and Access Management (IAM). El rol de IAM debe tener permisos para acceder a los datos de tu bucket de S3. Para obtener más información, consulta Parámetros.

6. Para mostrar y revisar los detalles de errores de carga de la tabla, consulta la vista de carga:

   testdb=# select * from loadview where table_name='venue1';tbl | 265190  
   table_name | venue1  
   query | 5790  
   starttime | 2017-07-03 11:54:22.864584  
   input | s3://  
   your_S3_bucket/venue/venue_pipe0000_part_00  
   line_number | 7  
   colname | venuestate  
   err_code | 1204  
   reason | Char length exceeds DDL length

   En el ejemplo anterior, la excepción se debe al valor de longitud y se debe agregar a la columna venuestate. El valor (NC ,25 |) es mayor que la longitud definida en el DDL VENUESTATE CHAR(2).
   Para resolver este problema, realiza una de las siguientes tareas:
   Si se prevé que los datos superen la longitud definida de la columna, actualiza la definición de la tabla para modificar la longitud de la columna.
   Alternativa:
   Si los datos no están formateados o transformados correctamente, modifica los datos del archivo para usar el valor correcto.
   El resultado de la consulta incluye la siguiente información:
   El archivo que causa el error
   La columna que causa el error
   El número de línea del archivo de entrada
   El motivo de la excepción

7. Modifica los datos del archivo de carga para usar los valores correctos:

   7|BMO Field|Toronto|ON|016|TD Garden|Boston|MA|0  
   23|The Palace of Auburn Hills|Auburn Hills|MI|0  
   28|American Airlines Arena|Miami|FL|0  
   37|Staples Center|Los Angeles|CA|0  
   42|FedExForum|Memphis|TN|0  
   52|PNC Arena|Raleigh|NC|0  
   59|Scotiabank Saddledome|Calgary|AB|0  
   66|SAP Center|San Jose|CA|0  
   73|Heinz Field|Pittsburgh|PA|65050

   Nota: La longitud debe alinearse con la longitud de columna definida.

8. Vuelve a cargar la carga de datos:

   testdb=# copy Demofrom 's3://your_S3_bucket/sales/'  
   iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3' delimiter '|' ;  
   INFO:  Load into table 'venue1' completed, 808 record(s) loaded successfully.

   Nota: La tabla STL_LOAD_ERRORS solo puede contener un número limitado de registros y durante aproximadamente 4 a 5 días. Los usuarios estándar solo pueden ver sus propios datos cuando consultan la tabla STL_LOAD_ERRORS. Para ver todos los datos de la tabla, debes ser un superusuario.

Información relacionada

Prácticas recomendadas de Amazon Redshift para el diseño de tablas

Prácticas recomendadas de Amazon Redshift para la carga de datos

Tablas de sistema para la solución de problemas de cargas de datos

Uso de las recomendaciones de Amazon Redshift Advisor