Exemplos de COPY

nota

Estes exemplos contêm quebras de linha para garantir legibilidade. Não inclua quebras de linha nem espaços na string credentials-args.

Carregar FAVORITEMOVIES de uma tabela do DynamoDB

Os SDKs da AWS incluem um exemplo simples de como criar uma tabela do DynamoDB chamada Movies. (Para esse exemplo, consulte Conceitos básicos do DynamoDB.) O exemplo a seguir carrega a tabela MOVIES do Amazon Redshift com dados da tabela do DynamoDB. A tabela do Amazon Redshift já deve existir no banco de dados.

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

Carregar LISTING de um bucket do Amazon S3

O exemplo a seguir carrega LISTING de um bucket do Amazon S3. O comando COPY carrega todos os arquivos na pasta /data/listing/.

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

Carregar LISTING de um cluster do Amazon EMR

O exemplo a seguir carrega a tabela SALES com dados delimitados por tabulação de arquivos compactados por lzop em um cluster do Amazon EMR. COPY carrega cada arquivo na pasta myoutput/ que começa com part-.

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

O exemplo a seguir carrega a tabela SALES com dados formatados JSON em um cluster do Amazon EMR. COPY carrega cada arquivo na pasta 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 um manifesto para especificar arquivos de dados

Você pode usar um manifesto para garantir que o comando COPY carregue todos os arquivos necessários, e somente os arquivos necessários, do Amazon S3. Você também pode usar um manifesto ao precisar carregar vários arquivos de buckets diferentes ou arquivos que não tenham o mesmo prefixo.

Por exemplo, suponha que você precise carregar os seguintes três arquivos: custdata1.txt, custdata2.txt e custdata3.txt. Você pode usar o seguinte comando para carregar todos os arquivos em mybucket que comecem com custdata especificando um prefixo:

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

Se somente dois dos arquivos existirem por causa de um erro, COPY carrega somente esses dois arquivos e será concluído com êxito, resultando em uma carga de dados incompleta. Se o bucket também contiver um arquivo indesejado que acabe usando o mesmo prefixo, como um arquivo chamado custdata.backup por exemplo, COPY carregará esse arquivo também, resultando no carregamento de dados indesejados.

Para garantir que todos os arquivos necessários sejam carregados e para evitar que arquivos indesejados sejam carregados, você pode usar um arquivo manifesto. O manifesto é um arquivo de texto formatado em JSON que lista os arquivos a serem processados pelo comando COPY. Por exemplo, o manifesto a seguir carrega os três arquivos no exemplo anterior.

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

O sinalizador mandatory opcional indica se COPY deverá ser encerrado se o arquivo não existir. O padrão é false. Independentemente de qualquer configuração obrigatória, o COPY encerra se nenhum arquivo for encontrado. Neste exemplo, COPY retornará um erro se algum dos arquivos não for encontrado. Arquivos indesejados que possam ter sido escolhidos se você tiver especificado somente um prefixo de chaves, como custdata.backup, são ignorados, pois não estão no manifesto.

Ao carregar arquivos de dados em ORC ou em formato Parquet, um campo meta é necessário, conforme exibido no seguinte exemplo.

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

O exemplo a seguir usa um manifesto nomeado cust.manifest.

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

Você pode usar um manifesto para carregar vários arquivos de buckets diferentes ou arquivos que não compartilham o mesmo prefixo. O exemplo a seguir mostra o JSON para carregar dados com arquivos cujos nomes começam com uma data e hora.

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

O manifesto pode listar arquivos em buckets diferentes, desde que os buckets estejam na mesma região da AWS do cluster.

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

Carregar LISTING de um arquivo delimitado por barras (delimitador padrão)

O exemplo a seguir é um caso muito simples em que nenhuma opção é especificada e o arquivo de entrada contém o delimitador padrão, um caractere de barra ('|').

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

Carregar LISTING usando dados de colunas no formato Parquet

O exemplo a seguir carrega dados de uma pasta no Amazon S3, chamada Parquet.

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

Carregar LISTING usando dados de colunas no formato ORC

O exemplo a seguir carrega dados de uma pasta no Amazon S3, chamada orc.

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

Carregar EVENT com opções

O exemplo a seguir carrega dados delimitados por barra na tabela EVENT e aplica as seguintes regras:

• Se forem usados para todas as strings de caracteres, os pares de aspas serão removidos.

• As strings vazias e as que contêm espaços em branco serão carregadas como valores NULL.

• A carga falha se mais de 5 erros forem retornados.

