When data is loaded from a file into an Amazon Redshift table, sometimes failures occur. However, data load errors are logged in the STL_LOAD_ERRORS table, which you can then use to identify and resolve the errors.
To troubleshoot and resolve a data load error, review the STL_LOAD_ERRORS. The following example uses an example dataset comprising both cities and venues:
1. Create a raw file named venue_pipe0000_part_00 in YOUR S3 BUCKET with the following content. For more information, see Load LISTING from a Pipe-Delimited File (Default Delimiter).
7|BMO Field|Toronto|ON|0 16|TD Banknorth 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|RBC Center|Raleigh|NC ,25 |0 59|Pengrowth Saddledome|Calgary|AB|0 66|HP Pavilion at San Jose|San Jose|CA|0 73|Heinz Field|Pittsburgh|PA|65050
2. Create the table master_schema.venue1 that is similar to the following example:
CREATE TABLE VENUE1( VENUEID SMALLINT, VENUENAME VARCHAR(100), VENUECITY VARCHAR(30), VENUESTATE CHAR(2), VENUESEATS INTEGER )
3. Create a load view that is used to diagnose the load failure:
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);
4. Load data using the Copy command:
master=# copy venue1 master-# from 's3://YOUR_S3_BUCKET/venue/' master-# iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3' delimiter '|' ;
5. Query the load view for the load details about the specific table:
master=# 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
The output from the previous step includes the following important information:
- The file causing the error.
- The column causing the error.
- The line number in the input file.
- The reason for the exception.
Note: In this example, the exception is caused by the length value, which must be added to the venuestate column. This value (NC ,25 |) is longer than the length defined in the ddl VENUESTATE CHAR(2).
To resolve this, modify the file and include the correct value. See the following example of the modified file:
7|BMO Field|Toronto|ON|0 16|TD Banknorth 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|RBC Center|Raleigh|NC|0 59|Pengrowth Saddledome|Calgary|AB|0 66|HP Pavilion at San Jose|San Jose|CA|0 73|Heinz Field|Pittsburgh|PA|65050
6. Reload the data load:
master=# copy venue1 from '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.
Did this page help you? Yes | No
Back to the AWS Support Knowledge Center
Need help? Visit the AWS Support Center
Published: 2018-10-30