cancel
Showing results for 
Search instead for 
Did you mean: 
Machine Learning
Dive into the world of machine learning on the Databricks platform. Explore discussions on algorithms, model training, deployment, and more. Connect with ML enthusiasts and experts.
cancel
Showing results for 
Search instead for 
Did you mean: 

Table name as a parameter in SQL UDF

elgeo
Valued Contributor II

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!

3 REPLIES 3

alm
New Contributor III

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

shan_chandra
Databricks Employee
Databricks Employee

@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
New Contributor III

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group