DAB dashboards variable substitution in dataset queries

[DaveRuijter]

Describe the issue

When developing a dashboard, currently it does not seem possible to parameterise the catalog and/or schema names used in the datasets queries of a dashboard.

I've tried to use the below formats but none of them seem to work:

• IDENTIFIER(:catalog)
• :catalog
• ':catalog'
• IDENTIFIER(':catalog')

Furthermore, when this would work I'd like to pass/overwrite the parameter value with a specific value per target in my databricks.yml configuration, to specify in my case the catalog to read from (which is different on each environment). Not sure if that is supported already?

Configuration / Steps to reproduce the behavior

1 Create a new dashboard 2 Go to the data tab and use this query: SELECT * FROM :catalog.information_schema.catalogs 3 See error:

[PARSE_SYNTAX_ERROR] Syntax error at or near ':'. SQLSTATE: 42601 (line 1, pos 23)

== SQL ==
DESCRIBE SELECT * FROM :catalog.information_schema.catalogs
-----------------------^^^

Expected Behavior

• I'd like to use IDENTIFIER(:catalog) or something similar in the queries.
• I'd like to be able to set such a parameter value via my databricks.yml configuration, maybe like this:

resources:
  dashboards:
    nyc_taxi_trip_analysis:
      display_name: "NYC Taxi Trip Analysis"
      file_path: ../src/nyc_taxi_trip_analysis.lvdash.json
      warehouse_id: ${var.warehouse_id}
      parameters:
         - name: catalog
           default: ${var.catalog}

Actual Behavior

See error message above

OS and CLI version

Databricks CLI v0.234.0

Is this a regression?

No

Debug Logs

N/A

[pietern]

Thanks for reporting this issue and feature requests.

W.r.t. the issue, this is a known problem. A fix is underway and is expected to be released in January. Once landed, you'll be able to use USE CATALOG IDENTIFIER(:catalog). In the meantime, you can work around this limitation with the SQL equivalent of an eval:

-- Default to catalog set in parameter
declare catalog_name string;
set var catalog_name = :catalog;
declare catalog_query string;
set var catalog_query = concat("use catalog ", catalog_name, ";");
execute immediate catalog_query;

-- Default to schema set in parameter
use schema identifier(:schema);

W.r.t. the feature request, we're working on sorting out how to best support this. Overriding parameters is not natively supported in the dashboard APIs, so we're figuring out the best course of action. I will post back here with updates to unblocking the pattern you're looking to achieve.

[github-actions[bot]]

This issue has not received a response in a while. If you want to keep this issue open, please leave a comment below and auto-close will be canceled.

[etspaceman]

Please keep this issue open.

[skinbody]

I've been working with a few customers who are eager to deploy dashboards using DABs, and the feedback I'm receiving is that the lack of support for the pattern that @DaveRuijter was describing - i.e. dynamically specifying catalogs and schemas in the dashboard queries - is somewhat stopping them.

Specifically, teams working with multiple environments (DTAP) would like to promote dashboards across environments similar to other assets. Usually, they have a catalog structure per environment - e.g. team1_dev, team1_acc, team1_prd - and would prefer to use variable(s) in the bundle rather than using workarounds. A common workaround I have seen is using placeholders in a template version of a lvdash.json file, which requires replacing them during CI/CD before deploying the bundle and feels hacky.

It would be great to hear if there’s been any progress on this or if there are any plans - I look forward to any updates!

[jope35]

any update on this @pietern ?

[tfrazeratvald]

Please keep this issue open

[gbwally]

Hi @pietern - Also wondering if there is an update here. Not ideal to have to preprocess dashboard json before deploying in the DAB in order to use a different catalog for different targets. Thank you!

[d-cegielka]

Hi @pietern, @andrewnester can we expect any update regarding this enhancement?

[karoladegroot]

@pietern @andrewnester @DaveRuijter just found out you can use SELECT * FROM IDENTIFIER(:catalog || '.' || :schema || '.' || :table) Or hardcode anything you dont need as variable like

SELECT * FROM IDENTIFIER('company' || '.' || :schema || '.' || 'customers') and then fill in the variable

[hapination]

I would really need this feature to be able to pass the catalog name per target during deployment of the dashboard

[johnartus]

I need this feature too ! please

[aborsato]

+1 here. We want to deploy dashboards as DABs, but the lack of support for parameters is holding us back.