Schritt 5: Ausführen der COPY-Befehle - Amazon Redshift
COPY-BefehlssyntaxLaden der SSB-Tabellen

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Schritt 5: Ausführen der COPY-Befehle

Sie führen COPY-Befehle aus, um jede der Tabellen im SSB-Schema zu laden. Die Beispiele für COPY-Befehle demonstrieren das Laden aus unterschiedlichen Dateiformaten unter Verwendung verschiedener COPY-Befehlsoptionen sowie die Behebung von Ladefehlern.

COPY-Befehlssyntax

Die grundlegende COPY-Befehlssyntax ist wie folgt. 

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

Zur Ausführung eines COPY-Befehls geben Sie die folgenden Werte an.

Tabellenname

Die Zieltabelle für den COPY-Befehl. Die Tabelle muss in der Datenbank bereits vorhanden sein. Die Tabelle kann temporär oder persistent sein. Der COPY-Befehl fügt die neuen Eingabedaten den vorhandenen Zeilen in der Tabelle an.

Spaltenliste

Standardmäßig lädt COPY Felder aus den Quelldaten in ihrer Reihenfolge in die Tabellenspalten. Sie können optional eine Spaltenliste angeben, d. h. eine durch Kommas getrennte Liste von Spaltennamen, um Datenfelder bestimmten Spalten zuzuordnen. In diesem Tutorial verwenden Sie keine Spaltenlisten. Weitere Informationen finden Sie unter Column List in der Referenz zum COPY-Befehl.

Datenquelle

Sie können den COPY-Befehl verwenden, um Daten aus einem Amazon-S3-Bucket, aus einem Amazon-EMR-Cluster, über eine SSH-Verbindung aus einem Remote-Host oder aus einer Amazon-DynamoDB-Tabelle zu laden. Für dieses Tutorial laden Sie Datendateien in einen Amazon-S3-Bucket. Beim Laden von Amazon S3 müssen Sie den Namen des Buckets und den Speicherort der Datendateien angeben. Dazu geben Sie entweder einen Objektpfad für die Datendateien oder den Speicherort einer Manifestdatei an, die jede Datendatei und ihren Speicherort explizit auflistet.

  • Schlüsselpräfix

    Ein in Amazon S3 gespeichertes Objekt wird durch einen Objektschlüssel eindeutig definiert, der den Bucketnamen, eventuelle Ordnernamen sowie den Objektnamen enthält. Ein Schlüsselpräfix bezieht sich auf eine Reihe von Objekten, die das gleiche Präfix haben. Der Objektpfad ist ein Schlüsselpräfix, das der COPY-Befehl verwendet, um alle Objekte mit dem gleichen Schlüsselpräfix zu laden. Beispielsweise kann sich das Schlüsselpräfix custdata.txt auf eine einzelne Datei oder auf einen Satz von Dateien einschließlich custdata.txt.001, custdata.txt.002 und so weiter beziehen.

  • Manifestdatei

    In einigen Fällen müssen Sie möglicherweise Dateien mit unterschiedlichen Präfixen laden, z. B. aus mehreren Buckets oder Ordnern. In anderen Fällen müssen Sie möglicherweise Dateien ausschließen, die ein Präfix verwenden. In diesen Fällen können Sie eine Manifestdatei verwenden. Eine Manifestdatei führt alle Ladedateien und ihre eindeutigen Objektschlüssel explizit auf. Sie verwenden eine Manifestdatei, um die PART-Tabelle später in diesem Tutorial zu laden.

Anmeldeinformationen

Um auf die AWS Ressourcen zuzugreifen, die die zu ladenden Daten enthalten, müssen Sie die AWS Zugangsdaten für einen Benutzer mit ausreichenden Rechten angeben. Diese Anmeldeinformationen enthalten einen Amazon-Ressourcennamen (ARN) für die IAM-Rolle. Um Daten aus Amazon S3 zu laden, müssen die Anmeldeinformationen ListBucket und GetObject Berechtigungen enthalten. Weitere Anmeldeinformationen sind erforderlich, wenn Ihre Daten verschlüsselt sind. Weitere Informationen finden Sie unter Autorisierungsparameter in der Referenz zum COPY-Befehl. Weitere Informationen zur Verwaltung des Zugriffs finden Sie unter Managing access permissions to your Amazon S3 resources (Verwaltung von Zugriffsberechtigungen für Ihre Amazon-S3-Ressourcen).

