Beispiele für COPY

Anmerkung

In den folgenden Beispielen werden aus Gründen der Lesbarkeit Zeilenumbrüche verwendet. Verwenden Sie in Ihrer Zeichenfolge credentials-args keine Zeilenumbrüche oder Leerzeichen.

Laden von FAVORITEMOVIES aus einer DynamoDB-Tabelle

Die AWS SDKs enthalten ein einfaches Beispiel für die Erstellung einer DynamoDB-Tabelle namens Movies. (Informationen zu diesem Beispiel finden Sie unter Erste Schritte mit DynamoDB.) Im folgenden Beispiel wird die Amazon-Redshift-Tabelle MOVIES mit Daten aus der DynamoDB-Tabelle geladen. Die Amazon-Redshift-Tabelle muss in der Datenbank bereits vorhanden sein.

copy favoritemovies from 'dynamodb://Movies'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
readratio 50;

Laden von LISTING aus einem Amazon-S3-Bucket

Im folgenden Beispiel wird LISTING aus einem Amazon-S3-Bucket geladen. Der COPY-Befehl lädt alle Dateien im Ordner /data/listing/.

copy listing
from 's3://mybucket/data/listing/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Laden von LISTING aus einem Amazon-EMR-Cluster

Im folgenden Beispiel wird die Tabelle SALES mit durch Tabulatoren getrennten Daten aus lzop-komprimierten Dateien in einem Amazon-EMR-Cluster geladen. COPY lädt jede Datei im Ordner myoutput/, die mit part- beginnt.

copy sales
from 'emr://j-SAMPLE2B500FC/myoutput/part-*' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '\t' lzop;

Im folgenden Beispiel wird die Tabelle SALES mit JSON-formatierten Daten in einem Amazon-EMR-Cluster geladen. COPY lädt jede Datei im Ordner myoutput/json/.

copy sales
from 'emr://j-SAMPLE2B500FC/myoutput/json/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
JSON 's3://mybucket/jsonpaths.txt';

Verwenden eines Manifests für die Angabe von Datendateien

Sie können ein Manifest verwenden, um sicherzustellen, dass der COPY-Befehl alle erforderlichen Dateien aus Amazon S3 und nur diese lädt. Sie können ein Manifest auch verwenden, wenn Sie mehrere Dateien aus verschiedenen Buckets laden müssen oder Dateien, die nicht dasselbe Präfix besitzen.

Angenommen, Sie müssen die folgenden drei Dateien laden: custdata1.txt, custdata2.txt und custdata3.txt. Sie könnten den folgenden Befehl verwenden, um alle Dateien in mybucket zu laden, die mit custdata beginnen, indem Sie ein Präfix angeben:

copy category
from 's3://mybucket/custdata' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Wenn aufgrund eines Fehlers nur zwei der Dateien vorhanden sind, lädt COPY nur diese beiden Dateien und wird erfolgreich abgeschlossen. Dies führt zu einem unvollständigen Datenladevorgang. Wenn der Bucket auch eine nicht erwünschte Datei enthält, die zufällig dasselbe Präfix verwendet, beispielsweise eine Datei namens custdata.backup, lädt COPY diese Datei ebenfalls. Dies führt dazu, dass nicht erwünschte Daten geladen werden.

Um sicherzustellen, dass alle erforderlichen Dateien geladen werden, und zu verhindern, dass nicht erwünschte Dateien geladen werden, können Sie eine Manifestdatei verwenden. Die Manifestdatei ist eine JSON formatierte Textdatei, die die Dateien auflistet, die durch den COPY-Befehl verarbeitet werden sollen. Beispielsweise lädt das folgende Manifest die drei Dateien aus dem vorherigen Beispiel.

{  
   "entries":[  
      {  
         "url":"s3://mybucket/custdata.1",
         "mandatory":true
      },
      {  
         "url":"s3://mybucket/custdata.2",
         "mandatory":true
      },
      {  
         "url":"s3://mybucket/custdata.3",
         "mandatory":true
      }
   ]
}

Das optionale mandatory-Flag gibt an, ob COPY beendet werden soll, wenn die Datei nicht vorhanden ist. Der Standardwert ist false. Unabhängig von obligatorischen Einstellungen wird COPY beendet, wenn keine Dateien gefunden werden. In diesem Beispiel gibt COPY einen Fehler zurück, wenn eine der Dateien nicht gefunden wird. Nicht erwünschte Dateien, die möglicherweise geladen werden, wenn Sie nur ein Schlüsselpräfix angeben, beispielsweise custdata.backup, werden ignoriert, da sie nicht im Manifest aufgelistet werden.

Beim Laden aus Datendateien im ORC- oder Parquet-Format ist ein meta-Feld erforderlich, wie im folgenden Beispiel gezeigt.

{  
   "entries":[  
      {  
         "url":"s3://mybucket-alpha/orc/2013-10-04-custdata",
         "mandatory":true,
         "meta":{  
            "content_length":99
         }
      },
      {  
         "url":"s3://mybucket-beta/orc/2013-10-05-custdata",
         "mandatory":true,
         "meta":{  
            "content_length":99
         }
      }
   ]
}

Im folgenden Beispiel wird ein Manifest namens verwendet cust.manifest.

copy customer
from 's3://mybucket/cust.manifest' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as orc
manifest;

Sie können ein Manifest verwenden, um Dateien aus verschiedenen Buckets hochzuladen oder Dateien, die nicht das gleiche Präfix verwenden. Das folgende Beispiel zeigt das JSON-Format, um Daten aus Dateien zu laden, deren Namen mit einem Datumsstempel beginnen.

