教學課程：從 Amazon S3 載入資料 - Amazon Redshift
必要條件概觀步驟 1：建立叢集步驟 2：下載資料檔案步驟 3：上傳檔案至 Amazon S3 儲存貯體步驟 4：建立範例資料表步驟 5：執行COPY命令步驟 6：清空及分析資料庫步驟 7：清理您的資源Summary

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

教學課程：從 Amazon S3 載入資料

本教學將從頭到尾引導您完成從 Amazon S3 儲存貯體中的資料檔案將資料載入 Amazon Redshift 資料庫資料表的過程。

在此教學課程中，您將執行下列操作：

  • 下載使用逗號分隔值 （CSV）、字元分隔和固定寬度格式的資料檔案。

  • 建立 Amazon S3 儲存貯體，然後上傳資料檔案至該儲存貯體。

  • 啟動 Amazon Redshift 叢集並建立資料庫資料表。

  • 使用 COPY 命令從 Amazon S3 上的資料檔案載入資料表。

  • 對載入錯誤進行故障診斷，並修改COPY命令以修正錯誤。

預估時間：60 分鐘

估計費用：叢集每小時 $1.00

必要條件

您需要以下的事前準備：

  • 用來啟動 Amazon Redshift 叢集並在 Amazon S3 中建立儲存貯體 AWS 的帳戶。

  • 您的 AWS 登入資料 （IAM 角色） 可從 Amazon S3 載入測試資料。如果您需要新的IAM角色，請前往建立IAM角色 。

  • SQL 用戶端，例如 Amazon Redshift 主控台查詢編輯器。

本教學課程設計為可獨立進行。除了本教學課程之外，我們也建議您完成下列教學課程，以便更全面地了解如何設計和使用 Amazon Redshift 資料庫：

概觀

您可以使用 INSERT命令或使用 COPY命令將資料新增至 Amazon Redshift 資料表。在 Amazon Redshift 資料倉儲的規模和速度下，該COPY命令比INSERT命令快上許多倍且更有效率。

此COPY命令使用 Amazon Redshift 大規模平行處理 （MPP） 架構，從多個資料來源平行讀取和載入資料。您可以從 Amazon S3、Amazon EMR或可透過 Secure Shell （SSH） 連線存取的任何遠端主機上的資料檔案載入。或者，您可以直接從 Amazon DynamoDB 資料表載入。

在本教學課程中，您可以使用 COPY命令從 Amazon S3 載入資料。在此運用的許多原則也適合用於從其他資料來源載入。

若要進一步了解如何使用 COPY命令，請參閱下列資源：

步驟 1：建立叢集

如果您已有想要使用的叢集，則可略過此步驟。

針對本教學中的練習，請使用四個節點的叢集。

建立叢集

  1. 登入 AWS Management Console 並在 開啟 Amazon Redshift 主控台https://console.aws.amazon.com/redshiftv2/。

    在導覽功能表上，選擇已佈建的叢集儀表板。

    重要

    請確定您具備執行叢集操作的必要許可。如需授予必要許可的資訊，請參閱授權 Amazon Redshift 存取 AWS 服務。

  2. 在右上角，選擇要 AWS 建立叢集的區域。基於本教學的用途，請選擇美國西部 (奧勒岡)。

  3. 在導覽選單上，選擇叢集，然後選擇建立叢集。建立叢集頁面隨即出現。

  4. 在建立叢集頁面上輸入叢集的參數。為參數選擇您自己的值，但變更下列值時除外：

    • 選擇 dc2.large 作為節點類型。

    • 選擇 4 作為節點數目。

    • 在叢集許可區段中，從可用IAM角色 中選擇角色。 IAM 此角色應為您先前建立的角色，且具備 Amazon S3 的存取權限。然後選擇關聯IAM角色，將其新增至叢集的關聯IAM角色清單。

  5. 選擇建立叢集。

請遵循 Amazon Redshift 入門指南步驟，從SQL用戶端連線至叢集並測試連線。您不需要完成「入門」其餘的建立資料表、上傳資料、嘗試範例查詢步驟。

步驟 2：下載資料檔案

在此步驟，您會下載一組範例資料檔案到您的電腦。在下一個步驟，您會將檔案上傳到 Amazon S3 儲存貯體。

下載資料檔案

  1. 下載壓縮檔案：LoadingDataSampleFiles.zip。

  2. 將檔案解壓縮至您電腦中的資料夾。

  3. 確認資料夾中包含下列檔案。

    customer-fw-manifest
customer-fw.tbl-000
customer-fw.tbl-000.bak
customer-fw.tbl-001
customer-fw.tbl-002
customer-fw.tbl-003
customer-fw.tbl-004
customer-fw.tbl-005
customer-fw.tbl-006
customer-fw.tbl-007
customer-fw.tbl.log
dwdate-tab.tbl-000
dwdate-tab.tbl-001
dwdate-tab.tbl-002
dwdate-tab.tbl-003
dwdate-tab.tbl-004
dwdate-tab.tbl-005
dwdate-tab.tbl-006
dwdate-tab.tbl-007
part-csv.tbl-000
part-csv.tbl-001
part-csv.tbl-002
part-csv.tbl-003
part-csv.tbl-004
part-csv.tbl-005
part-csv.tbl-006
part-csv.tbl-007

