Is it possible to retrieve an extended or full query history in google bigquery?
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
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.