How to connect and perform operations of Snowflake using Python

Snowflake is a powerful Data Warehouse of 2020 as many companies are migrating their data to Snowflake and hopefully there would be a list of companies who will migrate SOON!

To put cherry on the cake — for automation on snowflake, the programming language which is in the HIGH demand i.e. “PYTHON” can be leveraged to perform almost all the operations of snowflake using SQL statements.

Image for post
Image for post
Lets see power of Python
Image for post
Image for post
SNOWFLAKE Data Warehouse

Install Snowflake connector to use in Python

python -m pip install --upgrade pip

NOTE: Below instructions can be followed anytime you install any python library

If you are using Jupyter Notebook/Jupyter Lab/Spyder from Anaconda Distribution then run above command in Anaconda prompt by going in START menu of your computer and search for Anaconda prompt.

Else, if you are using python directly then use command prompt to verify above.

Now, let us install snowflake connector, run below command:

pip install --upgrade snowflake-connector-python

Make connection with Snowflake using Python

import snowflake.connector as sf
import pandas as pd
user='snowflake-username'password='snowflake-password'account='unique-acct-id.region' ### This can be found in your snowflake account URL. Example: kg00000.us-centeral1.gcp (if you are using Google cloud) or eca00000.us-east-1 (if using AWS cloud)database='database-name'warehouse='virtual-warehouse-name'schema = 'schema-name'role = 'role' ### Choose role which have access to above mentioned database, warehouse and schema, ACCOUNTADMIN is the highest level role in snowflake# Establishing connection with snowflake
conn = sf.connect(user = user,
password = password,
account = account,
)
snowflakecursor = conn.cursor()# user defined function to run sql queries
def run_query(connection,query):
cursor = connection.cursor()
cursor.execute(query)
cursor.close()
# This try and except block will help to pass user parameters on snowflake
try:
sql = 'use warehouse {}'.format(warehouse)
run_query(conn, sql)

# if virtual warehouse is in suspended state, this try/except block will resume the warehouse
try:
sql = 'alter warehouse {} resume'.format(warehouse)
run_query(conn, sql)
except:
pass

sql = 'use database {}'.format(database)
run_query(conn, sql)

sql = 'use role {}'.format(role)
run_query(conn, sql)

sql = 'use schema {}'.format(schema)
run_query(conn, sql)
# in case of any error, an exception will be raised
except Exception as e:
print(e)

Using above script, your connection will be established and required user parameters such as database, schema, role and virtual warehouse selection will be made on snowflake.

NOTE: While copying above script, INDENTATIONS could be disturbed so just focus on indentations if you get any errors.

Perform operations by running SQL statements on Python

sql = "create or replace table employee(id number(10), \
name varchar(20) \
department varchar(8))"
run_query(conn, sql)

NOTE:
a.) sql variable will be created which contains the sql statement to create employee table.
b.) at the end of 1st and 2nd line of sql statement, ‘\’ is used to tell python that the next line is in continuation which is why ‘\’ is not in the last line of sql statement
c.) run_query is a user defined function created in the previous section while establishing connection with snowflake. It requires two parameters: ‘conn’ which is the connection string of snowflake and other one is ‘sql’ which we have written above.

Store results of query in a Pandas DataFrame

import pandas as pdsql = "select * from employee limit 10"df = pd.read_sql(sql, conn)

~we have imported pandas module
~’sql’ string contains sql statement to fetch first 10 rows (using LIMIT keyword) from employee table
~using snowflake connection string ‘conn’, we are running ‘sql’ on snowflake and storing results in Pandas DataFrame ‘df’

We can perform automation using other functionalities/features of snowflake such as snowpipe/streams, etc. and also we can connect Python with AWS to interact with both simultaneously as per user needs.

Just an example: If you are migrating data from an X database to Snowflake then AWS s3 (Simple Storage Service) can act as an intermediate storage.

If you wish to learn Snowflake from scratch then access my Snowflake Masterclass at Udemy:

https://www.udemy.com/course/snowflake-zero-to-hero-masterclass/

Please feel free to drop any questions or queries by responding on this BLOG.

And yes, if you really liked this then show your support by CLAP on this BLOG which will motivate me to come back with some more exciting techie things…

HAPPY READING!!

Written by

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store