步驟 3：上傳檔案至 Amazon S3 儲存貯體

在此步驟，您要建立 Amazon S3 儲存貯體，並將資料檔案上傳至該儲存貯體。

將檔案上傳至 Amazon S3 儲存貯體

  1. 在 Amazon S3 中建立儲存貯體。

    如需有關建立儲存貯體的詳細資訊，請參閱《Amazon Simple Storage Service 使用者指南》中的建立儲存貯體。

    1. 登入 AWS Management Console 並在 開啟 Amazon S3 主控台https://console.aws.amazon.com/s3/。

    2. 選擇建立儲存貯體。

    3. 選擇 AWS 區域。

      在和叢集相同的區域中建立儲存貯體。如果叢集位於美國西部 (奧勒岡) 區域，請選擇美國西部 (奧勒岡) 區域 (us-west-2)。

    4. 在建立儲存貯體對話方塊的儲存貯體名稱中，輸入儲存貯體名稱。

      在 Amazon S3 現有的所有儲存貯體名稱中，您選擇的儲存貯體名稱必須是唯一的。其中一種協助確保唯一性的方法是在儲存貯體名稱的前面加上組織名稱。儲存貯體名稱必須符合一些規則。如需詳細資訊，請參閱 Amazon Simple Storage Service 使用者指南中的儲存貯體限制與局限。

    5. 為其餘選項選擇建議的預設值。

    6. 選擇建立儲存貯體。

      當 Amazon S3 成功建立您的儲存貯體，主控台會在儲存貯體面板中顯示您的空儲存貯體。

  2. 建立資料夾。

    1. 選擇新儲存貯體的名稱。

    2. 選擇建立資料夾按鈕。

    3. 將新資料夾命名為 load。

      注意

      您建立的儲存貯體不在沙盒中。在此練習中，您會將物件新增至真正的儲存貯體。您需要為您在儲存貯體中儲存物件的期間支付一筆名目費用。如需 Amazon S3 定價的相關資訊，請移至 Amazon S3 定價頁面。

  3. 將資料檔案上傳至新的 Amazon S3 儲存貯體。

    1. 選擇資料夾的名稱。

    2. 在上傳精靈中，選擇新增檔案。

      遵循 Amazon S3 主控台的指示，上傳您下載並擷取的所有檔案。

    3. 選擇上傳。

使用者登入資料

Amazon Redshift COPY命令必須具有讀取 Amazon S3 儲存貯體中檔案物件的存取權。如果您使用相同的使用者憑證來建立 Amazon S3 儲存貯體並執行 Amazon Redshift COPY命令，則該COPY命令具有所有必要的許可。如果您要使用不同的使用者登入資料，可以使用 Amazon S3 存取控制來授予存取權。Amazon Redshift COPY命令至少需要 ListBucket 和 GetObject 許可才能存取 Amazon S3 儲存貯體中的檔案物件。如需對 Amazon S3 資源的存取控制相關資訊，請參閱管理對 Amazon S3 資源的存取許可。

步驟 4：建立範例資料表

在本教學課程中，您可以使用一組以 Star Schema Benchmark （SSB） 結構描述為基礎的五個資料表。下圖顯示SSB資料模型。

SSB 結構描述的五個資料表及其彼此之間的關係。

SSB 資料表可能已存在於目前的資料庫中。如果是這樣，請在使用下一個步驟中的CREATETABLE命令建立資料表之前，先將資料表從資料庫移除。本教學課程中使用的資料表可能會有和現有資料表不同的屬性。

建立範例資料表

  1. 若要捨棄SSB資料表，請在SQL用戶端中執行下列命令。

    drop table part cascade;
drop table supplier;
drop table customer;
drop table dwdate;
drop table lineorder;

  2. 在SQL用戶端中執行下列CREATETABLE命令。

    CREATE TABLE part 
(
  p_partkey     INTEGER NOT NULL,
  p_name        VARCHAR(22) NOT NULL,
  p_mfgr        VARCHAR(6),
  p_category    VARCHAR(7) NOT NULL,
  p_brand1      VARCHAR(9) NOT NULL,
  p_color       VARCHAR(11) NOT NULL,
  p_type        VARCHAR(25) NOT NULL,
  p_size        INTEGER NOT NULL,
  p_container   VARCHAR(10) NOT NULL
);

CREATE TABLE supplier 
(
  s_suppkey   INTEGER NOT NULL,
  s_name      VARCHAR(25) NOT NULL,
  s_address   VARCHAR(25) NOT NULL,
  s_city      VARCHAR(10) NOT NULL,
  s_nation    VARCHAR(15) NOT NULL,
  s_region    VARCHAR(12) NOT NULL,
  s_phone     VARCHAR(15) NOT NULL
);

CREATE TABLE customer 
(
  c_custkey      INTEGER NOT NULL,
  c_name         VARCHAR(25) NOT NULL,
  c_address      VARCHAR(25) NOT NULL,
  c_city         VARCHAR(10) NOT NULL,
  c_nation       VARCHAR(15) NOT NULL,
  c_region       VARCHAR(12) NOT NULL,
  c_phone        VARCHAR(15) NOT NULL,
  c_mktsegment   VARCHAR(10) NOT NULL
);