{
  "entries": [
    {"url":"s3://mybucket/2013-10-04-custdata.txt","mandatory":true},
    {"url":"s3://mybucket/2013-10-05-custdata.txt","mandatory":true},
    {"url":"s3://mybucket/2013-10-06-custdata.txt","mandatory":true},
    {"url":"s3://mybucket/2013-10-07-custdata.txt","mandatory":true}
  ]
}

Das Manifest kann Dateien auflisten, die sich in verschiedenen Buckets befinden, sofern sich die Buckets in derselben AWS Region wie der Cluster befinden.

{
  "entries": [
    {"url":"s3://mybucket-alpha/custdata1.txt","mandatory":false},
    {"url":"s3://mybucket-beta/custdata1.txt","mandatory":false},
    {"url":"s3://mybucket-beta/custdata2.txt","mandatory":false}
  ]
}

Laden von LISTING aus einer Datei mit Pipe-Zeichen als Trennzeichen (Standardtrennzeichen)

Das folgende Beispiel stellt einen sehr einfachen Fall dar, in dem keine Optionen angegeben sind und die Eingabedatei das Standardtrennzeichen enthält, ein Pipe-Zeichen (|).

copy listing 
from 's3://mybucket/data/listings_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Laden von LISTING unter Verwendung von Spaltendaten im Parquet-Format

Im folgenden Beispiel werden Daten aus einem Ordner in einem von Amazon S3 benannten Parquet geladen.

copy listing 
from 's3://mybucket/data/listings/parquet/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as parquet;

Laden von LISTING unter Verwendung von Spaltendaten im ORC-Format

Im folgenden Beispiel werden Daten aus einem Ordner in Amazon S3 mit dem Namen orc geladen.

copy listing 
from 's3://mybucket/data/listings/orc/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as orc;

Laden von EVENT mit Optionen

Im folgenden Beispiel werden durch Pipe-Zeichen getrennte Daten in die Tabelle EVENT geladen und anschließend die folgenden Regeln angewendet:

• Wenn Zeichenfolgen durch Paare von Angebotszeichen eingeschlossen werden, werden sie entfernt.

• Sowohl leere Zeichenfolgen als auch Zeichenfolgen, die Leerzeichen enthalten, werden als NULL-Werte geladen.

• Der Ladevorgang schlägt fehl, wenn mehr als 5 Fehler zurückgegeben werden.

• Zeitstempelwerte müssen das angegebene Format einhalten. Ein gültiger Zeitstempel ist beispielsweise 2008-09-26 05:43:12.

copy event
from 's3://mybucket/data/allevents_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
removequotes
emptyasnull
blanksasnull
maxerror 5
delimiter '|'
timeformat 'YYYY-MM-DD HH:MI:SS';

Laden von VENUE aus einer Datendatei mit festen Spaltenbreiten

copy venue
from 's3://mybucket/data/venue_fw.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
fixedwidth 'venueid:3,venuename:25,venuecity:12,venuestate:2,venueseats:6';

Im vorherigen Beispiel wird angenommen, dass die Datendatei auf dieselbe Weise wie die gezeigten Beispieldateien formatiert ist. Im folgenden Beispiel dienen Leerzeichen als Platzhalter, sodass alle Spalten dieselbe Breite wie in der Spezifikation angegeben haben:

1  Toyota Park              Bridgeview  IL0
2  Columbus Crew Stadium    Columbus    OH0
3  RFK Stadium              Washington  DC0
4  CommunityAmerica BallparkKansas City KS0
5  Gillette Stadium         Foxborough  MA68756

Laden von CATEGORY aus einer CSV-Datei

Angenommen, Sie möchten CATEGORY mit den Werten laden, die in der folgenden Tabelle gezeigt werden.

catid	catgroup	catname	catdesc
12	Shows	Musicals	Musiktheater
13	Shows	Schauspiel	Theater, das kein Musiktheater ist
14	Shows	Oper	Alle Opern, sowohl leichte Opern als auch Rockopern
15	Konzerte	Klassisch	Alle Symphoniekonzerte, Konzerte und Chorkonzerte

Im folgenden Beispiel werden die Inhalte einer Textdatei gezeigt, deren Feldwerte durch Kommas getrennt sind.

12,Shows,Musicals,Musical theatre
13,Shows,Plays,All "non-musical" theatre  
14,Shows,Opera,All opera, light, and "rock" opera
15,Concerts,Classical,All symphony, concerto, and choir concerts

Wenn Sie die Datei mit dem Parameter DELIMITER laden, um Eingabedaten mit Kommas als Trennzeichen anzugeben, schlägt der COPY-Befehl fehl, da einige Eingabefelder Kommas enthalten. Sie können dieses Problem beheben, indem Sie den Parameter CSV verwenden und die Felder, die Kommas enthalten, in Anführungszeichen einschließen. Wenn ein Anführungszeichen innerhalb einer Zeichenfolge vorkommt, die durch Anführungszeichen eingeschlossen wird, müssen Sie dieses mit einer Escape-Markierung versehen, indem Sie das Anführungszeichen verdoppeln. Das Standardanführungszeichen ist ein doppeltes Anführungszeichen. Daher müssen Sie jedes doppelte Anführungszeichen mit einem zusätzlichen doppelten Anführungszeichen als Escape-Zeichen verwenden. Ihre neue Eingabedatei sieht ungefähr wie folgt aus.

12,Shows,Musicals,Musical theatre
13,Shows,Plays,"All ""non-musical"" theatre"
14,Shows,Opera,"All opera, light, and ""rock"" opera"
15,Concerts,Classical,"All symphony, concerto, and choir concerts"

Angenommen, der Dateiname ist category_csv.txt, dann können Sie die Datei mittels des folgenden COPY-Befehls laden:

copy category
from 's3://mybucket/data/category_csv.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
csv;

