CODEWITHSUNDEEP
google-bigquery
Guus
Guus

Reputation: 329

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

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)

Upvotes: 11

Views: 31005

Answers (3)

Anna
Anna

Reputation: 59

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.

Upvotes: 0

Patrice
Patrice

Reputation: 4692

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.

Upvotes: 4

Pentium10
Pentium10

Reputation: 207893

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

Upvotes: 16

Related Questions