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

Breve descripción

Utilice 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 ayudarle a realizar un seguimiento del progreso de una carga de datos y registrar cualquier fallo o error. Tras solucionar el problema, utilice el comando COPY para volver a cargar los datos del archivo plano.

Nota: Si utiliza el comando COPY para cargar un archivo plano en formato Parquet, también puede 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, siga estos pasos:

1. Compruebe los datos de su archivo plano de ejemplo y confirme 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, consulte Carga de LISTING de un archivo delimitado por la barra vertical (delimitador predeterminado).

2. Abra la consola de Amazon Redshift.

3. Utilice 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, cree 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, ejecute el comando COPY:

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

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

6. Para mostrar y revisar los detalles de errores de carga de la tabla, consulte 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 venustate. El valor (NC ,25 |) es mayor que la longitud definida en el DDL VENUESTATE CHAR(2).
   Para resolver este problema, realice una de las siguientes tareas:
   Si se prevé que los datos superen la longitud definida de la columna, actualice la definición de la tabla para modificar la longitud de la columna.
   Alternativa:
   Si los datos no están formateados o transformados correctamente, modifique 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. Modifique 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. Vuelva 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, debe 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