Um die Notwendigkeit zu vermeiden, die doppelten Anführungszeichen in Ihrer Eingabe durch Escape-Zeichen zu markieren, können Sie ein anderes Anführungszeichen angeben, indem Sie den Parameter QUOTE AS verwenden. Beispielsweise verwendet die folgende Version von category_csv.txt '%' als Anführungszeichen:

12,Shows,Musicals,Musical theatre
13,Shows,Plays,%All "non-musical" theatre%
14,Shows,Opera,%All opera, light, and "rock" opera%
15,Concerts,Classical,%All symphony, concerto, and choir concerts%

Der folgende COPY-Befehl verwendet QUOTE AS, um zu laden category_csv.txt:

copy category
from 's3://mybucket/data/category_csv.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
csv quote as '%';

Laden von VENUE mit expliziten Werte für eine IDENTITY-Spalte

Im folgenden Beispiel wird angenommen, dass bei Erstellung der Tabelle VENUE mindestens eine Spalte (beispielsweise die Spalte venueid) als IDENTITY-Spalte angegeben wurde. Dieser Befehl überschreibt das IDENTITY-Standardverhalten, bei dem Werte für eine IDENTITY-Spalte automatisch generiert werden, und lädt stattdessen die expliziten Werte aus der Datei venue.txt. Amazon Redshift überprüft nicht, ob doppelte IDENTITY-Werte in die Tabelle geladen werden, wenn die Option EXLICIT_IDS verwendet wird.

copy venue
from 's3://mybucket/data/venue.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
explicit_ids;

Laden von TIME aus einer GZIP-Datei mit Pipe-Zeichen als Trennzeichen

Im folgenden Beispiel wird die Tabelle TIME aus einer GZIP-Datei mit Pipe-Zeichen als Trennzeichen geladen:

copy time
from 's3://mybucket/data/timerows.gz' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
gzip
delimiter '|';

Laden eines Zeit- oder Datumsstempels

Im folgenden Beispiel werden Daten mit formatierten Zeitstempeln geladen.

Anmerkung

Das TIMEFORMAT von HH:MI:SS kann auch Bruchteile von Sekunden jenseits von SS bis zu einer Detailtiefe von Mikrosekunden unterstützen. Die in diesem Beispiel verwendete Datei time.txt enthält eine einzige Zeile, 2009-01-12 14:15:57.119568.

copy timestamp1 
from 's3://mybucket/data/time.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
timeformat 'YYYY-MM-DD HH:MI:SS';

Das Ergebnis dieser COPY-Operation ist wie folgt:

select * from timestamp1;
c1
----------------------------
2009-01-12 14:15:57.119568
(1 row)

Laden von Daten aus einer Datei mit Standardwerten

Im folgenden Beispiel wird eine Variante der Tabelle VENUE in der Datenbank TICKIT verwendet. Betrachten Sie die Tabelle VENUE_NEW, die mit der folgenden Anweisung definiert wird:

create table venue_new(
venueid smallint not null,
venuename varchar(100) not null,
venuecity varchar(30),
venuestate char(2),
venueseats integer not null default '1000');

Betrachten Sie die Datendatei venue_noseats.txt, die keine Werte für die Spalte VENUESEATS enthält, wie im folgenden Beispiel gezeigt:

1|Toyota Park|Bridgeview|IL|
2|Columbus Crew Stadium|Columbus|OH|
3|RFK Stadium|Washington|DC|
4|CommunityAmerica Ballpark|Kansas City|KS|
5|Gillette Stadium|Foxborough|MA|
6|New York Giants Stadium|East Rutherford|NJ|
7|BMO Field|Toronto|ON|
8|The Home Depot Center|Carson|CA|
9|Dick's Sporting Goods Park|Commerce City|CO|
10|Pizza Hut Park|Frisco|TX|

Die folgende COPY-Anweisung lädt die Tabelle erfolgreich aus der Datei und wendet den Standardwert (1000) auf die ausgelassene Spalte an:

copy venue_new(venueid, venuename, venuecity, venuestate) 
from 's3://mybucket/data/venue_noseats.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|';

Betrachten Sie nun die geladene Tabelle:

select * from venue_new order by venueid;
venueid |         venuename          |    venuecity    | venuestate | venueseats
---------+----------------------------+-----------------+------------+------------
1 | Toyota Park                | Bridgeview      | IL         |       1000
2 | Columbus Crew Stadium      | Columbus        | OH         |       1000
3 | RFK Stadium                | Washington      | DC         |       1000
4 | CommunityAmerica Ballpark  | Kansas City     | KS         |       1000
5 | Gillette Stadium           | Foxborough      | MA         |       1000
6 | New York Giants Stadium    | East Rutherford | NJ         |       1000
7 | BMO Field                  | Toronto         | ON         |       1000
8 | The Home Depot Center      | Carson          | CA         |       1000
9 | Dick's Sporting Goods Park | Commerce City   | CO         |       1000
10 | Pizza Hut Park             | Frisco          | TX         |       1000
(10 rows)

Im folgenden Beispiel wird zusätzlich zur Annahme, dass in der Datei keine VENUESEATS-Daten enthalten sind, auch angenommen, dass keine VENUENAME-Daten enthalten sind:

1||Bridgeview|IL|
2||Columbus|OH|
3||Washington|DC|
4||Kansas City|KS|
5||Foxborough|MA|
6||East Rutherford|NJ|
7||Toronto|ON|
8||Carson|CA|
9||Commerce City|CO|
10||Frisco|TX|

Bei Verwendung derselben Tabellendefinition schlägt die COPY-Anweisung fehl, da für VENUENAME kein DEFAULT-Wert angegeben wurde und VENUENAME eine NOT NULL-Spalte ist:

copy venue(venueid, venuecity, venuestate) 
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|';

