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:
Right-click the name of the archive file,
data-load-internal.zip
and save the link/file to your local file system.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
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" } }, ]
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;
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.