Loading CSV data into Snowflake Tables

Loading CSV data into Snowflake Tables via Snowflake Web-UI

In this tutorial, We will walk you through the Snowflake Web UI and SQL Statement to upload the CSV data, which reside on Local Storage to Snowflake database. Snowflake Web UI is very easy to use and quite powerful.

Therefore, I will show you the same steps via Web-UI and SQL Statement to complete the tutorial. Hope it will help you to begin your journal in Snowflake 

Before You Begin -

An activated Snowflake user to access Snowflake (sign up for a free trial if needed) and granted to create databases, tables, and virtual warehouses

• (Nice to have) Basic information about Snowflake
• (Nice to have) Basic knowledge about SQL
• Download sample data files from NZ Weblink (link)

Getting Started - 

1. Connect and login into Snowflake web interface 

      To log into the Snowflake web interface:

• Go to the URL provided by Snowflake for your account.
• Login with your credentials
• Create the dedicated warehouse for uploading of data in table, Industry recommend using a dedicated warehouse for bulk upload or incremental continuous upload to tables.

2.Create Snowflake Objects

After you log in, you need to create a database, a table, and a virtual warehouse.

• Create the sstech_training database using the CREATE DATABASE command

• Create a table named nz_migration_stats in sstech_training.public using the CREATE TABLE command:

Guide - Before uploading of CSV file we need to create the table in snowflake to keep the data into snowflake warehouse, This table columns data should be matchting to data exists into csv file.

In this example, Data column was having the data of YYYY-MM only so we create table with data type date but handled this conversion during creation of stage.

  

3.Load data the Target Table

There are many ways to import data into Snowflake. For example, to execute PUT or COPY to upload local data files to the table via SnowSQL only, not by running PUT and COPY commands in GUI Console.

The alternative way is loading data by Snowflake Wizard of the Web UI. Simple, effective, but with some limitations (file size limit: 50 MB).

This step is combined step 3.1 and step 3.2 of the tutorial at the official site. Uploaded data will be load into target.

3.1 Create File Format

Creates a named file format that can be used for bulk loading data into (and unloading data out of) Snowflake tables.

Guide - During creation of file format, we need to consider below things to populate the data without errors.

1. Column Separator - 

This supports, or | or Tab or Other Customized separator, as in the example we used CSV file so column separator “,” is used.

2. Filed Optionally Enclosed by -

 This is used for CSV where some string may contain “,” with Double Quotes, If this field is not specified then strange will be treated multiple columns.

3. Data Format - 

Snowflake use YYYY-MM-DD as default date format, if you are using different date-time format then it is recommended to use “Others” as date type to put the non-default format. 

In this example, CSV file consists of date in YYYY-MM format only so we chose “Others” with format “YYYY-MM”  

4. Timestamp Format -

Snowflake use YYYY-MM-DD:HH24:MM:SS as default date format, if you are using different date-time format then it is recommended to use “Others” as date type to put the non-default format. 

In the example, We used auto timestamp format because No column in excel has the timestamp data type.

Null String - 

This tells how Snowflake will handle the columns having blank values.

3.2.Load data the Target Table

Once File format created, We need to follow the below steps to upload the data into the table.

Step 1 -  Go to Database and Select Table, Click on Load Data and Select the warehouse which will be used to upload the data

Step 2 - Select Source File which need to get uploaded.

Step 3 - Select the file format

Guide - Please remember we created a file format especially for these files so select that file format only.

Step 4 - Load Options 

Here you will option to handle the errors.

Once you click on the Load Button, Upload Process will start uploading the data. This is a two-stage process 

1 - First it will upload to s3 of  the Staging Area 

2 - Once file uploaded into the staging area, A COPY process will move data into the table.

Congrats, We uploaded the whole data sets without any errors.

Step 4. Query the Loaded Data

After you loaded data into the target table “nz_migration_stats”, you can use SQL statements to query data.

Step 5. Clean Up

As this is a training tutorial so It is recommended to drop the database and warehouse. 

Complete Command Sets used to make this tutorial.

1. create database sstech_training;
2. create or replace warehouse sstech_training WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND=5 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
3. grant all on warehouse sstech_training to accountadmin;
4. use sstech_training;
5. create or replace table nz_migration_stats (year_month date,

 month_of_release date,  passenger_type varchar(255), direction varchar(100), sex char(6), age varchar(100), estimate INT, standard_error varchar(2000), status char(11));

6. grant all privileges on database sstech_training to accountadmin;
7. grant all privileges on table nz_migration_stats to accountadmin;
8. CREATE FILE FORMAT "SSTECH_TRAINING"."PUBLIC".NZ_Migration_Stats TYPE = 'CSV' COMPRESSION = 'AUTO'FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '\042' TRIM_SPACE = TRUE ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = '\134' DATE_FORMAT = 'YYYY-MM' TIMESTAMP_FORMAT = 'AUTO'NULL_IF = ('NULL');
9. select * from nz_migration_stats;