Betrachten Sie nun eine Variante der Tabelle VENUE, die eine IDENTITY-Spalte verwendet:

create table venue_identity(
venueid int identity(1,1),
venuename varchar(100) not null,
venuecity varchar(30),
venuestate char(2),
venueseats integer not null default '1000');

Nehmen Sie wie im vorherigen Beispiel an, dass es für die Spalte VENUESEATS keine entsprechenden Werte in der Quelldatei gibt. Die folgende COPY-Anweisung lädt die Tabelle erfolgreich einschließlich der vordefinierten IDENTITY-Datenwerte, anstatt diese Werte automatisch zu generieren:

copy venue(venueid, venuename, venuecity, venuestate) 
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|' explicit_ids;

Diese Anweisung schlägt fehl, da sie die Spalte IDENTITY nicht enthält (VENUEID fehlt in der Spaltenliste), aber den Parameter EXPLICIT_IDS einschließt:

copy venue(venuename, venuecity, venuestate) 
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|' explicit_ids;

Diese Anweisung schlägt fehl, da sie den Parameter EXPLICIT_IDS nicht enthält:

copy venue(venueid, venuename, venuecity, venuestate)
from 's3://mybucket/data/venue_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|';

COPY-Operation für Daten mit der Option ESCAPE

Im folgenden Beispiel wird gezeigt, wie Zeichen geladen werden, die mit dem Trennzeichen übereinstimmen (in diesem Fall dem Pipe-Zeichen). Stellen Sie sicher, dass in der Eingabedatei alle Pipe-Zeichen (|), die Sie laden möchten, mit dem Backslash-Zeichen (\) als Escape-Zeichen markiert sind. Laden Sie die Datei anschließend unter Verwendung des Parameters ESCAPE.

$ more redshiftinfo.txt
1|public\|event\|dwuser
2|public\|sales\|dwuser

create table redshiftinfo(infoid int,tableinfo varchar(50));

copy redshiftinfo from 's3://mybucket/data/redshiftinfo.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
delimiter '|' escape;

select * from redshiftinfo order by 1;
infoid |       tableinfo
-------+--------------------
1      | public|event|dwuser
2      | public|sales|dwuser
(2 rows)

Ohne den Parameter ESCAPE schlägt dieser COPY-Befehl mit dem Fehler Extra column(s) found fehl.

Wichtig

Wenn Sie Ihre Daten mittels einer COPY-Operation mit dem Parameter ESCAPE laden, müssen Sie den Parameter ESCAPE auch für Ihren UNLOAD-Befehl angeben, um die reziproke Ausgabedatei zu generieren. Wenn Sie UNLOAD unter Verwendung des Parameters ESCAPE ausführen, müssen Sie ESCAPE verwenden, um eine COPY-Operation für diese Daten auszuführen.

Beispiele für die COPY-Operation aus JSON

In den folgenden Beispielen wird die Tabelle CATEGORY mit den folgenden Daten geladen.

CATID	CATGROUP	CATNAME	CATDESC
1	Sport	MLB	Major League Baseball
2	Sport	NHL	National Hockey League
3	Sport	NFL	National Football League
4	Sport	NBA	National Basketball Association
5	Konzerte	Klassisch	Alle Symphoniekonzerte, Konzerte und Chorkonzerte

Themen

• Laden von JSON-Daten unter Verwendung der Option „auto“
• Laden von JSON-Daten unter Verwendung der Option „auto ignorecase“
• Laden von JSON-Daten unter Verwendung einer JSONPaths-Datei
• Laden von JSON-Arrays unter Verwendung einer JSONPaths-Datei

Laden von JSON-Daten unter Verwendung der Option „auto“

Um JSON-Daten unter Verwendung der Option 'auto' zu laden, müssen die JSON-Daten aus einem Satz von Objekten bestehen. Die Schlüsselnamen müssen mit den Spaltennamen übereinstimmen. In diesem Fall spielt die Reihenfolge jedoch keine Rolle. Im folgenden werden die Inhalte einer Datei namens gezeigt category_object_auto.json.

{
    "catdesc": "Major League Baseball",
    "catid": 1,
    "catgroup": "Sports",
    "catname": "MLB"
}
{
    "catgroup": "Sports",
    "catid": 2,
    "catname": "NHL",
    "catdesc": "National Hockey League"
}{
    "catid": 3,
    "catname": "NFL",
    "catgroup": "Sports",
    "catdesc": "National Football League"
}
{
    "bogus": "Bogus Sports LLC",
    "catid": 4,
    "catgroup": "Sports",
    "catname": "NBA",
    "catdesc": "National Basketball Association"
}
{
    "catid": 5,
    "catgroup": "Shows",
    "catname": "Musicals",
    "catdesc": "All symphony, concerto, and choir concerts"
}

Um Daten aus der JSON-Datei im vorherigen Beispiel zu laden, führen Sie den folgenden COPY-Befehl aus.

copy category
from 's3://mybucket/category_object_auto.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 'auto';

Laden von JSON-Daten unter Verwendung der Option „auto ignorecase“

Um JSON-Daten unter Verwendung der Option 'auto ignorecase' zu laden, müssen die JSON-Daten aus einem Satz von Objekten bestehen. Die Groß- und Kleinschreibung der Schlüsselnamen muss nicht mit den Spaltennamen übereinstimmen und die Reihenfolge spielt keine Rolle. Im folgenden werden die Inhalte einer Datei namens gezeigt category_object_auto-ignorecase.json.

