Updating a dashboard element query model using Python

[lambdamusic]

I have a dashboard and would like to update the LookML model used in all of its tiles / queries.

Context: we are migrating the dashboard to different model implementation. So trying to automate an otherwise tedious manual task.

I got this far by using update_dashboard_element, but struggle to understand the model updating API:

# THIS WORKS 
myelement = sdk.dashboard_element("1196")
print(myelement.title)
print(myelement.query.model)


# THIS WORKS TOO 
body = {}
body['title'] = "Title Updated Via API"
response = sdk.update_dashboard_element(
    dashboard_element_id=myelement.id,
    body=body
    )


# THIS FAILS  
body = {}
body['query'] = {}
body['query']['model'] = "fake_model"
# get a 200, but nothing changes in Looker! 
response = sdk.update_dashboard_element(
    dashboard_element_id=myelement.id,
    body=body
    )

Any help greatly appreciated!

[k7ragav]

Hi @lambdamusic. I do have a workaround

What could be done is

1. copy the query element as a new variable.
2. Change the model name
3. Then create a new query using create_query endpoint.
4. And add that new query ID to the Dashboard by using the update_dashboard_element and update the query.id

So something like this

altered_query = my_element.query
altered_query['model'] = 'fake_model'
altered_query['id'] = None # in order to reset the ID
new_query = create_query(body = altered_query)
sdk.update_dashboard_element(dashboard_element_id = myelement.id, body = WriteQuery(query_id = new_query.id))

Haven't tested the exact script above. But the flow should be the way as described above.

[lambdamusic]

Thanks @k7ragav for the tip! Seems to go in the right direction but can't get it to work still.

The WriteQuery builder does not have a query_id attribute.

from looker_sdk import models

altered_query = myelement.query
altered_query['model'] = 'fake_model'
altered_query['id'] = None 
altered_query['client_id'] = None # Needed by the create_query method below

new_query = sdk.create_query(body = altered_query)

# THIS BREAKS
sdk.update_dashboard_element(
    dashboard_element_id = myelement.id, 
    body = models.WriteQuery(query_id = new_query.id))

>>>
>>> TypeError: __init__() got an unexpected keyword argument 'query_id'
>>>

I tried to construct the WriteQuery instance based on other examples I found online.

That seems to be working in the sense that I get no errors and a 200 from Looker.

But no visible results in the Looker dashboard itself!

my_instance = models.WriteQuery(
        model=new_query.model,
        view=new_query.view,
        fields=new_query.fields,
        pivots=new_query.pivots,
        fill_fields=new_query.fill_fields,
        filters=None,
        sorts=new_query.sorts,
        limit=new_query.limit,
        column_limit=new_query.column_limit,
        total=new_query.total,
        row_total=new_query.row_total,
        subtotals=new_query.subtotals,
        dynamic_fields=new_query.dynamic_fields,
        query_timezone=new_query.query_timezone,
    )

# WORKS, BUT NO EFFECTS IN LOOKER
sdk.update_dashboard_element(
    dashboard_element_id = myelement.id, 
    body = my_instance)

After the last operation, in Looker I'd expect to see the query failing cause "fake_model" does not exist... unless I'm missing something here!

What do you think?

[k7ragav]

Hi @lambdamusic

Maybe could you try this in the first snippet.

sdk.update_dashboard_element(
    dashboard_element_id = myelement.id, 
    body = models.WriteDashboardElement(query_id=new_query.id))

Let's see if that works 🤞

[lambdamusic]

That works :-)

Posting again the full solution for posterity's sake:

import looker_sdk
sdk = looker_sdk.init40()
from looker_sdk import models

myelement = sdk.dashboard_element("1196") # change as needed

altered_query = myelement.query
print("Old Model: ", altered_query['model'])
altered_query['model'] = 'fake-model'
print("New Model: ", altered_query['model'])
altered_query['id'] = None 
altered_query['client_id'] = None 

new_query = sdk.create_query(body = altered_query)

sdk.update_dashboard_element(
    dashboard_element_id = myelement.id, 
    body = models.WriteDashboardElement(query_id = new_query.id))

Thanks again @k7ragav