Esempi di COPY

Nota

Questi esempi contengono interruzioni di riga per una migliore leggibilità. Non includere interruzioni di riga o spazi nella stringa credentials-args.

Caricamento di FAVORITEMOVIES da una tabella DynamoDB

Gli AWS SDK includono un semplice esempio di creazione di una tabella DynamoDB chiamata Movies. (Per questo esempio, consultare Nozioni di base su DynamoDB.) Nell'esempio seguente la tabella MOVIES di Amazon Redshift viene caricata con i dati della tabella DynamoDB. La tabella di Amazon Redshift deve esistere già nel database.

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

Caricamento di LISTING da un bucket Amazon S3

L'esempio seguente carica LISTING da un bucket Amazon S3. Il comando COPY carica tutti i file contenuti nella cartella /data/listing/.

copy listing
from 's3://DOC-EXAMPLE-BUCKET/data/listing/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Caricamento di LISTING da un cluster Amazon EMR

L'esempio seguente carica la tabella SALES con i dati delimitati da schede da file compressi lzop in un cluster Amazon EMR. COPY carica ogni file nella cartella myoutput/ che inizia con part-.

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

L'esempio seguente carica la tabella SALES con i dati con dati formattati in JSON in un cluster Amazon EMR. COPY carica ogni file nella cartella myoutput/json/.

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

Utilizzo di un manifest per specificare i fili di dati

È possibile utilizzare un manifest per assicurarsi che il comando COPY carichi tutti i file richiesti, e solo i file richiesti, da Amazon S3. È anche possibile utilizzare un manifest quando è necessario caricare più file da bucket o file diversi che non condividono lo stesso prefisso.

Ad esempio, supponi di dover caricare i seguenti tre file: custdata1.txt, custdata2.txt e custdata3.txt. È possibile usare il seguente comando per caricare tutti i file in DOC-EXAMPLE-BUCKET che iniziano con custdata specificando un prefisso:

copy category
from 's3://DOC-EXAMPLE-BUCKET/custdata' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Se solo due dei file esistono a causa di un errore, COPY carica solo questi due file e terminerà con successo, con un caricamento di dati che risulterà incompleto. Se il bucket contiene anche un file indesiderato che utilizza lo stesso prefisso, come ad esempio un file chiamato custdata.backup, COPY carica anche quel file, caricando così dati indesiderati.

Per assicurarsi che tutti i file richiesti siano caricati e per evitare che vengano caricati file indesiderati, è possibile utilizzare un file manifest. Il manifest è un file di testo con formattazione JSON che elenca i file che devono essere elaborati dal comando COPY. Ad esempio, il seguente manifest carica i tre file nell'esempio precedente.

{  
   "entries":[  
      {  
         "url":"s3://DOC-EXAMPLE-BUCKET/custdata.1",
         "mandatory":true
      },
      {  
         "url":"s3://DOC-EXAMPLE-BUCKET/custdata.2",
         "mandatory":true
      },
      {  
         "url":"s3://DOC-EXAMPLE-BUCKET/custdata.3",
         "mandatory":true
      }
   ]
}

Il flag opzionale mandatory indica se COPY deve terminare se il file non esiste. Il valore predefinito è false. Indipendentemente da eventuali impostazioni obbligatorie, COPY termina se non vengono trovati file. In questo esempio, COPY restituisce un errore se uno qualsiasi dei file non viene trovato. I file indesiderati che potrebbero essere stati raccolti se hai specificato solo un prefisso della chiave, come custdata.backup, vengono ignorati, perché non sono sul manifest.

Quando si carica da file di dati in formato ORC o Parquet, è necessario un campo meta, come mostrato nell'esempio seguente.

{  
   "entries":[  
      {  
         "url":"s3://DOC-EXAMPLE-BUCKET1/orc/2013-10-04-custdata",
         "mandatory":true,
         "meta":{  
            "content_length":99
         }
      },
      {  
         "url":"s3://DOC-EXAMPLE-BUCKET2/orc/2013-10-05-custdata",
         "mandatory":true,
         "meta":{  
            "content_length":99
         }
      }
   ]
}

L'esempio seguente utilizza un manifest denominato cust.manifest.

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

