Snowflake in 10 Minutes

This short, introductory tutorial uses SnowSQL, the Snowflake command line client, to introduce the key tasks for using Snowflake, including loading a small amount of data in CSV format into a database table and then querying the table.

To complete the tutorial:

Prerequisites

This tutorial assumes the following:

  • SnowSQL is installed in your local client environment.

  • You have an existing user in Snowflake with a role that has been granted the necessary privileges to create databases, tables, and virtual warehouses.

    If you do not have a user yet or your user does not have the appropriate role, please contact one of your account or security administrators (users with the ACCOUNTADMIN or SECURITYADMIN role).

  • You have an existing plain-text file containing CSV data to load into a table. The format of the data in the file must match the table into which the data will be loaded (see next section for details).

Preparing a Data File for Loading into a Table

If you do not have a plain-text CSV data file for loading into a table, create one now in your local file system:

  • The file should contain 1 record per line and use commas as the field separators within each record.

  • Each record must contain 3 fields (to match the table you will load):

    • First field can contain any string.
    • Second field can contain any string.
    • Third field can contain any number.

    However, because you are loading comma-separated values, if any of the strings have values that contain commas, the commas in the values must be escaped using the backslash character (\) or the entire string must be enclosed in double quotes (" ").

    For example, here are three records with the first field in each record enclosed in double quotes to escape the commas in the string:

    "Smith, John",Boston,35000
    "Doe, Jane",Los Angeles,40000
    "Thompson, Mary",Los Angeles,45000
    

    Note that there are no blank spaces before or after the commas separating each field in each record. This is important because this is the default that Snowflake expects when loading CSV data.

  • Name the file testdatafile.csv and store it in a root-level directory named tmp in your local file system, e.g.:

    Linux or Mac OS: /tmp/testdatafile.csv

    Windows: C:\tmp\testdatafile.csv

For more detailed information about loading data into Snowflake tables, see Data Loading.

Step 1: Log into Snowflake

Open a terminal window and start SnowSQL at the command prompt:

snowsql -a <account_name> -u <user_name>

Where:

  • <account_name> is the name assigned to your account by Snowflake. In the URL you received from Snowflake, your account name is the first segment in the domain.
  • <user_name> is the login name for your Snowflake user.

When prompted, enter the password for your Snowflake user. For more information, see Using SnowSQL.

Step 2: Create a Database and Table

Loading data requires a database and table. The database is the container object for the table. The table stores the data and is used in queries.

To create a database and table:

  1. Use the CREATE DATABASE command to create a database named testdb:

    CREATE DATABASE testdb;
    
  2. Use the CREATE TABLE command to create a table named testemp with the following 3 columns:

    CREATE OR REPLACE TABLE testemp (name STRING, city STRING, salary NUMBER);
    

Important

For the purposes of this tutorial, the 3 columns in this table must match the structure of the CSV data file that you will load (i.e. each record in the file consists of 2 string fields and 1 number field).

Step 3: Create a Virtual Warehouse

A running and active virtual warehouse is required to load data from a file into a table.

Use the CREATE WAREHOUSE command to create a running, active small virtual warehouse named testwarehouse that will automatically suspend after 15 minutes (900 seconds) of inactivity:

CREATE WAREHOUSE testwarehouse WITH WAREHOUSE_SIZE=SMALL AUTO_SUSPEND=900;

Note

Data loading is performed in 2 steps: 1) staging the file(s) and 2) copying data from the files into the table. However, a virtual warehouse is actually only required to perform the second step.

Step 4: Stage the Data File in Snowflake

Use the PUT command to stage the testdatafile.csv file in your user stage (assigned to your user at creation time):

Linux or Mac OS:

PUT file:///tmp/testdatafile.csv @~/demoload;

Windows:

PUT file://C:\tmp\testdatafile.csv @~/demoload;

In this example:

  • file:// specifies the full path to the data file that you stored in the tmp directory.
  • @~ specifies to use the file stage for your user.
  • /demoload is an optional prefix that will be added to the staged file. Prefixes can be used to organize staged files into a logical structure of “directories” and “sub-directories” within a staging location.

To view the file you just staged, use the LIST command:

LIST @~;

Step 5: Load Data from the Staged File into the Table

Use the COPY INTO table command to load data from your staged file into testemp:

COPY INTO testemp FROM @~/demoload FILE_FORMAT=(FIELD_OPTIONALLY_ENCLOSED_BY='"');

In this example:

  • @~/demoload matches the stage and prefix you specified in the previous step.
  • FIELD_OPTIONALLY_ENCLOSED_BY is a file format option that identifies double quotes as the character used to enclose the strings in the data file.

For more detailed information about loading data into Snowflake tables, see Data Loading.

Step 6: Query the Data You Loaded

Once the data loads successfully, you can execute queries on the table. For example:

SELECT * FROM testemp;

That’s it! You’re now ready to learn more about using Snowflake to harness the power of your data. For further reading, see Using Snowflake.