14 Jan 19 · npack · #Snowflake ·   Bookmark   ×

Load Data into Snowflake using SnowSQL

Login to SnowSQL

Connect to snowsql by using the below command (Just remember to use the proper account (Including region) and username)

snowsql -a your_account_name -u username

You will be prompted to enter the password, and you should be connected now.

Set the DB Context

USE WAREHOUSE TRAINING_WH;
USE DATABASE SALES_NAVEEN_DB;
USE SCHEMA SALES_DATA;

Create the table:

create or replace table emp_basic (
first_name string ,
last_name string ,
email string ,
streetaddress string ,
city string ,
start_date date
);

Download the Data file

Go ahead and download the data file from this link.  Unzip and Save the file in C Drive.

Run the below command to put (SFTP) the file to snowflake staging area:

put file://C:\Users\Naveen\Desktop\getting-started\employees0*.csv @SALES_NAVEEN_DB.SALES_DATA.%emp_basic;

List the staged files, just to make sure everything is good

list @SALES_NAVEEN_DB.SALES_DATA.%emp_basic;

Copy the data into Target table

copy into emp_basic
from @%emp_basic
file_format = (type = csv field_optionally_enclosed_by='"')
pattern = '.*employees0[1-5].csv.gz'
on_error = 'skip_file';

Query the loaded data

Return all rows and columns from the table:

 select * from emp_basic;

Let us also insert rows directly into a table using the INSERT DML command.

For example, to insert two additional rows into the table:

 insert into emp_basic values
('Clementine','Adamou','cadamou@sf_tuts.com','10510 Sachs Road','Klenak','2017-9-22') ,
('Marlowe','De Anesy','madamouc@sf_tuts.co.uk','36768 Northfield Plaza','Fangshan','2017-1-26');

Query Rows Based on Email Address

select email from emp_basic where email like '%.uk';

Use the DATEADD function to calculate when certain employee benefits might start. Filter the list by employees whose start date occurred earlier than January 1, 2017:

select first_name, last_name, dateadd('day',90,start_date) from emp_basic 
where start_date <= '2017-01-01';

Clean up the table

Let us go ahead and clean up the table that we just loaded

Drop table emp_basic;

📙 Course Index
◀ Prev: Snowflake Architecture
▶ Next: Unload data using SnowSQL

npack

posted on 14 Jan 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied