Predictive Hacks

How to Load Data from S3 to Snowflake

snowflake

In this tutorial, we will show you how to create a database and a warehouse and how to load data from S3.

Create a Database

The first thing that we need to do is to create a database. This is easy. We login into the Snowflake platform and we go to Databases.

How to Load Data from S3 to Snowflake 1

Then we click on Create. For this tutorial the database will be the GPIPIS_DB

How to Load Data from S3 to Snowflake 2
How to Load Data from S3 to Snowflake 3

In the database, we can create a schema, where the default is the “public” and we can create a table or a schema by clicking on the Create button.

How to Load Data from S3 to Snowflake 4

Let’s say that I want to create the MYIRISTABLE. In this tutorial, we will create the tables and the stages programmatically.

How to Load Data from S3 to Snowflake 5

Create a Warehouse

Similarly, we can create the warehouse by clicking on Warehouses and then on the Create button.

How to Load Data from S3 to Snowflake 6

We will create the smallest possible Warehouse called TEST_WH

How to Load Data from S3 to Snowflake 7

By clicking Finish, the Warehouse is ready.

Load Data from S3

In order to load data from S3, we can go to Worksheets. Do not forget to choose your database.

How to Load Data from S3 to Snowflake 8
How to Load Data from S3 to Snowflake 9

Our goal is to load the iris.csv dataset that we have in an S3 bucket called gpipis-iris-dataset. The first thing that we need to do, is to specify the file format, wherein my case is CSV.

create or replace file format mycsvformat
  type = 'CSV'
  field_delimiter = ','
  skip_header = 1;

Then, we need to create the table, called myiristable

create or replace table myiristable (
  sepal_length numeric,
  sepal_width numeric,
  petal_length numeric,
  petal_width numeric,
  variety string);
  

The next step is to create an S3 stage. You have to put your credentials.

  create or replace stage my_s3_stage url='s3://gpipis-iris-dataset/'
  credentials=(aws_key_id='xxx' aws_secret_key='xxx')
  file_format = mycsvformat;

Finally, we can copy the data to our table.

  copy into myiristable
  from @my_s3_stage;

and we can confirm that the data are in Snowflake!

select * from myiristable
How to Load Data from S3 to Snowflake 10
How to Load Data from S3 to Snowflake 11

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Leave a Comment

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

snowflake
Uncategorized

Get Started with Python UDFs in Snowflake

Finally, Snowflake supports UDF (user-define functions) in Python. Thank you Snowflake! Apart from Python, we can write UDFs in Java,