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.

Tutorial: So laden Sie Daten aus Amazon S3

In diesem Tutorial durchlaufen Sie den kompletten Prozess des Ladens von Daten in Ihre Amazon-Redshift-Datenbanktabellen aus Datendateien in einem Amazon-S3-Bucket.

In diesem Tutorial führen Sie folgende Aufgaben aus:

Geschätzte Zeit: 60 Minuten

Geschätzte Kosten 1,00 USD pro Stunde für den Cluster

Voraussetzungen

Sie benötigen die folgenden Voraussetzungen:

Dieses Tutorial kann unabhängig von anderen absolviert werden. Zusätzlich zu diesem Tutorial empfehlen wir die folgenden Tutorials, um ein umfassenderes Verständnis vom Entwurf und von der Verwendung von Amazon-Redshift-Datenbanken zu erhalten:

Übersicht

Sie können Daten zu Ihren Amazon Redshift Redshift-Tabellen hinzufügen, indem Sie entweder einen INSERT Befehl oder einen COPY Befehl verwenden. Bei der Größe und Geschwindigkeit eines Amazon Redshift Data Warehouse ist der COPY Befehl um ein Vielfaches schneller und effizienter als INSERT Befehle.

Der COPY Befehl verwendet die Massively Parallel Processing (MPP) -Architektur von Amazon Redshift, um Daten parallel aus mehreren Datenquellen zu lesen und zu laden. Sie können Dateien aus Amazon S3, Amazon oder einem beliebigen Remote-Host ladenEMR, auf den über eine Secure Shell (SSH) -Verbindung zugegriffen werden kann. Oder Sie können direkt aus einer Amazon-DynamoDB-Tabelle laden.

In diesem Tutorial verwenden Sie den COPY Befehl, um Daten aus Amazon S3 zu laden. Viele der hier vorgestellten Prinzipien gelten auch für das Laden aus anderen Datenquellen.

Weitere Informationen zur Verwendung des COPY Befehls finden Sie in diesen Ressourcen:

Schritt 1: Erstellen eines Clusters

Wenn Sie bereits einen Cluster haben, den Sie verwenden möchten, können Sie diesen Schritt überspringen.

Verwenden Sie für die Übungen in diesem Tutorial einen Vierknoten-Cluster.

Folgen Sie den Schritten im Amazon Redshift Getting Started Guide, um von einem SQL Client aus eine Verbindung zu Ihrem Cluster herzustellen und eine Verbindung zu testen. Sie müssen die verbleibenden Schritte aus "Erste Schritte" nicht durchführen, um Tabellen zu erstellen, Daten hochzuladen und Beispielabfragen auszuprobieren.

Schritt 2: Herunterladen der Datendateien

In diesem Schritt laden Sie einen Satz Beispieldatendateien auf Ihren Computer herunter. Im nächsten Schritt laden Sie die Dateien in einen Amazon S3 Bucket hoch.

Schritt 3: Hochladen der Datei in einen Amazon S3 Bucket

In diesem Schritt erstellen Sie einen Amazon S3 Bucket und laden die Datendateien in diesen Bucket.

Benutzeranmeldeinformationen

Der Amazon Redshift COPY Redshift-Befehl muss Zugriff haben, um die Dateiobjekte im Amazon S3 S3-Bucket zu lesen. Wenn Sie dieselben Benutzeranmeldedaten verwenden, um den Amazon S3 S3-Bucket zu erstellen und den Amazon Redshift COPY Redshift-Befehl auszuführen, verfügt der COPY Befehl über alle erforderlichen Berechtigungen. Wenn Sie andere Benutzeranmeldeinformationen verwenden möchten, können Sie den Zugriff über die Amazon-S3-Zugriffssteuerungselemente gewähren. Der Amazon Redshift COPY Redshift-Befehl erfordert mindestens ListBucket GetObject Berechtigungen für den Zugriff auf die Dateiobjekte im Amazon S3 S3-Bucket. Weitere Informationen über Zugriffsrichtlinien für Amazon-S3-Ressourcen finden Sie unter Managing access permissions to your Amazon S3 resources (Verwaltung der Zugriffsberechtigungen zu Ihren Amazon-S3-Ressourcen).