{
    "CatDesc": "Major League Baseball",
    "CatID": 1,
    "CatGroup": "Sports",
    "CatName": "MLB"
}
{
    "CatGroup": "Sports",
    "CatID": 2,
    "CatName": "NHL",
    "CatDesc": "National Hockey League"
}{
    "CatID": 3,
    "CatName": "NFL",
    "CatGroup": "Sports",
    "CatDesc": "National Football League"
}
{
    "bogus": "Bogus Sports LLC",
    "CatID": 4,
    "CatGroup": "Sports",
    "CatName": "NBA",
    "CatDesc": "National Basketball Association"
}
{
    "CatID": 5,
    "CatGroup": "Shows",
    "CatName": "Musicals",
    "CatDesc": "All symphony, concerto, and choir concerts"
}

Um Daten aus der JSON-Datei im vorherigen Beispiel zu laden, führen Sie den folgenden COPY-Befehl aus.

copy category
from 's3://mybucket/category_object_auto ignorecase.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 'auto ignorecase';

Laden von JSON-Daten unter Verwendung einer JSONPaths-Datei

Wenn die JSON-Datenobjekte den Spaltennamen nicht direkt entsprechen, können Sie eine JSONPaths-Datei verwenden, um die JSON-Elemente zu Spalten zuzuweisen. Die Reihenfolge der JSON-Quelldaten spielt keine Rolle. Die Reihenfolge der JSONPaths-Dateiausdrücke muss jedoch mit der Spaltenreihenfolge übereinstimmen. Angenommen, Sie verwenden die folgende Datendatei mit dem Namen category_object_paths.json.

{
    "one": 1,
    "two": "Sports",
    "three": "MLB",
    "four": "Major League Baseball"
}
{
    "three": "NHL",
    "four": "National Hockey League",
    "one": 2,
    "two": "Sports"
}
{
    "two": "Sports",
    "three": "NFL",
    "one": 3,
    "four": "National Football League"
}
{
    "one": 4,
    "two": "Sports",
    "three": "NBA",
    "four": "National Basketball Association"
}
{
    "one": 6,
    "two": "Shows",
    "three": "Musicals",
    "four": "All symphony, concerto, and choir concerts"
}

Die folgende JSONPaths-Datei namens category_jsonpath.json weist die Quelldaten zu den Tabellenspalten zu.

{
    "jsonpaths": [
        "$['one']",
        "$['two']",
        "$['three']",
        "$['four']"
    ]
}

Um Daten aus der JSON-Datei im vorherigen Beispiel zu laden, führen Sie den folgenden COPY-Befehl aus.

copy category
from 's3://mybucket/category_object_paths.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 's3://mybucket/category_jsonpath.json';

Laden von JSON-Arrays unter Verwendung einer JSONPaths-Datei

Um Daten aus JSON-Datendateien zu laden, die aus einem Satz von Arrays bestehen, müssen Sie eine JSONPaths-Datei verwenden, um die Array-Elemente zu Spalten zuzuweisen. Angenommen, Sie verwenden die folgende Datendatei mit dem Namen category_array_data.json.

[1,"Sports","MLB","Major League Baseball"]
[2,"Sports","NHL","National Hockey League"]
[3,"Sports","NFL","National Football League"]
[4,"Sports","NBA","National Basketball Association"]
[5,"Concerts","Classical","All symphony, concerto, and choir concerts"]

Die folgende JSONPaths-Datei namens category_array_jsonpath.json weist die Quelldaten zu den Tabellenspalten zu.

{
    "jsonpaths": [
        "$[0]",
        "$[1]",
        "$[2]",
        "$[3]"
    ]
}

Um Daten aus der JSON-Datei im vorherigen Beispiel zu laden, führen Sie den folgenden COPY-Befehl aus.

copy category
from 's3://mybucket/category_array_data.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 's3://mybucket/category_array_jsonpath.json';

Beispiele für die Kopie aus Avro

In den folgenden Beispielen wird die Tabelle CATEGORY mit den folgenden Daten geladen.

CATID	CATGROUP	CATNAME	CATDESC
1	Sport	MLB	Major League Baseball
2	Sport	NHL	National Hockey League
3	Sport	NFL	National Football League
4	Sport	NBA	National Basketball Association
5	Konzerte	Klassisch	Alle Symphoniekonzerte, Konzerte und Chorkonzerte

Themen

• Laden von Avro-Daten unter Verwendung der Option „auto“
• Laden von Avro-Daten unter Verwendung der Option „auto ignorecase“
• Laden von Avro-Daten unter Verwendung einer JSONPaths-Datei

Laden von Avro-Daten unter Verwendung der Option „auto“

Um Daten aus Avro-Datendateien unter Verwendung des Arguments 'auto' zu laden, müssen die Feldnamen im Avro-Schema mit den Spaltennamen übereinstimmen. Bei Verwendung des Arguments 'auto' spielt die Reihenfolge keine Rolle. Im folgenden wird das Schema für eine Datei namens gezeigt category_auto.avro.

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "catid", "type": "int"},
        {"name": "catdesc", "type": "string"},
        {"name": "catname", "type": "string"},
        {"name": "catgroup", "type": "string"},
}

Die Daten in einer Avro-Datei liegen im binären Format vor. Sie können daher nicht von Menschen gelesen werden. Im folgenden Beispiel wird eine JSON-Darstellung der Daten in der category_auto.avro-Datei gezeigt.

{
   "catid": 1,
   "catdesc": "Major League Baseball",
   "catname": "MLB",
   "catgroup": "Sports"
}
{
   "catid": 2,
   "catdesc": "National Hockey League",
   "catname": "NHL",
   "catgroup": "Sports"
}
{
   "catid": 3,
   "catdesc": "National Basketball Association",
   "catname": "NBA",
   "catgroup": "Sports"
}
{
   "catid": 4,
   "catdesc": "All symphony, concerto, and choir concerts",
   "catname": "Classical",
   "catgroup": "Concerts"
}

Um Daten aus der Avro-Datei im vorherigen Beispiel zu laden, führen Sie den folgenden COPY-Befehl aus.

