Skip to content

prefect-snowflake

PyPI

Welcome!

Prefect integrations for interacting with prefect-snowflake.

Getting Started

Python setup

Requires an installation of Python 3.7+.

We recommend using a Python virtual environment manager such as pipenv, conda or virtualenv.

These tasks are designed to work with Prefect 2.0. For more information about how to use Prefect, please refer to the Prefect documentation.

Installation

Install prefect-snowflake with pip:

pip install prefect-snowflake

A list of available blocks in prefect-snowflake and their setup instructions can be found here.

Query from table

from prefect import flow
from prefect_snowflake.credentials import SnowflakeCredentials
from prefect_snowflake.database import SnowflakeConnector, snowflake_query


@flow
def snowflake_query_flow():
    snowflake_credentials = SnowflakeCredentials(
        account="account",
        user="user",
        password="password",
    )
    snowflake_connector = SnowflakeConnector(
        database="database",
        warehouse="warehouse",
        schema="schema",
        credentials=snowflake_credentials
    )
    result = snowflake_query(
        "SELECT * FROM table WHERE id=%{id_param}s LIMIT 8;",
        snowflake_connector,
        params={"id_param": 1}
    )
    return result

snowflake_query_flow()

Write pandas to table using block attributes

import pandas as pd
from prefect import flow
from prefect_snowflake.credentials import SnowflakeCredentials
from prefect_snowflake.database import SnowflakeConnector, snowflake_query
from snowflake.connector.pandas_tools import write_pandas

@flow
def snowflake_write_pandas_flow():
    snowflake_connector = SnowflakeConnector.load("my-block")
    with snowflake_connector.get_connection() as conn:
        table_name = "TABLE_NAME"
        ddl = "NAME STRING, NUMBER INT"
        statement = f'CREATE TABLE IF NOT EXISTS {table_name} ({ddl})'
        with conn.cursor() as cur:
            cur.execute(statement)

        # case sensitivity matters here!
        df = pd.DataFrame([('Marvin', 42), ('Ford', 88)], columns=['NAME', 'NUMBER'])
        success, num_chunks, num_rows, _ = write_pandas(
            conn=conn,
            df=df,
            table_name=table_name,
            database=snowflake_connector.database,
            schema=snowflake_connector.schema_  # note the "_" suffix
        )

Execute get and put statements

To execute get and put statements, use snowflake_query_sync.

from prefect import flow
from prefect_snowflake.database import SnowflakeConnector, snowflake_query_sync

@flow
def snowflake_put_file_to_snowflake_stage():
    snowflake_connector = SnowflakeConnector.load("my-block")

    snowflake_query_sync(
        f"put file:///myfolder/myfile @mystage/mystagepath",
        snowflake_connector=snowflake_connector
    )

Use with_options to customize options on any existing task or flow:

from prefect import flow
from prefect_snowflake.database import SnowflakeConnector, snowflake_query_sync

custom_snowflake_query_sync = snowflake_query_sync.with_options(
    name="My custom task name",
    retries=2,
    retry_delay_seconds=10,
)

@flow
def example_with_options_flow():
snowflake_connector = SnowflakeConnector.load("my-block")

custom_snowflake_query_sync(
    f"put file:///myfolder/myfile @mystage/mystagepath",
    snowflake_connector=snowflake_connector
)

example_with_options_flow()

For more tips on how to use tasks and flows in a Collection, check out Using Collections!

Blocks Catalog

Below is a list of Blocks available for registration in prefect-snowflake.

To register blocks in this module to view and edit them on Prefect Cloud:

prefect block register -m prefect_snowflake
Note, to use the load method on Blocks, you must already have a block document saved through code or saved through the UI.

Credentials Module

To load the SnowflakeCredentials:

from prefect import flow
from prefect_snowflake.credentials import SnowflakeCredentials

@flow
def my_flow():
    my_block = SnowflakeCredentials.load("MY_BLOCK_NAME")

my_flow()

Database Module

To load the SnowflakeConnector:

from prefect import flow
from prefect_snowflake.database import SnowflakeConnector

@flow
def my_flow():
    my_block = SnowflakeConnector.load("MY_BLOCK_NAME")

my_flow()

Resources

If you encounter any bugs while using prefect-snowflake, feel free to open an issue in the prefect-snowflake repository.

If you have any questions or issues while using prefect-snowflake, you can find help in either the Prefect Discourse forum or the Prefect Slack community.

Feel free to star or watch prefect-snowflake for updates too!

Contributing

If you'd like to help contribute to fix an issue or add a feature to prefect-snowflake, please propose changes through a pull request from a fork of the repository.

Here are the steps:

  1. Fork the repository
  2. Clone the forked repository
  3. Install the repository and its dependencies:
    pip install -e ".[dev]"
    
  4. Make desired changes
  5. Add tests
  6. Insert an entry to CHANGELOG.md
  7. Install pre-commit to perform quality checks prior to commit:
    pre-commit install
    
  8. git commit, git push, and create a pull request