Script: Loading JSON Data into a Relational Table

The annotated script in this tutorial loads sample JSON data into separate columns in a relational table directly from staged data files, avoiding the need for a staging table.

The script uses the following functions to modify the staged data during loading:

Note that the script includes a PUT statement. We recommend executing the script in SnowSQL or another client that supports PUT statements. Clients such as Snowsight and the classic web interface do not support the PUT command.

In this Topic:

Prerequisites

  • Active, running virtual warehouse.

  • Sample JSON data file (sales.json). If clicking the link does not download the file, right-click the link and save the link/file to your local file system.

    Then, copy the file to your temporary folder/directory:

    • macOS or Linux: //tmp

    • Windows: Open an Explorer window and enter %TEMP% in the address bar.

Example of Sample Data

The following is a representative row in the sample JSON file:

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

SQL Script

Note

The PUT statement in this script assumes you are using a macOS or Linux environment. If you are using Windows, adjust the statement as noted in the comments.

/* 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;
Back to top