È possibile utilizzare un manifest per caricare file da diversi bucket o file che non condividono lo stesso prefisso. L'esempio seguente mostra il JSON per caricare dati con file i cui nomi iniziano con uno stamp di data.

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

Il manifest può elencare i file che si trovano in bucket diversi, purché i bucket si trovino nella stessa AWS regione del cluster.

{
  "entries": [
    {"url":"s3://DOC-EXAMPLE-BUCKET1/custdata1.txt","mandatory":false},
    {"url":"s3://DOC-EXAMPLE-BUCKET2/custdata1.txt","mandatory":false},
    {"url":"s3://DOC-EXAMPLE-BUCKET2/custdata2.txt","mandatory":false}
  ]
}

Caricamento di LISTING da un file delimitato da pipe (Delimitatore predefinito)

L'esempio seguente è un caso molto semplice in cui non sono specificate opzioni e il file di input contiene il delimitatore predefinito, un carattere pipe ("|").

copy listing 
from 's3://DOC-EXAMPLE-BUCKET/data/listings_pipe.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole';

Caricamento di LISTING utilizzando i dati a colonna nel formato Parquet

Il seguente esempio carica i dati da una cartella su Amazon S3 chiamata parquet.

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

Caricamento di LISTING utilizzando i dati a colonna nel formato ORC

Il seguente esempio carica i dati da una cartella su Amazon S3 chiamata orc.

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

Caricamento di EVENT con le opzioni

Il seguente esempio carica i dati delimitati da pipe nella tabella EVENT e applica le seguenti regole:

• Se si utilizzano coppie di virgolette per circondare una qualsiasi stringa di caratteri, vengono rimosse.

• Sia le stringhe vuote sia quelle che contengono spazi vuoti vengono caricate come valori NULL.

• Il caricamento non riesce se vengono restituiti più di 5 errori.

• I valori di timestamp devono essere conformi al formato specificato; ad esempio, un timestamp valido è 2008-09-26 05:43:12.

copy event
from 's3://DOC-EXAMPLE-BUCKET/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';

Caricamento di VENUE da un file di dati a larghezza fissa

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

L'esempio precedente assume un file di dati formattato nello stesso modo dei dati campione mostrati. Nel seguente esempio, gli spazi fungono da placeholder in modo che tutte le colonne abbiano la stessa larghezza indicata nelle specifiche:

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

Caricamento di CATEGORY da un file CSV

Supponi di voler caricare CATEGORY con i valori indicati nella tabella seguente.

catid	catgroup	catname	catdesc
12	Spettacoli	Musical	Musical teatrali
13	Spettacoli	Rappresentazioni	Tutto il teatro non musicale
14	Spettacoli	Opera	Tutta l'opera lirica, leggera e "rock"
15	Concerti	Classici	Tutti i concerti sinfonici, concertistici e corali

L'esempio seguente mostra il contenuto di un file di testo con i valori dei campi separati da virgole.

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

Se carichi il file utilizzando il parametro DELIMITER per specificare l'input delimitato da virgole, il comando COPY non riesce perché alcuni campi di input contengono virgole. È possibile evitare questo problema utilizzando il parametro CSV e racchiudendo i campi che contengono virgole tra virgolette. Se il carattere virgolette viene visualizzato all'interno di una stringa tra virgolette, è necessario eseguirne l'escape raddoppiando le virgolette. Il carattere di virgoletta di default è una virgoletta doppia, quindi è necessario eseguire l'escape di ogni virgoletta doppia con una virgoletta doppia aggiuntiva. Il nuovo file di input è simile a questo.

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"

Supponendo che il nome del file sia category_csv.txt, è possibile caricare il file utilizzando il seguente comando COPY:

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

In alternativa, per evitare di dover creare una sequenza di escape per le doppie virgolette nell'input, è possibile specificare un carattere virgolette diverso utilizzando il parametro QUOTE AS. Per esempio, la seguente versione di category_csv.txt utilizza "%" come carattere virgolette.

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%

Il seguente comando COPY utilizza QUOTE AS per caricare category_csv.txt:

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

Caricamento di VENUE con valori espliciti per una colonna IDENTITY

