Repository navigation
DAB dashboards variable substitution in dataset queries #1915
Description
Activity
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 aneval:-- 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.
Reacted by Dave RuijterReacted by Tim Frazer, DannyDevaprakash, Luke Fitzpatrick and rhossain-hcThis 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.
Please keep this issue open.
Reacted by Angelo and rhossain-hcI'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!
Reacted by Tim Frazer, Lukáš Hejcman, Joost, Simon van der Veldt and tetan-dbxPlease keep this issue open
Hi @pietern, @andrewnester
can we expect any update regarding this enhancement?Reacted by Tim Frazer and Isa Inalcik@pietern @andrewnester @DaveRuijter just found out you can use SELECT * FROM IDENTIFIER(:catalog || '.' || :schema || '.' || :table)
Or hardcode anything you dont need as variable likeSELECT * FROM IDENTIFIER('company' || '.' || :schema || '.' || 'customers')
and then fill in the variable
I would really need this feature to be able to pass the catalog name per target during deployment of the dashboard
Reacted by Tim Frazer, Tobias Schmocker and Isa InalcikI need this feature too ! please
Reacted by Tim Frazer, Tobias Schmocker and Isa Inalcik+1 here. We want to deploy dashboards as DABs, but the lack of support for parameters is holding us back.
Reacted by Tim Frazer+1. Will DABs support dataset_catalog parameter from lakeview api for dashboards?
Reacted by Tim Frazer+1. I really need this feature to make possible to use dashboards in production at scale
Reacted by Tim FrazerGuys, Someone (Maksim Pachkouski) just responded in the databricks community with a new feature. See his Medium Post
https://medium.com/@protmaks/dynamic-catalog-schema-in-databricks-dashboards-b7eea62270c6Reacted by Virgile Procureur, Maksim Pachkouski, katrielrr and VictorAtIfInsuranceGuys, Someone (Maksim Pachkouski) just responded in the databricks community with a new feature. See his Medium Post https://medium.com/@protmaks/dynamic-catalog-schema-in-databricks-dashboards-b7eea62270c6
I have just tried what's specified in the article and it works just fine for me.
If anyone's curious, it's not required to specify both
dataset_cataloganddataset_schema: I provide just thedataset_catalogparameter with a variable and let the schema be determined in the query.This goes like this:
# filename: databricks.yml resources: dashboards: your_dashboard: display_name: your_display_name dataset_catalog: {var.dataset_catalog} file_path: your_dashboard.lvdash.json
// filename: your_dashboard.lvdash.json { "datasets": [ { "name": "abc123", "displayName": "dataset_1", "queryLines": [ "SELECT * FROM your_schema.your_table" ], "catalog": "default_catalog" // I put here the name of my dev catalog as default value "schema": "default" }, ... ] }
Reacted by VictorAtIfInsurance@VProcureurCEGID it is until useless if you need to query more than one catalog, e.g. sales_dev -> sales_prd, financial_dev -> financial_prd
Reacted by lucasmartinsb, Tim Frazer and KevinShocked that this is still an open issue?! I've just come to deploy into production after creating a DEV dashboard and now learn that you cannot parameterize the catalog and schema like is supported on jobs. @pietern @DaveRuijter - do you know anything more about this? Thanks
Reacted by Tim FrazerYou can use
dataset_cataloganddataset_schemaon the dashboard resource to override the default dataset and catalog.Read more here: https://docs.databricks.com/aws/en/dev-tools/bundles/examples#dashboard-configuration
This issue is still open because complete variable substitution is indeed not supported.
Reacted by Dave RuijterJust FYI... you CAN use parameters in your dashboard queries IF you use serialized_dashboard instead of json_file in your dashboard yaml. The downside is the visual editor doesn't handle it well, and genie code has trouble with re-serializing changes back into the yaml file. Either supporting variable substitution in the json format or having the visual editor natively support the full yaml format for dashboard (with json in the serialized_dashboard field) would be significantly better.
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
targetin mydatabricks.ymlconfiguration, 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.catalogs3 See error:
Expected Behavior
IDENTIFIER(:catalog)or something similar in the queries.databricks.ymlconfiguration, maybe like this:Actual Behavior
See error message above
OS and CLI version
Databricks CLI v0.234.0
Is this a regression?
No
Debug Logs
N/A