SQL loading natively in Hamilton -- help choose the API!

[elijahbenizzy]

Hey folks, we're planning to build support for natively running SQL queries to load data with Hamilton. We want your help figuring out how!

We currently have two options we're considering, but are open to more. See original issue, and original design doc (it's evolved since then)

For the following, we'll be using the (somewhat complex, parameterized as a jinja template) query. Its based on our duckdb example.

Here's what it would look like currently:

# connection would be defined upstream/passed in

@extract_columns("facebook_spend", "tv_spend", ...)
def load_query(
    connection: Connection, 
    business_line: str, 
    start_date: str, end_date: str) -> pd.DataFrame:
    query = f"""SELECT
      marketing_spend.facebook as facebook_spend,
      marketing_spend.tv as tv_spend,
      marketing_spend.youtube as youtube_spend,
      marketing_spend.billboards as billboard_spend,
      churn.{business_line} as {business_line}_business_line_churn,
      signups.{business_line} as {business_line}_business_line_signups,
      marketing_spend.date as date
    FROM marketing_spend
    JOIN churn
      ON marketing_spend.date = churn.date
    JOIN signups
      ON signups.date = marketing_spend.date
    where marketing_spend.date between '{start_date}' and '{end_date}'"""
    df = conn.query(query).fetchdf()
    return df

SQL as part of the decorator

@extract_columns("facebook_spend", "tv_spend", ...)
@load.sql(
    query="""SELECT
      marketing_spend.facebook as facebook_spend,
      marketing_spend.tv as tv_spend,
      marketing_spend.youtube as youtube_spend,
      marketing_spend.billboards as billboard_spend,
      churn.{{business_line}} as {{business_line}}_business_line_churn,
      signups.{{business_line}} as {{business_line}}_business_line_signups,
      marketing_spend.date as date
    FROM marketing_spend
    JOIN churn
      ON marketing_spend.date = churn.date
    JOIN signups
      ON signups.date = marketing_spend.date
    where marketing_spend.date between '{{start_date}}' and '{{end_date}}'""",
    connection="postgres_connection"
)
def my_data(df: pd.DataFrame) -> pd.DataFrame:
    """This is passed the loaded dataframe, but could do additional manipulation"""
    return df

SQL returned by the function

@extract_columns("facebook_spend", "tv_spend", ...)
@load.sql(connection="postgres_connection", to=pd.DataFrame)
def my_data(start_date: str, end_date: str, business_line: str) -> str:
    return """SELECT
      marketing_spend.facebook as facebook_spend,
      marketing_spend.tv as tv_spend,
      marketing_spend.youtube as youtube_spend,
      marketing_spend.billboards as billboard_spend,
      churn.{{business_line}} as {{business_line}}_business_line_churn,
      signups.{{business_line}} as {{business_line}}_business_line_signups,
      marketing_spend.date as date
    FROM marketing_spend
    JOIN churn
      ON marketing_spend.date = churn.date
    JOIN signups
      ON signups.date = marketing_spend.date
    where marketing_spend.date between '{{start_date}}' and '{{end_date}}'""",

<Your idea here!>

[elijahbenizzy]

Driver code:

# this could be included in the decorator if you want to do ad-hoc
config = {
    "postgres_connection": 
        {
           type: "postgres",
           database: "spend_data"   ,
           password:  os.environ["SECURELY_STORED_PASSWORD"],
           port: 5432,
           username: "elijah",
           url: "postgres_url"
        }
}
driver = driver.Driver(config, data_loaders)
driver.execute(
    ["facebook_spend", ...], 
    inputs={
        "start_date" : "2021-03-01", 
        "end_date" : "2021-05-01",
        "business_line" : "womens"
    }

The idea is to use sqlglot to transpile between dialects as needed, allowing you to specify a dialect if you really want to bypass (otherwise we'll likely assume duckdb).```

1. We would pool connections, allowing for you to specify other connections
2. We would be able to pass in the connection ad-hoc
3. We could allow for specifying dialect of SQL to bypass translation (or to use in conjunction with?)
4. We’d allow for customization of connections/loaders later on (E.G. a class that you extend to do yours as well)