BigQuery Job ID for Looker Query

  • 9 February 2017
  • 14 replies


We’re doing some administration of Looker on BigQuery. Is there a way to associate a Looker query with a BigQuery Job ID? We’d like to be able to get detailed BigQuery job stats and execution plan for specific Looker queries that have run.

14 replies

Userlevel 3

+1 it would be great if there were a way to do this. I know we’ve talked about a comment in the SQL and it’s a hard problem, but at our BigQuery scale this is becoming a really hard problem.

Userlevel 3

Looker team?

Userlevel 1

Hi Josh, there are no plans as of this time but our Product team is aware of the benefits of surfacing Job ID and other query stats. @joshsiegel

Userlevel 3

Actually, there is already existing functionality in Looker to handle most of the relevant use cases, but it needs to be tweaked.

Looker has a field sql_text. A hash of this field concatenated with a query timestamp would let us work across the two datasets (i.e. logs from BigQuery and logs from Looker). However, at the moment the sql text is not populated uniformly across all queries.

Userlevel 4

In a related topic: Context Comments might be relevant

Please let us know if there is an update regarding this ticket.

Userlevel 7

you can fish out the historyid (lookers ID) from the context comments which are in the bq logs.

With context comments being being disabled in Looker 7.6 for BQ, how would we go about joining Looker logs to BQ logs with no History ID available, historically we used this to join the datasets together?

Userlevel 7

Looker should really lean on the Google BQ guys to get this changed (bq cache queries incorporating comments which is silly). Having the looker historyID in the logs of bq is probably very important to many people. @izzy could you mention this to the relevant people.



I’ve faced this issue also!

Userlevel 7
Badge +1

We didn’t realize how big of an impact this would have when we made the change with performance improvements in mind. We’ve taken steps to fix it post haste! We’re adding BigQuery context comments back in 7.10 as an optional toggle in the connection settings. It will be backported to 7.8 and 7.6 for those on older releases.

We haven’t managed to edit the BQ caching mechanism yet, but this will get back to the previously expected functionality 🎉

Any latest update on this feature request please, Looker Team?

Hey All, 
@ADP , I had the same requirement, where I needed to: 
1) Export Looker-Usage containing (Look-Title, Dashboard-Title, UserID, UserEmail, HistoryID) 
2) Join that with some BigQuery Audit (data-access) logs based on extracted HistoryID and UserID from the query context (at the beginning of the query). 
But The problem is that disabling Query-Context lets us gain BQ caching capability. 
So after some digging around, I did this workaround : 
 I setup a Logging Route in GCP/Logging where I direct all BigQuery data-access requests into a table in BQ, I named it (bigquery_data_access) it’s a versioned table (by day). 
Now, in the request that Looker sends, there are some very useful metadata, (the same as Query-Context). and you can extract them using a query: 

(select value from bigquery_data_access.protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.labels where key = 'looker-context-user_id') as UserID,
(select value from bigquery_data_access.protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.labels where key = 'looker-context-history_id')
FROM my_dataset.bigquery_data_access bigquery_data_access
WHERE date between x and y

After that, I was able to join with Looker-Usage and get insights about each report/look and the cost of each Looker report. 

Userlevel 5

Starting in Looker 22.16, the Query Performance Metrics explore in System Activity includes the BigQuery Job ID for queries run in Looker.