Topics: 6 min readtime

Snowflake 101: Loading Data from Local (3/4)

Written by Issye Margaretha
Friday 18 December, 2020

Loading Data from Local Environment


This blog is the third part of a 4-series Snowflake 101 blog. You can find the first two blogs here:
1. Why is Snowflake great?
2. Setting up your environment and database

In this article, we are going to learn how to load a Structured data from our computer into Snowflake. But first, make sure you have fulfilled all of these requirements below:

  1. Set up data warehouse & database: Make sure the data you are about to import has a “Home” within Snowflake, that being data warehouse, database, and schema being set-up. If you’re not sure how to create those, check out our previous blog post on Snowflake 101: Setting Up Environment and Database

  2. File format: The format of our file must be suitable for Snowflake. See the documentation from Snowflake below to make sure that your file type is supported.

Supported Snowflake files

 

Download this file to follow along: Superstore_Sales.csv

Create Table

 

Create table in Snowflake

 

  1. Drill into your database by clicking the database name. In case you’re following our previous tutorial, this would then be RETAIL_DB_SALES.

  2. From the table tab, click Create

  3. When the Create Table dialog box opens, put in the Table Name as SUPERSTORE_SALES

  4. Leave the Schema Name asSALES_DATA

  5. Provide a short Comment about the data, such as “Sales per Category”

  6. In the column section of the dialog, click + sign to add a new column detail. Check your data and adjust the type accordingly. If you expect the column to never be Null, then click on the Not Null column

  7. Finish

As an alternative to the UX, you can also create the table using query in the SQL Query Pane by running the code below:

USE WAREHOUSE SNOWFLAKE_WH;
USE DATABASE Retail_DB_Sales;
USE SCHEMA Sales_Data;

CREATE TABLE "RETAIL_DB_SALES"."SALES_DATA"."SUPERSTORE_SALES"
("Category" STRING,
"Customer Name" STRING,
"Order ID" STRING NOT NULL,
"Postal Code" INTEGER,
"Product Name" STRING NOT NULL,
"Quantity" INTEGER NOT NULL,
"Sales" INTEGER NOT NULL,
"Segment" STRING,
"Sub-Category" STRING)
COMMENT = 'Superstore Sales per Category';


Import CSV from Local Drive

 

Load local file Snowflake

 

  1. From the breadcrumb trail, make sure that you are still in SUPERSTORE_SALES(SALES_DATA) table

  2. From the table tab, click Tables

  3. Click Load Table. You can see from the pop-up that there are four steps here being Warehouse, Source Files, File Format, and Load Options

  4. Warehouse (to carry out the work): select SNOWFLAKE_WH -> Next

  5. Source Files (to identify the file we want to load): Select Load Files From Your Computer -> Next

  6. File Format (provide details of our file format): Click on the + sign

  7. Now go back to Tables option, make sure that in the breadcrumb trail, you see Databases > RETAIL_DB_SALES > SUPERSTORE_SALES (SALES_DATA). If not, select SUPERSTORE_SALES data from the table list. Then choose Load Table.

  8. You may now navigate to the RETAIL_DB_SALES database in the Navigation Pane and see that the SUPERSTORE_SALES table is there. You can also run a simple SELECT * statement or click Preview Data in the Preview Pane to view the data you’ve loaded.


In the Create File Format dialog box, provide the information below:

  • Name: Sales_Data_Comma

  • Column separator: Comma

  • Header lines to skip: 1

  • You can change other options as you’d like but for now, we will leave the remaining by default

 

Check file format Snowflake

 

  • Finish

 

You can also replace this whole step 6 with this piece of code below:

USE WAREHOUSE SNOWFLAKE_WH;
USE DATABASE Retail_DB_Sales;
USE SCHEMA Sales_Data;

 

CREATE FILE FORMAT SALES_DATA_COMMA
TYPE = 'CSV'
COMPRESSION = 'AUTO'
FIELD_DELIMITER = 'Comma'
RECORD_DELIMITER = '\n'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE'
TRIM_SPACE = FALSE
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
ESCAPE = 'NONE'
ESCAPE_UNENCLOSED_FIELD = '\134'
DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
NULL_IF = ('\\N');

 

Snowflake overview of Superstore data

 

The Load Data will pop-up as below. Follow these steps below:

  1. Warehouse: choose SNOWFLAKE_WH

  2. Source File: choose Load Files from your Computer, then Load the Superstore_Sales.csv you downloaded in the beginning of this tutorial

  3. File Format: choose SALES_DATA_COMMA from the drop down

  4. Load Options: select the second option (Stop loading, rollback, and return the error)

  5. Load.

 

Load data within Snowflake

 

This is the pop-up you will see once the data is successfully loaded.

 

Snowflake load results

 

If your file does not load and you don’t understand the issue, you may want to try some of the other Load Options from step d. You can choose the option “Continue loading...” that would let you see which rows successfully loaded and allow you to see which rows that did not.

 

Final Snowflake result

 

Congrats! You’ve made it so far and that means that you are ready to tackle the next challenge on how to Import a CSV from Cloud storage. So stay with us because we’ll be talking about how to load structured data from AWS S3 in the upcoming post.

Issye