Prerequisites

This tutorial requires the following objects:

  • Database with two tables

  • Virtual warehouse

  • Files containing CSV and JSON data to load into the tables.

You can complete this tutorial using your own existing objects in Snowflake; however, we recommend using the script and set of data files we’ve provided.

Script for Creating Required Tutorial Objects

The following script create 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;

/* Create target tables for CSV and JSON data. The tables are temporary, meaning they persist only for the duration of the user session and are not visible to other users. */

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 a warehouse

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

Data Files for Loading

Download the set of sample data files. Right-click the name of the archive file, data-load-internal.zip, and save the link/file to your local file system.

You can unpack the sample file to any location; however, we recommend using the directories referenced in the tutorial examples:

  • Linux or macOS: /tmp/load.

  • Windows: C:\temp\load.

The sample data files include dummy contact information in the following formats:

  • CSV files that contain a header row and five records. The field delimiter is the pipe (|) character.

    Example record:

    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
    
  • A single file in JSON format that contains one array and three objects.

    Example object:

    [
     {
       "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"
       }
     },
    ]
    

Next: Step 1. Create File Format Objects