topic Re: Table name as a parameter in SQL UDF in Machine Learning

Table name as a parameter in SQL UDF

elgeo — Tue, 15 Nov 2022 11:44:52 GMT

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!

Re: Table name as a parameter in SQL UDF

alm — Mon, 06 May 2024 11:42:39 GMT

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

Re: Table name as a parameter in SQL UDF

shan_chandra — Tue, 07 May 2024 16:26:23 GMT

@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.

Re: Table name as a parameter in SQL UDF

El_Tigre — Tue, 07 May 2024 21:13:35 GMT

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