Optionen

Sie können mit dem COPY-Befehl eine Reihe von Parametern angeben, um Dateiformate anzugeben, Datenformate zu verwalten, mit Fehlern umzugehen und andere Features zu steuern. In diesem Tutorial verwenden Sie die folgenden COPY-Befehlsoptionen und -Funktionen:

Laden der SSB-Tabellen

Mit den folgenden COPY-Befehlen laden Sie jede der Tabellen im SSB-Schema. Der Befehl für jede Tabelle demonstriert unterschiedliche COPY-Optionen und Fehlerbehebungstechniken.

Gehen Sie zum Laden der SSB-Tabellen wie folgt vor:

  1. Ersetzen Sie den Bucket-Namen und die AWS Anmeldeinformationen

  2. Laden der Tabelle PART mit NULL AS

  3. Laden der Tabelle SUPPLIER mit REGION

  4. Laden der Tabelle CUSTOMER mit MANIFEST

  5. Laden der Tabelle DWDATE mit DATEFORMAT

  6. Laden der Tabelle LINEORDER unter Verwendung mehrerer Tabellen

Ersetzen Sie den Bucket-Namen und die AWS Anmeldeinformationen

Die COPY-befehle in diesem Tutorial werden im folgenden Format angegeben.

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

Gehen Sie für jeden COPY-Befehl wie folgt vor:

  1. Ersetzen Sie <your-bucket-name> durch den Namen eines Buckets in derselben Region, in der sich auch Ihr Cluster befindet.

    Für diesen Schritt wird davon ausgegangen, dass Bucket und Cluster sich in derselben Region befinden. Alternativ können Sie die Region mittels der Option REGION mit dem Befehl COPY angeben.

  2. Ersetzen Sie <aws-account-id>und <role-name>durch Ihre eigene AWS-Konto und IAM-Rolle. Das Segment der Anmeldedatenzeichenfolge, das in einfachen Anführungszeichen eingeschlossen ist, darf keine Leerzeichen oder Zeilenumbrüche enthalten. Beachten Sie, dass sich das Format des ARNs geringfügig von dem im Beispiel verwendeten Format unterscheiden kann. Am besten kopieren Sie den ARN für die Rolle aus der IAM-Konsole, um sicherzustellen, dass er korrekt ist, wenn Sie die COPY-Befehle ausführen.

Laden der Tabelle PART mit NULL AS

In diesem Schritt verwenden Sie die Optionen CSV und NULL AS, um die PART-Tabelle zu laden.

Der COPY-Befehl kann Daten aus mehreren Dateien parallel laden, was viel schneller ist als das Laden aus einer einzelnen Datei. Zur Demonstration dieses Prinzips sind die Daten für jede Tabelle in diesem Tutorial in acht Dateien unterteilt, obwohl die Dateien sehr klein sind. In einem späteren Schritt vergleichen Sie den Zeitunterschied zwischen dem Laden aus einer einzelnen Datei und dem Laden aus mehreren Dateien. Weitere Informationen finden Sie unter Laden von Datendateien.

Schlüsselpräfix

Sie können aus mehreren Dateien durch die Angabe eines Schlüsselpräfixes den Dateiensatz oder durch die explizite Auflistung der Dateien in einer Manifestdatei laden. In diesem Schritt verwenden Sie ein Schlüsselpräfix. In einem späteren Schritt verwenden Sie eine Manifestdatei. Das Schlüsselpräfix 's3://mybucket/load/part-csv.tbl' lädt den folgenden Satz der Dateien im Ordner 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-Format

