Table name as a parameter in SQL UDF

elgeo · ‎11-15-2022

Hello experts,

We would like to create a UDF function with input parameter a table_name. Please check the below simple example:

CREATE OR REPLACE FUNCTION F_NAME(v_table_name STRING,

v_where_value INT)

RETURNS INT

RETURN SELECT MAX(id) FROM v_table_name WHERE code = v_where_value

However, it seems that v_table_name is not recognized as a table in the Select clause. Could you please suggest a workaround?

Thank you in advance!

alm · ‎05-06-2024

Did you find a solutions? I'm having the same problem

shan_chandra · ‎05-07-2024

@elgeo @alm - passing a parameter within a function is not currently available. we can create a python UDF that generate the underlying code to pass the parameter. Then do a select statement to pass the dynamic value to execute the function.

Say for example,

def sayHello(*args):

query = """

CREATE OR REPLACE FUNCTION F_NAME(v_table_name STRING,

                   v_where_value INT)

RETURNS INT  

RETURN SELECT MAX(id) FROM v_table_name WHERE code = {0}


"""

df = spark.sql(query.format(*args))
print(df.show(truncate=False))
return

%sql
select F_NAME('v_where_value')

Please note: The above code snippet is not syntax verified.

El_Tigre · ‎05-07-2024

Use the identifier clause (more like a function) in Spark SQL. It requires runtime 13.3 LTS or higher.