Schritt 4: Erstellen der Beispieltabellen

Für dieses Tutorial verwenden Sie einen Satz von fünf Tabellen, die auf dem Star Schema Benchmark (SSB) -Schema basieren. Das folgende Diagramm zeigt das SSB Datenmodell.

Die SSB Tabellen sind möglicherweise bereits in der aktuellen Datenbank vorhanden. Falls ja, löschen Sie die Tabellen, um sie aus der Datenbank zu entfernen, bevor Sie sie mit den CREATE TABLE Befehlen im nächsten Schritt erstellen. Die in diesem Tutorial verwendeten Tabellen haben möglicherweise andere Attribute als die vorhandenen Tabellen.

Schritt 5: Führen Sie die COPY Befehle aus

Sie führen COPY Befehle aus, um jede der Tabellen im SSB Schema zu laden. Die COPY Befehlsbeispiele veranschaulichen das Laden aus verschiedenen Dateiformaten, die Verwendung verschiedener COPY Befehlsoptionen und die Behebung von Ladefehlern.

COPYBefehlssyntax

Die grundlegende COPY-Befehlssyntax ist wie folgt.

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

Um einen COPY Befehl auszuführen, 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 an alle vorhandenen Zeilen in der Tabelle an.

Spaltenliste

COPYLädt standardmäßig Felder der Reihe nach aus den Quelldaten 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 Column List in der COPY Befehlsreferenz.

Datenquelle

Sie können den COPY Befehl verwenden, um Daten aus einem Amazon S3 S3-Bucket, einem EMR Amazon-Cluster, einem Remote-Host über eine SSH Verbindung oder 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.

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 beinhalten eine IAM Rolle Amazon Resource Name (ARN). 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 Autorisierungsparameter in der COPY Befehlsreferenz. 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

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

Die SSB Tabellen werden geladen

Sie verwenden die folgenden COPY Befehle, um jede der Tabellen im SSB Schema zu laden. Der Befehl für jede Tabelle zeigt verschiedene COPY Optionen und Techniken zur Problembehandlung.

Gehen Sie folgendermaßen vor, um die SSB Tabellen zu laden:

Ersetzen Sie den Bucket-Namen und die AWS Anmeldeinformationen

Die COPY Befehle in diesem Tutorial werden im folgenden Format dargestellt.

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:

Laden Sie die PART Tabelle 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://amzn-s3-demo-bucket/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

CSVformatieren