CSV (Comma Separated Values) ist ein für den Import und den Export von Spreadsheet-Daten häufig verwendetes Format. CSV ist flexibler als das Comma-Delimited Format, da es den Einschluss von Zeichenfolgen mit Anführungszeichen in Feldern erlaubt. Das Standard-Anführungszeichen für COPY aus dem CSV-Format ist das doppelte Anführungszeichen ("), Sie können aber mit der Option QUOTE AS ein anderes Zeichen angeben. Wenn Sie das Anführungszeichen innerhalb des Feldes verwenden, verwenden Sie ein weiteres Anführungszeichen als Escape-Zeichen.

Der folgende Auszug aus einer CSV-formatierten Datendatei für die PART-Tabelle zeigt Zeichenfolgen, die in doppelte Anführungszeichen () eingeschlossen sind. ("LARGE ANODIZED BRASS"). Er zeigt außerdem eine Zeichenfolge, die in zwei doppelte Anführungszeichen innerhalb einer Zeichenfolge mit Anführungszeichen eingeschlossen () ist. ("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

Die Daten für die PART-Tabelle enthalten Zeichen, die dazu führen, dass COPY fehlschlägt. In dieser Übung finden Sie die Fehler und beheben sie.

Um Daten im CSV-Format zu laden, fügen Sie Ihrem COPY-Befehl csv hinzu. Führen Sie den folgenden Befehl aus, um die PART-Tabelle zu laden. 

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;

Sie könnten eine Fehlermeldung ähnlich der folgenden erhalten.

An error occurred when executing the SQL command:
copy part from 's3://mybucket/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.

Um mehr Informationen zu dem Fehler zu erhalten, fragen Sie die Tabelle STL_LOAD_ERRORS ab. Die folgende Abfrage verwendet die Funktion SUBSTRING zum Kürzen von Spalten zur besseren Lesbarkeit sowie LIMIT 10, um die Anzahl der ausgegebenen Zeilen zu reduzieren. Sie können die Werte in substring(filename,22,25) an die Länge Ihres Bucketnamens anpassen.

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

Die part-csv.tbl-Datendateien verwenden das NUL-Begrenzungszeichen (\x000 oder \x0) zur Anzeige von NULL-Werten.

Anmerkung

Trotz ihrer sehr ähnlichen Schreibweise sind NUL und NULL nicht identisch. NUL ist ein UTF-8-Zeichen mit Codepunkt x000, das oft zur Kennzeichnen des Datensatzendes (End of Record, EOR) verwendet wird. NULL ist ein SQL-Wert, der für die Abwesenheit von Daten steht.

Standardmäßig behandelt COPY das Begrenzungszeichen NUL als EOR-Zeichen und beendet den Datensatz, was oft zu unerwarteten Ergebnissen oder einem Fehler führt. Es gibt keine einzige Standardmethode, um NULL in Textdaten anzuzeigen. Mit der Befehlsoption NULL AS COPY können Sie also angeben, welches Zeichen beim Laden der Tabelle durch NULL ersetzt werden soll. In diesem Beispiel soll COPY das NUL-Begrenzungszeichen als NULL-Wert behandeln.

Anmerkung

Die Tabellenspalte, die den NULL-Wert erhält, muss als nullfähig konfiguriert sein. Das bedeutet, dass sie die NOT NULL-Einschränkung in der CREATE TABLE-Spezifikation nicht enthalten darf.

Führen Sie den folgenden COPY-Befehl aus, um PART mit der Option NULL AS zu laden.

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';

Um zu prüfen, ob COPY NULL-Werte geladen hat, führen Sie den folgenden Befehl aus, um nur die Zeilen auszuwählen, die NULL enthalten.

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)

Laden der Tabelle SUPPLIER mit REGION

In diesem Schritt verwenden Sie die Optionen DELIMITER und REGION, um die Tabelle SUPPLIER zu laden.

Anmerkung

Die Dateien zum Laden der SUPPLIER-Tabelle befinden sich in einem AWS Muster-Bucket. Für diesen Schritt müssen Sie keine Daten hochladen.

Zeichengetrenntes Format

Die Felder in einer zeichengetrennten Datei werden von einem speziellen Teichen, etwa einem senkrechten Strich (|), einem Komma (,) oder einem Tabulatorzeichen (\t) abgetrennt. Dafür können alle einzelnen ASCII-Zeichen verwendet werden, auch nicht gedruckte ASCII-Zeichen. Sie geben das Trennzeichen mit der Option DELIMITER an. Das Standardtrennzeichen ist der senkrechte Strich (|).

Der folgende Auszug aus den Daten für die Tabelle SUPPLIER verwendet den senkrechten Strich als Trennzeichen. 

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

Wann immer möglich, sollten Sie Ihre Ladedaten in derselben AWS Region wie Ihr Amazon Redshift Redshift-Cluster lokalisieren. Wenn sich Ihre Daten und Ihr Cluster in derselben Region befinden, reduzieren Sie die Latenz und vermeiden Kosten für den regionenübergreifenden Datentransfer. Weitere Informationen finden Sie unter Bewährte Methoden für Amazon Redshift zum Laden von Daten

Wenn Sie Daten aus einer anderen AWS Region laden müssen, verwenden Sie die Option REGION, um die AWS Region anzugeben, in der sich die Ladedaten befinden. Wenn Sie eine Region angeben, müssen sich alle Ladedaten, einschließlich der Manifestdateien, in der benannten Region befinden. Weitere Informationen finden Sie unter REGION.

Wenn sich Ihr Cluster in der Region USA Ost (Nord-Virginia) befindet, führen Sie den folgenden Befehl aus, um die Tabelle SUPPLIER aus mit | getrennten Daten in einem Amazon S3 Bucket in der Region USA West (Oregon) zu laden. Ändern Sie für dieses Beispiel den Namen des Buckets nicht. 

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';

Wenn sich Ihr Cluster nicht in der Region USA Ost (Nord-Virginia) befindet, führen Sie den folgenden Befehl aus, um die Tabelle SUPPLIER aus durch | getrennten Daten in einem Amazon S3 Bucket zu laden, der sich in der Region USA Ost (Nord-Virginia) befindet. Ändern Sie für dieses Beispiel den Namen des Buckets nicht.

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';

Laden der Tabelle CUSTOMER mit MANIFEST

In diesem Schritt laden Sie die Tabelle CUSTOMER mit den Optionen FIXEDWIDTH, MAXERROR, ACCEPTINVCHARS und MANIFEST.

Die Beispieldaten für diese Übung enthalten Zeichen, die beim Laden durch COPY Fehler verursachen. Mit der Option MAXERRORS und der Systemtabelle STL_LOAD_ERRORS können Sie die Ladefehler ausfindig machen und mit den Optionen ACCEPTINVCHARS und MANIFEST die Fehler beheben.

Format mit fester Breite

Das Format mit fester Breite definiert jedes Feld mit einer festen Anzahl von Zeichen, anstatt die Felder durch Trennzeichen voneinander zu scheiden. Der folgende Auszug aus den Daten für die Tabelle CUSTOMER verwendet das Format mit fester Breite.

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

Die Reihenfolge der Paare aus Bezeichnung/Breite muss exakt mit der Reihenfolge der Tabellenspalten übereinstimmen. Weitere Informationen finden Sie unter FIXEDWIDTH.

Die Spezifikationszeichenfolge für die feste Breite der Daten für die Tabelle CUSTOMER ist die folgende.

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'

Um die Tabelle CUSTOMER aus Daten mit fester Breite zu laden, führen Sie den folgenden Befehl aus.

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';

Sie sollten eine Fehlermeldung ähnlich der folgenden erhalten.

An error occurred when executing the SQL command:
copy customer
from 's3://mybucket/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

Standardmäßig schlägt der COPY-Befehl beim ersten Auftreten eines Fehlers fehl und gibt eine Fehlermeldung aus. Um beim testen Zeit zu sparen, können Sie die Option MAXERROR verwenden, damit COPY eine bestimmte Anzahl von Fehlern übergeht, bevor der Befehl fehlschlägt. Da wir beim ersten Test des Ladens der Daten der Tabelle CUSTOMER Fehler erwarten, fügen Sie dem COPY-Befehl maxerror 10 hinzu.

Führen Sie zum Test mit den Optionen FIXEDWIDTH und MAXERROR den folgenden Befehl aus.

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;

Diesmal erhalten Sie statt einer Fehlermeldung eine Warnmeldung, ähnlich der folgenden.

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.

Die Warnung gibt an, dass COPY auf sieben Fehler gestoßen ist. Fragen Sie zur Prüfung der Fehler die Tabelle STL_LOAD_ERRORS ab, wie im folgenden Beispiel gezeigt.

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;

Das Ergebnis der Abfrage von STL_LOAD_ERRORS sollte ähnlich wie folgt aussehen.

 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)

