Tutorial: carregamento em massa a partir do Amazon S3 usando COPY¶

Sobre os arquivos de dados de amostra¶

O Snowflake fornece arquivos de dados de amostra preparados em um bucket S3 público.

Os arquivos de dados de amostra incluem informações de contato de amostra nos seguintes formatos:

• Arquivos CSV que contêm uma linha de cabeçalho e cinco registros. O delimitador de campo é o caractere de canal (|). O exemplo a seguir mostra uma linha de cabeçalho e um registro:

  ID|lastname|firstname|company|email|workphone|cellphone|streetaddress|city|postalcode
  6|Reed|Moses|Neque Corporation|eget.lacus@facilisis.com|1-449-871-0780|1-454-964-5318|Ap #225-4351 Dolor Ave|Titagarh|62631
  

  Copy

• Um único arquivo no formato JSON que contém uma matriz e três objetos. A seguir, um exemplo de uma matriz que contém um dos objetos:

  [
   {
     "customer": {
       "address": "509 Kings Hwy, Comptche, Missouri, 4848",
       "phone": "+1 (999) 407-2274",
       "email": "blankenship.patrick@orbin.ca",
       "company": "ORBIN",
       "name": {
         "last": "Patrick",
         "first": "Blankenship"
       },
       "_id": "5730864df388f1d653e37e6f"
     }
   },
  ]
  

  Copy

Criação do banco de dados, tabelas e warehouses¶

Execute as seguintes instruções para criar um banco de dados, duas tabelas (para dados csv e json) e um warehouse virtual necessário para este tutorial. Depois de concluir o tutorial, você poderá descartar estes objetos.

CREATE OR REPLACE DATABASE mydatabase;

CREATE OR REPLACE TEMPORARY TABLE mycsvtable (
     id INTEGER,
     last_name STRING,
     first_name STRING,
     company STRING,
     email STRING,
     workphone STRING,
     cellphone STRING,
     streetaddress STRING,
     city STRING,
     postalcode STRING);

CREATE OR REPLACE TEMPORARY TABLE myjsontable (
     json_data VARIANT);

CREATE OR REPLACE WAREHOUSE mywarehouse WITH
     WAREHOUSE_SIZE='X-SMALL'
     AUTO_SUSPEND = 120
     AUTO_RESUME = TRUE
     INITIALLY_SUSPENDED=TRUE;

Observe o seguinte:

• A instrução CREATE DATABASE cria um banco de dados. O banco de dados inclui automaticamente um esquema chamado “public”.

• As instruções CREATE TABLE criam tabelas de destino para dados CSV e JSON. As tabelas são temporárias, ou seja, elas existem apenas durante a sessão do usuário e não ficam visíveis para os outros usuários.

• A instrução CREATE WAREHOUSE cria um warehouse inicialmente suspenso. A instrução também define AUTO_RESUME = true, que inicia automaticamente o warehouse quando você executa instruções SQL que exigem recursos computacionais.

Criação de um objeto de formato de arquivo para dados CSV¶

Execute o comando CREATE FILE FORMAT para criar o formato de arquivo mycsvformat.

CREATE OR REPLACE FILE FORMAT mycsvformat
   TYPE = 'CSV'
   FIELD_DELIMITER = '|'
   SKIP_HEADER = 1;

Onde:

• TYPE = 'CSV' indica o tipo de formato do arquivo de origem. CSV é o tipo de formato de arquivo padrão.

• FIELD_DELIMITER = '|' indica que o caractere “|” é um separador de campo. O valor padrão é “,”.

• SKIP_HEADER = 1 indica que o arquivo de origem inclui uma linha de cabeçalho. O comando COPY ignora essas linhas de cabeçalho ao carregar dados. O valor padrão é 0.

Criação de um objeto de formato de arquivo para dados JSON¶

Execute o comando CREATE FILE FORMAT para criar o formato de arquivo myjsonformat.

CREATE OR REPLACE FILE FORMAT myjsonformat
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE;

Copy

Onde:

• TYPE = 'JSON' indica o tipo de formato do arquivo de origem.

• STRIP_OUTER_ARRAY = TRUE direciona o comando COPY para excluir os colchetes ([]) ao carregar dados na tabela.

Criação de um estágio para arquivos de dados CSV¶

Execute CREATE STAGE para criar o estágio my_csv_stage:

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat
  URL = 's3://snowflake-docs';

Criação de um estágio para arquivos de dados JSON¶

Execute CREATE STAGE para criar o estágio my_json_stage:

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat
  URL = 's3://snowflake-docs';

CSV¶

Para carregar os dados dos arquivos CSV de amostra:

1. Comece carregando os dados de um dos arquivos no prefixo /tutorials/dataloading/ (pasta) chamado contacts1.csv na tabela mycsvtable. Execute o seguinte:

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/contacts1.csv
     ON_ERROR = 'skip_file';
   

   Copy

   Onde:

   • A cláusula FROM especifica a localização do arquivo de dados preparado (nome do estágio seguido do nome do arquivo).

   • A cláusula ON_ERROR = 'skip_file' especifica o que fazer quando o comando COPY encontra erros nos arquivos. Se o comando encontrar um erro de dados em qualquer um dos registros, ele ignorará o arquivo. Se você não especificar uma cláusula ON_ERROR, o padrão será abort_statement, o que aborta o comando COPY no primeiro erro encontrado em qualquer um dos registros de um arquivo.

   O comando COPY retorna um resultado mostrando o nome do arquivo copiado e informações relacionadas:

   +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   | file                                                    | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
   |---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | s3://snowflake-docs/tutorials/dataloading/contacts1.csv | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
   +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   

   Copy

