如何排查在 Amazon Redshift 中使用 COPY 命令时出现的数据加载错误？

简短描述

使用 STL_LOAD_ERRORS 表来识别平面文件加载期间发生的数据加载错误。STL_LOAD_ERRORS 表可以帮助您跟踪数据加载进度并记录任何失败或错误。解决问题后，使用 COPY 命令重新加载平面文件中的数据。

**注意：**如果您使用 COPY 命令加载 Parquet 格式的平面文件，则还可以使用 SVL_S3LOG 表来识别错误。

解决方法

**注意：**以下步骤使用城市和场馆的示例数据集。

要使用 STL_LOAD_ERRORS 表来识别数据加载错误，请完成以下步骤：

1. 检查示例平面文件中的数据并确认源数据有效：

   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

   在前面的 demo.txt 示例文件中，使用了竖线字符分隔其中的五个字段。有关更多信息，请参见从以竖线（默认分隔符）分隔的文件中加载 LISTING。

2. 打开 Amazon Redshift 控制台。

3. 使用以下数据定义语言（DDL）创建一个示例表：

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

4. 要确定数据加载错误的原因，请创建一个视图来预览 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. 要加载数据，请运行 COPY 命令：

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

   **注意：**将 your_S3_bucket 替换为您的 S3 存储桶的名称，并将 arn:aws:iam::123456789012:role/redshiftcopyfroms3 替换为您的 AWS Identity and Access Management（IAM）角色的 ARN。IAM 角色必须有权访问您的 S3 存储桶中的数据。有关详细信息，请参阅参数。

6. 要显示和查看表中的错误加载详情，请查询加载视图：

   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

   在前面的示例中，异常是由长度值引起的，必须将其添加到 venuestate 列中。(NC ,25 |) 值长于 VENUESTATE CHAR(2) DDL 中定义的长度。
   若要解决此问题，请完成以下任务之一：
   如果预计数据将超过该列的定义长度，则更新表定义以修改该列的长度。
   -或-
   如果数据格式不正确或转换不正确，则修改文件中的数据以使用正确的值。
   查询的输出包括以下信息：
   导致错误的文件
   导致错误的列
   输入文件中的行号
   异常的原因

7. 修改加载文件中的数据以使用正确的值：

   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

   **注意：**长度必须小于等于定义的列长度。

8. 重新加载数据：

   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.

   **注意：**STL_LOAD_ERRORS 表只能保存有限数量的日志，并且可以保存大约 4 到 5 天的日志。标准用户在查询 STL_LOAD_ERRORS 表时只能查看自己的数据。要查看所有表数据，您必须是超级用户。

相关信息

设计表的 Amazon Redshift 最佳实践

Amazon Redshift 加载数据的最佳实践

用于解决数据加载问题的系统表

采用 Amazon Redshift Advisor 的建议