Snowflake

Python

In the example below, we show how to set up a connection to a Snowflake data source using Python. We demonstrate how to read data from a Snowflake table to a Pandas DataFrame and vice versa. To do this, we need Python 3.7 or newer and install the following packages:

snowflake-sqlalchemy (required to set up the connection)
SQLAlchemy (required for Pandas)
pandas
python-dotenv

We recommend using a virtual environment and adding the packages to a requirements.txt file. In this file, you can add the following:

snowflake-sqlalchemy  # tested with version 1.3.1
SQLAlchemy            # tested with version 1.4.21
pandas                # tested with version 1.3.0
python-dotenv         # tested with version 0.18.0

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 `Configure 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.

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 `Configure 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)

Configure dotenv

In the above examples, we used:

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

These two lines make it possible 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 of 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}

Add AskAnna 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 and corresponding values:

DB_ACCOUNT
DB_USER
DB_PASSWORD
DB_DATABASE
DB_SCHEMA
DB_WAREHOUSE
DB_ROLE

Warning

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