topic Re: Select from a dynamic table name returned by databricks function in Data Engineering

Select from a dynamic table name returned by databricks function

sgupta — Fri, 29 Mar 2024 14:37:31 GMT

I have a databricks function that returns a table_name

CREATE OR REPLACE FUNCTION test_func()

RETURNS string

READS SQL DATA

RETURN 'table_name'

I want to select from the table that is returned by this function. How can I make it work in SQL, something like this -

SELECT * FROM (SELECT test_func())

Currently this SELECT is returning a single row with the table name and not the contents of the table name.

Re: Select from a dynamic table name returned by databricks function

sgupta — Sat, 30 Mar 2024 16:08:41 GMT

Thanks @Retired_mod . This does not work in Databricks notebook, error attached. This looks like a SQL Server solution.

Re: Select from a dynamic table name returned by databricks function

sgupta — Sat, 30 Mar 2024 16:51:50 GMT

I looked at this post

https://stackoverflow.com/questions/77475436/in-databricks-workbook-using-spark-sql-how-to-pass-parameters-thru-sql-udf-func

What I want is to replace the static table name with the table name passed as parameter (param_table_name). Is it possible?

CREATE OR REPLACE FUNCTION mydb.tbl_filter(a_val INT, param_table_name string) RETURNS TABLE(a INT, b INT) RETURN SELECT * FROM tbl_filter.param_table_name tf WHERE tf.a = tbl_filter.a_val;