14 Jan 19 · npack ·       Add to Favorites  

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


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


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