Table name as a parameter in SQL UDF

[elgeo]

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]

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

[shan_chandra]

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

[MarkN]

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