2. Carregue o resto dos arquivos preparados na tabela mycsvtable.

   O exemplo a seguir usa a correspondência de padrões para carregar dados de arquivos que correspondem à expressão regular .*contacts[1-5].csv para a tabela mycsvtable.

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/
     PATTERN='.*contacts[1-5].csv'
     ON_ERROR = 'skip_file';
   

   Copy

   Em que a cláusula PATTERN especifica que o comando deve carregar dados dos nomes dos arquivos que correspondem a esta expressão regular .*contacts[1-5].csv.

   O comando COPY retorna um resultado mostrando o nome do arquivo copiado e informações relacionadas:

   +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   | file                                                    | status      | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                                                                                                                          | first_error_line | first_error_character | first_error_column_name |
   |---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | s3://snowflake-docs/tutorials/dataloading/contacts2.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | s3://snowflake-docs/tutorials/dataloading/contacts3.csv | LOAD_FAILED |           5 |           0 |           1 |           2 | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error |                3 |                     1 | "MYCSVTABLE"[11]        |
   | s3://snowflake-docs/tutorials/dataloading/contacts4.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | s3://snowflake-docs/tutorials/dataloading/contacts5.csv | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

   Observe os seguintes destaques no resultado:

   • Os dados em contacts1.csv são ignorados porque você já carregou os dados com sucesso.

   • Os dados contidos nestes arquivos foram carregados com sucesso: contacts2.csv, contacts4.csv e contacts5.csv.

   • Os dados em contacts3.csv foram ignorados devido a 2 erros de dados. A próxima etapa neste tutorial trata de como validar e corrigir os erros.

JSON¶

Carregue o arquivo de dados preparado contacts.json na tabela myjsontable.

COPY INTO myjsontable
  FROM @my_json_stage/tutorials/dataloading/contacts.json
  ON_ERROR = 'skip_file';

Copy

O COPY retorna um resultado mostrando o nome do arquivo copiado e informações relacionadas:

+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                                                    | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts.json | LOADED |           3 |           3 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Validação dos arquivos de dados de amostra e recuperação de eventuais erros¶

Primeiro você precisa da ID da consulta de recuperação associada ao comando COPY INTO que você executou anteriormente. Em seguida, chame a função VALIDATE, especificando a ID de consulta.

1. Recupere a ID de consulta.

   1. Faça login no Snowsight.

   2. Certifique-se de que a função no Snowsight seja a mesma que você está usando em SnowSQL para executar instruções SQL para este tutorial.

   3. Selecione Monitoring » Query History.

   4. Selecione a linha para o comando específico COPY INTO para abrir o painel de informações da consulta.

   5. Copie o valor Query ID.

2. Valide a execução do comando COPY INTO, representado pela ID da consulta, e salve os erros em uma nova tabela chamada save_copy_errors.

   1. No SnowSQL, execute o seguinte comando: Substitua query_id pelo valor Query ID.

      CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>'));
      

      Copy

   2. Consultar a tabela save_copy_errors.

      SELECT * FROM SAVE_COPY_ERRORS;
      

      Copy

      A consulta retorna os seguintes resultados:

      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      | ERROR                                                                                                                                                                | FILE                                | LINE | CHARACTER | BYTE_OFFSET | CATEGORY |   CODE | SQL_STATE | COLUMN_NAME                   | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD                                                                                                                                     |
      |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------|
      | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | mycsvtable/contacts3.csv.gz         |    3 |         1 |         234 | parsing  | 100080 |     22000 | "MYCSVTABLE"[11]              |          1 |              2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 |
      | Field delimiter '|' found while expecting record delimiter '\n'                                                                                                      | mycsvtable/contacts3.csv.gz         |    5 |       125 |         625 | parsing  | 100016 |     22000 | "MYCSVTABLE"["POSTALCODE":10] |          4 |              5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188                  |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Copy

O resultado mostra dois erros de dados em mycsvtable/contacts3.csv.gz:

• Number of columns in file (11) does not match that of the corresponding table (10)

  Na Linha 1, um hífen foi erroneamente substituído pelo caractere de canal (|), o delimitador do arquivo de dados, criando efetivamente uma coluna adicional no registro.

  

• Field delimiter '|' found while expecting record delimiter 'n'

  Na Linha 5, um caractere de canal (|) adicional foi introduzido após um hífen, quebrando o registro.

  

Solução de erros e novo carregamento dos arquivos de dados¶

No uso regular, você corrigiria os registros problemáticos manualmente e os gravaria em um novo arquivo de dados. Então você prepararia os arquivos de dados fixos para o bucket S3 e tentaria recarregar os dados dos arquivos. Para este tutorial, você está usando os dados de amostra fornecidos pelo Snowflake, que você não corrigirá.

Verificar os dados carregados¶

Execute uma instrução SELECT para verificar se os dados foram carregados com sucesso.

Tutorial de limpeza (opcional)¶

Execute os seguintes comandos DROP <objeto> para retornar o sistema ao seu estado antes de iniciar o tutorial:

DROP DATABASE IF EXISTS mydatabase;
DROP WAREHOUSE IF EXISTS mywarehouse;

Copy

Descartar o banco de dados remove automaticamente todos os objetos do banco de dados filho, tais como tabelas.

Outros tutoriais de carregamento de dados¶

• Snowflake em 20 minutos

• Tutorial: carregamento em massa a partir de um sistema de arquivo local usando COPY