COPY コマンドを使用してフラットファイルをロードしようとしましたが、Amazon Redshift でデータロードの問題やエラーが発生します。
簡単な説明
STL_LOAD_ERRORS テーブルを使用して、フラットファイルのロード中に発生するデータロードエラーを特定します。STL_LOAD_ERRORS テーブルは、データロードの進行状況を追跡し、失敗やエラーを記録するのに役立ちます。問題のトラブルシューティングが完了したら、COPY コマンドを使用してフラットファイルのデータを再ロードします。
**注:**COPY コマンドを使用して Parquet 形式のフラットファイルをロードする場合は、SVL_S3LOG テーブルを使用してエラーを識別することもできます。
解決策
注: 次の手順では、都市と会場のサンプルデータセットを使用します。
STL_LOAD_ERRORS テーブルを使用してデータロードエラーを特定するには、次の手順を実行します。
-
サンプルフラットファイルのデータをチェックして、ソースデータが有効であることを確認します。
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 をロードする」を参照してください。
-
Amazon Redshift コンソールを開きます。
-
次のデータ定義言語 (DDL) を使用してサンプルテーブルを作成します。
CREATE TABLE VENUE1(VENUEID SMALLINT,
VENUENAME VARCHAR(100),
VENUECITY VARCHAR(30),
VENUESTATE CHAR(2),
VENUESEATS INTEGER
) DISTSTYLE EVEN;
-
データロードエラーの原因を特定するには、ビューを作成して 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);
-
データをロードするには、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 バケットからデータにアクセスする権限が必要です。詳細については、「パラメータ」を参照してください。
-
テーブルのエラーロードの詳細を表示して確認するには、ロードビューでクエリを実行します。
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|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
注: 長さは、定義された列の長さと一致する必要があります。
-
データロードをリロードします。
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 からのレコメンデーションの使用