copy category
from 's3://mybucket/category_auto.avro'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as avro 'auto';

Laden von Avro-Daten unter Verwendung der Option „auto ignorecase“

Um Daten aus Avro-Datendateien unter Verwendung des Arguments 'auto ignorecase' zu laden, muss die Groß-/Kleinschreibung der Feldnamen im Avro-Schema nicht mit den Spaltennamen übereinstimmen. Bei Verwendung des Arguments 'auto ignorecase' spielt die Reihenfolge keine Rolle. Im folgenden wird das Schema für eine Datei namens gezeigt category_auto-ignorecase.avro.

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "CatID", "type": "int"},
        {"name": "CatDesc", "type": "string"},
        {"name": "CatName", "type": "string"},
        {"name": "CatGroup", "type": "string"},
}

Die Daten in einer Avro-Datei liegen im binären Format vor. Sie können daher nicht von Menschen gelesen werden. Im folgenden Beispiel wird eine JSON-Darstellung der Daten in der category_auto-ignorecase.avro-Datei gezeigt.

{
   "CatID": 1,
   "CatDesc": "Major League Baseball",
   "CatName": "MLB",
   "CatGroup": "Sports"
}
{
   "CatID": 2,
   "CatDesc": "National Hockey League",
   "CatName": "NHL",
   "CatGroup": "Sports"
}
{
   "CatID": 3,
   "CatDesc": "National Basketball Association",
   "CatName": "NBA",
   "CatGroup": "Sports"
}
{
   "CatID": 4,
   "CatDesc": "All symphony, concerto, and choir concerts",
   "CatName": "Classical",
   "CatGroup": "Concerts"
}

Um Daten aus der Avro-Datei im vorherigen Beispiel zu laden, führen Sie den folgenden COPY-Befehl aus.

copy category
from 's3://mybucket/category_auto-ignorecase.avro'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
format as avro 'auto ignorecase';

Laden von Avro-Daten unter Verwendung einer JSONPaths-Datei

Wenn die Feldnamen im Avro-Schema den Spaltennamen nicht direkt entsprechen, können Sie eine JSONPaths-Datei verwenden, um die Schema-Elemente zu Spalten zuzuweisen. Die Reihenfolge der JSONPaths-Dateiausdrücke muss mit der Reihenfolge der Spalten übereinstimmen.

Angenommen, Sie verwenden eine Datendatei namens category_paths.avro, die dieselben Daten wie im vorherigen Beispiel enthält, jedoch mit dem folgenden Schema.

{
    "name": "category",
    "type": "record",
    "fields": [
        {"name": "id", "type": "int"},
        {"name": "desc", "type": "string"},
        {"name": "name", "type": "string"},
        {"name": "group", "type": "string"},
        {"name": "region", "type": "string"} 
     ]
}

Die folgende JSONPaths-Datei namens category_path.avropath weist die Quelldaten zu den Tabellenspalten zu.

{
    "jsonpaths": [
        "$['id']",
        "$['group']",
        "$['name']",
        "$['desc']"
    ]
}

Um Daten aus der Avro-Datei im vorherigen Beispiel zu laden, führen Sie den folgenden COPY-Befehl aus.

copy category
from 's3://mybucket/category_object_paths.avro'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
format avro 's3://mybucket/category_path.avropath ';

Vorbereiten von Dateien auf die COPY-Operation mit der Option ESCAPE

Im folgenden Beispiel wird beschrieben, wie Sie Daten vorbereiten können, damit Zeichen für neue Zeilen mit Escape-Zeichen markiert werden, bevor die Daten unter Verwendung des COPY-Befehls mit dem Parameter ESCAPE in eine Amazon-Redshift-Tabelle geladen werden. Wenn die Zeichen für neue Zeilen in den Daten nicht mit Escape-Zeichen markiert werden, gibt Amazon Redshift Ladefehler zurück, sobald Sie den COPY-Befehl ausführen, da die Zeichen für neue Zeilen normalerweise als Datensatztrennzeichen verwendet werden.

Betrachten Sie beispielsweise eine Datei oder eine Spalte in einer externen Tabelle, die Sie in eine Amazon-Redshift-Tabelle kopieren möchten. Wenn die Datei oder Spalte XML-formatierte Inhalte oder ähnliche Daten enthält, müssen Sie sicherstellen, dass alle Zeichen für neue Zeilen (\n), die Teil des Inhalts sind, mit dem Backslash-Zeichen (\) als Escape-Zeichen markiert werden.

Eine Datei oder Tabelle, die eingebettete Zeichen für Zeilenumbrüche enthält, bietet ein vergleichsweise einfaches Muster für den Vergleich. Die eingebetteten Zeichen für neue Zeilen folgen wahrscheinlich meistens einem >-Zeichen, wobei sich dazwischen möglicherweise einige Leerstellen (' ' oder Tabulatorzeichen) befinden, wie Sie im folgenden Beispiel für eine Textdatei namens nlTest1.txt sehen können.

$ cat nlTest1.txt
<xml start>
<newline characters provide>
<line breaks at the end of each>
<line in content>
</xml>|1000
<xml>
</xml>|2000

Im folgenden Beispiel können Sie ein Textverarbeitungsprogramm ausführen, um die Quelldatei vorab zu bearbeiten und an den nötigen Stellen Escape-Zeichen einzufügen. (Das Zeichen | soll als Trennzeichen verwendet werden, um Spaltendaten beim Kopieren in eine Amazon-Redshift-Tabelle zu trennen).

$ sed -e ':a;N;$!ba;s/>[[:space:]]*\n/>\\\n/g' nlTest1.txt > nlTest2.txt

Ähnlich können Sie Perl verwenden, um eine vergleichbare Operation auszuführen:

