How to create a simple table in snowflake with external data

Create a table in snowflake using external data

Recently I needed to upload a data into snowflake as a table and perform some operations on it. Surprisingly as it is, I found that although I knew how to create a table in sql; I didn't know how to upload a data into snowflake. Obviously, I could have gone into internet to find out python libraries to solve this problem; i.e. access snowflake via a python session, then get the data and upload it in some probable template for tables. But it was a quick work and I wanted to do it fast. So I chose to upload it in a s3 data bucket and then create an empty table in snowflake; in which I would then put the data, pulling it into snowflake from s3. Now that is a nice and easy way to do; and let's see the exact procedure and how do we complete each of the parts.

Upload the data into s3:

Uploading data into s3 can be done again in two ways. The first one is to use a boto3 session and then upload the data into s3 via that boto session. If you have not read about boto yet, read about them in this writing by realpython about aws,s3 and python. Anywhoo, we are going to upload the data manually as I told it is a quick job. 

For manual uploading, 

(1) you need to access your s3.console; navigate to the specific bucket.

(2) then finally click the upload option. 

(3) select the option to "add files" and browse your local and add the file. Finish uploading the file, clicking the upload button.

This will start a progress bar and once that finishes, the upload is done. 

Creating a table in snowflake:

To create a table, we will have to use Create table statement as follows:

create table [data_base_name].[schema_name].table_name(
pk_column data_type primary key,
column1 data_type NOT NULL,
column2 data_type NOT NULL
table constraints)


Here you need to consider this fact that, if your database or the table needs an primary key, then only include the pk_column line. If you don't, then just replace the first line also with 'column data_type NOT NULL' format line. There are further technicalities of creating more complicated table and applying table constraints.If you are interested in more complicated table construction, follow this link.

Pulling data from s3 to snowflake:

For this section, you already have the internal connection of snowflake and s3 database. But that being more of a dev work, that is out of the scope. So lets consider now that the connection is already built. Then, to pull the data you can use the following format:

copy into abc_table   
from s3://snowflakebucket/data/abc_files 
credentials=(aws_key_id='$KEY_ID' aws_secret_key='$SECRET_KEY') 

file_format = (type = csv field_delimiter = ',');

Using this code, just provide the file path to the right file you have uploaded; and then voila your table is populated with the data you wanted.

Things to check:

The file you upload and the table you create must have 

(1) same order of columns

(2) same columns

Otherwise you will get errors and your task will not follow through.

Conclusion:

In conclusion, we learnt how to create a simple table in sql, snowflake interface and then load a data from s3 and populate the table with the same. Thanks for reading!