Script : chargement de données JSON dans une table relationnelle

Le script annoté de ce tutoriel charge des données d’échantillon JSON dans des colonnes séparées d’une table relationnelle directement à partir de fichiers de données en zone de préparation, évitant ainsi de devoir recourir à une table de mise en zone de préparation.

Le script utilise les fonctions suivantes pour modifier les données mises en zone de préparation pendant le chargement :

Notez que le script inclut une instruction PUT. Nous vous recommandons d’exécuter le script dans SnowSQL ou un autre client qui prend en charge les instructions PUT. Les clients tels que Snowsight et classic web interface ne prennent pas en charge la commande PUT.

Dans ce chapitre :

Conditions préalables

  • Un entrepôt virtuel actif et fonctionnel.

  • Exemple de fichier de données JSON (sales.json). Si cliquer sur le lien ne télécharge pas le fichier, cliquez avec le bouton droit sur le lien et enregistrez le lien/le fichier sur votre système de fichiers local.

    Copiez ensuite le fichier dans votre dossier/répertoire temporaire :

    • macOS ou Linux : //tmp

    • Windows : ouvrez une fenêtre d’explorateur et saisissez %TEMP% dans la barre d’adresse.

Exemple de données d’échantillon

Ce qui suit est une ligne représentative dans le fichier exemple JSON :

{
   "location": {
      "state_city": "MA-Lexington",
      "zip": "40503"
   },
   "sale_date": "2017-3-5",
   "price": "275836"
}

Script SQL

Note

L’instruction PUT dans ce script suppose que vous utilisez un environnement macOS ou Linux. Si vous utilisez Windows, ajustez l’instruction comme indiqué dans les commentaires.

/* Create a target relational table for the JSON data. The table is temporary, meaning it persists only for   */
/* the duration of the user session and is not visible to other users.                                        */

CREATE OR REPLACE TEMPORARY TABLE home_sales (
  city STRING,
  zip STRING,
  state STRING,
  type STRING DEFAULT 'Residential',
  sale_date timestamp_ntz,
  price STRING
  );

/* Create a named file format with JSON as the file type.                                                     */

CREATE OR REPLACE FILE FORMAT sf_tut_json_format
  TYPE = JSON;

/* Create a temporary internal stage that references the file format object.                                  */
/* Similar to temporary tables, temporary stages are automatically dropped at the end of the session.         */

CREATE OR REPLACE TEMPORARY STAGE sf_tut_stage
  FILE_FORMAT = sf_tut_json_format;

/* Stage the data file.                                                                                       */
/*                                                                                                            */
/* Note that the example PUT statement references the macOS or Linux location of the data file.               */
/* If you are using Windows, execute the following statement instead:                                         */

-- PUT file://%TEMP%/sales.json @sf_tut_stage;

PUT file:///tmp/sales.json @sf_tut_stage;

/* Load the JSON data into the relational table.                                                              */
/*                                                                                                            */
/* A SELECT query in the COPY statement identifies a numbered set of columns in the data files you are        */
/* loading from. Note that all JSON data is stored in a single column ($1).                                   */

COPY INTO home_sales(city, state, zip, sale_date, price)
   FROM (SELECT SUBSTR($1:location.state_city,4), SUBSTR($1:location.state_city,1,2),
                $1:location.zip, to_timestamp_ntz($1:sale_date), $1:price
         FROM @sf_tut_stage/sales.json.gz t)
   ON_ERROR = 'continue';

/* Query the relational table                                                                                 */

SELECT * from home_sales;
Revenir au début