hello 👋 I have a group of sql server resources that I would like to distribute across a fanned out op. I was thinking the best way to do this would be to create some sort of

compute_server_round_robin

resource that would get a compute server resource based on the modulus of the total number of compute servers and the index of the fanned out op. Is there a way to get a resource to use based on a value passed into a resource? Or do I need to rethink my strategy?

Resources are instantiated at the beginning of a run. So if you had a resource representing a SQL server which actually selected the "right" or "next" SQL Server to use on instantiation, then every time you run a job the SQL Server resource would select a SQL Server and make that available to the ops in that run.

Resources aren't mapping-key / fan-out index aware. Instead, when generating your mapping_key for your DynamicOutputs in your fan-out you could use an integer index, then have your sql server resource be a resource factory that uses the mapping key to determine which sql server to use. something like

@resource
def sql_server_round_robin(context):

    def factory(index):
        # custom logic for generating server name from index
        server = get_server_using_index(index)
        cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                              f"Server={server};"
                              "Database=db_name;"
                              "Trusted_Connection=yes;")
        return cnxn
    return factory

@op(required_resource_keys={"sql_server_selector"})
def fanned_out_op(context: OpExecutionContext):
    connection = context.resources.sql_server_selector(context.get_mapping_key())

Thanks @Zach that's exactly what I was thinking of, but didn't know if resources took parameters. Appreciate it!

hmm I'm not sure it's necessary to formally generate the resources dynamically outside of the factory function. I would just embed that logic within the factory function, and then ops that depend on the resource just depend on

sql_server_round_robin

and dynamically generate their server using that as a factory. this would require directly instantiating the sql server object within

sql_server_round_robin

with the server / database, instead of using the

.configured()

api on another resource. more like

@resource()
def sql_server_round_robin(context):
    RESOURCES = []
    for index, row in databases.iterrows():
        server = str(row["ServerName"])

        print(f"{str(row['DatabaseLabel']).replace('-','_')}_resource")
        RESOURCES.append(pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                              f"Server={server};"
                              "Database={row['DatabaseName']};"
                              "Trusted_Connection=yes;"))

    def factory(index):
        # custom logic for generating server name from index
        return RESOURCES[index%len(RESOURCES)]

    return factory

but maybe I'm missing something. also, isn't

index % 2

only ever going to match 0 or 1?

yeah I'm just beginning a benchmarking test to see if we can do this with a subset of our data. we have all our database information in a centralized database, so I'm reading that in and generating the sql server resources from that. If it's not possible to use those dynamically generated resources, I'll have to look at doing it the way you mentioned above (generate the connections inside the round robin resource), but it feels like duplicating code

One problem I didn't initially point out is that I don't think you can use the

.configured()

API within a resource definition on one of its dependent resources -

.configured()

is only available on ResourceDefinitions - when you access

context.resources.resource_name

you're accessing an object that has been instantiated as a result of resolving a ResourceDefinition - in other words you're accessing whatever object you're returning from the resource definition mapped to

context.resources.resource_name

, not the ResourceDefinition itself. you can use

.configured()

on resources at a global level or within

@graph

/

@job

definitions because the definition objects have not