CSV, was für kommagetrennte Werte steht, ist ein gängiges Format für den Import und Export von Tabellenkalkulationsdaten. CSVist flexibler als das kommagetrennte Format, da es Ihnen ermöglicht, Zeichenketten in Anführungszeichen in Felder einzufügen. Das standardmäßige Anführungszeichen für das COPY CSV Absenderformat ist ein doppeltes Anführungszeichen ("). Mit der Option AS können Sie jedoch auch ein anderes Anführungszeichen angeben. QUOTE Wenn Sie das Anführungszeichen innerhalb des Feldes verwenden, verwenden Sie ein weiteres Anführungszeichen als Escape-Zeichen.

Der folgende Auszug aus einer Datendatei im CSV -Format für die PART Tabelle zeigt Zeichenketten, 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 zu Fehlern führenCOPY. In dieser Übung finden Sie die Fehler und beheben sie.

Um Daten im CSV Format zu laden, fügen Sie Ihrem COPY Befehl etwas 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://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.

Um weitere Informationen zu dem Fehler zu erhalten, fragen Sie die ERRORS Tabelle STL _ LOAD _ ab. Die folgende Abfrage verwendet die SUBSTRING Funktion, um Spalten aus Gründen der Lesbarkeit zu verkürzen, und verwendet LIMIT 10, um die Anzahl der zurückgegebenen 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

NULLALS

Die part-csv.tbl Datendateien verwenden das NUL Abschlusszeichen (\x000oder\x0), um NULL Werte anzugeben.

COPYBehandelt ein NUL Abschlusszeichen standardmäßig als ein EOR Zeichen und beendet den Datensatz, was häufig zu unerwarteten Ergebnissen oder einem Fehler führt. Es gibt keine einheitliche Standardmethode für die Angabe von NULL Textdaten. Mit der NULL COPY AS-Befehlsoption können Sie daher angeben, durch welches Zeichen NULL beim Laden der Tabelle ersetzt werden soll. In diesem Beispiel COPY möchten Sie das NUL Abschlusszeichen als NULL Wert behandeln.

Führen PART Sie den NULL folgenden COPY Befehl aus, um mit der AS-Option 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 überprüfen, ob die NULL Werte COPY geladen sind, führen Sie den folgenden Befehl aus, um nur die Zeilen auszuwählen, die Folgendes 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 Sie die SUPPLIER Tabelle mit REGION

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

Zeichengetrenntes Format

Die Felder in einer zeichengetrennten Datei werden von einem speziellen Teichen, etwa einem senkrechten Strich (|), einem Komma (,) oder einem Tabulatorzeichen (\t) abgetrennt. Durch Zeichen getrennte Dateien können jedes einzelne ASCII Zeichen, einschließlich eines der nicht druckbaren Zeichen, als ASCII Trennzeichen verwendet werden. Sie geben das Trennzeichen mithilfe der Option an. DELIMITER Das Standardtrennzeichen ist der senkrechte Strich (|).

Der folgende Auszug aus den Daten für die SUPPLIER Tabelle verwendet ein durch Leerzeichen getrenntes Format.

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 REGION Option, 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 SUPPLIER Tabelle aus durch Pipen getrennten Daten in einem Amazon S3 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 SUPPLIER Tabelle aus durch Pipes getrennten Daten in einem Amazon S3 S3-Bucket in der Region USA Ost (Nord-Virginia) zu laden. Ä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 Sie die Tabelle mit CUSTOMER MANIFEST

In diesem Schritt verwenden Sie die MANIFEST OptionenFIXEDWIDTH, MAXERRORACCEPTINVCHARS, und, um die CUSTOMER Tabelle zu laden.

Die Beispieldaten für diese Übung enthalten Zeichen, die beim COPY Versuch, sie zu laden, Fehler verursachen. Sie verwenden die MAXERRORS Option und die ERRORS Systemtabelle STL _ LOAD _, um die Ladefehler zu beheben, und verwenden dann die MANIFEST Optionen ACCEPTINVCHARS und, um die Fehler zu 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 CUSTOMER Tabelle 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 mit fester Breite für die CUSTOMER Tabellendaten lautet wie folgt.

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'

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

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://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

Standardmäßig schlägt der Befehl fehl, wenn COPY zum ersten Mal ein Fehler auftritt, und es wird eine Fehlermeldung zurückgegeben. Um beim Testen Zeit zu sparen, können Sie die MAXERROR Option verwenden, um anzuweisenCOPY, dass eine bestimmte Anzahl von Fehlern übersprungen wird, bevor der Test fehlschlägt. Da beim ersten Test beim Laden der CUSTOMER Tabellendaten Fehler zu erwarten sind, sollten Sie maxerror 10 den COPY Befehl ergänzen.

Führen Sie den folgenden Befehl aus, um die Verwendung der MAXERROR Optionen FIXEDWIDTH und zu testen.

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 weist darauf hin, dass sieben Fehler COPY aufgetreten sind. Um die Fehler zu überprüfen, fragen Sie die ERRORS Tabelle STL LOAD _ _ 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;

Die Ergebnisse der STL _ LOAD _ ERRORS -Abfrage sollten in etwa 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:

Ein weiteres Problem mit der Last ist schwieriger zu erkennen – die Last führte zu unerwarteten Ergebnissen. Um dieses Problem zu untersuchen, führen Sie den folgenden Befehl aus, um die CUSTOMER Tabelle abzufragen.

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 sie eine überflüssige Datei COPY geladen haben,. customer-fw.tbl0000.bak

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

ACCEPTINVCHARS

Wenn COPY auf ein Zeichen gestoßen wird, das vom Datentyp der Spalte nicht unterstützt wird, wird standardmäßig die Zeile übersprungen und es wird ein Fehler zurückgegeben. Hinweise zu ungültigen UTF -8 Zeichen finden Sie unter. Fehler beim Laden von Multibyte-Zeichen

Sie könnten die MAXERRORS Option verwenden, um Fehler zu ignorieren und mit dem Laden fortzufahren, dann STL _ LOAD _ abfragen, ERRORS um die ungültigen Zeichen zu finden, und dann die Datendateien korrigieren. Sie eignet MAXERRORS sich jedoch am besten zur Behebung von Ladeproblemen und sollte generell nicht in einer Produktionsumgebung verwendet werden.

ACCEPTINVCHARSDiese Option ist normalerweise die bessere Wahl für die Verwaltung ungültiger Zeichen. ACCEPTINVCHARSweist COPY an, jedes ungültige Zeichen durch ein bestimmtes gültiges Zeichen zu ersetzen und mit dem Ladevorgang fortzufahren. Sie können jedes gültige ASCII Zeichen außer NULL als Ersatzzeichen angeben. Das Standard-Austauschzeichen ist ein Fragezeichen (?). ). COPYersetzt Multibyte-Zeichen durch eine Ersatzzeichenfolge gleicher Länge. Ein 4-Byte-Zeichen wird etwa durch ersetzt '????'.

