Didacticiel : Chargement en masse à partir d’un système de fichiers local à l’aide de COPY¶

Téléchargement des fichiers de données d’exemple¶

Pour ce tutoriel, vous devez télécharger les fichiers de données d’exemple fournis par Snowflake.

Pour télécharger et décompresser les fichiers de données d’exemple :

1. Cliquez avec le bouton droit de la souris sur le nom du fichier d’archive, data-load-internal.zip et enregistrez le lien/fichier dans votre système de fichiers local.

2. Décompressez les fichiers d’exemple. Le tutoriel suppose que vous avez décompressé les fichiers dans les répertoires suivants :

• Linux/macOS : /tmp/load

• Windows : C:\tempload

Ces fichiers de données comprennent des données de contact d’exemple dans les formats suivants :

• Des fichiers CSV qui contiennent une ligne d’en-tête et cinq enregistrements. Le délimiteur de champ est le caractère de barre verticale (|). L’exemple suivant montre une ligne d’en-tête et un enregistrement :

  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

• Un seul fichier au format JSON qui contient un tableau et trois objets. Voici un exemple de tableau contenant l’un de ces objets :

  [
   {
     "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

Création de la base de données, des tables et de l’entrepôt¶

Exécutez les instructions suivantes pour créer une base de données, deux tables (pour les données CSV et JSON) et un entrepôt virtuel nécessaires pour ce tutoriel. Après avoir terminé le tutoriel, vous pouvez détruire ces objets.

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

L’instruction CREATE WAREHOUSE configure l’entrepôt pour qu’il soit suspendu initialement. L’instruction définit également AUTO_RESUME = true, qui démarre l’entrepôt automatiquement lorsque vous exécutez des instructions SQL qui nécessitent des ressources de calcul.

Création d’un objet de format de fichier pour des données CSV¶

Exécutez la commande CREATE FILE FORMAT pour créer le format de fichier mycsvformat.

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

Où :

• TYPE = 'CSV' indique le type de format du fichier source. CSV est le type de format de fichier par défaut.

• FIELD_DELIMITER = '|' indique que le caractère « | » est un séparateur de champ. La valeur par défaut est « , ».

• SKIP_HEADER = 1 indique que le fichier source comprend une ligne d’en-tête. La commande COPY ignore ces lignes d’en-tête lors du chargement des données. La valeur par défaut est 0.

Création d’un objet de format de fichier pour des données JSON¶

Exécutez la commande CREATE FILE FORMAT pour créer le format de fichier myjsonformat.

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

Où :

• TYPE = 'JSON' indique le type de format du fichier source.

• STRIP_OUTER_ARRAY = TRUE indique à la commande COPY d’exclure les crochets ([]) lors du chargement de données dans la table.

Création d’une zone de préparation pour des fichiers de données CSV¶

Exécutez CREATE STAGE pour créer la zone de préparation my_csv_stage :

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat;

Notez que si vous spécifiez l’option FILE_FORMAT lors de la création de la zone de préparation, il n’est pas nécessaire de spécifier la même option FILE_FORMAT dans la commande COPY utilisée pour charger des données depuis la zone de préparation.

Création d’une zone de préparation pour des fichiers de données JSON¶

Exécutez CREATE STAGE pour créer la zone de préparation my_json_stage :

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat;

Mise en zone de préparation des fichiers de données d’exemple CSV¶

Exécutez la commande PUT pour charger les fichiers CSV depuis votre système de fichiers 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

Regardons la commande de plus près :

• file://<file-path>[/]contacts*.csv spécifie le chemin complet du répertoire et les noms des fichiers sur votre machine locale vers la zone de préparation. Notez que les caractères génériques du système de fichiers sont autorisés.

• @my_csv_stage est le nom de la zone de préparation où les données seront placées.

• auto_compress=true; indique à la commande de compresser les données lors de la mise en zone de préparation. Il s’agit également de la valeur par défaut.

La commande renvoie le résultat suivant, montrant les fichiers en zone de préparation :

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| 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 |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

Mise en zone de préparation des fichiers de données d’exemple JSON¶

Exécutez la commande PUT pour charger le fichier JSON de votre système de fichiers local vers la zone de préparation nommée.

• 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

La commande renvoie le résultat suivant, montrant les fichiers en zone de préparation :

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

Liste des fichiers en zone de préparation (facultatif)¶

Vous pouvez répertorier les fichiers en zone de préparation en utilisant la commande LIST.

CSV¶

Pour charger les données des fichiers CSV d’exemple :

1. Commencez par charger les données d’un des fichiers (contacts1.csv.gz). Exécutez ce qui suit :

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

   Copy

   Où :

   • La clause FROM spécifie l’emplacement du fichier de données en zone de préparation (nom de la zone de préparation suivi du nom du fichier).

   • La clause ON_ERROR spécifie ce qu’il faut faire lorsque la commande COPY rencontre des erreurs dans les fichiers. Par défaut, la commande arrête le chargement des données lorsque la première erreur est rencontrée. Cependant, nous lui avons demandé d’ignorer tout fichier contenant une erreur et de passer au chargement du fichier suivant. Notez qu’il ne s’agit que d’une illustration, aucun des fichiers de ce tutoriel ne contient d’erreurs.

   La commande COPY renvoie un résultat indiquant le nom du fichier copié et les informations connexes :

   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   | 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. Chargez le reste des fichiers en zone de préparation dans la table mycsvtable.

   L’exemple suivant utilise le filtrage par critères spéciaux pour charger les données des fichiers correspondant à l’expression régulière .*contacts[1-5].csv.gz à l’intérieur de la table mycsvtable .

   COPY INTO mycsvtable
     FROM @my_csv_stage
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     PATTERN='.*contacts[1-5].csv.gz'
     ON_ERROR = 'skip_file';
   

   Copy

   Où la clause PATTERN spécifie que la commande doit charger les données à partir des noms de fichiers correspondant à cette expression régulière (.*employees0[1-5].csv.gz).

   La commande COPY renvoie un résultat indiquant le nom du fichier copié et les informations connexes :

   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   | 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

   Notez les points suivants dans le résultat :

   • Les données de contacts1.csv.gz sont ignorées parce que nous avons déjà chargé les données avec succès.

   • Les données de ces fichiers ont été chargées avec succès : contacts2.csv.gz, contacts4.csv.gz, et contacts5.csv.gz.

   • Les données dans contacts3.csv.gz sont ignorées en raison de 2 erreurs de données. L’étape suivante de ce tutoriel traite de la validation et de la correction des erreurs.

JSON¶

Chargez le fichier de données en zone de préparation contacts.json.gz dans la table myjsontable.

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

La commande COPY renvoie un résultat indiquant le nom du fichier copié et les informations connexes :

+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| 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 |
+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Validation des fichiers de données d’exemple et récupération des erreurs éventuelles¶

Vous avez d’abord besoin de l’ID de requête associé à la commande COPY INTO que vous avez exécutée précédemment. Vous appelez ensuite la fonction VALIDATE en spécifiant l’ID de requête.

1. Récupérez la requête ID.

   1. Connectez-vous à Snowsight.

   2. Assurez-vous que le rôle dans Snowsight est le même que celui que vous utilisez dans SnowSQL pour exécuter les instructions SQL de ce didacticiel.

   3. Sélectionnez Monitoring » Query History.

   4. Sélectionnez la ligne correspondant à la commande COPY INTO spécifique pour ouvrir le volet d’informations sur la requête.

   5. Copiez la valeur Query ID.

2. Validez l’exécution de la commande COPY INTO, représentée par l’ID de requête et enregistrez les erreurs dans une nouvelle table nommée save_copy_errors.

   1. Dans SnowSQL, exécutez la commande suivante : Remplacez query_id par la valeur Query ID.

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

      Copy

   2. Interroger la table save_copy_errors.

      SELECT * FROM SAVE_COPY_ERRORS;
      

      Copy

      La requête renvoie les résultats suivants :

      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      | 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

Le résultat montre deux erreurs de données dans mycsvtable/contacts3.csv.gz :

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

  Dans la ligne 1, un trait d’union a été remplacé par erreur par le caractère de barre verticale (|), le délimiteur de fichier de données, créant ainsi une colonne supplémentaire dans l’enregistrement.

  

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

  Dans la ligne 5, un caractère de barre verticale supplémentaire (|) a été introduit après un trait d’union, ce qui a conduit à un problème d’enregistrement.

  

Correction des erreurs et rechargement des fichiers de données¶

1. Corrigez les erreurs dans les enregistrements manuellement dans le fichier contacts3.csv dans votre environnement local.

2. Utilisez la commande PUT pour charger le fichier de données modifié vers la zone de préparation. Le fichier modifié écrase le fichier préparé existant.

   • 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. Copiez les données des fichiers en zone de préparation dans les tables.

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

   Copy

Snowflake renvoie les résultats suivants indiquant que les données de contacts3.csv.gz sont chargées avec succès.

+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| 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

Vérification des données chargées¶

Exécutez une requête SELECT pour vérifier que les données ont été chargées avec succès.

Didacticiel Nettoyage (facultatif)¶

Exécutez les commandes DROP <objet> suivantes pour remettre votre système dans son état initial avant de commencer le tutoriel :

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

Copy

Détruire la base de données supprime automatiquement toutes les objets de base de données liés, par exemple les tables.

Autres didacticiels sur le chargement de données¶

• Snowflake en 20 minutes

• Didacticiel : Chargement en masse à partir d’Amazon S3 à l’aide de COPY