Como solucionar erros de carregamento de dados ao usar o comando COPY no Amazon Redshift?

Breve descrição

Use a tabela STL_LOAD_ERRORS para identificar erros de carregamento de dados que ocorrem durante o carregamento de um arquivo simples. A tabela STL_LOAD_ERRORS pode ajudá-lo a rastrear o progresso de um carregamento de dados e registrar falhas ou erros. Depois de solucionar o problema, use o comando COPY para recarregar os dados no arquivo simples.

Observação: se você usar o comando COPY para carregar um arquivo simples no formato Parquet, também poderá usar a tabela SVL_S3LOG para identificar erros.

Resolução

Observação: as etapas a seguir usam um exemplo de conjunto de dados de cidades e locais.

Para usar a tabela STL_LOAD_ERRORS para identificar erros de carregamento de dados, realize as seguintes etapas:

1. Verifique os dados em seu arquivo simples de amostra e confirme se os dados de origem são válidos:

   7|BMO Field|Toronto|ON|016|TD Garden|Boston|MA|0  
   23|The Palace of Auburn Hills|Auburn Hills|MI|0  
   28|American Airlines Arena|Miami|FL|0  
   37|Staples Center|Los Angeles|CA|0  
   42|FedExForum|Memphis|TN|0  
   52|PNC Arena|Raleigh|NC  ,25   |0  
   59|Scotiabank Saddledome|Calgary|AB|0  
   66|SAP Center|San Jose|CA|0  
   73|Heinz Field|Pittsburgh|PA|65050

   No exemplo anterior do arquivo demo.txt, um caractere de barra separa os cinco campos que são usados. Para obter mais informações, consulte Carregar LISTING de um arquivo delimitado por barras (delimitador padrão).

2. Abra o console do Amazon Redshift.

3. Use a linguagem de definição de dados (DDL) a seguir para criar uma tabela de amostra:

   CREATE TABLE VENUE1(VENUEID SMALLINT,  
   VENUENAME VARCHAR(100),  
   VENUECITY VARCHAR(30),  
   VENUESTATE CHAR(2),  
   VENUESEATS INTEGER  
   ) DISTSTYLE EVEN;

4. Para identificar a causa do erro de carregamento de dados, crie uma exibição para visualizar as colunas relevantes da tabela STL_LOAD_ERRORS:

   create view loadview as(select distinct tbl, trim(name) as table_name, query, starttime,  
   trim(filename) as input, line_number, colname, err_code,  
   trim(err_reason) as reason  
   from stl_load_errors sl, stv_tbl_perm sp  
   where sl.tbl = sp.id);

5. Execute o comando COPY para carregar os dados:

   copy Demofrom 's3://your_S3_bucket/venue/'  
   iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3'  
   delimiter '|' ;

   Observação: substitua your_S3_bucket pelo nome do seu bucket do S3 e arn:aws:iam::123456789012:role/redshiftcopyfroms3 pelo ARN do seu perfil do AWS Identity and Access Management (IAM). O perfil do IAM deve ter permissões para acessar dados do seu bucket do S3. Para obter mais informações, consulte Parâmetros.

6. Para exibir e analisar os detalhes do erro de carregamento da tabela, consulte a visualização de carregamento:

   testdb=# select * from loadview where table_name='venue1';tbl | 265190  
   table_name | venue1  
   query | 5790  
   starttime | 2017-07-03 11:54:22.864584  
   input | s3://  
   your_S3_bucket/venue/venue_pipe0000_part_00  
   line_number | 7  
   colname | venuestate  
   err_code | 1204  
   reason | Char length exceeds DDL length

   No exemplo anterior, a exceção é causada pelo valor do comprimento e deve ser adicionada à coluna venuestate. O valor (NC ,25 |) é maior do que o comprimento definido no DDL VENUESTATE CHAR (2).
   Para resolver esse problema, realize uma das seguintes tarefas:
   Se for esperado que os dados excedam o comprimento definido da coluna, atualize a definição da tabela para modificar o comprimento da coluna.
   -ou-
   Se os dados não estiverem corretamente formatados ou transformados, modifique os dados no arquivo para usar o valor correto.
   A saída da consulta inclui as seguintes informações:
   O arquivo que causa o erro
   A coluna que causa o erro
   O número da linha no arquivo de entrada
   O motivo da exceção

7. Modifique os dados do arquivo de carregamento para usar os valores corretos:

   7|BMO Field|Toronto|ON|016|TD Garden|Boston|MA|0  
   23|The Palace of Auburn Hills|Auburn Hills|MI|0  
   28|American Airlines Arena|Miami|FL|0  
   37|Staples Center|Los Angeles|CA|0  
   42|FedExForum|Memphis|TN|0  
   52|PNC Arena|Raleigh|NC|0  
   59|Scotiabank Saddledome|Calgary|AB|0  
   66|SAP Center|San Jose|CA|0  
   73|Heinz Field|Pittsburgh|PA|65050

   Observação: o comprimento deve estar alinhado com o comprimento definido da coluna.

8. Recarregue o carregamento de dados:

   testdb=# copy Demofrom 's3://your_S3_bucket/sales/'  
   iam_role 'arn:aws:iam::123456789012:role/redshiftcopyfroms3' delimiter '|' ;  
   INFO:  Load into table 'venue1' completed, 808 record(s) loaded successfully.

   Observação: a tabela STL_LOAD_ERRORS pode conter somente um número limitado de logs, por aproximadamente 4 a 5 dias. Os usuários padrão podem visualizar apenas seus próprios dados quando consultam a tabela STL_LOAD_ERRORS. É preciso ser um superusuário para visualizar todos os dados da tabela.

Informações relacionadas

Práticas recomendadas do Amazon Redshift para projetar tabelas

Práticas recomendadas do Amazon Redshift para carregamento de dados

Tabelas de sistema para solucionar problemas de carregamento de dados

Trabalhar com recomendações do Amazon Redshift Advisor