L'esempio seguente presuppone che quando è stata creata la tabella VENUE almeno una colonna (come la colonna venueid) sia stata specificata come colonna IDENTITY. Questo comando sostituisce il comportamento di IDENTITY predefinito dei valori autogeneranti per una colonna IDENTITY e carica invece i valori espliciti dal file venue.txt. Amazon Redshift non verifica se i valori IDENTITY duplicati vengono caricati nella tabella quando utilizza l'opzione EXLICIT_IDS.

copy venue
from 's3://DOC-EXAMPLE-BUCKET/data/venue.txt' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
explicit_ids;

Caricamento di TIME da un file GZIP delimitato da pipe

L'esempio seguente carica la tabella TIME da un file GZIP delimitato dal pipe:

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

Caricamento di un Timestamp o di un Datestamp

L'esempio seguente carica i dati con un timestamp formattato.

Nota

Il TIMEFORMAT di HH:MI:SS può anche supportare i secondi frazionari oltre il SS a un livello di dettaglio di microsecondi. Il file time.txt utilizzato in questo esempio contiene una riga 2009-01-12 14:15:57.119568.

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

Il risultato di questa copia è il seguente:

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

Caricamento dei dati da un file con valori predefiniti

L'esempio seguente usa una variazione della tabella VENUE nel database TICKIT. Considera una tabella VENUE_NEW definita con la seguente dichiarazione:

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

Considera un file di dati venue_noseats.txt che non contiene valori per la colonna VENUESEATS, come mostrato nell'esempio seguente:

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|

La seguente dichiarazione COPY caricherà la tabella dal file e applicherà il valore DEFAULT ("1000") alla colonna omessa:

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

Ora visualizza la tabella caricata:

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)

Per l'esempio seguente, oltre a supporre che nel file non siano inclusi dati VENUESEATS, supponi anche che non siano inclusi dati VENUENAME:

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|

Usando la stessa definizione della tabella, la seguente dichiarazione COPY non riesce perché non è stato specificato alcun valore DEFAULT per VENUENAME, il quale è una colonna NOT NULL:

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

Considera ora una variazione della tabella VENUE che utilizza una colonna IDENTITY:

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

Come per l'esempio precedente, supponi che la colonna VENUESEATS non abbia valori corrispondenti nel file sorgente. La seguente dichiarazione COPY carica correttamente la tabella, inclusi i valori dei dati IDENTITY predefiniti, invece di generare tali valori autonomamente:

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

Questa dichiarazione non riesce perché non include la colonna IDENTITY (VENUEID è assente dall'elenco delle colonne) ma include un parametro EXPLICIT_IDS:

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

Questa dichiarazione non riesce perché non include un parametro EXPLICIT_IDS:

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

Dati COPY con l'opzione ESCAPE

L'esempio seguente mostra come caricare i caratteri che corrispondono al carattere del delimitatore (in questo caso, il carattere pipe). Nel file di input, assicurati che tutti i caratteri pipe (|) che desideri caricare siano resi una sequenza di escape con il carattere di barra rovesciata (\). Quindi carica il file con il parametro ESCAPE.

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

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

copy redshiftinfo from 's3://DOC-EXAMPLE-BUCKET/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)

Senza il parametro ESCAPE, questo comando COPY fallisce con un errore Extra column(s) found.

Importante

Se carichi i dati utilizzando un COPY con il parametro ESCAPE, è necessario specificare anche il parametro ESCAPE con il comando UNLOAD per generare il file di output reciproco. Allo stesso modo, se utilizzi UNLOAD usando il parametro ESCAPE, è necessario usare ESCAPE quando effettui un COPY sugli stessi dati.

Esempi di copia da JSON

Negli esempi seguenti, carica la tabella CATEGORY con i seguenti dati.

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	Concerti	Classici	Tutti i concerti sinfonici, concertistici e corali

Argomenti

• Caricamento da dati JSON utilizzando l'opzione "auto".
• Caricamento da dati JSON utilizzando l'opzione 'auto ignorecase'
• Caricamento da dati JSON utilizzando un file JSONPath
• Caricamento da array JSON utilizzando un file JSONPath

Caricamento da dati JSON utilizzando l'opzione "auto".

Per caricare dai dati JSON utilizzando l'opzione 'auto', i dati JSON devono essere costituiti da un set di oggetti. I nomi delle chiavi devono corrispondere ai nomi delle colonne, ma l'ordine non ha importanza. Di seguito viene mostrato il contenuto di un file denominato 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"
}

