UDF already defined error when using it into a DLT pipeline

[6502]

I'm using Unity catalog and defined some UDFs in my catalog.database, as reported by 

show functions in main.default
main.default.getgender
main.default.tointlist
main.default.tostrlist

I can use them from a start warehouse pro:

SELECT main.default.get_gender(10,80,10)

The above statement works.
 
However, from inside a pipeline: 

@dlt.view(
name="vudf"
,comment="Extra data collected from sensor tower APIs required to build the silver table"
)
def test():
   return SQL("""
SELECT ${env.database}.get_gender(10, 80 ,20)
""")

 I got:

pyspark.errors.exceptions.AnalysisException: [ROUTINE_ALREADY_EXISTS] Cannot create the function `main`.`default`.`get_gender` because it already exists.
Choose a different name, drop or replace the existing function, or add the IF NOT EXISTS clause to tolerate a pre-existing function.; line 2 pos 15

[Kaniz]

Hi @6502, This is a common issue that many users have faced when using UDFs in Unity Catalog.

There are a few possible solutions that you can try to fix this error:

• You can drop the existing function main.default.get_gender from your catalog database before creating a new one. To do this, you can use the following SQL statement:

DROP FUNCTION main.default.get_gender;

• You can rename the existing function main.default.get_gender to something else, such as main.default.get_gender_old, and then create a new one with the same name but with a different schema. For example, if your catalog database is called catalog, you can use the following SQL statements:

ALTER FUNCTION main.default.get_gender RENAME TO main.default.get_gender_old; CREATE FUNCTION main.default.get_gender RETURNS INT LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL COMMENT 'Get gender from sensor tower APIs' RETURN (get_gender(10, 80 ,20)::INT);

• You can use the IF NOT EXISTS clause when creating a new function in Unity Catalog to avoid the error if the function already exists. For example, you can use the following SQL statement:

CREATE FUNCTION target_catalog.target_schema.get_gender RETURNS INT LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL COMMENT 'Get gender from sensor tower APIs' RETURN (get_gender(10, 80 ,20)::INT) IF NOT EXISTS;

I hope this helps you resolve your issue. If you need more information about UDFs in Unity Catalog, you can check out these resources:

• UDF/UDAF/SQL Function not working in Unity catalog Error … - Databricks
• Set up and manage Unity Catalog - Azure Databricks
• Unity Catalog best practices - Azure Databricks

Have a great day! 😊