1

I want to create a Snowflake Snowpark UDF which returns a single value. However, to converge to this value, I want to run a SQL query inside the function and iterate over the resultant rows.

My code looks something like this:

@udf(.....)
def myFunction(a:str) -> int:
    lookup_df = session.sql('select * from MyTable').collect()
  
    value = 0
    for row in lookup_df:
        value += 1

    return value

However, upon registring this, Snowflake thrws an TypeError:

TypeError: cannot pickle '_thread.lock' object: you might have to save the unpicklable object in the local environment first, add it to the UDF with session.add_import(), and read it from the UDF.

What would be the correct way of going about my problem?

Thank you in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vid Stropnik
  • 182
  • 10

1 Answers1

0

UDFs are not usually able to issue other queries - use a stored procedure instead:

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325