Per caricare dal file dati JSON nell'esempio precedente, emettere il seguente comando COPY.

copy category
from 's3://DOC-EXAMPLE-BUCKET/category_object_auto.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 'auto';

Caricamento da dati JSON utilizzando l'opzione 'auto ignorecase'

Per caricare dai dati JSON utilizzando l'opzione 'auto ignorecase', i dati JSON devono essere costituiti da un set di oggetti. Il formato maiuscolo/minuscolo dei nomi delle chiavi non deve corrispondere ai nomi delle colonne e l'ordine non ha importanza. Di seguito viene mostrato il contenuto di un file denominato 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"
}

Per caricare dal file di dati JSON nell'esempio precedente, emettere il seguente comando COPY.

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

Caricamento da dati JSON utilizzando un file JSONPath

Se gli oggetti dei dati JSON non corrispondono direttamente ai nomi delle colonne, è possibile utilizzare un file JSONPath per mappare gli elementi JSON sulle colonne. Anche in questo caso, l'ordine non ha importanza nei dati di origine JSON, ma l'ordine delle espressioni dei file JSONPath deve corrispondere all'ordine delle colonne. Supponi di avere il seguente file di dati, denominato 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"
}

Il seguente file JSONPath, denominato category_jsonpath.json, mappa i dati sorgente nelle colonne della tabella.

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

Per caricare dal file di dati JSON nell'esempio precedente, emettere il seguente comando COPY.

copy category
from 's3://DOC-EXAMPLE-BUCKET/category_object_paths.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 's3://DOC-EXAMPLE-BUCKET/category_jsonpath.json';

Caricamento da array JSON utilizzando un file JSONPath

Per caricare dai dati JSON costituiti da un insieme di array, è necessario utilizzare un file JSONPath per mappare gli elementi dell'array sulle colonne. Supponi di avere il seguente file di dati, denominato 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"]

Il seguente file JSONPath, denominato category_array_jsonpath.json, mappa i dati sorgente nelle colonne della tabella.

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

Per caricare dal file di dati JSON nell'esempio precedente, emettere il seguente comando COPY.

copy category
from 's3://DOC-EXAMPLE-BUCKET/category_array_data.json'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' 
json 's3://DOC-EXAMPLE-BUCKET/category_array_jsonpath.json';

Esempi di copia da Avro

Negli esempi seguenti, carica la tabella CATEGORY con i seguenti dati.

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	Concerti	Classici	Tutti i concerti sinfonici, concertistici e corali

Argomenti

• Caricamento dai dati Avro utilizzando l'opzione "auto".
• Caricamento da dati Avro utilizzando l'opzione 'auto ignorecase'
• Caricamento dai dati Avro utilizzando un file JSONPath

Caricamento dai dati Avro utilizzando l'opzione "auto".

Per caricare dai dati Avro usando l'argomento 'auto', i nomi dei campi nello schema Avro devono corrispondere ai nomi delle colonne. Quando si utilizza l'argomento 'auto', l'ordine non ha importanza. Di seguito viene mostrato lo schema per un file denominato category_auto.avro.

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

I dati in un file Avro sono in formato binario, quindi non sono leggibili dall'utente. Di seguito viene mostrata una rappresentazione JSON dei dati nel file category_auto.avro.

{
   "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"
}

Per caricare dal file di dati Avro nell'esempio precedente, emettere il seguente comando COPY.

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

Caricamento da dati Avro utilizzando l'opzione 'auto ignorecase'

Per caricare dai dati Avro con l'argomento 'auto ignorecase', il formato maiuscolo/minuscolo dei nomi dei campi nello schema Avro non deve corrispondere al formato dei nomi delle colonne. Quando si utilizza l'argomento 'auto ignorecase', l'ordine non ha importanza. Di seguito viene mostrato lo schema per un file denominato 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"},
}

I dati in un file Avro sono in formato binario, quindi non sono leggibili dall'utente. Di seguito viene mostrata una rappresentazione JSON dei dati nel file category_auto-ignorecase.avro.

{
   "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"
}

Per caricare dal file di dati Avro nell'esempio precedente, emettere il seguente comando COPY.

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

Caricamento dai dati Avro utilizzando un file JSONPath