CREATE TABLE dwdate 
(
  d_datekey            INTEGER NOT NULL,
  d_date               VARCHAR(19) NOT NULL,
  d_dayofweek          VARCHAR(10) NOT NULL,
  d_month              VARCHAR(10) NOT NULL,
  d_year               INTEGER NOT NULL,
  d_yearmonthnum       INTEGER NOT NULL,
  d_yearmonth          VARCHAR(8) NOT NULL,
  d_daynuminweek       INTEGER NOT NULL,
  d_daynuminmonth      INTEGER NOT NULL,
  d_daynuminyear       INTEGER NOT NULL,
  d_monthnuminyear     INTEGER NOT NULL,
  d_weeknuminyear      INTEGER NOT NULL,
  d_sellingseason      VARCHAR(13) NOT NULL,
  d_lastdayinweekfl    VARCHAR(1) NOT NULL,
  d_lastdayinmonthfl   VARCHAR(1) NOT NULL,
  d_holidayfl          VARCHAR(1) NOT NULL,
  d_weekdayfl          VARCHAR(1) NOT NULL
);
CREATE TABLE lineorder 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
);

步驟 5：執行COPY命令

您可以執行COPY命令來載入SSB結構描述中的每個資料表。COPY 命令範例示範使用數個COPY命令選項從不同檔案格式載入，以及疑難排解載入錯誤。

COPY 命令語法

COPY 命令基本語法如下。

COPY table_name [ column_list ] FROM data_source CREDENTIALS access_credentials [options]

若要執行COPY命令，請提供下列值。

資料表名稱

COPY 命令的目標資料表。此資料表必須已存在於資料庫中。此資料表可以是暫時性或持久性。COPY 命令會將新的輸入資料附加到資料表中的任何現有資料列。

資料欄清單

根據預設， 會依序將來源資料中的欄位COPY載入資料表資料欄。您可以選擇指定資料欄清單 (以逗號分隔資料欄名稱的清單)，以便將資料欄位映射到特定的資料欄。您在本教學中不會使用資料行清單。如需詳細資訊，請參閱 COPY 命令參考Column List中的 。

資料來源

您可以使用 COPY命令從 Amazon S3 儲存貯體、Amazon EMR叢集、使用 SSH 連線的遠端主機或 Amazon DynamoDB 資料表載入資料。在本教學中，您會從 Amazon S3 儲存貯體中的資料檔案載入資料。從 Amazon S3 進行載入時，您必須提供儲存貯體的名稱和資料檔案的位置。若要執行此作業，請提供資料檔案的物件路徑，或是明確列出每個資料檔案及其位置的資訊清單檔案位置。

  • 索引鍵字首

    使用物件索引鍵可以唯一識別儲存在 Amazon S3 中的物件。物件索引鍵包含儲存貯體名稱、資料夾名稱、物件名稱 (如果有)。物件索引鍵字首是指具有相同字首的多個物件。物件路徑是 COPY命令用來載入共用金鑰字首的所有物件的金鑰字首。例如，索引鍵字首 custdata.txt 可以是指單一檔案或數個檔案，包括 custdata.txt.001、custdata.txt.002 等。

  • 清單檔案

    在某些案例中，您可能需要載入具有不同字首的檔案，例如從多個儲存貯體或資料夾進行載入。在其他案例中，您可能需要排除具有特定字首的檔案。在這些案例中，您可以使用資訊清單檔案。資訊清單檔案會明確列出每個載入檔案及其唯一物件索引鍵。您可以在本教學課程稍後使用資訊清單檔案載入PART資料表。

登入資料

若要存取包含要載入資料 AWS 的資源，您必須為具有足夠權限的使用者提供 AWS 存取憑證。這些憑證包含IAM角色 Amazon Resource Name （ARN）。若要從 Amazon S3 載入資料，憑證必須包含 ListBucket 和 GetObject 許可。如果您的資料已加密，則需要其他登入資料。如需詳細資訊，請參閱 COPY 命令參考授權參數中的 。如需管理存取權的相關資訊，請前往管理對 Amazon S3 資源的存取許可。

選項

您可以使用 COPY命令指定許多參數，以指定檔案格式、管理資料格式、管理錯誤，以及控制其他功能。在本教學課程中，您可以使用下列COPY命令選項和功能：

載入SSB資料表

您可以使用下列COPY命令來載入SSB結構描述中的每個資料表。每個資料表的 命令會示範不同的COPY選項和疑難排解技術。

若要載入SSB資料表，請遵循下列步驟：

  1. 取代儲存貯體名稱和 AWS 憑證

  2. 使用 NULL AS 載入PART資料表

  3. 使用 載入SUPPLIER資料表 REGION

  4. 使用 載入CUSTOMER資料表 MANIFEST

  5. 使用 載入DWDATE資料表 DATEFORMAT

  6. 使用多個檔案載入LINEORDER資料表

取代儲存貯體名稱和 AWS 憑證

