Parameters in dashboards data section passing via asset bundles

[drag7ter]

A new functionality allows deploy dashboards with a asset bundles. Here is an example :

# This is the contents of the resulting baby_gender_by_county.dashboard.yml file.
resources:
  dashboards:
    baby_gender_by_county:
      display_name: "Baby gender by county"
      warehouse_id: aae11o8e6fe9zz79
      file_path: ../src/baby_gender_by_county.lvdash.json

In my dashboard Data section I have datasets and I want pass parameters to the query and use as parameter the same as I can do in jobs. Lets say catalog name, so I can specify variable and pass it in the bundle to deploy same dashboard to dev, prod:

SELECT * FROM IDENTIFIER(:catalog||'.my_schema.my_table')

And dashboard json file will have my parameters value from the bundle:

"datasets": [
    {
      "displayName": "My Dashboard",
      "name": "16b8c208",
      "parameters": [
        {
          "dataType": "STRING",
          "defaultSelection": {
            "values": {
              "dataType": "STRING",
              "values": [
                {
                  "value": "prod"
                }
              ]
            }
          },
          "displayName": "catalog",
          "keyword": "catalog"
        }
      ],
      "query": "SELECT * FROM IDENTIFIER(:catalog||'.my_schema.my_table')"
    },

Is it possible to pass parameters for Dashboards Datasets in asset bundles?

Or may be there is another way how to deploy the same json dashboard to PROD, DEV and somehow change queries, to use dev catalog in dev workspace, prod catalog in prod?

[Alberto_Umana]

Yes, it is possible to pass parameters for Dashboards Datasets in asset bundles. You can define custom variables in the bundle configuration file and use these variables in your dashboard queries

You can define variables in the databricks.yml file. For instance, you can define a variable for the catalog name:

variables:

  catalog:

    description: "Catalog name for the dataset"

    default: "dev"

You can reference these variables in your dashboard configuration file. For example, in your baby_gender_by_county.dashboard.yml file, you can use the variable in the query:

resources:

  dashboards:

    baby_gender_by_county:

      display_name: "Baby gender by county"

      warehouse_id: aae11o8e6fe9zz79

      file_path: ../src/baby_gender_by_county.lvdash.json

      parameters:

        catalog: ${var.catalog}

https://docs.databricks.com/en/dev-tools/bundles/settings.html

[drag7ter]

Thx for advice, but unfortunately it doesn't work, my cli version is 232:

Deploying resources...
Updating deployment state...
Deployment complete!
Warning: unknown field: parameters
at resources.dashboards.my_dashboard
in resources\dashboards\bundles\my_dashboard.yml:7:7

[drag7ter]

variables:

  catalog:

    description: "Catalog name for the dataset"

    default: "dev"

parameters:

        catalog: ${var.catalog}

doesn't replace parameter values prod -> dev in json when it is being deployed

"datasets": [

    {

      "displayName": "my_table",

      "name": "16b8c208",

      "parameters": [

        {

          "dataType": "STRING",

          "defaultSelection": {

            "values": {

              "dataType": "STRING",

              "values": [

                {

                  "value": "prod"

                }

              ]

            }

          },

          "displayName": "catalog",

          "keyword": "catalog"

        }

      ],

      "query": "SELECT * FROM IDENTIFIER(:catalog||'.my_schema.my_table')"

    },

[mydefaultlogin]

Hi @drag7ter ,

I have the same problem on my project - the "catalog" part of the table location must be dynamic.

 @Alberto_Umana 's answer obviously does not work.

Were you able to find any workaround for this? My workaround is to create two different versions of the .lvdash.json file (e.g. dash_cat_dev.lvdash.json and dash_cat_prod.lvdash.json) and use a DAB variable in the file_path field. This feels cumbersome though

[ewwhitley]

Just chiming in to say I'm also looking for a solution that lets us use DAB variables in parameters. This would really let us improve reuse and configuration across multiple environments and clients. Then we can set the various options via a deployment pipeline to improve reusability.

My only other thought is to serialize the dashboard json and treat it as a string "serialized_dashboard" (https://docs.databricks.com/aws/en/dev-tools/bundles/resources#dashboard), but that seems really ugly.

[mydefaultlogin]

My comment above comes from June. In July I also discovered the approach with the `serialized_dashboard` and I have been successfully using it since then.

I agree that it seems ugly (especially git diffs look horrible) but it does its job.

I generate the dashboard yaml files in an automated way, the `serialized_dashboard` is obtained by calling json.dumps().

[drag7ter]

Could you pls provide an example how do you use dashboard as `serialized_dashboard`? How you asset bundle look like?  And how you replace parameters like catalog_name during deployment?

[JonA]

I am also interested in a solution, I have the same problem

[Karola_de_Groot]

Is there already a better answer from Databricks on this case? @Alberto_Umana 
We also need to deploy dashboards to different workspaces where the catalog in the dataset queries also need to change. Now AI/BI dashboard queries only allow actual select * from catalogname.calculations for instance, no parameters.

[Hubert-Dudek]

Me the same. It is one of the most critical fixes required to encourage more people to deploy dashboards to production. Actually, it could be the same parameters/setup as are for task jobs.

[szymon_dybczak]

Hi @Hubert-Dudek , @Karola_de_Groot 

There's an open issue on github regarding this change. We can try to generate some traffic in comment section and maybe it will be raised sooner.

DAB dashboards variable substitution in dataset queries · Issue #1915 · databricks/cli

[Karola_de_Groot]

I did however just found out there is parameterization possible.. dont know yet how to incorporate it into asset bundle deploy but at least i have a first step. 
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

So if anyone has the next answer on how to change this variable via asset bundle deploy so i get prod on prod and dev on dev. would be awesome.