Select from a dynamic table name returned by databricks function

[sgupta]

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

[Kaniz]

Hi @sgupta, To achieve your goal of selecting data from the table returned by your Databricks function, you’ll need to use a table-valued function in SQL.

Let’s break down the steps:

1. Create a Table-Valued Function (TVF):

   • Instead of using a scalar function (which returns a single value), you’ll create a TVF that returns a table.
   • Define the TVF to return the desired columns and rows based on your logic.

2. Use the TVF in Your Query:

   • Once the TVF is defined, you can use it in your query just like any other table.
   • The TVF will act as a virtual table, allowing you to join, filter, and select data from it.

Here’s an example of how you can modify your code to achieve this:

-- Create a table-valued function
CREATE OR ALTER FUNCTION dbo.test_func()
RETURNS @Result TABLE (
    -- Define the columns returned by the function
    table_name NVARCHAR(255)
)
AS
BEGIN
    INSERT INTO @Result (table_name)
    VALUES ('table_name'); -- Replace with your actual logic

    RETURN;
END;

-- Now you can use the TVF in your query
SELECT *
FROM dbo.test_func();

Explanation:

• The @Result table variable within the TVF holds the data you want to return.
• Modify the TVF logic inside the INSERT INTO @Result statement to fetch the actual data you need from your Databricks function.
• The final SELECT * FROM dbo.test_func(); retrieves the data from the TVF.

Remember to replace 'table_name' with your actual data retrieval logic in the TVF.

Once you’ve defined the TVF, you can use it in your queries just like any other table¹²³.

Feel free to adapt this example to your specific use case, and adjust the TVF columns and logic accordingly. Happy querying! 🚀

[sgupta]

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

[sgupta]

I looked at this post

https://stackoverflow.com/questions/77475436/in-databricks-workbook-using-spark-sql-how-to-pass-para...

 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;