How to Load Data from S3 to 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.

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

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.

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

Create a Warehouse

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

We will create the smallest possible Warehouse called TEST_WH

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.

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