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.
Tópicos
- Carregar FAVORITEMOVIES de uma tabela do DynamoDB
- Carregar LISTING de um bucket do Amazon S3
- Carregar LISTING de um cluster do Amazon EMR
- Uso de um manifesto para especificar arquivos de dados
- Carregar LISTING de um arquivo delimitado por barras (delimitador padrão)
- Carregar LISTING usando dados de colunas no formato Parquet
- Carregar LISTING usando dados de colunas no formato ORC
- Carregar EVENT com opções
- Carregar VENUE de um arquivo de dados de largura fixa
- Carregar CATEGORY de um arquivo CSV
- Carregar VENUE com valores explícitos para uma coluna IDENTITY
- Carregar TIME de um arquivo GZIP delimitado por barras
- Carregar um time stamp ou date stamp
- Carregar dados de um arquivo com valores padrão
- Dados de COPY com a opção ESCAPE
- Copiar de exemplos JSON
- Copiar de exemplos Avro
- Preparar arquivos para COPY com a opção ESCAPE
- Carregar um shapefile no Amazon Redshift
- Comando COPY com a opção NOLOAD
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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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 amzn-s3-demo-bucket
que comecem com custdata
especificando um prefixo:
copy category from 's3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/custdata.1", "mandatory":true }, { "url":"s3://amzn-s3-demo-bucket/custdata.2", "mandatory":true }, { "url":"s3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket1/orc/2013-10-04-custdata", "mandatory":true, "meta":{ "content_length":99 } }, { "url":"s3://amzn-s3-demo-bucket2/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/2013-10-04-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-05-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/2013-10-06-custdata.txt","mandatory":true}, {"url":"s3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket1/custdata1.txt","mandatory":false}, {"url":"s3://amzn-s3-demo-bucket2/custdata1.txt","mandatory":false}, {"url":"s3://amzn-s3-demo-bucket2/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-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';
Carregar VENUE de um arquivo de dados de largura fixa
copy venue from 's3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-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)
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"
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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/category_object_paths.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/category_array_data.json' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' json 's3://amzn-s3-demo-bucket/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"
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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/category_object_paths.avro' iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole' format avro 's3://amzn-s3-demo-bucket/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://amzn-s3-demo-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)
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.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://amzn-s3-demo-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.