0

When I run select myadd(2,2); in the Worksheet tab of the Snowflake Web console, I get an error "SQL compilation error: Unknown function MYADD" but the UDF works in Snowpark.

Why cannot I execute the Snowpark created UDF in Snowflake worksheet? It is listed in show user functions.

I created the myadd UDF in Snowpark as follows:

@udf(name="myadd", replace=True)
def myadd(x:int, y:int) -> int:
    return x + y

and when I execute it in Snowpark as session.sql("select myadd(2,2)").collect() I get the answer.
Why cannot I execute this UDF in Snowflake worksheet?

psabela
  • 324
  • 1
  • 3
  • 16
  • Correction: I does not show up in the ``` show user functions ```, but it shows up when I navigate in web console to -> INFORMATION_SCHEMA -> FUNCTIONS and do Preview Data. – psabela Aug 31 '22 at 13:58

2 Answers2

1

The answer is in the session. When creating UDF in Snowpark it has one session. When trying to run UDF in Snowflake Web console worksheet, you are in another session. Creating UDF in this format:

@udf(name="myadd", replace=True)
def myadd(x:int, y:int) -> int:
    return x + y

will only create this UDF for the Snowpark session. When you close the Snowpark session, the UDF goes away. This why the UDF is "Unknown function" and is not visible to the Snowflake Web console worksheet session.

In order for UDF to be accessible by other sessions, like from the worksheet, it must set as permanent. Doc says: "To create a permanent UDF, call the register method or the udf function and set the is_permanent argument to True. When you create a permanent UDF, you must also set the stage_location argument to the stage location where the Python file for the UDF and its dependencies are uploaded."

Therefore, the UDF must be created as follows:

@udf(name="myadd", replace=True, is_permanent=True, stage_location="@stage")
def myadd(x:int, y:int) -> int:
    return x + y

After this, I am able to execute the UDF from Snowflake Web console worksheet.

psabela
  • 324
  • 1
  • 3
  • 16
0

Snowflake UDFs are owned by a database and schema. When you call session.sql("select myadd(2,2)").collect(), you're running this SQL:

select myadd(2,2)

This will run a UDF with a fully-qualified path and signature of

<database>.<schema>.MYADD(int, int).

Since the SQL calls the UDF without specifying the database and schema, it will use the current database and schema for the session.

You mention that the UDF is visible when you run show user functions. That's good. You can then call the UDF by either specifying the database and schema when calling it, or by setting the context to the database and schema. In the show user function output table, the database name appears as "catalog_name" and the schema appears as "schema_name".

So you can do this:

select my_db.my_schema.myadd(2,2)

or this:

use schema MY_DB.MY_SCHEMA;
select myadd(2,2);
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
  • Even when I fully qualify the function name with database and schema I get the same Unknown function. [Correction: I does not show up in the ``` show user functions ```, but it shows up when I navigate in web console to -> INFORMATION_SCHEMA -> FUNCTIONS and do Preview Data.] – psabela Aug 31 '22 at 14:00