IDENTIFIER in SQL Views not supported?

[Richard3]

Dear community,

We are phasing out the dollar param `${catalog_name}` because it has been deprecated since runtime 15.2.

We use this parameter in many queries and should now be replaced by the IDENTIFIER clause.

In the query below where we retrieve data from a single catalog, we can switch to the default catalog environment variable from the cluster and remove the catalog from the namespace.

Old situation:

CREATE OR REPLACE TEMPORARY VIEW vw_example as (

SELECT * FROM ${bronze.env}.schema.table 
)

New situation with default catalog environment

CREATE OR REPLACE TEMPORARY VIEW vw_example as (

SELECT * FROM schema.table

)

Where we cannot replace the current dollar param 1-to-1 is in queries where we use multiple catalogs.

Example:

CREATE OR REPLACE TEMPORARY VIEW vw_example as (

SELECT *

FROM ${bronze.env}.schema.table

JOIN ${metadata.env}.schema.table

ON 1 = 1 )

According to the documentation, views are supported.

Source: https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-names-identifier-clau...

But I can't get this to work with test cases. I might be doing something wrong. CTEs work, but views don't, and I'd like to call the view in a different notebook cell.

If I create the view in one cell and try to call it in another, I get the error message:

A column, variable, or function parameter with the name `env` cannot be resolved.
First cell:

%sql

DECLARE OR REPLACE env STRING DEFAULT 'dev';

CREATE OR REPLACE TEMPORARY VIEW vw_example AS (

SELECT

*

FROM

IDENTIFIER('silver_' || env || '.schema.table') AS u

JOIN

IDENTIFIER('gold_' || env || '.schema.table') AS m

ON 1 = 1)

Second cell:

%sql

Select * from vw_example

Error: A column, variable, or function parameter with name `env` cannot be resolved. SQLSTATE: 42703

Curious about your perspective on implementing the IDENTIFIER clause in views with various catalogs as a replacement for the dollar param. I might be doing something wrong and it might just work out of the box.

Thanks! 

Regards,
Richard 

[Poorva21]

Views CAN accept parameters using DEFINE VIEW parameters:

CREATE OR REPLACE VIEW vw_example (

env STRING

) AS

SELECT *

FROM IDENTIFIER('silver_' || env || '.schema.table') u

JOIN IDENTIFIER('gold_' || env || '.schema.table') m

ON 1=1;

Then call it like:

SELECT * FROM vw_example('dev');

This is the most “dbt-like” and robust pattern.

Advantages:

Works across notebooks, clusters, warehouses

No need for global variables

Can inject env at query time

[Richard3]

Hello!
Thanks for taking the time to respond. I just can't get this to work. I don't think SQL VIEWS support variables.

[Hubert-Dudek]

I had recently the same problem...

[mnorland]

There are two options you may want to consider:

1. Switch to using SQL UDTFs from views in certain cases
2. For each session, dynamically recreate the view using CREATE VIEW via EXECUTE IMMEDIATE or via Python string templating:

[Hubert-Dudek]

I have good news: in runtime 18, IDENTIFIER and parameter markers are supported everywhere! We need to wait a month or two as the SQL warehouse and serverless are still on runtime 17.

[Richard3]

Thank you for the info Hubert-Dudek! It's good to see theIDENTIFIER becoming more widely available in Runtime 18. We will be beta testing and monitoring the release for SQL Warehouse.