Is it possible to retrieve an extended or full query history in google bigquery?

Question

I recently discovered I accidentally deleted a table from BigQuery, which was constructed by querying other tables (which I still have).

As table deletes in BigQuery are permanent (right?), I would like to reconstruct the lost table, preferably without rewriting the query.

The Query History in the webUI obviously only displays a limited number of queries. The one I am looking for is unfortunately not in that list.

So my question is, is it possible to somehow recover the queries which have disappeared from the query history?

(I do know the creation date of the query I am looking for)

Note you can undelete your table, if you get to it quickly enough: http://stackoverflow.com/questions/27537720/how-can-i-undelete-a-bigquery-table — Jordan Tigani, Jun 30 '15 at 19:12
Nah was too late (It did copy the table schema, but with zero rows) — Guus, Jun 30 '15 at 20:01

score 16 · Answer 1 · answered Jun 30 '15 at 15:15

In the CLI, you can run bq ls -j -a to retrieve jobs for all users in a project.

Then you can run for each job id a bq show -j <job_id> and in order to have more details you will choose to use the json response:

bq show --format=prettyjson -j job_joQEqPwOiOoBlOhDBEgKxQAlKJQ

this returns the following format which have your query, your user and bytesprocessed etc...

{
  "configuration": {
    "dryRun": false, 
    "query": {
      "createDisposition": "CREATE_IF_NEEDED", 
      "destinationTable": {
        "datasetId": "", 
        "projectId": "", 
        "tableId": ""
      }, 
      "query": "", 
      "writeDisposition": "WRITE_TRUNCATE"
    }
  }, 
  "etag": "", 
  "id": "", 
  "jobReference": {
    "jobId": "", 
    "projectId": ""
  }, 
  "kind": "bigquery#job", 
  "selfLink": "", 
  "statistics": {
    "creationTime": "1435006022346", 
    "endTime": "1435006144730", 
    "query": {
      "cacheHit": false, 
      "totalBytesProcessed": "105922683030"
    }, 
    "startTime": "1435006023171", 
    "totalBytesProcessed": "105922683030"
  }, 
  "status": {
    "state": "DONE"
  }, 
  "user_email": ""
}

Using the API you need to pass allUsers property to list jobs from all users https://cloud.google.com/bigquery/docs/reference/v2/jobs/list#allUsers

score 4 · Accepted Answer · edited Jul 01 '15 at 12:36

4

Using the JobID, you can query for a specific job (documented here). This will give you a Jobs resource, which will contain your query.

If you don't know the JobID... it depends on how the query was ran I assume. It's possibly logged by the App Engine (if you ran it via code) in the Logs section of the Developer console. You could also take a look at the Jobs List (credit to the OP for that one) and look in there for your recent jobs. From the list you get Jobs Resources as well, so they will contain all you need.

edited Jul 01 '15 at 12:36

Pentium10

204,586
122
423
502

answered Jun 30 '15 at 14:00

Patrice

4,641
9
33
43

@Guus very welcome :) As you can see in the docs, these are kept for 6 months, so it shouldn't be a problem. Only downside is you NEED the JobID to get it, you can't just get a list of all jobs ran (AFAIK) – Patrice Jun 30 '15 at 14:03
Unfortunately, probably because I ran the queries through the web-interface and because my logging api was disabled, I was not able to find logs. – Guus Jun 30 '15 at 14:14
@Guus oh... this was all ran from the webUI?.... Then make sure you're logged in as the user who ran that query and look in its recent jobs, and hope for the best? :S – Patrice Jun 30 '15 at 14:15
It is not in "recent", however using the [jobs.list](https://cloud.google.com/bigquery/docs/reference/v2/jobs/list) API I was able retrieve a list of jobs with the required information. – Guus Jun 30 '15 at 15:04
1

@Guus I added Jobs List in my answer, just to make it more complete :). – Patrice Jun 30 '15 at 15:05

score 0 · Answer 3 · answered May 05 '20 at 22:20

This can be done using stackdriver audit logs. Here is more info.

Even if you haven't set up the stackdriver logging you can still try to find your query. BigQuery logs are enabled by default and you can retrieve those from stackdriver within 30 days.

Is it possible to retrieve an extended or full query history in google bigquery?

3 Answers3

Linked