Bei der Untersuchung der Ergebnisse sehen Sie zwei Meldungen in der Spalte error_reasons:

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

    Diese Fehler wurden von der Datei customer-fw.tbl.log verursacht. Das Problem besteht darin, dass es sich um eine Protokolldatei und nicht um eine Datendatei handelt, die daher nicht geladen werden sollte. Sie können eine Manifestdatei verwenden, um das Laden falscher Dateien zu vermeiden. 

  • String contains invalid or unsupported UTF8

    Der Datentyp VARCHAR unterstützt Multibyte-UTF-8-Zeichen mit bis zu drei Byte. Wenn die Ladedaten nicht unterstützte oder ungültige Zeichen enthalten, können Sie jedes ungültige Zeichen mit der Option ACCEPTINVCHARS gegen ein angegebenes Alternativzeichen austauschen.

Ein weiteres Problem mit der Last ist schwieriger zu erkennen – die Last führte zu unerwarteten Ergebnissen. Fragen Sie mit dem folgenden Befehl die Tabelle CUSTOMER ab, um dieses Problem zu untersuchen.

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)

Die Zeilen sollten eindeutig sein, es gibt jedoch Duplikate.

Eine weitere Möglichkeit zur Untersuchung unerwarteter Ergebnisse besteht darin, die Anzahl der geladenen Zeilen zu prüfen. In unserem Fall sollten 100000 Zeilen geladen werden, die Lademeldung gab 112497 geladene Datensätze an. Die zusätzlichen Zeilen wurden geladen, weil COPY eine überzählige Datei, , geladen hat., customer-fw.tbl0000.bak.

