如何检索与 BigQuery 作业关联的用户和 SQL 代码? [英] How retrieve the user and SQL code associated with a BigQuery job?
问题描述
出于安全目的,我希望能够审核谁在某个项目中运行了哪些类型的查询.这可能吗?
For security purposes, I'd like to be able to audit who is running what kinds of queries within a certain project. Is this possible?
在命令行中使用 bq ls -j
会提供一些信息;bg show -j
给出了更多信息.但既不显示用户也不显示查询本身.
Using bq ls -j
from the command line gives some info; bg show -j
gives a little more. But neither show the user or the query itself.
推荐答案
在 CLI 中,您可以运行 bq ls -j -a
来检索项目中所有用户的作业.
In the CLI, you can run bq ls -j -a
to retrieve jobs for all users in a project.
然后您可以为每个作业 id 运行 bq show -j
并且为了获得更多详细信息,您将选择使用 json 响应:
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": ""
}
使用 API,您需要传递 allUsers 属性以列出所有用户的作业 https://cloud.google.com/bigquery/docs/reference/v2/jobs/list#allUsers
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
这篇关于如何检索与 BigQuery 作业关联的用户和 SQL 代码?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!