19913d85-f662-4518-a9a1-7adc1793dfd4.png){kind=small}
How are snowflake sessions managed in Dagster? If ...
# integration-snowflakec
clay
04/19/2023, 2:38 AMHow are snowflake sessions managed in Dagster? If I have two subsequent assets that each require a snowflake resource that is set up with
snowflake_resource.configuredObject does not exist, or operation cannot be performed.USE WAREHOUSE ..t
Tim Castillo
04/19/2023, 3:15 AMI believe resources are re-initialized with each op/asset that uses it. So it'd be a new session each time.
Aaah, yeah I don't believe you can swap warehouses in-flight. That's odd behavior though, as I'd assume Snowflake wouldn't limit you if you spun up another session.
Let me see what I can dig up about this, though. In the meanwhile, I'm curious about how other tools can support this. I know you can swap out warehouses for individual models in dbt.
Tim Castillo
04/19/2023, 3:17 AMIn terms of your question about getting the connection URL, the
SnowflakeResourcec
clay
04/19/2023, 7:47 PMI figured out what was happening... We swap roles and warehouses all the time, so it's natural in our flow... Because of this, I have a bootstrapping query in DBeaver to run
USE SECONDARY ROLES ALLt
Tim Castillo
04/19/2023, 7:49 PMaaah, I had hunch it was a role thing when I saw the error message, but that second half (
or operation cannot be performed.use secondary rolesc
clay
04/19/2023, 7:50 PMFWIW, I was trying to set up two warehouses to run Snowflake's benchmarking queries simultaneously... I have each of the 100 or so queries in it's own
.sqlclay
04/19/2023, 7:50 PM Copy code
def execute_snowflake_sql(context, filename) -> None:
"""
Executes a SQL query on Snowflake
"""
with context.resources.marketing_analytics_snowflake.get_connection() as conn:
<http://context.log.info|context.log.info>("Snowflake connection established.")
with closing(conn.cursor()) as cursor:
<http://context.log.info|context.log.info>("Snowflake cursor assigned.")
secondary = "USE SECONDARY ROLES ALL"
<http://context.log.info|context.log.info>(secondary)
cursor.execute(secondary)
# These are specific to the benchmarking
warehouse = "USE WAREHOUSE MKTG_PUB_ANLY_ETL"
<http://context.log.info|context.log.info>(warehouse)
cursor.execute(warehouse)
schema = "USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL"
<http://context.log.info|context.log.info>(schema)
cursor.execute(schema)
cache = "ALTER SESSION SET use_cached_result = FALSE"
<http://context.log.info|context.log.info>(cache)
cursor.execute(cache)
f = file_relative_path(__file__, filename)
with open(f, "r") as fd:
q1 = fd.read()
<http://context.log.info|context.log.info>(q1)
cursor.execute(q1)
return Noneclay
04/19/2023, 7:51 PMAnd voila
clay
04/19/2023, 7:52 PMSnowflake would have you run the queries in sequence but I want to run them in parallel and let Dagster manage it all because I want to put maximum pressure on the warehouses to see what will happen
t
Tim Castillo
04/19/2023, 7:52 PMbeautiful
c
clay
04/19/2023, 7:52 PMAnyhow, I should write up an example repo for you all because it's a useful thing to be able to do. 🙂
👌 1
t
Tim Castillo
04/19/2023, 7:54 PMIf there's ever anything that you're proud about doing with Dagster, please do! And post it in #dagster-showcase, we also usually hold a bi-monthly event for community members to demo their cools tricks to each other.
❤️ 1
51 Views