snowflakemagic 1.0.0

IPython magic function for Snowflake Query Language (SQL)

Snowflake Magic

An ipython magic function to simplify usage of Snowflake SQL in your notebooks.

Example

import pandas as pd
result = %snowflake SELECT timestamp, value FROM mytable;
df = pd.DataFrame(result)
df.plot.line();

Setup and Configuration

Install the extension

pip install snowflakemagic

Load extension

%reload_ext snowflakemagic

Available magic functions

%snowflake_auth

Inline function connecting to your snowflake account. Reads connection parameters from .env file:

You can either authenticate via SSO, which opens an external browser, or using credentials.

Provide your snowflake account details:

snowflake_account="<YOUR-SNOWFLAKE-ACCOUNT>"

If you want to connect via sso, provide your sso username:

snowflake_ssouser="<YOUR-SSO-USERNAME>"

If you want to connect via use-credentials, provide the password, otherwise SSO authentication is used.

snowflake_user="<YOUR-USERNAME>"
snowflake_password="<YOUR-PASSWORD>"

For more details on .env file see How to NOT embedded credential in Jupyter notebook or python-dotenv

%%snowflake, %snowflake or %snowflake_script

• Executes a snowflake query/script and returns the result as a json object.
• Multiple queries/statements separated by ; can be exceuted, but only last result will be returned.
• A query MUST end with a semi-colon (;)

Example 1

Query in code-cell

%%snowflake my_result
SELECT * 
    FROM xyz;

.. use result in another code cell:

import pandas as pd

#put result into a dataframe
df = pd.DataFrame(my_result)

#...

Example 2 - Inline query

import pandas as pd

my_result = %snowflake SELECT * FROM xyz;
df = pd.DataFrame(my_result)

#...

Example 3 - From script

Query using external query script files e.g. myscript.snowql

SELECT * FROM xyz;

Then in your code-cell, pass the script name

import pandas as pd

my_result = %snowflake_script myscript.snowql
df = pd.DataFrame(my_result)

#...

Example 4 - Parameterized script

Query using external query script files e.g. myscript.snowql which can be parameterized

SELECT * FROM xyz WHERE mycolumn=@MYVALUE@@;

Then in your code-cell, pass the script name

import pandas as pd

my_result = %snowflake_script myscript.snowql @@MYVALUE@@=test
df = pd.DataFrame(my_result)

#...

Example 5 - Chaining multiple scripts

You can also chain multiple scripts

mycte.snowql

WITH
    my_cte AS (
        SELECT col_1, col_2
            FROM xyz
    )

myscript.snowql

SELECT * FROM my_cte WHERE col_1=@MYVALUE@@;

Then in your code-cell, you can append the various script files

import pandas as pd

my_result = %snowflake_script mycte.snowql<<myscript.snowql @@MYVALUE@@=test
df = pd.DataFrame(my_result)

#...