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

À propos des fichiers de données d’exemple¶

Snowflake fournit des exemples de fichiers de données préparés dans un compartiment S3 public.

Les fichiers de données d’exemple comprennent des informations 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 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;

Remarques :

• L’instruction CREATE DATABASE crée une base de données. La base de données comprend automatiquement un schéma nommé « public ».

• Les instructions CREATE TABLE créent des tables cibles pour les données CSV et JSON. Les tables sont temporaires, c’est-à-dire qu’elles n’existent que pendant la durée de la session de l’utilisateur et ne sont pas visibles pour les autres utilisateurs.

• L’instruction CREATE WAREHOUSE crée un entrepôt initialement suspendu. 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;

Copy

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
  URL = 's3://snowflake-docs';

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
  URL = 's3://snowflake-docs';

CSV¶

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

1. Commencez par charger les données d’un des fichiers du préfixe (dossier) /tutorials/dataloading/ nommé contacts1.csv dans la table mycsvtable. Exécutez ce qui suit :

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/contacts1.csv
     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 = 'skip_file' spécifie ce qu’il faut faire lorsque la commande COPY rencontre des erreurs dans les fichiers. Dans ce cas, lorsque la commande rencontre une erreur de données sur l’un des enregistrements dans un fichier, elle ignore le fichier. Si vous ne spécifiez pas de clause ON_ERROR, la valeur par défaut est abort_statement, ce qui annule la commande COPY lors de la première erreur rencontrée sur l’un des enregistrements d’un fichier.

   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 |
   |---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | s3://snowflake-docs/tutorials/dataloading/contacts1.csv | 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 à l’intérieur de la table mycsvtable.

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/
     PATTERN='.*contacts[1-5].csv'
     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 .*contacts[1-5].csv.

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

   Notez les points suivants dans le résultat :

   • Les données de contacts1.csv 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, contacts4.csv, et contacts5.csv.

   • Les données dans contacts3.csv 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 dans la table myjsontable.

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

Copy

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

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

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 de récupération 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¶

En principe, vous corrigez manuellement les enregistrements posant problème et les écrivez dans un nouveau fichier de données. Vous pouvez ensuite préparer les fichiers de données réparés dans le compartiment S3 et tenter de recharger les données à partir de ces fichiers. Pour ce tutoriel, vous utilisez les données d’exemple fournies par Snowflake, que vous ne corrigez pas.

Vérification des données chargées¶

Exécutez une instruction 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’un système de fichiers local à l’aide de COPY