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

Download dos arquivos de dados de amostra¶

Para este tutorial, você precisa baixar os arquivos de dados de amostra fornecidos pelo Snowflake.

Para baixar e descompactar os arquivos de dados de amostra:

1. Clique com o botão direito do mouse sobre o nome do arquivo, data-load-internal.zip e salve o link/arquivo em seu sistema de arquivo local.

2. Descompacte os arquivos de amostra. O tutorial considera que você tenha descompactado os arquivos nos seguintes diretórios:

• Linux/macOS: /tmp/load

• Windows: C:\tempload

Estes arquivos de dados incluem amostras de dados de contato 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 a database. A database automatically includes a schema named 'public'.

CREATE OR REPLACE DATABASE mydatabase;

/* Create target tables for CSV and JSON data. The tables are temporary, meaning they persist only for the duration of the user session and are not visible to other users. */

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 a warehouse

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

Copy

A instrução CREATE WAREHOUSE configura o warehouse a ser suspenso inicialmente. 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;

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;

Observe que se você especificar a opção FILE_FORMAT ao criar o estágio, não será necessário especificar a mesma opção FILE_FORMAT no comando COPY usado para carregar dados do estágio.

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;

Preparação de arquivos de dados de amostra CSV¶

Execute o comando PUT para carregar os arquivos CSV de seu sistema de arquivos local.

• Linux ou macOS

  PUT file:///tmp/load/contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

Vamos analisar melhor o comando:

• file://<caminho-arquivo>[/]contacts*.csv especifica o caminho completo do diretório e os nomes dos arquivos na máquina local que devem ser preparados. Você pode usar caracteres curinga do sistema de arquivos.

• @my_csv_stage é o nome do estágio onde os dados devem ser preparados.

• auto_compress=true; direciona o comando para comprimir os dados durante a preparação. Este também é o padrão.

O comando retorna o seguinte resultado, mostrando os arquivos preparados:

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts1.csv | contacts1.csv.gz |         694 |         506 | NONE               | GZIP               | UPLOADED |         |
| contacts2.csv | contacts2.csv.gz |         763 |         565 | NONE               | GZIP               | UPLOADED |         |
| contacts3.csv | contacts3.csv.gz |         771 |         567 | NONE               | GZIP               | UPLOADED |         |
| contacts4.csv | contacts4.csv.gz |         750 |         561 | NONE               | GZIP               | UPLOADED |         |
| contacts5.csv | contacts5.csv.gz |         887 |         621 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

Preparação de arquivos de dados de amostra JSON¶

Execute o comando PUT para carregar o arquivo JSON de seu sistema de arquivos local no estágio nomeado.

• Linux ou macOS

  PUT file:///tmp/load/contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

O comando retorna o seguinte resultado, mostrando os arquivos preparados:

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts.json | contacts.json.gz |         965 |         446 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

Listagem dos arquivos preparados (opcional)¶

Você pode listar os arquivos preparados usando o comando LIST.

CSV¶

Para carregar os dados dos arquivos CSV de amostra:

1. Comece carregando os dados de um dos arquivos (contacts1.csv.gz). Execute o seguinte:

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts1.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     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 especifica o que fazer quando o comando COPY encontra erros nos arquivos. Por padrão, o comando interrompe o carregamento de dados quando o primeiro erro é encontrado. No entanto, demos uma instrução para que ele pule qualquer arquivo que contenha erro e prossiga para carregar o próximo arquivo. Lembre-se que isso é apenas um exemplo. Nenhum arquivo deste tutorial contém erros.

   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 |
   |-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts1.csv.gz | 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 todos os arquivos que correspondem à expressão regular .*contacts[1-5].csv.gz para a tabela mycsvtable.

   COPY INTO mycsvtable
     FROM @my_csv_stage
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     PATTERN='.*contacts[1-5].csv.gz'
     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 (.*employees0[1-5].csv.gz).

   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 |
   |-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts2.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts3.csv.gz | 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]        |
   | mycsvtable/contacts4.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts5.csv.gz | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

   Observe os seguintes destaques no resultado:

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

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

   • Os dados em contacts3.csv.gz 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.gz na tabela myjsontable.

COPY INTO myjsontable
  FROM @my_json_stage/contacts.json.gz
  FILE_FORMAT = (FORMAT_NAME = myjsonformat)
  ON_ERROR = 'skip_file';

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 |
|------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| myjsontable/contacts.json.gz | 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 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¶

1. Corrija os erros nos registros manualmente no arquivo contacts3.csv em seu ambiente local.

2. Use o comando PUT para carregar o arquivo de dados modificado no estágio. O arquivo modificado substitui o arquivo preparado existente.

   • Linux ou macOS:

     PUT file:///tmp/load/contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

   • Windows:

     PUT file://C:\temp\load\contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

3. Copie os dados dos arquivos preparados nas tabelas.

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts3.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

O Snowflake retorna os seguintes resultados, indicando que os dados em contacts3.csv.gz foram carregados com sucesso.

+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| mycsvtable/contacts3.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Copy

Verificar os dados carregados¶

Execute uma consulta 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 do Amazon S3 usando COPY