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

6
votes

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)

3
google-bigquery
Note you can undelete your table, if you get to it quickly enough: stackoverflow.com/questions/27537720/… - Jordan Tigani
Nah was too late (It did copy the table schema, but with zero rows) - Guus

3 Answers

12
votes

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

3
votes

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.

0
votes

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.