Question

Merged Result API

  • 10 July 2018
  • 9 replies
  • 224 views

Userlevel 1

Hi All,


Does anyone have any documentation (or is it possible) to do an API on a merged result in a dashboard?


Best,

Kyle


9 replies

Hi @kpmartin87,


Thanks for writing in. Would you mind elaborating on what you’re trying to do with the API and merge results (i.e. Are you trying to run a dashboard with an merge result tile via the API? Add a merge result tile to a dashboard? Etc.)?


Best,

Elliot

Userlevel 1

Hi @Ellio,


I am specifically trying to get all the data from a merged query tile on a dashboard and post that data elsewhere.


Best,

Kyle

Gotcha, thanks for the context Kyle!


While it’s currently not possible to run merged queries (and get merged query results) via the API, the ability to do so will come with the official release of Looker API 3.1. There’s no firm timeline on when API 3.1 will be released, but keep your eyes on our release notes for more information!


Have a good one,

Elliot

Hi folks, wondering if there’s been any progress or updates? It’d be really great to have the API return the merge results.


Thanks!

Hi, @Ellio, is there a way to run merged queries via the API in the latest version?

Userlevel 7
Badge +1

No, that functionality is still not available. If it were to come out, it will definitely be in that release’s Release Notes.

Userlevel 6
Badge

Is this possible yet? Had a quick look around and couldn’t see anything simple yet?

Userlevel 5
Badge

Pushing this up the list once again. Sad that it’s still not possible or has anyone found some ways?

Userlevel 3

OK, so technically it is possible. Feasible maybe not so. If you are ok with each of the merged queries in the tile being a separate file and you plan to just merge the files elsewhere / manually, this is super easy. If you want to download each query into one file as if it were a normal dashboard tile AND have unlimited results this gets a bit more tricky. 

In either case, the first step ends up being the same; get the merge query ids from `dashboard_element`, get query details for each of the queries from the `merge_query` endpoint, and change the `limit` to `-1` (unlimited). This can end up looking something like this:

 data = sdk.search_dashboard_elements(dashboard_id=dash_id, title=tile_title )
mergeid = data[0].merge_result_id

# hit merge_query to get source query ids
mergedata = sdk.merge_query(merge_query_id=mergeid)
source_qs = mergedata.source_queries
source_qids = []
num_merged = len(source_qs)

for x in range(num_merged):
source_qids.append(source_qs[x].query_id)
query_body = sdk.query(query_id=source_qids[x])
elements_needed = ['model','view','fields','filters','sorts','limit','query_timezone']
query_body['limit'] = '-1'
listlen = len(elements_needed)
final_body = {}

If you’re ok with having each query saved as a different tile, you can then just iterate through running each of the queries and saving as a resultset then writing to a file. Could look something like this:

< some for loop goes here >
...
resultset = sdk.run_inline_query('csv', final_body)

with open(tile_title + 'q '+str(x)+'.csv', 'w', newline='') as file:
file.write(resultset)

If you want to save everything unlimited into the same file, you end up needing to do a bit of heavy lifting outside the API. The easiest way I’ve found is just to use a pandas dataframe for each query and then do a merge in a similar way that Looker merges merged results under the hood (like a left join). That can be computationally expensive to the machine you’re downloading on though, depending on how big the query result sets are. 

Full scripts here:

Hope this helps!

Reply