Ejemplos de COPY

nota

Estos ejemplos contienen saltos de línea por motivos de legibilidad. No incluya saltos de líneas ni espacios en la cadena credentials-args (credenciales-argumentos).

Carga de FAVORITEMOVIES de una tabla de DynamoDB

Los AWS SDK incluyen un ejemplo sencillo de la creación de una tabla de DynamoDB denominada Movies (Películas). (Para obtener información sobre este ejemplo, consulte Introducción a DynamoDB). En el siguiente ejemplo, se carga la tabla MOVIES de Amazon Redshift con los datos de la tabla de DynamoDB. La tabla de Amazon Redshift ya debe existir en la base de datos.

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

Carga de LISTING desde un bucket de Amazon S3

En el siguiente ejemplo, se carga LISTING desde un bucket de Amazon S3. El comando COPY carga todos los archivos en la carpeta /data/listing/.

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

Carga de LISTING desde un clúster de Amazon EMR

En el siguiente ejemplo, se carga la tabla SALES con datos delimitados por tabuladores de archivos comprimidos con lzop en un clúster de Amazon EMR. COPY carga cada archivo en la carpeta myoutput/ que comienza con part-.

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

En el siguiente ejemplo, se carga la tabla SALES con datos en formato JSON en un clúster de Amazon EMR. COPY carga cada archivo en la carpeta myoutput/json/.

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

Uso de un manifiesto para especificar archivos de datos

Puede utilizar un manifiesto para asegurarse de que el comando COPY cargue todos los archivos requeridos, y solo esos, de Amazon S3. También puede utilizar un manifiesto cuando necesite cargar varios archivos de diferentes buckets o archivos que no compartan el mismo prefijo.

Por ejemplo, suponga que necesita cargar los siguientes tres archivos: custdata1.txt, custdata2.txt y custdata3.txt. Puede utilizar el siguiente comando para cargar todos los archivos de mybucket que comienzan con custdata especificando un prefijo:

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

Si debido a un error solo existen dos de los archivos, COPY carga solo esos dos archivos y termina su ejecución correctamente, lo que se traduce en una carga de datos incompleta. Si el bucket también contiene un archivo no deseado que utiliza el mismo prefijo, como un archivo denominado custdata.backup, por ejemplo, COPY también carga ese archivo, lo que se traduce en una carga de datos no deseados.

Para asegurar que se carguen todos los archivos requeridos y evitar que se carguen archivos no deseados, puede utilizar un archivo de manifiesto. El manifiesto es un archivo de texto con formato JSON que enumera los archivos a procesarse por el comando COPY. Por ejemplo, el siguiente manifiesto carga los tres archivos del ejemplo anterior.

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

La marca opcional mandatory indica si COPY debe terminar en caso de que el archivo no exista. El valor predeterminado es false. Independientemente de cualquier configuración obligatoria, COPY termina si no se encuentran archivos. En este ejemplo, COPY devuelve un error si no se encuentra alguno de los archivos. Los archivos no deseados que podrían haberse seleccionado si se especificó solo un prefijo de clave, como custdata.backup, se ignoran, ya que no están en el manifiesto.

Cuando la carga se realiza a partir de archivos de datos con formato ORC o Parquet, se necesita un campo meta, tal y como se muestra en el siguiente ejemplo.

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

En el siguiente ejemplo, se utiliza un manifiesto llamado cust.manifest.

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

Puede usar un manifiesto para cargar archivos de diferentes buckets o archivos que no compartan el mismo prefijo. En el siguiente ejemplo, se muestra el JSON para cargar datos con archivos cuyos nombres comiencen con una marca de fecha.

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

El manifiesto puede enumerar los archivos que se encuentran en buckets diferentes, siempre que los buckets estén en la misma región de AWS que el clúster.

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

Carga de LISTING de un archivo delimitado por la barra vertical (delimitador predeterminado)

En el siguiente ejemplo, se muestra un caso muy simple en el que no se especifican opciones y el archivo de entrada contiene el delimitador predeterminado, una barra vertical ("|").

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

Carga de LISTING utilizando datos en columnas con el formato de Parquet

En el siguiente ejemplo, se cargan datos desde una carpeta de Amazon S3 denominada parquet.

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

Carga de LISTING utilizando datos en columnas con el formato de ORC

En el siguiente ejemplo, se cargan datos desde una carpeta de Amazon S3 denominada orc.

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

Carga de EVENT con opciones