In dieser Übung verwenden Sie eine Manifestdatei, um das Laden der falschen Dateien zu vermeiden.

ACCEPTINVCHARS

Standardmäßig gilt, dass COPY beim Treffen auf eine von dem Datentyp der Spalte nicht unterstütztes Zeichen die Zeile überspringt und einen Fehler ausgibt. Informationen zu ungültigen UTF-8-Zeichen finden Sie unter Fehler beim Laden von Multibyte-Zeichen.

Sie können die Option MAXERRORS verwenden, um Fehler zu ignorieren und den Ladevorgang fortzusetzen, dann STL_LOAD_ERRORS abfragen, um die ungültigen Zeichen zu finden und anschließend die Datendateien korrigieren. MAXERRORS wird jedoch sinnvollerweise für die Behebung von Ladeproblemen verwendet und sollte generell nicht in einer Produktionsumgebung genutzt werden.

Die Option ACCEPTINVCHARS ist normalerweise die bessere Wahl für den Umgang mit ungültigen Zeichen. ACCEPTINVCHARS instruiert COPY dazu, jedes ungültige Zeichen durch ein festgelegtes gültiges Zeichen zu ersetzen und den Ladevorgang fortzusetzen. Sie können jedes gültige ASCII-Zeichen, ausgenommen NULL, als Austauschzeichen festlegen. Das Standard-Austauschzeichen ist ein Fragezeichen (?). COPY ersetzt Multibyte-Zeichen durch eine Austauschzeichenfolge gleicher Länge. Ein 4-Byte-Zeichen wird etwa durch ersetzt '????'.

COPY gibt die Anzahl der Zeilen zurück, die ungültige UTF-8-Zeichen enthielten. Außerdem wird für jede betroffene Zeile ein Eintrag in die Systemtabelle STL_REPLACEMENTS hinzugefügt (bis zu maximal 100 Zeilen pro Knotenebene). Zusätzliche ungültige UTF-8-Zeichen werden ebenfalls ersetzt. Diese Ersetzungsereignisse werden jedoch nicht aufgezeichnet.

ACCEPTINVCHARS ist nur für VARCHAR-Spalten gültig.

Für diesen Schritt fügen Sie die ACCEPTINVCHARS mit dem Ersetzungszeichen '^' hinzu.

MANIFEST

