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)

Answer 1

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

Answer 2

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.