En el siguiente ejemplo, se cargan datos delimitados por llaves en la tabla EVENT y se aplican las siguientes reglas:

• Si se utilizan pares de comillas para rodear cualquier cadena de caracteres, estos se eliminan.

• Las cadenas vacías y las que contienen espacios en blanco se cargan como valores NULL.

• La carga falla si se devuelven más de 5 errores.

• Los valores de marca temporal deben cumplir con el formato especificado; por ejemplo, una marca temporal válida es 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';

Carga de VENUE de un archivo de datos de ancho fijo

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

En el ejemplo anterior se supone un archivo de datos con el mismo formato que muestran los datos de ejemplo. En la siguiente muestra, los espacios actúan como marcadores de posición para que todas las columnas tengan el mismo ancho que se indicó en la especificación:

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

Carga de CATEGORY de un archivo CSV

Suponga que desea cargar CATEGORY con los valores que se muestran en la siguiente tabla.

catid	catgroup	catname	catdesc
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

En el siguiente ejemplo, se muestra el contenido de un archivo de texto con los valores de campo separados por comas.

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

Si carga el archivo con el parámetro DELIMITER para especificar una entrada delimitada por comas, el comando COPY falla debido a que algunos campos de entrada contienen comas. Puede evitar ese problema si utiliza el parámetro CSV y encierra los campos que contienen comas entre comillas. Si los caracteres de comillas aparecen dentro de una cadena entre comillas, deberá aplicar escape duplicando las comillas. El carácter de comilla predeterminado es la comilla doble, por lo que debe aplicar escape a cada comilla doble con una comilla doble adicional. El archivo de entrada nuevo tiene el aspecto siguiente.

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"

Si el nombre del archivo es category_csv.txt, puede cargarlo con el siguiente comando COPY:

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

Además, para evitar la necesidad de aplicar escape a las comillas dobles de la entrada, puede especificar un carácter de comilla diferente con el parámetro QUOTE AS. Por ejemplo, la siguiente versión de category_csv.txt utiliza “%” como el carácter de comilla.

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%

El siguiente comando COPY utiliza QUOTE AS para cargar category_csv.txt:

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

Carga de VENUE con valores explícitos para una columna IDENTITY

En el siguiente ejemplo, se supone que en el momento que se creó la tabla VENUE se especificó al menos una columna (como la columna venueid) para que sea una columna de IDENTITY. Este comando anula el comportamiento predeterminado de IDENTITY, que genera automáticamente valores para una columna IDENTITY, y en su lugar carga los valores explícitos del archivo venue.txt. Amazon Redshift no comprueba si se cargan valores IDENTITY duplicados en la tabla al utilizar la opción EXLICIT_IDS.

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

Carga de TIME de un archivo GZIP delimitado por la barra vertical

En el siguiente ejemplo, se carga la tabla TIME desde un archivo GZIP delimitado por la barra vertical:

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

Carga de una marca temporal o de fecha

En el siguiente ejemplo, se cargan datos con una marca temporal con formato.

nota

El TIMEFORMAT de HH:MI:SS también puede admitir fracciones de segundos que sobrepasen SS hasta un nivel de detalle de microsegundos. El archivo time.txt utilizado en este ejemplo contiene una fila, 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';

El resultado de este copy es el siguiente:

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

Carga de datos de un archivo con valores predeterminados

En el siguiente ejemplo, se utiliza una variación de la tabla VENUE de la base de datos TICKIT. Piense en una tabla VENUE_NEW definida con la siguiente instrucción:

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

Piense en un archivo de datos venue_noseats.txt que no contenga valores para la columna VENUESEATS, como se muestra en el siguiente ejemplo:

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 siguiente instrucción COPY cargará correctamente la tabla del archivo y aplicará el valor DEFAULT ("1000") a la columna omitida:

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

Ahora observe la tabla cargada:

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)

Para el siguiente ejemplo, además de suponer que no se incluyen datos de VENUESEATS en el archivo, también se supone que no se incluyen datos de 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|

Al usar la misma definición de tabla, la siguiente instrucción COPY falla ya que no se especificó ningún valor DEFAULT para VENUENAME y esta es una columna NOT NULL:

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

Ahora piense en una variación de la tabla VENUE que utilice una columna 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');

Como en el ejemplo anterior, se supone que la columna VENUESEATS no tiene valores correspondientes en el archivo de origen. La siguiente instrucción COPY carga la tabla correctamente, incluidos los valores de datos IDENTITY predefinidos en lugar de autogenerar esos valores:

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