Se i nomi dei campi nello schema Avro non corrispondono direttamente ai nomi delle colonne, è possibile utilizzare un file JSONPath per mappare gli elementi dello schema sulle colonne. L'ordine delle espressioni dei file JSONPath deve corrispondere all'ordine delle colonne.

Supponi di avere un file di dati chiamato category_paths.avro che contenga gli stessi dati dell'esempio precedente, ma con lo schema seguente.

{
    "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"} 
     ]
}

Il seguente file JSONPath, denominato category_path.avropath, mappa i dati sorgente nelle colonne della tabella.

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

Per caricare dal file di dati Avro nell'esempio precedente, emettere il seguente comando COPY.

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

Preparazione dei file per COPY con l'opzione ESCAPE

L'esempio seguente descrive come preparare i dati per eseguire un "escape" dei caratteri newline prima di importarli in una tabella Amazon Redshift usando il comando COPY con il parametro ESCAPE. Senza preparare i dati per delimitare i caratteri newline, Amazon Redshift restituisce errori di caricamento quando si esegue il comando COPY, perché il carattere newline è normalmente usato come separatore di record.

Ad esempio, si consideri un file o una colonna in una tabella esterna che si desidera copiare in una tabella Amazon Redshift. Se il file o la colonna ha del contenuto formattato in XML o dati simili, è necessario assicurarsi che tutti i caratteri newline (\n) che fanno parte del contenuto siano inseriti in una sequenza di escape con il carattere di barra rovesciata (\).

Un file o una tabella che contiene caratteri newline incorporati è che fornisce un modello relativamente facile da associare. Ogni carattere newline incorporato molto probabilmente segue sempre un carattere > con potenzialmente alcuni caratteri di spazio (' ' o tabulazione) in mezzo, come è possibile vedere nel seguente esempio di un file di testo denominato nlTest1.txt.

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

Con l'esempio seguente, è possibile eseguire un'utilità di elaborazione del testo per pre-elaborare il file sorgente e inserire caratteri di escape, se necessario. (Il carattere | deve essere usato come delimitatore per separare i dati delle colonne quando viene copiato in una tabella Amazon Redshift).

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

Allo stesso modo, è possibile utilizzare Perl per eseguire un'operazione simile:

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

Per facilitare il caricamento dei dati dal file nlTest2.txt in Amazon Redshift, è stata creata una tabella a due colonne in Amazon Redshift. La prima colonna c1, è una colonna di caratteri che ha il contenuto in formato XML dal file nlTest2.txt. La seconda colonna c2 contiene i valori interi caricati dallo stesso file.

Dopo aver eseguito il comando sed, è possibile caricare correttamente i dati dal file nlTest2.txt in una tabella Amazon Redshift utilizzando il parametro ESCAPE.

Nota

Quando includi il parametro ESCAPE al comando COPY, crea una sequenza di escape con un certo numero di caratteri speciali che includono il carattere di barra rovesciata (incluso newline).

copy t2 from 's3://DOC-EXAMPLE-BUCKET/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)

In modo analogo è possibile preparare i file di dati esportati da database esterni. Ad esempio, con un database Oracle, è possibile utilizzare la funzione REPLACE su ogni colonna interessata in una tabella che si desideri copiare in Amazon Redshift.

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

Inoltre, molti strumenti di esportazione, estrazione, trasformazione e caricamento di database (ETL) che elaborano regolarmente grandi quantità di dati forniscono opzioni per specificare i caratteri di escape e i delimitatori.

Caricamento di uno shapefile in Amazon Redshift

Negli esempi seguenti viene illustrato come caricare uno shapefile Esri mediante COPY. Per ulteriori informazioni sul caricamento di shapefile, consultare Caricamento di uno shapefile in Amazon Redshift.

Caricamento di uno shapefile

I passaggi seguenti mostrano come importare OpenStreetMap dati da Amazon S3 utilizzando il comando COPY. Questo esempio presuppone che l'archivio degli shapefile norvegesi dal sito di download di Geofabrik sia stato caricato in un bucket Amazon S3 privato nella tua regione. AWS I file .shp, .shx e .dbf devono condividere lo stesso prefisso Amazon S3 e lo stesso nome file.

Importazione dei dati senza semplificazione