Wenn Sie von Amazon S3 mit einem Schlüsselpräfix kopieren, besteht die Gefahr, dass Sie unerwünschte Tabellen laden. Beispielsweise enthält der Ordner 's3://mybucket/load/ acht Datendateien, die das Schlüsselpräfix customer-fw.tbl gemeinsam haben: customer-fw.tbl0000, customer-fw.tbl0001 usw. Dieser Ordner enthält aber auch die überschüssigen Dateien customer-fw.tbl.log und customer-fw.tbl-0001.bak.

Um sicherzustellen, dass Sie alle und nur die korrekten Dateien laden, verwenden Sie eine Manifestdatei. Das Manifest ist eine Textdatei im JSON-Format, die den eindeutigen Objektschlüssel für jede zu ladende Quelldatei ausdrücklich aufführt. Die Dateiobjekte können sich in verschiedenen Ordnern oder Buckets, müssen sich aber in derselben Region befinden. Weitere Informationen finden Sie unter MANIFEST.

Nachfolgend sehen Sie den customer-fw-manifest-Text. 

{
  "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"} 
    ]
}
So laden Sie die Daten für die Tabelle CUSTOMER mit der Manifestdatei:

  1. Öffnen Sie die Datei customer-fw-manifest in einem Text-Editor.

  2. Ersetzen Sie <your-bucket-name> durch den Namen Ihres Buckets.

  3. Speichern Sie die Datei.

  4. Laden Sie die Datei in den Ladeordner in Ihrem Bucket.

  5. Führen Sie den folgenden COPY-Befehl aus.

    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;

Laden der Tabelle DWDATE mit DATEFORMAT

In diesem Schritt verwenden Sie die Optionen DELIMITER und DATEFORMAT, um die Tabelle DWDATE zu laden.

Beim Laden der Spalten DATE und TIMESTAMP erwartet COPY das Standardformat, s. h. JJJJ-MM-TT für Datumsangaben und JJJJ-MM-TT-HH:MI:SS für Zeitstempel. Wenn die Ladedaten das Standardformat nicht verwenden, können Sie das Format mithilfe von DATEFORMAT und TIMEFORMAT angeben.

Der folgende Auszug zeigt Datumsformate in der Tabelle DWDATE. Beachten Sie, dass die Datumsformate in Spalte zwei nicht konsistent sind.

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

Sie können nur ein Datumsformat angeben. Wenn die Ladedaten inkonsistente Formate enthalten, möglicherweise in verschiedenen Spalten, oder wenn das Format zum Zeitpunkt des Ladevorgangs nicht bekannt ist, verwenden Sie DATEFORMAT mit dem Argument 'auto'. Wenn 'auto' angegeben ist, erkennt COPY jedes gültige Datums- oder Zeitformat und konvertiert es in das Standardformat. Die Option 'auto' erkennt verschiedene Formate, die bei der Verwendung einer DATEFORMAT- und TIMEFORMAT-Zeichenfolge nicht unterstützt werden. Weitere Informationen finden Sie unter Verwenden der automatischen Erkennung bei DATEFORMAT und TIMEFORMAT.

Führen Sie den folgenden COPY-Befehl aus, um die Tabelle DWDATE zu laden.

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';

Laden der Tabelle LINEORDER unter Verwendung mehrerer Tabellen

Dieser Schritt verwendet die Optionen GZIP und COMPUPDATE zum Laden der Tabelle LINEORDER.

In dieser Übung laden Sie die Tabelle LINEORDER aus einer einzigen Datendatei und laden sie dann erneut aus mehreren Dateien. Auf diese Weise können Sie die Ladezeiten der beiden Methoden vergleichen.

Anmerkung

Die Dateien zum Laden der LINEORDER-Tabelle werden in einem AWS Beispiel-Bucket bereitgestellt. Für diesen Schritt müssen Sie keine Daten hochladen.

GZIP, LZOP und BZIP2

Sie können Ihre Dateien mit den Kompressionsformaten gzip, lzop und bzip2 komprimieren. Beim laden aus komprimierten Dateien dekomprimiert COPY diese während des Ladevorgangs. Die Komprimierung Ihrer Dateien spart Speicherplatz und verkürzt die Ladezeiten.

COMPUPDATE