本教學課程中的COPY命令會以下列格式顯示。

copy table from 's3://<your-bucket-name>/load/key_prefix' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>'
options;

針對每個COPY命令，執行下列動作：

  1. Replace (取代) <your-bucket-name> 與叢集位於相同區域中的儲存貯體名稱。

    本步驟假設儲存貯體與叢集位於同一區域。或者，您可以使用 REGION選項搭配 COPY命令來指定區域。

  2. Replace (取代) <aws-account-id> 以及 <role-name> 您自己的 AWS 帳戶 和 IAM 角色。以單引號括住的登入資料字串區段不可包含任何空格或分行符號。請注意， 的格式ARN可能與範例略有不同。當您執行COPY命令時，最好從IAM主控台複製角色ARN的 ，以確保其準確。

使用 NULL AS 載入PART資料表

在此步驟中，您可以使用 CSV和 NULL AS 選項載入PART資料表。

COPY 命令可以平行載入多個檔案的資料，這比從單一檔案載入更快。為了示範這個原則，本教學課程將每個資料表中的資料分成八個檔案，即使檔案很小。在稍後的步驟中，您會比較從單一檔案與從多個檔案載入之間的時間差異。如需詳細資訊，請參閱載入資料檔案。

索引鍵字首

您可以透過為數個檔案指定索引鍵字首，或在資訊清單檔案中明確列出檔案，來從多個檔案載入資料。在此步驟中，您會使用金鑰前綴。在稍後的步驟中，您會使用資訊清單檔案。's3://amzn-s3-demo-bucket/load/part-csv.tbl' 索引鍵字首會載入 load 資料夾中的下列這些檔案。

part-csv.tbl-000
part-csv.tbl-001
part-csv.tbl-002
part-csv.tbl-003
part-csv.tbl-004
part-csv.tbl-005
part-csv.tbl-006
part-csv.tbl-007
CSV 格式

CSV代表逗號分隔值的 是用於匯入和匯出試算表資料的常見格式。CSV 比逗號分隔格式更具彈性，因為它可讓您在欄位中包含引號字串。COPY 來自 CSV 格式的預設引號字元是雙引號 （ " ），但您可以使用 QUOTE AS 選項指定另一個引號字元。在欄位內使用引號字元時，請多加一個引號字元來逸出此字元。

PART 下表的 CSV格式化資料檔案中摘錄顯示以雙引號 （） 括住的字串"LARGE ANODIZED BRASS"。其也顯示了引用字串中以兩個雙引號括起來的字串 ("MEDIUM ""BURNISHED"" TIN")。

15,dark sky,MFGR#3,MFGR#47,MFGR#3438,indigo,"LARGE ANODIZED BRASS",45,LG CASE
22,floral beige,MFGR#4,MFGR#44,MFGR#4421,medium,"PROMO, POLISHED BRASS",19,LG DRUM
23,bisque slate,MFGR#4,MFGR#41,MFGR#4137,firebrick,"MEDIUM ""BURNISHED"" TIN",42,JUMBO JAR

PART 資料表的資料包含導致COPY失敗的字元。在此練習中，您會對錯誤進行故障診斷，並修正錯誤。

若要載入 CSV 格式的資料，請csv新增至您的COPY命令。執行下列命令以載入PART資料表。

copy part from 's3://<your-bucket-name>/load/part-csv.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>'
csv;

您可能會看到類似下列的錯誤訊息。

An error occurred when executing the SQL command:
copy part from 's3://amzn-s3-demo-bucket/load/part-csv.tbl' 
credentials' ...

ERROR: Load into table 'part' failed.  Check 'stl_load_errors' system table for details. [SQL State=XX000] 

Execution time: 1.46s

1 statement(s) failed.
1 statement(s) failed.

若要取得有關錯誤的詳細資訊，請查詢 STL_LOAD_ERRORS 資料表。下列查詢使用 SUBSTRING函數來縮短資料欄的可讀性，並使用 LIMIT 10 來減少傳回的資料列數。您可以調整 substring(filename,22,25) 中的值，以符合您的儲存貯體名稱長度。

select query, substring(filename,22,25) as filename,line_number as line, 
substring(colname,0,12) as column, type, position as pos, substring(raw_line,0,30) as line_text,
substring(raw_field_value,0,15) as field_text, 
substring(err_reason,0,45) as reason
from stl_load_errors 
order by query desc
limit 10;
 query  |    filename      | line |  column   |    type    | pos |      
--------+-------------------------+-----------+------------+------------+-----+----
 333765 | part-csv.tbl-000 |    1 |           |            |   0 |

 line_text        | field_text |                    reason
------------------+------------+----------------------------------------------
 15,NUL next,     |            | Missing newline: Unexpected character 0x2c f
NULL AS

part-csv.tbl 資料檔案使用NUL終止字元 （\x000 或 \x0） 來指示NULL值。

注意

儘管拼寫非常相似，NUL但 NULL 並不相同。NUL 是 UTF-8 字元，具有通常用於指示記錄結束 （） x000的編碼點EOR。NULL 是表示資料不存在SQL的值。