Esta instrucción falla porque no contiene la columna IDENTITY (falta VENUEID en la lista de columnas), pero incluye un parámetro EXPLICIT_IDS:

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

Esta instrucción falla porque no contiene un parámetro EXPLICIT_IDS:

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

Uso de COPY de datos con la opción ESCAPE

En el siguiente ejemplo, se muestra cómo cargar caracteres que coincidan con el carácter delimitador (en este caso, la barra vertical). En el archivo de entrada, asegúrese de que a todos los caracteres de barra vertical (|) que desea cargar se les aplicó el carácter de escape de barra oblicua inversa (\). Luego cargue el archivo con el parámetro 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)

Sin el parámetro ESCAPE, este comando COPY producirá un error Extra column(s) found.

importante

Si carga los datos a través de un comando COPY con el parámetro ESCAPE, también debe especificar el parámetro ESCAPE con el comando UNLOAD para generar el archivo de salida recíproco. De forma similar, si utiliza UNLOAD con el parámetro ESCAPE, necesita usar ESCAPE cuando utilice COPY con los mismos datos.

Ejemplos de Copy de JSON

En el siguiente ejemplo, cargue la tabla CATEGORY con los siguientes datos.

CATID	CATGROUP	CATNAME	CATDESC
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

Temas

• Carga desde datos JSON con la opción 'auto'
• Carga desde datos JSON con la opción 'auto ignorecase'
• Carga desde datos JSON con un archivo JSONPaths
• Carga desde matrices de JSON con un archivo JSONPaths

Carga desde datos JSON con la opción 'auto'

Para cargar desde datos JSON con la opción 'auto', los datos JSON deben estar compuestos por un conjunto de objetos. Los nombres de clave deben coincidir con los nombres de las columnas, pero el orden no importa. A continuación se muestra el contenido de un archivo denominado 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"
}

Para cargar contenido desde los archivos de datos JSON del ejemplo anterior, ejecute el siguiente comando COPY.

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

Carga desde datos JSON con la opción 'auto ignorecase'

Para cargar desde datos JSON con la opción 'auto ignorecase', los datos JSON deben estar compuestos por un conjunto de objetos. No es necesario que las mayúsculas y las minúsculas de los nombres de clave coincidan con los nombres de las columnas y el orden no importa. A continuación se muestra el contenido de un archivo denominado 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"
}

Para cargar contenido desde los archivos de datos JSON del ejemplo anterior, ejecute el siguiente comando COPY.

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

Carga desde datos JSON con un archivo JSONPaths

Si los objetos de datos JSON no corresponden directamente con los nombres de las columnas, puede utilizar un archivo JSONPaths para asignar los elementos JSON a las columnas. El orden de los datos de origen JSON no importa, pero el orden de las expresiones del archivo JSONPaths debe coincidir con el orden de las columnas. Suponga que tiene el siguiente archivo de datos, denominado 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"
}

El siguiente archivo JSONPaths, denominado category_jsonpath.json, asigna los datos de origen a las columnas de la tabla.

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

Para cargar contenido desde los archivos de datos JSON del ejemplo anterior, ejecute el siguiente comando COPY.

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

Carga desde matrices de JSON con un archivo JSONPaths

Para cargar desde datos JSON que están compuestos por un conjunto de matrices, debe utilizar un archivo JSONPaths para asignar los elementos de las matrices a las columnas. Suponga que tiene el siguiente archivo de datos, denominado 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"]

El siguiente archivo JSONPaths, denominado category_array_jsonpath.json, asigna los datos de origen a las columnas de la tabla.

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

Para cargar contenido desde los archivos de datos JSON del ejemplo anterior, ejecute el siguiente comando COPY.

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

Ejemplos de Copy de Avro

En el siguiente ejemplo, cargue la tabla CATEGORY con los siguientes datos.

CATID	CATGROUP	CATNAME	CATDESC
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

Temas

• Carga desde datos de Avro con la opción 'auto'
• Carga desde datos Avro con la opción 'auto ignorecase'
• Carga desde datos de Avro con un archivo JSONPaths

Carga desde datos de Avro con la opción 'auto'

Para cargar desde datos de Avro con el argumento 'auto', los nombres de campo del esquema de Avro deben coincidir con los nombres de las columnas. Cuando se utiliza el argumento 'auto', el orden no importa. A continuación se muestra el esquema para un archivo denominado category_auto.avro.

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

Los datos del archivo de Avro están en formato binario, por lo que no son legibles. A continuación se muestra una representación de JSON de los datos del archivo 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"
}

