如何在 Amazon Redshift 中使用 COPY 命令時,對資料載入錯誤進行疑難排解?

2 分的閱讀內容
0

我嘗試使用 COPY 命令載入一般檔案。但是,我在 Amazon Redshift 中遇到資料載入問題或錯誤。

簡短描述

使用 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 的建議

AWS 官方
AWS 官方已更新 1 年前