COPYgibt die Anzahl der Zeilen zurück, die ungültige UTF -8 Zeichen enthielten. Außerdem wird der REPLACEMENTS Systemtabelle STL _ für jede betroffene Zeile ein Eintrag hinzugefügt, bis zu einem Maximum von 100 Zeilen pro Node-Slice. Zusätzliche ungültige UTF -8 Zeichen werden ebenfalls ersetzt, aber diese Ersetzungsereignisse werden nicht aufgezeichnet.

ACCEPTINVCHARSist nur für VARCHAR Spalten gültig.

Für diesen Schritt fügen Sie das ACCEPTINVCHARS mit dem Ersatzzeichen hinzu'^'.

MANIFEST

Wenn Sie COPY von Amazon S3 aus ein key prefix verwenden, besteht das Risiko, dass Sie unerwünschte Tabellen laden. Beispielsweise enthält der Ordner 's3://amzn-s3-demo-bucket/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 in einem JSON Format, das den eindeutigen Objektschlüssel für jede zu ladende Quelldatei explizit auflistet. 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"} 
    ]
}

Laden Sie die DWDATE Tabelle mit DATEFORMAT

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

COPYErwartet beim Laden von DATE TIMESTAMP Spalten das Standardformat, das YYYY-MM-DD für Datumsangaben und YYYY-MM-DD HH:MI:SS für Zeitstempel gilt. Wenn die Ladedaten kein Standardformat verwenden, können Sie DATEFORMAT und TIMEFORMAT verwenden, um das Format anzugeben.

Der folgende Auszug zeigt die Datumsformate in der DWDATE Tabelle. 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 unterschiedlichen Spalten, oder wenn das Format zum Zeitpunkt des Ladevorgangs nicht bekannt ist, verwenden Sie es DATEFORMAT mit dem 'auto' Argument. Wenn angegeben 'auto' wird, COPY erkennt jedes gültige Datums- oder Uhrzeitformat und konvertiert es in das Standardformat. Die 'auto' Option erkennt mehrere Formate, die bei Verwendung einer DATEFORMAT TIMEFORMAT UND-Zeichenfolge nicht unterstützt werden. Weitere Informationen finden Sie unter Verwenden der automatischen Erkennung mit DATEFORMAT und TIMEFORMAT.