Para cargar contenido desde los archivos de datos Avro del ejemplo anterior, ejecute el siguiente comando COPY.

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

Carga desde datos Avro con la opción 'auto ignorecase'

Para cargar contenido desde datos Avro con el argumento 'auto ignorecase', no es necesario que las mayúsculas y las minúsculas de los nombres de campo del esquema de Avro coincidan con las mayúsculas y las minúsculas de los nombres de las columnas. Cuando se utiliza el argumento 'auto ignorecase', el orden no importa. A continuación se muestra el esquema para un archivo denominado 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"},
}

Los datos del archivo de Avro están en formato binario, por lo que no son legibles. A continuación se muestra una representación de JSON de los datos del archivo 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"
}

Para cargar contenido desde los archivos de datos Avro del ejemplo anterior, ejecute el siguiente comando COPY.

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

Carga desde datos de Avro con un archivo JSONPaths

Si los nombres de campo del esquema de Avro no corresponden directamente con los nombres de las columnas, puede utilizar un archivo JSONPaths para asignar los elementos del esquema a las columnas. El orden de las expresiones del archivo JSONPaths debe coincidir con el orden de las columnas.

Suponga que tiene un archivo de datos denominado category_paths.avro que contiene los mismos datos que el ejemplo anterior, pero con el siguiente esquema.

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

El siguiente archivo JSONPaths, denominado category_path.avropath, asigna los datos de origen a las columnas de la tabla.

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

Para cargar contenido desde los archivos de datos Avro del ejemplo anterior, ejecute el siguiente comando COPY.

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

Preparación de archivos para utilizar COPY con la opción ESCAPE

En el siguiente ejemplo, se describe cómo puede preparar datos para “aplicar escape” a los caracteres de línea nueva antes de importar los datos a una tabla de Amazon Redshift mediante el comando COPY con el parámetro ESCAPE. Sin la preparación de los datos para delimitar los caracteres de línea nueva, Amazon Redshift devuelve errores de carga cuando ejecuta el comando COPY, ya que el carácter de línea nueva, por lo general, se utiliza como un separador de registros.

Por ejemplo, piense en un archivo o una columna de una tabla externa que desea copiar a una tabla de Amazon Redshift. Si el archivo o la columna tiene contenido con formato XML o datos similares, debe asegurarse de que a todos los caracteres de línea nueva (\n) que forman parte del contenido se les aplique escape con la barra oblicua inversa (\).

Un archivo o una tabla que contienen caracteres de línea nueva insertados proporcionan un patrón relativamente fácil para el cual buscar coincidencias. Lo más probable es que cada carácter de línea nueva insertado siga siempre a un carácter > con algunos caracteres de espacio en blanco potenciales (' ' o tabulación) en el medio, como se puede observar en el siguiente ejemplo de un archivo de texto denominado 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 el siguiente ejemplo, puede ejecutar un procesador de textos para procesar previamente el archivo de origen e insertar caracteres de escape cuando los necesite. (El carácter | está pensado para utilizarse como delimitador para separar datos de columnas cuando se copian en una tabla de Amazon Redshift).

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

Del mismo modo, puede utilizar Perl para realizar una operación similar:

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

Para adaptar la carga de datos del archivo nlTest2.txt en Amazon Redshift, creamos una tabla con dos columnas en dicho servicio. La primera columna c1, es una columna de caracteres que tiene el contenido con formato XML del archivo nlTest2.txt. La segunda columna c2 contiene los valores enteros cargados desde el mismo archivo.

Después de ejecutar el comando sed, puede cargar correctamente los datos del archivo nlTest2.txt en una tabla de Amazon Redshift mediante el parámetro ESCAPE.

nota

Cuando incluya el parámetro ESCAPE con el comando COPY, se aplica escape a un número de caracteres especiales que incluyen la barra oblicua inversa (incluida la línea nueva).

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)

Puede preparar los archivos de datos exportados de bases de datos externas de forma similar. Por ejemplo, con una base de datos de Oracle, puede utilizar la función REPLACE en cada columna afectada de una tabla que desee copiar en Amazon Redshift.

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

Además, varias herramientas de extracción, transformación y carga (ETL) y de exportación de bases de datos que generalmente procesan grandes cantidades de datos brindan opciones para especificar caracteres delimitadores y de escape.

Carga de un shapefile en Amazon Redshift

En los siguientes ejemplos, se muestra cómo cargar un ESRI shapefile con COPY. Para obtener más información sobre cómo cargar shapefiles, consulte Carga de un shapefile en Amazon Redshift.

