bq コマンドを使って確認する方法
まずは、bq ls コマンドを使ってジョブの一覧を取得する。-j はジョブを出力するためのオプション。-a はすべてのジョブを出力するオプション。プロジェクトのオーナー権限を持っていれば自分以外のジョブも含めて確認することが可能。
$ bq ls -j -a
jobId Job Type State Start Time Duration
------------------------------ ---------- --------- ----------------- ----------
bquijob_2e179c0a_1549dexxxxx query SUCCESS 11 May 12:42:50 0:00:53
bquijob_8a812b8_1549afxxxxx query FAILURE 10 May 23:03:46 0:00:02
bquijob_17658ecd_154935xxxxx query SUCCESS 09 May 11:35:58 0:01:12
bquijob_292f46d0_154935xxxxx query SUCCESS 09 May 11:31:45 0:00:01
bquijob_62aa456e_1545acxxxxx load SUCCESS 28 Apr 11:53:30 0:00:01
bquijob_434a7ecb_1545acxxxxx query SUCCESS 28 Apr 11:53:07 0:00:00
...
特定のjobIDをピックアップして bq show コマンドを実行するとそのジョブの概要を確認することが可能。
$ bq show -j bquijob_2e179c0a_1549dexxxxx
Job project:bquijob_2e179c0a_1549dexxxx
Job Type State Start Time Duration User Email Bytes Processed Bytes Billed Billing Tier
---------- --------- ----------------- ---------- -------------------- ----------------- -------------- --------------
query SUCCESS 11 May 12:42:50 0:00:53 poo@gmail.com 35487178862 35488006144 1
クエリージョブの場合は、誰がいつ実行して何バイト処理したのかが分かるようになっているけど、もう少し詳細が必要な場合は json 形式で出力してあげる。bqコマンドに --format prettyjsonをつけて実行する。
$ bq --format prettyjson show -j bquijob_2e179c0a_1549dxxxxx
{
"configuration": {
"query": {
"createDisposition": "CREATE_IF_NEEDED",
"destinationTable": {
"datasetId": "_ccf1fa0a59a1479e1ec782cce6708e9edxxxxxxx",
"projectId": "project",
"tableId": "anon556b2d5c_89a7_465e_98df_34a0756xxxxxx"
},
"query": "SELECT\n language,\n SUM(views) AS views\nFROM\n [bigquery-samples:wikimedia_pageviews.201201] \n WHERE\n NOT title CONTAINS ':'\n AND wikimedia_project='wp'\nGROUP BY\n language\nORDER BY\n views DESC\nLIMIT\n 100;",
"useQueryCache": false,
"writeDisposition": "WRITE_TRUNCATE"
}
},
"etag": "\"oGMCLvGjZO7RB3oFjn17umMEDU4/EeSXp6NHdetsd6pfjrxiO-2pCgw\"",
"id": "project:bquijob_4914bb40_1545abxxxxx",
"jobReference": {
"jobId": "bquijob_4914bb40_1545axxxxx",
"projectId": "project"
},
"kind": "bigquery#job",
"selfLink": "https://www.googleapis.com/bigquery/v2/projects/project/jobs/bquijob_4914bb40_1545abxxxxx",
"statistics": {
"creationTime": "1461811113336",
"endTime": "1461811124331",
"query": {
"billingTier": 1,
"cacheHit": false,
"queryPlan": [
{
"computeRatioAvg": 0.1846392704406356,
"computeRatioMax": 0.22426073392703202,
"id": "1",
"name": "Stage 1",
"readRatioAvg": 0.5717204210533614,
"readRatioMax": 1.0,
"recordsRead": "1739542268",
"recordsWritten": "113509",
"steps": [
{
"kind": "READ",
"substeps": [
"language, title, views, wikimedia_project",
"FROM bigquery-samples:wikimedia_pageviews.201201",
"WHERE LOGICAL_AND(LOGICAL_NOT(STRING_CONTAINS(title, ':')), EQUAL(wikimedia_project, 'wp'))"
]
},
{
"kind": "AGGREGATE",
"substeps": [
"SUM(views) AS views",
"GROUP BY language"
]
},
{
"kind": "WRITE",
"substeps": [
"language, views",
"TO __stage1_output",
"BY HASH(language)"
]
}
],
"waitRatioAvg": 0.0016124914121492162,
"waitRatioMax": 0.0017565984133518038,
"writeRatioAvg": 0.00038691719472697486,
"writeRatioMax": 0.0005590019617612618
},
{
"computeRatioAvg": 0.0005556558642862923,
"computeRatioMax": 0.0007651221564364496,
"id": "2",
"name": "Stage 2",
"readRatioAvg": 0.0,
"readRatioMax": 0.0,
"recordsRead": "113509",
"recordsWritten": "316",
"steps": [
{
"kind": "READ",
"substeps": [
"language, views",
"FROM __stage1_output AS bigquery-samples:wikimedia_pageviews.201201"
]
},
{
"kind": "AGGREGATE",
"substeps": [
"SUM(views) AS views",
"GROUP BY language"
]
},
{
"kind": "SORT",
"substeps": [
"views DESC",
"LIMIT 100"
]
},
{
"kind": "WRITE",
"substeps": [
"language, views",
"TO __stage2_output"
]
}
],
"waitRatioAvg": 0.0012881721697913227,
"waitRatioMax": 0.0012881721697913227,
"writeRatioAvg": 0.0005689601240218813,
"writeRatioMax": 0.0017282867311910085
},
{
"computeRatioAvg": 0.00019802824842424135,
"computeRatioMax": 0.00019802824842424135,
"id": "3",
"name": "Stage 3",
"readRatioAvg": 0.0,
"readRatioMax": 0.0,
"recordsRead": "316",
"recordsWritten": "100",
"steps": [
{
"kind": "READ",
"substeps": [
"language, views",
"FROM __stage2_output AS bigquery-samples:wikimedia_pageviews.201201"
]
},
{
"kind": "SORT",
"substeps": [
"views DESC",
"LIMIT 100"
]
},
{
"kind": "WRITE",
"substeps": [
"language, views",
"TO __output"
]
}
],
"waitRatioAvg": 0.0019908115351320445,
"waitRatioMax": 0.0019908115351320445,
"writeRatioAvg": 0.0004939892005240635,
"writeRatioMax": 0.0004939892005240635
}
],
"referencedTables": [
{
"datasetId": "wikimedia_pageviews",
"projectId": "bigquery-samples",
"tableId": "201201"
}
],
"totalBytesBilled": "78039220224",
"totalBytesProcessed": "78038988491"
},
"startTime": "1461811113577",
"totalBytesProcessed": "78038988491"
},
"status": {
"state": "DONE"
},
"user_email": "poo@gmail.com"
}
...
これで、クエリーの中身やbillingTier, queryPlan の詳細といった細かい情報が確認できる。