Skip to content

Snowflake

Python

In the example below, we show how to set up a connection to a Snowflake data source using Python. We demonstrate how you can read data from a Snowflake table to a Pandas DataFrame, and vice versa. To do this, we need Python 3.6 or newer (how to install Python). In a virtual environment, you should install:

We recommend using a requirements.txt file. In this file, you can add:

snowflake-sqlalchemy
SQLAlchemy
pandas
python-dotenv

Read from Snowflake table

In the following example code, we first set up a connection to Snowflake using SQLAlchemy. With pandas, we read a table from Snowflake to a Pandas DataFrame.

import os
from snowflake.sqlalchemy import URL as snowflake_url
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section dotenv
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(snowflake_url(
    account=os.getenv("DB_ACCOUNT"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    database=os.getenv("DB_DATABASE"),
    schema=os.getenv("DB_SCHEMA"),
    warehouse=os.getenv("DB_WAREHOUSE"),
    role=os.getenv("DB_ROLE"),
))

df = pd.read_sql_table(table_name, engine)

Info

For Python, there are many ways to set up a connection to Snowflake. The example we show here is to demonstrate a method you could use with Pandas and how you could apply it in AskAnna. The AskAnna platform is flexible. For example, if you don't use Pandas you could setup the connection directly using snowflake-python-connector.

Project variables

To run the above example as a job in AskAnna, you should add project variables. On the project page, go to the tab variables. Here you can create new variables. To run the above example, you should add variables with names:

  1. DB_ACCOUNT
  2. DB_USER
  3. DB_PASSWORD
  4. DB_DATABASE
  5. DB_SCHEMA
  6. DB_WAREHOUSE
  7. DB_ROLE

Warning

Make sure that at least the variable DB_PASSWORD is set to masked. You don't want to expose this value.

dotenv

In the above example, we used:

from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

These two lines make it a lot easier to develop your Python code locally, while you can also run the same code in AskAnna. When you add project variables, these variables will become available as environment variables in the run environment. Locally, you can add a file .env and when you run the Python code locally, the environment variables are loaded from this file. Read more about this on the project page python-dotenv.

To run the above example, you need a .env file with:

DB_ACCOUNT={account_identifier}
DB_USER={user}
DB_PASSWORD={password}
DB_DATABASE={database}
DB_SCHEMA={schema}
DB_WAREHOUSE={warehouse}
DB_ROLE={role}

Write to Snowflake table

We reuse almost to full setup as with reading the data from Snowflake. Instead of reading data, we first create a Pandas DataFrame and then write the data to Snowflake.

import os
from snowflake.sqlalchemy import URL as snowflake_url
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section dotenv
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(snowflake_url(
    account=os.getenv("DB_ACCOUNT"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    database=os.getenv("DB_DATABASE"),
    schema=os.getenv("DB_SCHEMA"),
    warehouse=os.getenv("DB_WAREHOUSE"),
    role=os.getenv("DB_ROLE"),
))

df = pd.DataFrame({'example' : ['value 1', 'value 2', 'value 3']})
df.to_sql(table_name, engine, method=pd_writer)