databricks sql create function - input table name as parameter and returns complete table.

[saurabh_aher]

hi , 
I am trying to create a databricks sql unity catalog function which will take table_name as input parameter and returns the full table as output. I am getting error, kindly help 

CREATE OR REPLACE FUNCTION catalog.schema.get_table( table_name STRING)

  RETURNS TABLE

  RETURN

    (
select * from IDENTIFIER('catalog.schema.' || get_table.table_name)
)

error ---
[NOT_A_CONSTANT_STRING.NOT_CONSTANT] The expression concat('kdp_bronze_prod.salesforce.', outer(get_table.user_id)) used for the routine or clause IDENTIFIER must be a constant STRING which is NOT NULL. To be considered constant the expression must not depend on any columns, contain a subquery, or invoke a non deterministic function such as rand(). SQLSTATE: 42601

[BS_THE_ANALYST]

@saurabh_aher if you're referring to an object i.e. a table I think you should be using backticks `` and not single quotes ''.  

However, based on the error message: concat('kdp_bronze_prod.salesforce.', outer(get_table.user_id)). 
I think in the concat function, it often helps to wrap a string in a LIT function. 

All the best,
BS

[szymon_dybczak · Accepted solution]

Hi @saurabh_aher ,

It looks like this is a limitation of identifier clause. The identifier requires a constant STRING expression. When you're passing table_name as an input of that function it's no longer considered as a constant (known at complied time), but rather runtime value.

[saurabh_aher]

any workaround for this ?? 

[szymon_dybczak]

Unfortunately, if we're talking about databricks sql functions I don't know workaround. 

[ilir_nuredini]

Hello @saurabh_aher ,

As a workaround maybe you can use SQL procedures, a quick example I created:

CREATE OR REPLACE PROCEDURE workspace.default.get_table_proc(IN p_fqn STRING)
LANGUAGE SQL
SQL SECURITY INVOKER
AS
BEGIN
  DECLARE sql_str STRING;
  SET sql_str = CONCAT('SELECT * FROM ', p_fqn);
  EXECUTE IMMEDIATE sql_str;
END;

And then you can call it like this:

CALL workspace.default.get_table_proc('workspace.default.sample_trips_test');

 Hope that helps a bit. 

Best, Ilir

[szymon_dybczak]

Yep, that will work. Now, if that's okay with you @saurabh_aher that can be solution. If you want to stick to using databricks sql function then what I wrote before still applies.

"Unfortunately, if we're talking about databricks sql functions I don't know workaround. "

[BS_THE_ANALYST]

@szymon_dybczak I admire that you always find the appropriate information in the documentation. I will try my best to emulate this behaviour with other posts 👍.

@saurabh_aher great workaround suggestion with a stored procedure. 

Lots of lessons learned here, for me, thanks guys 👏

All the best,
BS

[szymon_dybczak]

Thanks for kind words @BS_THE_ANALYST . I think you're doing really well so far and I remember your first post on community (Zero to Hero). Actually, it's really impressive how quickly you became proficient with databricks and valuable member of community.