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




Subbu-jan4Mar 26 10:11

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

npackMar 26 12:10

What is the error message that it throws

Subbu-jan4Apr 01 05:05

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.

npackApr 01 05:49

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

Hi, I am using Dbeaver to connect to Snowflake on-prem DWH. Dbeaver is a SQL software app which gives you access to almost all the Databases. Install Dbeaver and connect to Snowflake. You can execute the queries in this tool instead of command line.

Hi, 

1) Regarding table creation.  We are using create or replace command, what if table is already exists in the schema?  will it overwrite it?  if yes, how to handle it. as table might have created by some other user.

2) After insertion how does get stores in database?  is there commit; or rollback; like in sql commands?

1. If you are worried about accidentally overwriting tables, you can remove the "replace" from the statement. And just do a plain create table instead

2. It's autocommited. No need to explicitly mention any commits


Community Software by Hittly