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




put file://C:UsersAdministratorDesktopSNOWFLAKESNOWFLAKEPRACTICEExercisesgetting-startedemployees0*.csv@SALES_SUBBU_DB.SALES_DATA.%emp_basic;

above command i am getting error.

without ';' it is keep on executing many times

npack100d

What is the error message that it throws

is there any other third party tool (like toda, sql developer) we can use to work snowsql. other than command line. If yes please shae the steps to configure and work.

You want a simple file to load files to a table in snowflake ? I am not aware of a tool. But we can write a tiny script to achieve this. We could make it a generic one, so that the end users can just double click on it to upload data.

When I get time, I will build that script and share here, Keep an eye


Community Software by Hittly