CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
chillerm
chillerm

Reputation: 51

Taggin BigQuery jobs / queries with custom meta data

Good day,

Currently we are have both access and activity logging enabled, and pushing logs back to a BQ data set. Is there any way to tag BigQuery jobs or individual queries with additional meta data tags? Teradata has a feature like this called query banding, where you can submit groups of key value pairs and they are then tied to either a single query or a session, depending on how it is set.

The use case I envision here would be for me to be able to aggregate certain information from the audit logs using these tags. For example, I am trying to build a benchmarking process that I plan to run multiple times against multiple static data sets. It would be nice if I could somehow tag a specific query so that I can gather metrics around different executions of the same process, but also tie this together at different levels with different tags.

When I want to analyze I could then just run a query similar to this:

select 
custom_metadata.<custom_key2>, custom_metadata.<custom_key>, duration, bytesprocessed, querytext
from data_access_log
where
custom_metadata.<custom_key> in ('TY_LY_SLS', 'DLY_AGG')
and custom_metadata.<custom_key2> in ('SLS_BENCH')

or

select 
custom_metadata.<custom_key2>, DATE(start_time), sum(duration)
from data_access_log
where
custom_metadata.<custom_key2> = 'SLS_BENCH'
GROUP BY 1,2

Upvotes: 2

Views: 226

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

I am using jobid for this. For any job you can supply your own value - you just need to make sure it is unique. You can "ingest" your custom keys here and than parse them out and use them in your usage analysis.
Another option was to use comments in query text - but I didn't like this idea as it makes query analysys itself junky and specific only for query jobs, whereas jobid can be customized for any type of jobs, so more generic

Upvotes: 2

Related Questions