依預設， COPY會將NUL終止器字元視為EOR字元並終止記錄，這通常會導致意外的結果或錯誤。在文字資料NULL中沒有單一的標準指示方法。因此，NULLAS COPY命令選項可讓您指定載入資料表NULL時要取代的字元。在此範例中，您要COPY將NUL終止器字元視為NULL值。

注意

接收NULL值的資料表欄必須設定為 nullable。也就是說，它不得在CREATETABLE規格中包含NOTNULL限制條件。

若要PART使用 NULL AS 選項載入，請執行下列COPY命令。

copy part from 's3://<your-bucket-name>/load/part-csv.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
csv
null as '\000';

若要驗證COPY載入NULL的值，請執行下列命令，以僅選取包含 的資料列NULL。

select p_partkey, p_name, p_mfgr, p_category from part where p_mfgr is null;
 p_partkey |  p_name  | p_mfgr | p_category
-----------+----------+--------+------------
        15 | NUL next |        | MFGR#47
        81 | NUL next |        | MFGR#23
       133 | NUL next |        | MFGR#44 
(2 rows)

使用 載入SUPPLIER資料表 REGION

在此步驟中，您可以使用 DELIMITER和 REGION選項載入SUPPLIER資料表。

注意

用於載入SUPPLIER資料表的檔案提供於 AWS 範例儲存貯體中。您不需要在這個步驟上傳檔案。

字元分隔的格式

字元分隔檔案中的欄位是以特殊字元隔開，例如縱線字元 ( | )、逗號 ( , ) 或 Tab 字元 ( \t )。字元分隔檔案可以使用任何單一ASCII字元作為分隔符號，包括其中一個非列印ASCII字元。您可以使用 DELIMITER選項指定分隔符號字元。預設分隔符號是縱線字元 ( | )。

下表的資料摘錄SUPPLIER使用管道分隔格式。

1|1|257368|465569|41365|19950218|2-HIGH|0|17|2608718|9783671|4|2504369|92072|2|19950331|TRUCK
1|2|257368|201928|8146|19950218|2-HIGH|0|36|6587676|9783671|9|5994785|109794|6|19950416|MAIL
REGION

您應該盡可能在與 Amazon Redshift 叢集相同的 AWS 區域中找到負載資料。如果您的資料和叢集位於相同區域中，可以降低延遲，並且避免跨區域傳輸資料的成本。如需詳細資訊，請參閱 載入資料的 Amazon Redshift 最佳實務

如果您必須從不同 AWS 區域載入資料，請使用 REGION選項指定載入資料所在的 AWS 區域。如果指定區域，則所有載入資料 (包括資訊清單檔案) 都必須位於指定的區域。如需詳細資訊，請參閱REGION。

如果您的叢集位於美國東部 （維吉尼亞北部） 區域，請執行下列命令，將SUPPLIER資料表從位於美國西部 （奧勒岡） 區域的 Amazon S3 儲存貯體中以管道分隔的資料載入。在這個範例中，請勿變更儲存貯體名稱。

copy supplier from 's3://awssampledbuswest2/ssbgz/supplier.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '|' 
gzip
region 'us-west-2';

如果您的叢集不在美國東部 （維吉尼亞北部） 區域，請執行下列命令，將SUPPLIER資料表從管道分隔資料載入位於美國東部 （維吉尼亞北部） 區域的 Amazon S3 儲存貯體。在這個範例中，請勿變更儲存貯體名稱。

copy supplier from 's3://awssampledb/ssbgz/supplier.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '|' 
gzip
region 'us-east-1';

使用 載入CUSTOMER資料表 MANIFEST

在此步驟中，您可以使用 FIXEDWIDTH、ACCEPTINVCHARS、 MAXERROR和 MANIFEST選項來載入CUSTOMER資料表。

此練習的範例資料包含當COPY嘗試載入時會導致錯誤的字元。您可以使用 MAXERRORS選項和 STL_LOAD_ERRORS 系統資料表來疑難排解載入錯誤，然後使用 ACCEPTINVCHARS和 MANIFEST選項來消除錯誤。

固定寬度格式

固定寬度格式將每個欄位定義為固定數量的字元，而不是使用分隔符號分隔欄位。下表的資料摘錄CUSTOMER使用固定寬度格式。

1   Customer#000000001   IVhzIApeRb           MOROCCO  0MOROCCO  AFRICA      25-705 
2   Customer#000000002   XSTf4,NCwDVaWNe6tE   JORDAN   6JORDAN   MIDDLE EAST 23-453
3   Customer#000000003   MG9kdTD              ARGENTINA5ARGENTINAAMERICA     11-783

標籤/寬度配對的順序必須完全符合資料表欄的順序。如需詳細資訊，請參閱FIXEDWIDTH。

CUSTOMER 資料表資料的固定寬度規格字串如下所示。

fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, 
c_region :12, c_phone:15,c_mktsegment:10'

若要從固定寬度資料載入CUSTOMER資料表，請執行下列命令。

copy customer
from 's3://<your-bucket-name>/load/customer-fw.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10';

您應該會看到類似下列的錯誤訊息。

