Prerequisites¶

You complete this tutorial using a script and data file we’ve provided. The instructions require you to load JSON data into a table. If you aren’t familiar with data loading in Snowflake, we recommend you first complete Tutorial: Bulk Loading from Amazon S3 Using COPY.

This tutorial requires the SnowSQL (CLI Client) to execute SQL statements. For installation instructions, see Installing SnowSQL.

Script for Creating Required Tutorial Objects¶

The following script creates objects specifically for use with this tutorial. When you have completed the tutorial, you can drop the objects to remove them.

-- Create a database. A database automatically includes a schema named 'public'.

CREATE OR REPLACE DATABASE mydatabase;

-- Specify the active database and schema for the current user session. Specifying
-- a database now enables you to perform your work in this database without having
-- to provide the name each time it is requested.

USE SCHEMA mydatabase.public;

-- Create a target table for the JSON data

CREATE OR REPLACE TABLE raw_source (
  SRC VARIANT);

-- Create a warehouse

CREATE OR REPLACE WAREHOUSE mywarehouse WITH
  WAREHOUSE_SIZE='X-SMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED=TRUE;

-- Specify the active warehouse for the current user session.
-- Snowflake does not actually require or use a warehouse until you load data using the COPY command.

USE WAREHOUSE mywarehouse;

-- Create an external stage that points to the S3 bucket containing the sample file for this tutorial

CREATE OR REPLACE STAGE my_stage
  URL = 's3://snowflake-docs/tutorials/json';

Data File for Loading¶

Snowflake provides a sample data file staged in a public S3 bucket. The sample data file contains the following JSON data:

 {
  "device_type": "server",
  "events": [
    {
      "f": 83,
      "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19",
      "t": 1437560931139,
      "v": {
        "ACHZ": 42869,
        "ACV": 709489,
        "DCA": 232,
        "DCV": 62287,
        "ENJR": 2599,
        "ERRS": 205,
        "MXEC": 487,
        "TMPI": 9
      },
      "vd": 54,
      "z": 1437644222811
    },
    {
      "f": 1000083,
      "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22",
      "t": 1437036965027,
      "v": {
        "ACHZ": 6953,
        "ACV": 346795,
        "DCA": 250,
        "DCV": 46066,
        "ENJR": 9033,
        "ERRS": 615,
        "MXEC": 0,
        "TMPI": 112
      },
      "vd": 626,
      "z": 1437660796958
    }
  ],
  "version": 2.6
}

Next: Step 1. Copy Data Into the Target Table