BigQuery で実行済みのクエリージョブの内容を確認する方法

### 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 の詳細といった細かい情報が確認できる。