cat nlTest1.txt | perl -p -e 's/>\s*\n/>\\\n/g' > nlTest2.txt

Um die Daten aus der Datei nlTest2.txt in Amazon Redshift laden zu können, wurde in eine Tabelle mit zwei Spalten erstellt. Die erste Spalte c1 ist eine Zeichenspalte, die XML-formatierte Inhalte aus der Datei nlTest2.txt aufnimmt. Die zweite Spalte c2 enthält Ganzzahlwerte, die aus derselben Datei geladen wurden.

Nach Ausführung des Befehls sed können Sie Daten unter Verwendung des Parameters ESCAPE korrekt aus der Datei nlTest2.txt in eine Amazon-Redshift-Tabelle laden.

Anmerkung

Wenn Sie den COPY-Befehl zusammen mit dem Parameter ESCAPE verwenden, wird eine Reihe von Sonderzeichen mit dem Escape-Zeichen markiert, zu denen auch das Backslash-Zeichen gehört (einschließlich des Zeichens für neue Zeilen).

copy t2 from 's3://mybucket/data/nlTest2.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'  
escape
delimiter as '|';

select * from t2 order by 2;

c1           |  c2
-------------+------
<xml start>
<newline characters provide>
<line breaks at the end of each>
<line in content>
</xml>
| 1000
<xml>
</xml>       | 2000
(2 rows)

Sie können Datendateien, die aus externen Datenbanken exportiert wurden, auf ähnliche Weise vorbereiten. Im Fall einer Oracle-Datenbank können Sie beispielsweise die Funktion REPLACE auf alle betroffenen Spalten in einer Tabelle anwenden, die Sie zu Amazon Redshift kopieren möchten.

SELECT c1, REPLACE(c2, \n',\\n' ) as c2 from my_table_with_xml

Zusätzlich stellen zahlreiche Tools für den Export sowie das Extrahieren, das Transformieren und das Laden (Extract, Transform, Load, ETL) von Datenbanken, die routinemäßig große Datenmengen verarbeiten, Optionen für die Angabe von Escape- und Trennzeichen bereit.

Laden eines Shapefile in Amazon Redshift

In den folgenden Beispielen wird gezeigt, wie Sie ein Esri-Shapefile mit COPY laden. Weitere Informationen zum Laden von Shapefiles finden Sie unter Laden eines Shapefile in Amazon Redshift.

Laden eines Shapefile

Die folgenden Schritte zeigen, wie Sie mit dem Befehl COPY OpenStreetMap Daten aus Amazon S3 aufnehmen. In diesem Beispiel wird davon ausgegangen, dass das norwegische Shapefile-Archiv von der Download-Site von Geofabrik in einen privaten Amazon S3 S3-Bucket in Ihrer Region hochgeladen wurde. AWS Die Dateien .shp, .shx und.dbf müssen dasselbe Amazon-S3-Präfix und denselben Dateinamen haben.

Erfassung von Daten ohne Vereinfachung

Mit den folgenden Befehlen werden Tabellen erstellt und Daten erfasst, die ohne Vereinfachung in die maximale Geometriegröße passen. Öffnen Sie gis_osm_natural_free_1.shp in Ihrer bevorzugten GIS-Software und überprüfen Sie die Spalten in diesem Layer. Standardmäßig werden entweder IDENTITY- oder GEOMETRY-Spalten zuerst angezeigt. Wenn eine GEOMETRY-Spalte zuerst angezeigt wird, können Sie die Tabelle wie folgt erstellen.

CREATE TABLE norway_natural (
   wkb_geometry GEOMETRY,
   osm_id BIGINT,
   code INT,
   fclass VARCHAR,
   name VARCHAR);            

Wenn eine IDENTITY-Spalte an erster Stelle steht, können Sie die Tabelle wie folgt erstellen.

CREATE TABLE norway_natural_with_id (
   fid INT IDENTITY(1,1),
   wkb_geometry GEOMETRY,
   osm_id BIGINT,
   code INT,
   fclass VARCHAR,
   name VARCHAR);            

Jetzt können Sie die Daten mit COPY erfassen.

COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO: Load into table 'norway_natural' completed, 83891 record(s) loaded successfully

Oder Sie können die Daten wie folgt erfassen.

COPY norway_natural_with_id FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO: Load into table 'norway_natural_with_id' completed, 83891 record(s) loaded successfully.

Erfassung von Daten mit Vereinfachung

Mit den folgenden Befehlen wird eine Tabelle erstellt und es wird versucht, Daten zu erfassen, die ohne Vereinfachung nicht in die maximale Geometriegröße passen. Untersuchen Sie das gis_osm_water_a_free_1.shp-Shapefile und erstellen Sie die entsprechende Tabelle wie folgt.

CREATE TABLE norway_water (
   wkb_geometry GEOMETRY,
   osm_id BIGINT,
   code INT,
   fclass VARCHAR,
   name VARCHAR);

Wenn der Befehl COPY ausgeführt wird, führt dies zu einem Fehler.

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
ERROR:  Load into table 'norway_water' failed.  Check 'stl_load_errors' system table for details.              

Eine Abfrage von STL_LOAD_ERRORS zeigt an, dass die Geometrie zu groß ist.

SELECT line_number, btrim(colname), btrim(err_reason) FROM stl_load_errors WHERE query = pg_last_copy_id();
 line_number |    btrim     |                                 btrim
-------------+--------------+-----------------------------------------------------------------------
     1184705 | wkb_geometry | Geometry size: 1513736 is larger than maximum supported size: 1048447              

Zur Vereinfachung der Geometrien wird der Parameter SIMPLIFY AUTO zum COPY-Befehl hinzugefügt.

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp'
FORMAT SHAPEFILE
SIMPLIFY AUTO
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';

