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();
pip install snowflakemagic
%reload_ext snowflakemagic
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
- 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 (;)
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)
#...
import pandas as pd
my_result = %snowflake SELECT * FROM xyz;
df = pd.DataFrame(my_result)
#...
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)
#...
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)
#...
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)
#...
If you are new to Jupyter notebooks, I recommend getting started using Jupyter notebooks in Visual Studio Code
- Configure .env file providing connection parameters as explained above
- Start using getting-started.ipynp to learn how to use the magic functions