Carga de un shapefile

Los siguientes pasos muestran cómo capturar datos de OpenStreetMap desde Amazon S3 a través del comando COPY. Este ejemplo asume que el archivo shapefile de Noruega del sitio de descarga de Geofabrik se cargó en un bucket de Amazon S3 privado en su región de AWS. Los archivos .shp, .shx y .dbf deben compartir el mismo prefijo y nombre de archivo de Amazon S3.

Ingesta de datos sin simplificación

Los siguientes comandos crean tablas y capturan datos que caben en el tamaño máximo de geometría sin ninguna simplificación. Abra gis_osm_natural_free_1.shp en su software de SIG preferido e inspeccione las columnas de esta capa. De manera predeterminada, las columnas IDENTITY o GEOMETRY son las primeras. Cuando una columna GEOMETRY es la primera, puede crear la tabla como se muestra a continuación.

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

Cuando una columna IDENTITY es la primera, puede crear la tabla como se muestra a continuación.

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

Ahora, puede capturar los datos mediante 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

También puede capturar los datos como se muestra a continuación.

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.

Ingesta de datos con simplificación

Los siguientes comandos crean una tabla e intentan capturar datos que no caben en el tamaño máximo de la geometría sin ninguna simplificación. Inspeccione el shapefile gis_osm_water_a_free_1.shp y cree la tabla adecuada como se muestra a continuación.

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

Cuando se ejecuta el comando COPY, se produce un error.

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.              

La consulta de STL_LOAD_ERRORS muestra que la geometría es demasiado 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              

Para superar esto, el parámetro SIMPLIFY AUTO se agrega al comando COPY para simplificar la geometría.

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.              

Para ver las filas y las geometrías que se simplificaron, consulte 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               

El uso de SIMPLIFY AUTO max_tolerance con una tolerancia inferior a las calculadas de manera automática probablemente resulte en un error de ingesta. En este caso, utilice MAXERROR para ignorar los errores.

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.               

Consulte SVL_SPATIAL_SIMPLIFY de nuevo para identificar el registro que COPY no logró cargar.

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             

En este ejemplo, el primer registro no logró ajustarse; por lo tanto, la columna simplified se muestra como false. El segundo registro se cargó en el marco de la tolerancia determinada. No obstante, el tamaño final es mayor que si se utiliza la tolerancia calculada automáticamente sin especificar la tolerancia máxima.

Carga desde un shapefile comprimido

COPY de Amazon Redshift admite la ingesta de datos de un shapefile comprimido. Todos los componentes de shapefile deben tener el mismo prefijo de Amazon S3 y el mismo sufijo de compresión. Por ejemplo, supongamos que desea cargar los datos del ejemplo anterior. En este caso, los archivos gis_osm_water_a_free_1.shp.gz, gis_osm_water_a_free_1.dbf.gz y gis_osm_water_a_free_1.shx.gz deben compartir el directorio de Amazon S3. El comando COPY requiere la opción GZIP, y la cláusula FROM debe especificar el archivo comprimido correcto, como se muestra a continuación.

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.          

Carga de datos en una tabla con un orden de columnas diferente

Si tiene una tabla en la que GEOMETRY no es la primera columna, puede utilizar el mapeo de columnas para asignar columnas a la tabla de destino. Por ejemplo, cree una tabla con osm_id especificado como primera columna.

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

Luego, capture un shapefile mediante el mapeo de columnas.

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.         

Carga de datos en una tabla con una columna geography

Si tiene una tabla con una columna GEOGRAPHY, primero captúrela en una columna GEOMETRY y, a continuación, convierta los objetos en objetos GEOGRAPHY. Por ejemplo, después de copiar el shapefile en una columna GEOMETRY, modifique la tabla para agregar una columna de tipo de datos GEOGRAPHY.

ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY;

A continuación, convierta las columnas geometry en geography.

UPDATE norway_natural SET wkb_geography = wkb_geometry::geography;

De forma opcional, puede eliminar la columna GEOMETRY.

ALTER TABLE norway_natural DROP COLUMN wkb_geometry;

Comando COPY con la opción NOLOAD

Para validar los archivos de datos antes de que los datos se carguen, use la opción NOLOAD con el comando COPY. Amazon Redshift analiza el archivo de entrada y muestra los errores que se producen. En el siguiente ejemplo se utiliza la opción NOLOAD donde no se carga ninguna fila en la tabla.

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.