INFO:  Load into table 'norway_water' completed, 1989196 record(s) loaded successfully.              

Um die Zeilen und Geometrien anzuzeigen, die vereinfacht wurden, fragen Sie ab SVL_SPATIAL_SIMPLIFY.

SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id();
 query | line_number | maximum_tolerance | initial_size | simplified | final_size |   final_tolerance
-------+-------------+-------------------+--------------+------------+------------+----------------------
    20 |     1184704 |                -1 |      1513736 | t          |    1008808 |   1.276386653895e-05
    20 |     1664115 |                -1 |      1233456 | t          |    1023584 | 6.11707814796635e-06               

Die Verwendung von SIMPLIFY AUTO max_tolerance mit einer Toleranz, die niedriger ist als die automatisch berechnete, führt wahrscheinlich zu einem Einlesefehler. In diesem Fall sollten Sie MAXERROR verwenden, um Fehler zu ignorieren.

COPY norway_water FROM 's3://bucket_name/shapefiles/norway/gis_osm_water_a_free_1.shp'
FORMAT SHAPEFILE
SIMPLIFY AUTO 1.1E-05
MAXERROR 2
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';

INFO:  Load into table 'norway_water' completed, 1989195 record(s) loaded successfully.
INFO:  Load into table 'norway_water' completed, 1 record(s) could not be loaded.  Check 'stl_load_errors' system table for details.               

Fragen Sie SVL_SPATIAL_SIMPLIFY erneut ab, um den Datensatz zu identifizieren, den COPY nicht laden konnte.

SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id();
 query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance
-------+-------------+-------------------+--------------+------------+------------+-----------------
    29 |     1184704 |           1.1e-05 |      1513736 | f          |          0 |               0
    29 |     1664115 |           1.1e-05 |      1233456 | t          |     794432 |         1.1e-05             

In diesem Beispiel konnte der erste Datensatz nicht eingefügt werden, sodass die Spalte simplified falsch angezeigt wird. Der zweite Datensatz wurde innerhalb der vorgegebenen Toleranz geladen. Die endgültige Größe ist jedoch größer als bei Verwendung der automatisch berechneten Toleranz ohne Angabe der maximalen Toleranz.

Laden aus einem komprimierten Shapefile

Die COPY-Funktion von Amazon Redshift unterstützt die Erfassung von Daten aus einem komprimierten Shapefile. Alle Shapefile-Komponenten müssen dasselbe Amazon-S3-Präfix und dasselbe Komprimierungssuffix aufweisen. Nehmen wir an, Sie möchten die Daten aus dem vorherigen Beispiel laden. In diesem Fall müssen sich die Dateien gis_osm_water_a_free_1.shp.gz, gis_osm_water_a_free_1.dbf.gz und gis_osm_water_a_free_1.shx.gz das gleiche Amazon-S3-Verzeichnis teilen. Der COPY-Befehl erfordert die Option GZIP, und in der FROM-Klausel muss die richtige komprimierte Datei angegeben werden, wie im Folgenden gezeigt.

COPY norway_natural FROM 's3://bucket_name/shapefiles/norway/compressed/gis_osm_natural_free_1.shp.gz'
FORMAT SHAPEFILE
GZIP
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO:  Load into table 'norway_natural' completed, 83891 record(s) loaded successfully.          

Laden von Daten in eine Tabelle mit einer anderen Spaltenreihenfolge

Wenn Sie eine Tabelle haben, die nicht GEOMETRY als erste Spalte hat, können Sie die Spalten-Mapping verwenden, um die Spalten der Zieltabelle zuzuordnen. Erstellen Sie z. B. eine Tabelle mit osm_id als erste Spalte.

CREATE TABLE norway_natural_order (
   osm_id BIGINT,
   wkb_geometry GEOMETRY,
   code INT,
   fclass VARCHAR,
   name VARCHAR);         

Laden Sie dann ein Shapefile unter Verwendung des Spalten-Mappings.

COPY norway_natural_order(wkb_geometry, osm_id, code, fclass, name) 
FROM 's3://bucket_name/shapefiles/norway/gis_osm_natural_free_1.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRoleName';
INFO:  Load into table 'norway_natural_order' completed, 83891 record(s) loaded successfully.         

Laden von Daten in eine Tabelle mit einer Geografiespalte

Wenn Sie eine Tabelle mit einer GEOGRAPHY-Spalte haben, erfassen Sie zuerst in eine GEOMETRY-Spalte und wandeln dann die Objekte in GEOGRAPHY-Objekte um. Zum Beispiel: Nachdem Sie Ihr Shapefile in eine GEOMETRY-Spalte kopiert haben, ändern Sie die Tabelle, um eine Spalte mit dem Datentyp GEOGRAPHY hinzuzufügen.

ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY;

Dann konvertieren Sie die Geometrien in Geografien.

UPDATE norway_natural SET wkb_geography = wkb_geometry::geography;

Optional können Sie auch die GEOMETRY-Spalte entfernen.

ALTER TABLE norway_natural DROP COLUMN wkb_geometry;

COPY-Befehl mit der Option NOLOAD

Verwenden Sie die Option NOLOAD mit dem COPY-Befehl, um Datendateien zu validieren, bevor sie tatsächlich geladen werden. Amazon Redshift analysiert die Eingabedatei und zeigt alle auftretenden Fehler an. Das folgende Beispiel verwendet die Option NOLOAD und es werden tatsächlich keine Zeilen in die Tabelle geladen.

COPY public.zipcode1
FROM 's3://mybucket/mydata/zipcode.csv' 
DELIMITER ';' 
IGNOREHEADER 1 REGION 'us-east-1'
NOLOAD
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/myRedshiftRole';

Warnings:
Load into table 'zipcode1' completed, 0 record(s) loaded successfully.