Prerequisites

The tutorial assumes the following:

  • You have a Snowflake account and a user with a role that grants the necessary privileges to create a database, tables, and virtual warehouse objects.

  • You have SnowSQL installed.

The Snowflake in 20 Minutes tutorial provides the related step-by-step instructions to meet these requirements.

In addition, you need to do the following before you start the tutorial:

  • Download sample files provided for this exercise.

  • Create a database, tables, and a virtual warehouse for this tutorial. These are the basic Snowflake objects needed for most Snowflake activities.

Downloading the Sample Data Files

For this tutorial you need to download the sample data files provided by Snowflake.

To download and unzip the sample data files:

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

  2. Unzip the sample files. The tutorial assumes you unpacked files in to the following directories:

  • Linux/macOS: /tmp/load

  • Windows: C:\\temp\load

These data files include sample contact data in the following formats:

  • CSV files that contain a header row and five records. The field delimiter is the pipe (|) character. The following example shows a header row and one 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
    Copy

  • A single file in JSON format that contains one array and three objects. The following is an example of an array that contains one of the objects:

    [
 {
   "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

Creating the Database, tables, and Warehouse

Execute the following statements to create a database, two tables (for csv and json data), and a virtual warehouse needed for this tutorial. After you complete the tutorial, you can drop these objects.

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

The CREATE WAREHOUSE statement sets up the warehouse to be suspended initially. The statement also sets AUTO_RESUME = true, which starts the warehouse automatically when you execute SQL statements that require compute resources.

Next: Step 1. Create File Format Objects

Language: English