• Os valores de data e hora devem respeitar o formato especificado; por exemplo, uma data e hora válida é 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';

Carregar VENUE de um arquivo de dados de largura fixa

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

O exemplo anterior pressupõe um arquivo de dados formatado da mesma maneira que os dados de exemplo mostrados. No exemplo a seguir, os espaços funcionam como espaços reservados, de maneira que todas as colunas tenham a mesma largura conforme observado na especificação:

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

Carregar CATEGORY de um arquivo CSV

Suponhamos que você queira carregar CATEGORY com os valores mostrados na tabela a seguir.

catid	catgroup	catname	catdesc
12	Shows	Musicais	Casa de espetáculo
13	Shows	Peças	Todos os teatros "não musicais"
14	Shows	Opera	Todas as óperas, leves e óperas "rock"
15	Concertos	Clássicos	Todas as sinfonias, os concertos e os corais

O exemplo a seguir mostra o conteúdo de um arquivo de texto com os valores de campo separados por vírgulas.

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 você carregar o arquivo usando o parâmetro DELIMITER para especificar uma entrada delimitada por vírgulas, o comando COPY falha porque alguns campos de entrada contêm vírgulas. Você pode evitar esse problema usando o parâmetro CSV e colocando os campos que contenham vírgulas entre aspas. Se as aspas forem exibidas dentro de uma string com aspas, você precisará escapá-las dobrando as aspas. Como as aspas padrão são duplas, você precisa escapar todas as aspas duplas com aspas adicionais. O novo arquivo de entrada é algo assim.

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"

Supondo que o nome de arquivo seja category_csv.txt, você pode carregar o arquivo usando o seguinte comando COPY:

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

Como alternativa, para evitar a necessidade de escapar as aspas duplas na entrada, você pode especificar aspas diferentes usando o parâmetro QUOTE AS. Por exemplo, a versão a seguir de category_csv.txt usa '%' como aspas.

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%

O seguinte comando COPY usa QUOTE AS para carregar category_csv.txt:

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

Carregar VENUE com valores explícitos para uma coluna IDENTITY

O exemplo a seguir pressupõe que, quando a tabela VENUE foi criada, pelo menos uma coluna (como a coluna venueid) foi especificada para ser uma coluna IDENTITY. Esse comando substitui o comportamento de IDENTITY padrão de valores gerados automaticamente para uma coluna IDENTITY e, em vez disso, carrega os valores explícitos do arquivo venue.txt. O Amazon Redshift não verifica se valores de IDENTITY duplicados são carregados na tabela ao usar a opção EXLICIT_IDS.

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

Carregar TIME de um arquivo GZIP delimitado por barras

O seguinte exemplo carrega a tabela TIME de um arquivo GZIP delimitado por barras:

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

Carregar um time stamp ou date stamp

O exemplo a seguir carrega dados com uma data e hora formatada.

nota

O TIMEFORMAT de HH:MI:SS também pode dar suporte a segundos fracionários além do SS para um nível detalhado de microssegundos. O arquivo time.txt usado nesse exemplo contém uma linha 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';

O resultado dessa cópia é o seguinte:

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

Carregar dados de um arquivo com valores padrão

O exemplo a seguir usa uma variação da tabela VENUE no banco de dados TICKIT. Leve em consideração uma tabela VENUE_NEW definida com o seguinte comando:

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

Leve em consideração um arquivo de dados venue_noseats.txt que não contenha valores para a coluna VENUESEATS, conforme mostrado no seguinte exemplo:

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|

A seguinte instrução COPY carregará com êxito a tabela do arquivo e aplicará o valor DEFAULT ('1000') à coluna omitida:

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

Agora veja a tabela carregada:

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 o seguinte exemplo, além de pressupor que nenhum dado de VENUESEATS esteja incluído no arquivo, também pressuponha que não haja dados VENUENAME incluídos:

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 a mesma definição de tabela, a seguinte instrução COPY falha porque nenhum valor DEFAULT foi especificado para VENUENAME, e VENUENAME é uma coluna NOT NULL:

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

Agora leve em consideração uma variação da tabela VENUE que usa uma coluna 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');

Assim como acontece com o exemplo anterior, pressuponha que a coluna VENUESEATS não tenha valores correspondentes no arquivo de origem. A instrução COPY a seguir carrega a tabela com êxito, inclusive os valores de dados IDENTITY predefinidos em vez de gerar automaticamente esses 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;

Ocorre uma falha nessa instrução porque não inclui a coluna IDENTITY (VENUEID não é encontrado na lista de colunas), além de incluir um 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;

