2

I'm attempting to find out which users are accessing the different BigQuery tables in our project. I can't find anything in the BQ documentation that references this. Many searches on the Internet have failed because people use BQ to process logs for other applications. I can't find any mention of BQ logs.

Rob Reid
  • 21
  • 1
  • 5
  • Possible duplicate of [Is it possible to retrieve an extended or full query history in google bigquery?](http://stackoverflow.com/questions/31140104/is-it-possible-to-retrieve-an-extended-or-full-query-history-in-google-bigquery) – Pentium10 Nov 18 '15 at 15:46

1 Answers1

2

You should use jobs list API - https://cloud.google.com/bigquery/docs/reference/v2/jobs/list

Lists all jobs that you started in the specified project. Job information is available for a six month period after creation. The job list is sorted in reverse chronological order, by job creation time. Requires the Can View project role, or the Is Owner project role if you set the allUsers property.

User_email property is what you are looking for - along with all other goodies of course

Please pay attention to allUsers parameters when do api request

After jobs list is available you should look for jobs.configuration.query.query This property is text of query that user has run. If you are looking for specific table(s) you can just write relatively simple match (using REGEXP_MATCH - https://cloud.google.com/bigquery/query-reference#regularexpressionfunctions) logic to filter those who query tables of your interests

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • There's a frustrating weakness in this, though. The jobs API only returns those jobs that were executed from within your project's context. In other words, if you share a dataset with someone else, and they query it from the context of a different project (which they can always do), you get no record of that query. Would love to get an official answer from Google on this. @Felipe Hoffa? – Michael Ames Nov 18 '15 at 19:52
  • Agree. This approach works for only queries issued from within your project(s). Doubt that you can see activity from not your project! Good to have, but that would be some sort of "privacy violation". Would be great to hear from Google Team. – Mikhail Berlyant Nov 18 '15 at 20:04
  • 1
    BigQuery does not currently provide this functionality, but we're working on it! Stay tuned. – Jeremy Condit Nov 18 '15 at 20:06
  • @MikhailBerlyant -- Much more than a "good to have" for certain types of data, like protected health information, which in contexts are required by policy or regulation to have all access logged and reportable. :-( – Michael Ames Nov 19 '15 at 06:20