Prerequisites¶
The tutorial assumes the following:
You have a Snowflake account that is configured to use Amazon Web Services (AWS) and a user with a role that grants the necessary privileges to create a database, tables, and virtual warehouse objects.
You have SnowSQL installed.
Refer to the Snowflake in 20 minutes for instructions to meet these requirements.
Snowflake provides sample data files in a public Amazon S3 bucket for use in this tutorial. But before you start, you need to create a database, tables, and a virtual warehouse for this tutorial. These are the basic Snowflake objects needed for most Snowflake activities.
About the sample data files¶
Snowflake provides sample data files staged in a public S3 bucket.
Note
In regular use, you would stage your own data files using the AWS Management Console, AWS Command Line Interface, or an equivalent client application. See the Amazon Web Services documentation for instructions.
The sample data files include sample contact information 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 OR REPLACE DATABASE mydatabase;
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 OR REPLACE WAREHOUSE mywarehouse WITH
WAREHOUSE_SIZE='X-SMALL'
AUTO_SUSPEND = 120
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED=TRUE;
Note the following:
The
CREATE DATABASE
statement creates a database. The database automatically includes a schema named ‘public’.The
CREATE TABLE
statements create target tables for CSV and JSON data. The tables are temporary, that is, they persist only for the duration of the user session and are not visible to other users.The
CREATE WAREHOUSE
statement creates an initially suspended warehouse. The statement also setsAUTO_RESUME = true
, which starts the warehouse automatically when you execute SQL statements that require compute resources.