Ocorre uma falha nessa instrução porque não inclui um 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 '|';

Dados de COPY com a opção ESCAPE

O exemplo a seguir mostra como carregar caracteres correspondentes ao caractere delimitador (nesse caso, o caractere de barra). No arquivo de entrada, verifique se todos os caracteres de barra (|) que você deseja carregar sejam escapados com o caractere de barra invertida (\). Em seguida, carregue o arquivo com o 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)

Sem o parâmetro ESCAPE, esse comando COPY falha com um erro Extra column(s) found.

Importante

Se carregar os dados usando um COPY com o parâmetro ESCAPE, você também deverá especificar o parâmetro ESCAPE com o comando UNLOAD para gerar o arquivo de saída recíproco. Da mesma maneira, se usar UNLOAD com o parâmetro ESCAPE, você precisa usar ESCAPE quando usar o comando COPY nos mesmos dados.

Copiar de exemplos JSON

Nos exemplos a seguir, você carrega a tabela CATEGORY com os dados a seguir.

CATID	CATGROUP	CATNAME	CATDESC
1	Esportes	MLB	Major League Baseball
2	Esportes	NHL	National Hockey League
3	Esportes	NFL	National Football League
4	Esportes	NBA	National Basketball Association
5	Concertos	Clássicos	Todas as sinfonias, os concertos e os corais

Tópicos

• Carregar de dados JSON usando a opção "auto"
• Carregar de dados JSON usando a opção "auto ignorecase"
• Carregar de dados JSON usando um arquivo JSONPaths
• Carregar de matrizes JSON usando um arquivo JSONPaths

Carregar de dados JSON usando a opção "auto"

Para carregar de dados JSON usando o argumento 'auto', os dados JSON devem consistir em um conjunto de objetos. Os nomes de chave devem corresponder aos nomes de coluna, mas, nesse caso, a ordem não importa. A seguir, o conteúdo de um arquivo chamado 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 carregar do arquivo de dados JSON no exemplo anterior, execute o comando COPY a seguir.

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

Carregar de dados JSON usando a opção "auto ignorecase"

Para carregar de dados JSON usando o argumento 'auto ignorecase', os dados JSON devem consistir em um conjunto de objetos. As maiúsculas e minúsculas dos nomes de chave não precisam corresponder aos nomes de coluna e a ordem não importa. A seguir, o conteúdo de um arquivo chamado 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 carregar do arquivo de dados JSON no exemplo anterior, execute o comando COPY a seguir.

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

Carregar de dados JSON usando um arquivo JSONPaths

Se os objetos de dados JSON não corresponderem diretamente aos nomes de coluna, você poderá usar um arquivo JSONPaths a fim de mapear os elementos JSON para colunas. Além disso, a ordem não importa nos dados de origem JSON, mas a ordem das expressões do arquivo JSONPaths deve corresponder à ordem da coluna. Suponha que você tenha o seguinte arquivo de dados, chamado 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"
}

O arquivo JSONPaths a seguir, chamado category_jsonpath.json, mapeia os dados de origem para as colunas de tabela.

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

Para carregar do arquivo de dados JSON no exemplo anterior, execute o comando COPY a seguir.

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

Carregar de matrizes JSON usando um arquivo JSONPaths

Para carregar de dados JSON que consistem em um conjunto de matrizes, você deve usar um arquivo JSONPaths a fim de mapear os elementos de matriz para as colunas. Suponha que você tenha o seguinte arquivo de dados, chamado 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"]

O arquivo JSONPaths a seguir, chamado category_array_jsonpath.json, mapeia os dados de origem para as colunas de tabela.

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

Para carregar do arquivo de dados JSON no exemplo anterior, execute o comando COPY a seguir.

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

Copiar de exemplos Avro

Nos exemplos a seguir, você carrega a tabela CATEGORY com os dados a seguir.

CATID	CATGROUP	CATNAME	CATDESC
1	Esportes	MLB	Major League Baseball
2	Esportes	NHL	National Hockey League
3	Esportes	NFL	National Football League
4	Esportes	NBA	National Basketball Association
5	Concertos	Clássicos	Todas as sinfonias, os concertos e os corais

Tópicos

• Carregar de dados Avro usando a opção "auto"
• Carregar de dados Avro usando a opção "auto ignorecase"
• Carregar de dados Avro usando um arquivo JSONPaths

Carregar de dados Avro usando a opção "auto"