I seguenti comandi creano tabelle e importano dati che possono adattarsi alle dimensioni massime della geometria senza alcuna semplificazione. Aprire il gis_osm_natural_free_1.shp nel software GIS preferito e ispezionare le colonne di questo livello. Per impostazione predefinita, le colonne IDENTITY o GEOMETRY sono le prime. Quando una colonna GEOMETRY è prima, è possibile creare la tabella come illustrato di seguito.

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

Quando invece è la colonna IDENTITY a essere prima, è possibile creare la tabella come illustrato di seguito.

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

Ora è possibile importare i dati usando COPY.

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

In alternativa, è possibile importare i dati come illustrato di seguito.

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.

Importazione dei dati con semplificazione

I seguenti comandi creano tabelle e provano a importare i dati che possono adattarsi alle dimensioni massime della geometria senza alcuna semplificazione. Ispezionare lo shapefile gis_osm_water_a_free_1.shp e creare la tabella appropriata come illustrato di seguito.

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

Quando viene eseguito il comando COPY, viene generato un errore.

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.              

L'esecuzione della query su STL_LOAD_ERRORS ,ostra che la geometria è troppo grande.

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              

Per superare questo problema, viene aggiunto il parametro SIMPLIFY AUTO al comando COPY per semplificare le geometrie.

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.              

Per visualizzare le righe e le geometrie semplificate, eseguire una query su 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               

L'uso di di SIMPLIFY AUTO tolleranza max con una tolleranza inferiore a quella calcolata automaticamente probabilmente restituisce un errore di importazione. In questo caso, utilizzare MAXERROR per ignorare gli errori.

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.               

Eseguire di nuovo la query su SVL_SPATIAL_SIMPLIFY per identificare il record che COPY non è riuscito a caricare.

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 questo esempio, il primo record non è riuscito a adattarsi, quindi la colonna simplified riporta false. Il secondo record è stato caricato entro la tolleranza specificata. Tuttavia, la dimensione finale è maggiore rispetto all'utilizzo della tolleranza calcolata automaticamente senza specificare la tolleranza massima.

Caricamento da uno shapefile compresso

Il comando COPY di Amazon Redshift supporta l'importazione di dati da uno shapefile compresso. Tutti i componenti shapefile devono avere lo stesso prefisso Amazon S3 e lo stesso suffisso di compressione. Ad esempio, si supponga di voler caricare i dati dell'esempio precedente. In questo caso, i file gis_osm_water_a_free_1.shp.gz, gis_osm_water_a_free_1.dbf.gz e gis_osm_water_a_free_1.shx.gz devono condividere la stessa directory Amazon S3. Il comando COPY richiede l'opzione GZIP e la clausola FROM deve specificare il file compresso corretto, come illustrato di seguito.

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.          

Caricamento di dati in una tabella con un ordine di colonna diverso

Se hai una tabella che non dispone di GEOMETRY come prima colonna, è possibile mappare le colonne alla tabella di destinazione. Ad esempio, creare una tabella con osm_id specificato come prima colonna.

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

Quindi importare uno shapefile utilizzando la mappatura delle colonne.

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.         

Caricamento di dati in una tabella con una colonna geografica

Se hai una tabella con una colonna GEOGRAPHY, prima importa in una colonna GEOMETRY e quindi converti gli oggetti in oggetti GEOGRAPHY. Ad esempio, dopo aver copiato lo shapefile in una colonna GEOMETRY, modifica la tabella per aggiungere una colonna del tipo di dati GEOGRAPHY.

ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY;

Quindi converti le geometrie in aree geografiche.

UPDATE norway_natural SET wkb_geography = wkb_geometry::geography;

Facoltativamente, è possibile eliminare la colonna GEOMETRY.

ALTER TABLE norway_natural DROP COLUMN wkb_geometry;

Comando COPY con l'opzione NOLOAD

Per convalidare i file di dati prima di caricare effettivamente i dati, utilizza l'opzione NOLOAD con il comando COPY. Amazon Redshift analizza il file di input e visualizza gli eventuali errori che si verificano. L'esempio seguente utilizza l'opzione NOLOAD e nessuna riga viene effettivamente caricata nella tabella.

COPY public.zipcode1
FROM 's3://DOC-EXAMPLE-BUCKET/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.