An error occurred when executing the SQL command:
copy customer
from 's3://amzn-s3-demo-bucket/load/customer-fw.tbl'
credentials'...

ERROR: Load into table 'customer' failed.  Check 'stl_load_errors' system table for details. [SQL State=XX000] 

Execution time: 2.95s

1 statement(s) failed.
MAXERROR

根據預設，第一次COPY遇到錯誤，命令會失敗並傳回錯誤訊息。為了節省測試時間，您可以使用 MAXERROR選項COPY指示在失敗之前略過指定數量的錯誤。由於我們預期第一次測試載入CUSTOMER資料表資料時會出現錯誤，因此請maxerror 10新增至 COPY 命令。

若要使用 FIXEDWIDTH和 MAXERROR選項進行測試，請執行下列命令。

copy customer
from 's3://<your-bucket-name>/load/customer-fw.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10'
maxerror 10;

這次，您不會看到錯誤訊息，而是得到類似下列的警示訊息。

Warnings:
Load into table 'customer' completed, 112497 record(s) loaded successfully.
Load into table 'customer' completed, 7 record(s) could not be loaded.  Check 'stl_load_errors' system table for details.

警告表示 COPY遇到七個錯誤。若要檢查錯誤，請查詢 STL_LOAD_ERRORS 資料表，如下列範例所示。

select query, substring(filename,22,25) as filename,line_number as line, 
substring(colname,0,12) as column, type, position as pos, substring(raw_line,0,30) as line_text,
substring(raw_field_value,0,15) as field_text, 
substring(err_reason,0,45) as error_reason
from stl_load_errors 
order by query desc, filename 
limit 7;

STL_LOAD_ERRORS 查詢的結果應類似於以下內容。

 query  |         filename          | line |  column   |    type    | pos |           line_text           | field_text |              error_reason
--------+---------------------------+------+-----------+------------+-----+-------------------------------+------------+----------------------------------------------
 334489 | customer-fw.tbl.log       |    2 | c_custkey | int4       |  -1 | customer-fw.tbl               | customer-f | Invalid digit, Value 'c', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    6 | c_custkey | int4       |  -1 | Complete                      | Complete   | Invalid digit, Value 'C', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    3 | c_custkey | int4       |  -1 | #Total rows                   | #Total row | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    5 | c_custkey | int4       |  -1 | #Status                       | #Status    | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl.log       |    1 | c_custkey | int4       |  -1 | #Load file                    | #Load file | Invalid digit, Value '#', Pos 0, Type: Integ
 334489 | customer-fw.tbl000        |    1 | c_address | varchar    |  34 | 1         Customer#000000001  | .Mayag.ezR | String contains invalid or unsupported UTF8
 334489 | customer-fw.tbl000        |    1 | c_address | varchar    |  34 | 1         Customer#000000001  | .Mayag.ezR | String contains invalid or unsupported UTF8
(7 rows)

檢查結果時，您可以看到 error_reasons 資料欄中有兩則訊息：

  • Invalid digit, Value '#', Pos 0, Type: Integ

    這些錯誤是由 customer-fw.tbl.log 檔案引起。問題在於，它是日誌檔案不是資料檔案，不應載入它。您可以使用資訊清單檔案來避免載入錯誤的檔案。

  • String contains invalid or unsupported UTF8

    VARCHAR 資料類型支援最多三個位元組的多位元組 UTF-8 個字元。如果載入資料包含不支援或無效的字元，您可以使用 ACCEPTINVCHARS選項，將每個無效的字元取代為指定的替代字元。

另一個載入的問題比較難偵測 — 載入產生非預期的結果。若要調查此問題，請執行下列命令來查詢CUSTOMER資料表。

select c_custkey, c_name, c_address        
from customer
order by c_custkey
limit 10;

 c_custkey |          c_name           |         c_address
-----------+---------------------------+---------------------------
         2 | Customer#000000002        | XSTf4,NCwDVaWNe6tE
         2 | Customer#000000002        | XSTf4,NCwDVaWNe6tE
         3 | Customer#000000003        | MG9kdTD
         3 | Customer#000000003        | MG9kdTD
         4 | Customer#000000004        | XxVSJsL
         4 | Customer#000000004        | XxVSJsL
         5 | Customer#000000005        | KvpyuHCplrB84WgAi
         5 | Customer#000000005        | KvpyuHCplrB84WgAi
         6 | Customer#000000006        | sKZz0CsnMD7mp4Xd0YrBvx
         6 | Customer#000000006        | sKZz0CsnMD7mp4Xd0YrBvx
(10 rows)

這些資料列應該是獨一無二的，但其中有重複。

另一個檢查非預期結果的方法，是去確有已載入的資料列數量。在我們的案例中，應該載入 100000 列資料，但載入訊息指出載入 112497 個記錄。已載入額外的資料列，因為 COPY已載入外部檔案 customer-fw.tbl0000.bak。

在這個練習中，您會使用資訊清單檔案來避免載入錯誤的檔案。

ACCEPTINVCHARS

根據預設，當COPY遇到資料欄資料類型不支援的字元時，它會略過資料列並傳回錯誤。如需有關無效 UTF-8 個字元的資訊，請參閱 多位元組字元載入錯誤。