Para carregar de dados Avro usando o argumento 'auto', os nomes de campo no esquema Avro devem corresponder aos nomes de coluna. Durante o uso do argumento 'auto', a ordem não importa. A seguir, o esquema de um arquivo chamado category_auto.avro.

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

Como estão em formato binário, os dados em um arquivo Avro não são legíveis. A seguir, uma representação JSON dos dados no arquivo 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 carregar do arquivo de dados Avro no exemplo anterior, execute o comando COPY a seguir.

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

Carregar de dados Avro usando a opção "auto ignorecase"

Para carregar de dados Avro usando o argumento 'auto ignorecase', a maiúscula e minúscula nos nomes de campo no esquema Avro devem corresponder aos nomes de coluna. Durante o uso do argumento 'auto ignorecase', a ordem não importa. A seguir, o esquema de um arquivo chamado 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"},
}

Como estão em formato binário, os dados em um arquivo Avro não são legíveis. A seguir, uma representação JSON dos dados no arquivo 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 carregar do arquivo de dados Avro no exemplo anterior, execute o comando COPY a seguir.

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

Carregar de dados Avro usando um arquivo JSONPaths

Se os nomes de campo no esquema Avro não corresponderem diretamente aos nomes de coluna, você poderá usar um arquivo JSONPaths a fim de mapear os elementos de esquema para colunas. A ordem das expressões do arquivo JSONPaths deve corresponder à ordem das colunas.

Suponha que você tenha um arquivo de dados chamado category_paths.avro contendo os mesmos dados do exemplo anterior, mas com o esquema a seguir.

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

O arquivo JSONPaths a seguir, chamado category_path.avropath, mapeia os dados de origem para as colunas de tabela.

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

Para carregar do arquivo de dados Avro no exemplo anterior, execute o comando COPY a seguir.

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

Preparar arquivos para COPY com a opção ESCAPE

O exemplo a seguir descreve como convém preparar dados para "escapar" caracteres de nova linha antes de importar os dados para uma tabela do Amazon Redshift usando o comando COPY com o parâmetro ESCAPE. Sem preparar os dados para delimitar os caracteres de nova linha, o Amazon Redshift retorna erros de carga quando você executar o comando COPY, porque o caractere de nova linha normalmente é usado como um separador de registros.

Por exemplo, leve em consideração um arquivo ou uma coluna em uma tabela externa que você deseja copiar para uma tabela do Amazon Redshift. Se o arquivo ou a coluna apresentar conteúdo formatado em XML ou dados semelhantes, você precisa verificar se todos os caracteres de nova linha (\n) que fazem parte do conteúdo são escapados com o caractere de barra invertida (\).

Um arquivo ou tabela contendo caracteres de nova linha incorporados fornece um padrão relativamente fácil de combinar. Cada caractere de nova linha interno quase sempre vem depois de um caractere > com alguns caracteres de espaço em branco em potencial (' ' ou tabulação) entre eles, como você pode ver no exemplo a seguir de um arquivo de texto chamado 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

Com o exemplo a seguir, você pode executar um utilitário de processamento de texto para pré-processar o arquivo de origem e inserir os caracteres de escape quando necessário. (O caractere | deve ser usado como delimitador para separar dados de coluna quando copiados para uma tabela do Amazon Redshift.)

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

Da mesma maneira, você pode usar Perl para realizar uma operação semelhante:

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

Para acomodar ao carregamento dos dados do arquivo nlTest2.txt para o Amazon Redshift, criamos uma tabela de duas colunas no Amazon Redshift. A primeira coluna c1 é uma coluna de caracteres que mantém o conteúdo formatado do arquivo nlTest2.txt. A segunda coluna c2 mantém valores inteiros carregados do mesmo arquivo.

Depois de executar o comando sed, você poderá carregar corretamente dados do arquivo nlTest2.txt para uma tabela do Amazon Redshift usando o parâmetro ESCAPE.

nota

Quando você inclui o parâmetro ESCAPE com o comando COPY, ele escapa vários caracteres especiais, dentre os quais estão caractere de barra invertida (inclusive nova linha).

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)

Você pode preparar arquivos de dados exportados de bancos de dados externos de maneira semelhante. Por exemplo, com um banco de dados Oracle, você pode usar a função REPLACE em cada coluna afetada em uma tabela que deseja copiar para o Amazon Redshift.

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

Além disso, muitas ferramentas Export e Extract, Transform, Load (ETL – Exportação e extração, transformação, carga) de banco de dados que processam sempre muitos dados oferecem opções para especificar caracteres de escape e delimitadores.

Carregar um shapefile no Amazon Redshift