Führen Sie den folgenden COPY Befehl aus, um die DWDATE Tabelle 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 Sie die LINEORDER Tabelle mit mehreren Dateien

In diesem Schritt werden die COMPUPDATE Optionen GZIP und verwendet, um die LINEORDER Tabelle zu laden.

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

GZIP, LZOP und BZIP2

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

COMPUPDATE

Beim COPY Laden einer leeren Tabelle ohne Komprimierungskodierungen werden die Ladedaten analysiert, um die optimalen Kodierungen zu ermitteln. 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 das Gerät ausschaltenCOMPUPDATE. Um eine genaue Auswertung der COPY Zeiten zu ermöglichen, COMPUPDATE schalten Sie für diesen Schritt aus.

Mehrere Dateien

Der COPY Befehl kann Daten sehr effizient laden, wenn er aus mehreren Dateien parallel statt aus einer einzigen Datei geladen wird. 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.

Der folgende Screenshot zeigt die Datendateien fürLINEORDER.

Schritt 6: Bereinigen und Analysieren der Datenbank

Immer wenn Sie eine beträchtliche Anzahl von Zeilen hinzufügen, löschen oder ändern, sollten Sie einen VACUUM Befehl und dann einen ANALYZE Befehl ausführen. Eine Bereinigung gewinnt den Speicherplatz gelöschter Zeilen zurück und stellt die Sortierfolge wieder her. Der ANALYZE Befehl aktualisiert die Statistik-Metadaten, sodass der Abfrageoptimierer genauere Abfragepläne generieren kann. Weitere Informationen finden Sie unter Bereinigen von Tabellen.

Wenn Sie die Daten in der Reihenfolge des Sortierschlüssels laden, geht die Bereinigung sehr schnell. In diesem Tutorial haben Sie eine große Zahl von Zeilen hinzugefügt, jedoch in leere Tabellen. Daher muss die Sortierung nicht wiederhergestellt werden; weiterhin haben Sie keine Zeilen gelöscht. COPYaktualisiert die Statistiken automatisch, nachdem eine leere Tabelle geladen wurde. Das sollte auch bei Ihren Statistiken der Fall sein up-to-date. Aus Gründen der guten Organisation schließen Sie dieses Tutorial jedoch ab, indem Sie ein Vacuuming und eine Analyse Ihrer Datenbank durchführen.

Führen Sie zum Bereinigen und Analysieren der Datenbank die folgenden Befehle aus.

vacuum;
analyze;

Schritt 7: Bereinigen Ihrer Ressourcen

Solange dieser ausgeführt wird, fallen Gebühren für Ihren Cluster an. Wenn Sie dieses Tutorial abgeschlossen haben, sollten Sie Ihre Umgebung wieder auf den ursprünglichen Zustand zurücksetzen, indem Sie die Schritte in Schritt 5: Aufheben des Zugriffs und Löschen Ihres Beispielclusters im Handbuch Erste Schritte in Amazon Redshift ausführen.

Wenn Sie den Cluster behalten, aber den von den SSB Tabellen verwendeten Speicher wiederherstellen möchten, führen Sie die folgenden Befehle aus.

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

Next

Übersicht

Übersicht

In diesem Tutorial haben Sie Datendateien in Amazon S3 hochgeladen und dann COPY Befehle verwendet, um die Daten aus den Dateien in Amazon Redshift Redshift-Tabellen zu laden.

Sie haben Daten unter Verwendung der folgenden Formate geladen:

Sie haben die ERRORS Systemtabelle STL LOAD _ _ verwendet, um Ladefehler zu beheben, und dann die OptionenREGION,MANIFEST,MAXERROR, ACCEPTINVCHARSDATEFORMAT, und NULL AS verwendet, um die Fehler zu beheben.

Zum Laden der Daten haben Sie die folgenden bewährten Verfahren verwendet:

Für weitere Informationen zu bewährten Verfahren für Amazon Redshift vgl. die folgenden Links: