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

Introduction

Ce tutoriel décrit comment charger des données à partir de fichiers dans un compartiment Amazon Simple Storage Service (Amazon S3) existant dans une table. Dans ce tutoriel, vous apprendrez à :

  • Créer des formats de fichiers nommés qui décrivent vos fichiers de données.

  • Créer des objets préparés nommés.

  • Charger les données situées dans votre compartiment S3 dans des tables Snowflake.

  • Résoudre les erreurs dans vos fichiers de données.

Le tutoriel couvre le chargement de données CSV et JSON.

Conditions préalables

Le tutoriel suppose les éléments suivants :

  • Vous disposez d’un compte Snowflake configuré pour utiliser Amazon Web Services (AWS) et d’un utilisateur avec un rôle qui accorde les privilèges nécessaires pour créer une base de données, des tables et des objets d’entrepôt virtuel.

  • Vous avez installé SnowSQL.

Consultez les Snowflake en 20 minutes pour obtenir des instructions afin de répondre à ces exigences.

Snowflake fournit des exemples de fichiers de données dans un compartiment public Amazon S3 à utiliser dans ce tutoriel. Mais avant de commencer, vous devez créer une base de données, des tables et un entrepôt virtuel pour ce tutoriel. Ce sont les objets Snowflake de base nécessaires pour la plupart des activités Snowflake.

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

Note

En principe, vous pouvez mettre en zone de préparation vos propres fichiers de données à l’aide de la console de gestion d’AWS, de l’interface de ligne de commande d’AWS ou d’une application cliente équivalente. Voir la documentation d”Amazon Web Services pour obtenir des instructions.

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

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’objets de format de fichier

Lorsque vous chargez des fichiers de données d’un compartiment S3 dans une table, vous devez décrire le format du fichier et spécifier comment les données du fichier doivent être interprétées et traitées. Par exemple, si vous chargez des données délimitées par des barres verticales à partir d’un fichier CSV, vous devez spécifier que le fichier utilise le format CSV avec des symboles de barre verticale comme délimiteurs.

Lorsque vous exécutez la commande COPY INTO <table> vous spécifiez ces informations de format. Vous pouvez soit spécifier ces informations sous forme d’options dans la commande (par exemple TYPE = CSV, FIELD_DELIMITER = '|', etc.), soit spécifier un objet de format de fichier qui contient ces informations de format. Vous pouvez créer un objet de format de fichier nommé à l’aide de la commande CREATE FILE FORMAT.

Dans cette étape, vous créez des objets de format de fichier décrivant le format des données CSV et JSON d’exemple fournies pour ce tutoriel.

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

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’objets de zone de préparation

Une zone de préparation indique où les fichiers de données sont stockés (c’est-à-dire « préparés ») afin que les données contenues dans les fichiers puissent être chargées dans une table. Une zone de préparation externe nommée est un emplacement de stockage Cloud géré par Snowflake. Une zone de préparation externe fait référence à des fichiers de données stockés dans un compartiment S3. Dans ce cas, nous sommes en train de créer une zone de préparation qui fait référence aux exemples de fichiers de données nécessaires pour effectuer ce tutoriel.

La création d’une zone de préparation externe nommée est utile si vous souhaitez que plusieurs utilisateurs ou processus puissent charger des fichiers. Si vous prévoyez de charger les fichiers de données en zone de préparation juste vous, ou juste que dans une seule table, alors vous préférerez peut-être utiliser votre zone de préparation d’utilisateur ou la zone de préparation de table. Pour plus d’informations, voir Chargement en masse à partir d’Amazon S3.

Dans cette étape, vous créez des zones de préparation nommées pour les différents types de fichiers de données d’exemple.

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';
Copy

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';
Copy

Note

En utilisation normale, si vous créez une zone de préparation qui pointe vers vos fichiers de données privés, vous référencez une intégration de stockage créée à l’aide de CREATE STORAGE INTEGRATION par un administrateur de compte (c’est-à-dire un utilisateur avec le rôle ACCOUNTADMIN) ou un rôle avec le privilège global CREATE INTEGRATION :

CREATE OR REPLACE STAGE external_stage
  FILE_FORMAT = mycsvformat
  URL = 's3://private-bucket'
  STORAGE_INTEGRATION = myint;
Copy

Copie de données dans la table cible

Exécutez COPY INTO <table> pour charger les données en zone de préparation dans les tables cibles.

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

Nettoyage

Bravo, vous avez terminé le tutoriel.

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

Langage: Français