Os exemplos a seguir demonstram como carregar um shapefile da Esri usando COPY. Para obter mais informações o carregamento de shapefiles, consulte Carregar um shapefile no Amazon Redshift.

Carregar um shapefile

As etapas a seguir mostram como ingerir dados do OpenStreetMap do Amazon S3 usando o comando COPY. Este exemplo pressupõe que o arquivo shapefile da Noruega do site de download de Geofabrik foi carregado para um bucket privado do Amazon S3 em sua região da AWS. Os arquivos .shp, .shx e.dbf devem compartilhar o mesmo prefixo do Amazon S3 e nome de arquivo.

Ingestão de dados sem simplificação

Os comandos a seguir criam tabelas e ingerem dados que podem caber no tamanho máximo da geometria sem qualquer simplificação. Abra o gis_osm_natural_free_1.shp em seu software GIS preferido e inspecione as colunas nesta camada. Por padrão, as colunas IDENTITY ou GEOMETRY são as primeiras. Quando uma coluna GEOMETRY é a primeira, você pode criar a tabela como mostrado a seguir.

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

Ou, quando uma coluna IDENTITY é a primeira, você pode criar a tabela como mostrado a seguir.

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

Agora você pode ingerir os dados 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

Ou você pode ingerir os dados como mostrado a seguir.

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.

Ingestão de dados com simplificação

Os comandos a seguir criam uma tabela e tentam ingerir dados que não podem caber no tamanho máximo da geometria sem qualquer simplificação. Inspecione o shapefile gis_osm_water_a_free_1.shp e crie a tabela apropriada como mostrado a seguir.

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

Quando o comando COPY é executado, isso resulta em um erro.

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.              

Consultar STL_LOAD_ERRORS mostra que a geometria é muito 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 isso, o parâmetro SIMPLIFY AUTO é adicionado ao comando COPY para simplificar geometrias.

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 exibir as linhas e geometrias que foram simplificadas, 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               

Utilizar SIMPLIFY AUTO max_tolerance com a tolerância menor do que as calculadas automaticamente provavelmente resulta em um erro de ingestão. Nesse caso, use MAXERROR para ignorar erros.

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 novamente para identificar o registro que COPY não conseguiu carregar.

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             

Neste exemplo, o primeiro registro não conseguiu caber, então o simplified está mostrando “false”. O segundo registro foi carregado dentro da tolerância dada. No entanto, o tamanho final é maior do que usar a tolerância calculada automaticamente sem especificar a tolerância máxima.

Carregar a partir de um shapefile compactado

O Amazon Redshift COPY oferece suporte à ingestão de dados de um shapefile compactado. Todos os componentes shapefile devem ter o mesmo prefixo do Amazon S3 e o mesmo sufixo de compactação. Como exemplo, suponha que você deseja carregar os dados do exemplo anterior. Neste caso, os arquivos 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 devem compartilhar o mesmo diretório do Amazon S3. O comando COPY requer a opção GZIP e a cláusula FROM deve especificar o arquivo compactado correto, conforme mostrado a seguir.

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.          

Carregar dados em uma tabela com uma ordem de coluna diferente

Se você tiver uma tabela que não tenha GEOMETRY como a primeira coluna, você pode usar o mapeamento de coluna para mapear colunas para a tabela de destino. Por exemplo, crie uma tabela com osm_id especificado como uma primeira coluna.

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

Em seguida, faça a ingestão de um shapefile usando o mapeamento de coluna.

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.         

Carregar dados em uma tabela com uma coluna geography

Caso você tenha uma tabela com uma coluna GEOGRAPHY, ingira primeiro para uma coluna GEOMETRY e converta os objetos para objetos GEOGRAPHY. Por exemplo, depois de copiar seu arquivo de formato para uma coluna GEOMETRY, altere a tabela para adicionar uma coluna do tipo de dados GEOGRAPHY.

ALTER TABLE norway_natural ADD COLUMN wkb_geography GEOGRAPHY;

Então converta geometrias em geografias.

UPDATE norway_natural SET wkb_geography = wkb_geometry::geography;

Se preferir, você pode descartar a coluna GEOMETRY.

ALTER TABLE norway_natural DROP COLUMN wkb_geometry;

Comando COPY com a opção NOLOAD

Para validar os arquivos de dados antes de realmente carregar os dados, use a opção NOLOAD com o comando COPY. O Amazon Redshift analisa o arquivo de entrada e exibe todos os erros que ocorrem. O exemplo a seguir usa a opção NOLOAD e nenhuma linha é realmente carregada na tabela.

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.