How to pass Table Name as a parameter in Databricks SQL?

80 Views Asked by At

I am trying to create a function which takes some values and table name as a parameter and returns 1 or 0. The function is not replacing tableName with the parameter and hence throws an error.

I am creating a function in Databricks SQL like this:

%sql
CREATE FUNCTION  IF NOT EXISTS getValues(val1 STRING, val2 STRING, val3 STRING, tableName STRING)
RETURNS Integer
LANGUAGE SQL
RETURN SELECT case when concat(val1, val2, val3) in (SELECT concat(col1, col2,col3) from tableName) THEN 1 ELSE 0  end AS valCount

I am not able to pass tableName as a parameter here and this will throw an error.

Let's say tableName is 'table1' If I use this directly in this function, it works fine

%sql
CREATE FUNCTION  IF NOT EXISTS getValues(val1 STRING, val2 STRING, val3 STRING)
RETURNS Integer
LANGUAGE SQL
RETURN SELECT case when concat(val1, val2, val3) in (SELECT concat(col1, col2,col3) from table1) THEN 1 ELSE 0  end AS valCount

I want to pass this value 'table1' dynamically to this function, How can this de done? As this in databricks,

I know I can format this query as a python string and use spark.sql(query) to run this with tableName as a parameter to the string but I do not want to use python for this and need a way where I can pass the tableName parameter directly to the SQL Query

0

There are 0 best solutions below