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 ファイルの例では、使用される 5 つのフィールドがパイプ文字で区切られています。詳細については、「パイプ区切りファイル (デフォルトの区切り記号）から 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 からのレコメンデーションの使用