Snowflake in 10 Minutes

This short, introductory tutorial uses SnowSQL, the Snowflake command line client, to introduce the key tasks for using Snowflake. It assumes the following:

  • You have an existing user in Snowflake (with the necessary privileges to create databases, tables, and virtual warehouses).
  • You have installed SnowSQL.

To complete the tutorial:

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 in the first segment (e.g. abc123 in https://abc123.snowflakecomputing.com).
  • user_name is your Snowflake user login name.

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

Step 2: Create a Database and Table

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

CREATE DATABASE testdb;

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

Step 3: Prepare a Data File for Loading into the Table

On your machine, create a plain-text CSV file:

  1. The file should contain one record per line and use commas as the column separator within each record.

  2. Each record must contain three fields that match the table you created:

    • First field can contain any string (corresponds to the name column).
    • Second field can contain any string (corresponds to the city column).
    • Third field can contain any number (corresponds to the salary column).

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

    For example, here are three records with the first field in each field enclosed in double quotes because it contains commas:

    "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 an important point because this is the default that Snowflake expects when loading CSV data.

  3. Name the file testdatafile.csv and store it in a root-level directory named tmp on your machine, 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 4: Stage the Data File in Snowflake

Use the PUT command to stage the testdatafile.csv file in your user staging location:

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 staging location provided for your user.
  • /demoload is a prefix that is 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: Create a Virtual Warehouse (Required for Loading Data into a Table)

Use the CREATE WAREHOUSE command to create a virtual warehouse named testwarehouse:

CREATE WAREHOUSE testwarehouse WITH WAREHOUSE_SIZE=SMALL;

Step 6: 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='"');

The FIELD_OPTIONALLY_ENCLOSED_BY file format identifies double quotes as the character used to enclose strings in the data file.

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

Step 7: 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.