Wenn COPY eine leere Tabelle ohne Kompressionskodierungen lädt, analysiert der Befehl die Ladedaten, um die optimalen Kodierungen zu bestimmen. Anschließend ändert er die Tabelle, um diese Kodierungen vor dem beginn des Ladevorgangs anzuwenden. Diese Analyse nimmt Zeit in Anspruch, findet aber höchstens einmal pro Tabelle statt. Um Zeit zu sparen, können Sie diesen Schritt überspringen, indem Sie COMPUPDATE ausschalten. Um eine genaue Auswertung der COPY-Zeiten zu ermöglichen, schalten Sie COMPUPDATE für diesen Schritt aus.

Mehrere Dateien

Der Befehl COPY kann Daten sehr effizient laden, wenn er aus mehreren Dateien parallel statt aus einer einzigen Datei lädt. Sie können Ihre Daten in Dateien aufteilen, sodass die Anzahl der Dateien ein Vielfaches der Anzahl der Schichten in Ihrem Cluster beträgt. In diesem Fall teilt Amazon Redshift den Workload auf und verteilt die Daten gleichmäßig auf die Schichten. Die Anzahl der Slices pro Knoten ist von der Knotengröße des Clusters abhängig. Weitere Informationen zur Anzahl der Slices für die einzelnen Knotengrößen finden Sie unter About Clusters and Nodes (Informationen zu Clustern und Knoten) im Amazon-Redshift-Verwaltungshandbuch.

Beispiel: Die in diesem Tutorial verwendeten Datenverarbeitungsknoten der Größe dc2.large haben jeweils zwei Slices, der aus vier Knoten bestehende Cluster hat also acht Slices. In früheren Schritten waren die Ladedaten in acht Dateien enthalten, obwohl diese sehr klein waren. In diesem Schritt vergleichen Sie den Zeitunterschied zwischen dem Laden aus einer einzigen großen Datei und dem Laden aus mehreren Dateien.

Die Dateien, die Sie für dieses Tutorial verwenden, enthalten etwa 15 Millionen Datensätze und belegen etwa 1,2 GB. Für Amazon Redshift sind dies sehr kleine Dateien, sie reichen jedoch aus, um den Leistungsvorteil des Ladens aus mehreren Dateien zu illustrieren. Die Dateien sind so groß, dass zu viel Zeit erforderlich ist, um sie im Rahmen dieses Tutorials herunterzuladen und dann zu Amazon S3 hochzuladen. Somit laden Sie die Dateien direkt aus einem AWS Beispiel-Bucket.

Das folgende Bildschirmfoto zeigt die Datendateien für LINEORDER.

So evaluieren Sie die Leistung von COPY mit mehreren Dateien:

  1. Führen Sie den folgenden COPY-Befehl aus, um aus einer einzelnen Datei zu kopieren. Ändern Sie den Namen des Buckets nicht.

    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. Ihre Ergebnisse sollten wie folgt aussehen. Beachten Sie die Ausführungszeit.

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

0 row(s) affected.
copy executed successfully

Execution time: 51.56s

  3. Führen Sie den folgenden COPY-Befehl aus, um aus mehreren Dateien zu kopieren. Ändern Sie den Namen des Buckets nicht.

    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. Ihre Ergebnisse sollten wie folgt aussehen. Beachten Sie die Ausführungszeit.

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

0 row(s) affected.
copy executed successfully

Execution time: 17.7s

  5. Vergleichen Sie die Ausführungszeiten.

    In unserem Beispiel sank die Zeit für das Ladern von 15 Millionen Datensätzen von 51,56 Sekunden auf 17,7 Sekunden, d. h. um 65,7 Prozent.

    Diese Ergebnisse basieren auf der Verwendung eines Clusters mit vier Knoten. Wenn Ihr Cluster mehr Knoten hat, vervielfachen sich die Zeiteinsparungen. Bei typischen Amazon-Redshift-Clustern mit manchmal Hunderten von Knoten ist der Unterschied noch viel deutlicher. Wenn Sie einen Cluster mit nur einem Knoten haben, besteht nur ein geringer Unterschied zwischen den Ausführungszeiten.

Nächster Schritt

Schritt 6: Bereinigen und Analysieren der Datenbank