您可以使用 MAXERRORS選項忽略錯誤並繼續載入，然後查詢 STL_LOAD_ERRORS 以尋找無效字元，然後修正資料檔案。不過， MAXERRORS 最適合用於疑難排解負載問題，通常不應用於生產環境。

ACCEPTINVCHARS 此選項通常是管理無效字元的更好選擇。ACCEPTINVCHARS 指示COPY使用指定的有效字元取代每個無效字元，並繼續載入操作。您可以指定除 以外的任何有效ASCII字元NULL作為取代字元。預設的替代字元是問號 ( ? ）。 會以相同長度的取代字串COPY取代多位元組字元。例如，4 個位元組的字元會被取代為 '????'。

COPY 傳回包含無效 UTF-8 個字元的列數。它也會為每個受影響的資料列將項目新增至 STL_REPLACEMENTS 系統資料表，每個節點層最多 100 個資料列。其他無效 UTF-8 個字元也會被取代，但這些取代事件不會記錄下來。

ACCEPTINVCHARS 僅適用於 VARCHAR欄。

對於此步驟，您可以使用替換字元 ACCEPTINVCHARS來新增 '^'。

MANIFEST

當您COPY從 Amazon S3 使用金鑰字首時，您可能會載入不需要的資料表。例如，'s3://amzn-s3-demo-bucket/load/ 資料夾包含 8 個檔案，它們有相同的索引鍵字首 customer-fw.tbl：customer-fw.tbl0000、customer-fw.tbl0001 等。然而，同一資料夾中也包含無關的檔案 customer-fw.tbl.log 和 customer-fw.tbl-0001.bak。

為了確保載入所有正確的檔案，且只有正確的檔案，請使用資訊清單檔案。資訊清單是JSON格式的文字檔案，明確列出每個要載入來源檔案的唯一物件金鑰。檔案物件可以位於不同的資料夾或儲存貯體，但必須在同一個區域中。如需詳細資訊，請參閱MANIFEST。

以下顯示 customer-fw-manifest 的文字。

{
  "entries": [
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-000"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-001"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-002"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-003"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-004"},    
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-005"},
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-006"}, 
    {"url":"s3://<your-bucket-name>/load/customer-fw.tbl-007"} 
    ]
}
使用資訊清單檔案載入CUSTOMER資料表的資料

  1. 在文字編輯器中開啟 customer-fw-manifest 檔案。

  2. Replace (取代) <your-bucket-name> 儲存貯體的名稱。

  3. 儲存檔案。

  4. 將檔案上傳到儲存貯體上的 load 資料夾。

  5. 執行下列 COPY 命令。

    copy customer from 's3://<your-bucket-name>/load/customer-fw-manifest'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10'
maxerror 10 
acceptinvchars as '^'
manifest;

使用 載入DWDATE資料表 DATEFORMAT

在此步驟中，您可以使用 DELIMITER和 DATEFORMAT選項載入DWDATE資料表。

載入 DATE 和 TIMESTAMP 欄時， COPY預期預設格式，即 YYYY-MM-DD日期的格式，以及 YYYY-MM-DD時間戳記的 HH：MI：SS。如果載入資料不使用預設格式，您可以使用 DATEFORMAT和 TIMEFORMAT 來指定格式。

下列摘錄顯示DWDATE資料表中的日期格式。注意兩個資料欄的日期格式不一致。

19920104	1992-01-04          Sunday		January	1992	199201	Jan1992	1	4	4	1...
19920112	January 12, 1992	Monday		January	1992	199201	Jan1992	2	12	12	1...
19920120	January 20, 1992	Tuesday	    January	1992	199201	Jan1992	3	20	20	1...
DATEFORMAT

您只能指定一個日期格式。如果載入資料包含不一致的格式，可能位於不同的資料欄中，或者如果載入時不知道該格式，則可以DATEFORMAT搭配'auto'引數使用 。指定 'auto' 時， 會COPY識別任何有效的日期或時間格式，並將其轉換為預設格式。'auto' 選項可識別使用 DATEFORMAT和 TIMEFORMAT字串時不支援的多種格式。如需詳細資訊，請參閱搭配 DATEFORMAT和 使用自動辨識 TIMEFORMAT。

若要載入DWDATE資料表，請執行下列COPY命令。

copy dwdate from 's3://<your-bucket-name>/load/dwdate-tab.tbl'
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
delimiter '\t' 
dateformat 'auto';

使用多個檔案載入LINEORDER資料表

此步驟使用 GZIP和 COMPUPDATE選項載入LINEORDER資料表。

在本練習中，您可以從單一資料檔案載入LINEORDER資料表，然後再次從多個檔案載入資料表。執行此作業可讓您比較兩個方法的載入時間。

注意

用於載入LINEORDER資料表的檔案提供於 AWS 範例儲存貯體中。您不需要在這個步驟上傳檔案。

GZIP、 LZOP和 BZIP2

您可以使用 gzip、lzop 或 bzip2 壓縮格式來壓縮您的檔案。從壓縮檔案載入時， 會在載入過程中COPY取消壓縮檔案。將檔案壓縮可以節省儲存空間及縮短載入時間。

COMPUPDATE

當 在沒有壓縮編碼的情況下COPY載入空資料表時，它會分析載入資料以判斷最佳編碼。接著它會將資料表修改為使用這些編碼，再開始載入。這個分析程序需要一點時間，但幾乎在每個資料表都會發生一次。若要節省時間，您可以透過COMPUPDATE關閉來略過此步驟。若要啟用COPY時間的準確評估，請COMPUPDATE關閉此步驟。

多個檔案

COPY 命令從多個檔案平行載入時，可以非常有效地載入資料，而不是從單一檔案載入。您可以將您的資料分割為檔案，使得檔案的數量為您叢集中配量數量的倍數。若您執行此作業，Amazon Redshift 會分割工作負載，並在配量中平均分配資料。每一節點的配量數目取決於叢集的節點大小。如需每個節點大小有多少配量的相關資訊，請移至《Amazon Redshift 管理指南》中的關於叢集和節點。

例如，在本教學中使用的 dc2.large 運算節點有兩個配量，所以四個叢集總共有八個配量。先前的步驟將載入的資料分成八個檔案，即使檔案很小。在這個步驟中，您會比較從單一大檔案與從多個檔案載入之間的時間差異。

您用於本教學的檔案約有 1500 萬筆記錄，大小約 1.2 GB。以 Amazon Redshift 的規模來說這些檔案很小，但足以示範從多個檔案載入的效能優點。由於在本教學中這些檔案已夠大，載入這些檔案再將其上傳到 Amazon S3 所需的時間很長。因此，您可以直接從 AWS 範例儲存貯體載入檔案。

下列螢幕擷取畫面顯示 的資料檔案LINEORDER。

LINEORDER 資料表中的資料分為九個檔案。
COPY 使用多個檔案評估 的效能

  1. COPY 從單一檔案對 執行下列命令。請勿變更儲存貯體名稱。

    copy lineorder from 's3://awssampledb/load/lo/lineorder-single.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
gzip
compupdate off
region 'us-east-1';

  2. 結果類似以下這樣。請注意執行時間。

    Warnings:
Load into table 'lineorder' completed, 14996734 record(s) loaded successfully.

0 row(s) affected.
copy executed successfully

Execution time: 51.56s

  3. COPY 從多個檔案對 執行下列命令。請勿變更儲存貯體名稱。

    copy lineorder from 's3://awssampledb/load/lo/lineorder-multi.tbl' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>' 
gzip
compupdate off
region 'us-east-1';

  4. 結果類似以下這樣。請注意執行時間。

    Warnings:
Load into table 'lineorder' completed, 14996734 record(s) loaded successfully.

0 row(s) affected.
copy executed successfully

Execution time: 17.7s

  5. 比較執行時間。

    在我們的範例中，載入 1500 萬個記錄的時間從 51.56 秒降到 17.7 秒，減少了百分之 65.7。

    這些結果來自於使用具有四個節點的叢集。如果您的叢集有更多節點，節省的時間會加倍。典型的 Amazon Redshift 叢集有數十到數百個節點，差別更巨大。如果您的叢集只有單一節點，執行時間的差異很小。

步驟 6：清空及分析資料庫

每當您新增、刪除或修改大量資料列時，您應該執行 VACUUM命令，然後執行 ANALYZE命令。vacuum (清空) 會恢復已刪除資料列的空間，並還原排序。ANALYZE 命令會更新統計資料中繼資料，讓查詢最佳化工具能夠產生更準確的查詢計劃。如需詳細資訊，請參閱清空資料表。

如果您以排序索引鍵的順序載入資料，清空很快。在本教學課程中，您已新增大量資料列，但是將它們新增到空資料表。在這種情況下，不需要重新排序，您也沒有刪除任何資料列。COPY 會在載入空資料表後自動更新統計資料，因此您的統計資料應為 up-to-date。但基於良好的內務處理，您會清空並分析資料庫以完成本教學。

若要清空並分析資料庫，執行下列命令。

vacuum;
analyze;

步驟 7：清理您的資源

叢集只要執行就會繼續產生費用。完成本教學課程後，您應按照 Amazon Redshift 入門指南中的步驟 5：撤銷存取權並刪除範例叢集中的步驟，將環境恢復到先前的狀態。

如果您想要保留叢集，但復原SSB資料表使用的儲存體，請執行下列命令。

drop table part;
drop table supplier;
drop table customer;
drop table dwdate;
drop table lineorder;

下一頁

Summary

Summary

在本教學課程中，您將資料檔案上傳到 Amazon S3，然後使用COPY命令將檔案中的資料載入 Amazon Redshift 資料表。

您已使用以下格式載入資料：

  • 字元分隔

  • CSV

  • 固定寬度

您可以使用 STL_LOAD_ERRORS 系統資料表來疑難排解載入錯誤，然後使用 REGION、MANIFEST、ACCEPTINVCHARS、 DATEFORMAT和 NULL AS MAXERROR選項來解決錯誤。

您已運用下列最佳實務來載入資料：

如需 Amazon Redshift 最佳實務的相關資訊，請參閱下列連結：