[BigQuery]6ヶ月以内の過去のクエリをAPIを使って発掘する

履歴はWebでも見れるが・・・

BigQueryのjobの実行履歴は普通にWebからも見れます。

ここ押してから

ここに表示されます。が、ここは 1000件 しか表示されません。
select文だけでなく、exportや毎日のバッチ処理でのテーブルでも1件なので思ったよりも表示されなかったりします。

もっと昔の履歴が見たい！

Web上にはないので消えているかと思いきや、APIやCLIだと過去6ヶ月間のものは取得可能なようです。 なんでWebで見れるようにしてくれないんだ

https://cloud.google.com/bigquery/docs/bigquery-web-ui#viewing_job_and_query_history
履歴内にあるすべての該当ジョブを一覧表示するには、jobs.list API メソッドか bq ls CLI コマンドを使用します。

ちゃんと書いてありますね。

自分はCLI入れていないので、サクッと使えるAPIで試してみます。
今回の対象は jobs.list API です

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/list
指定したプロジェクトで開始したすべてのジョブをリストします。ジョブ情報は、作成後6か月間利用可能です。

APIを動かす

基本的に公式に書いてある手順を踏むだけです。

• https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/list

まずはコンソールからAPIが使えるように、認証トークンを貰いましょう。
こちらを参考にすれば、クレデンシャルファイルをダウンロードできるはずです。

• https://www.magellanic-clouds.com/blocks/guide/create-gcp-service-account-key/

次にパスを通してあげます

$ export GOOGLE_APPLICATION_CREDENTIALS="DLしてきたクレデンシャルファイルのパス"
例）
$ export GOOGLE_APPLICATION_CREDENTIALS="/home/user/Downloads/[クレデンシャルファイル名].json"

次に、一時的なアクセストークンを取得します。GoogleCloudSDKを使います。

• https://cloud.google.com/sdk/?hl=ja

$ ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"

一時的なものなのでexportしてないんですかね。一応外しておきます。

次にアクセストークンを使ってAPIにリクエストを送信します。
クエリパラメータの以下を設定します。

• projection=FULL 実行クエリの内容も全て表示
• maxResults=10000 表示件数とりあえず1万。デフォルトだと50件しか返ってこない。少ない。

$ curl -H "Authorization: Bearer $ACCESS_TOKEN" "https://www.googleapis.com/bigquery/v2/projects/[自分のプロジェクト名]/jobs?projection=FULL&maxResults=10000"

{
  "etag": string,
  "kind": string,
  "nextPageToken": string,
  "jobs": [
    {
      "id": string,
      "kind": string,
      "jobReference": {
        object (JobReference)
      },
      "state": string,
      "errorResult": {
        object (ErrorProto)
      },
      "statistics": {
        object (JobStatistics)
      },
      "configuration": {
        object (JobConfiguration) // ここにクエリ内容が表示される
      },
      "status": {
        object (JobStatus) // 成功可否
      },
      "user_email": string
    }
  ]
}

履歴がバーっと表示されたらOKです！

かなりの量が返ってくると思うので、絞り込みしたい場合はクエリパラメーターで絞り込みができます。適宜設定してください。

• https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/list#query-parameters

jqコマンドにパイプするのもオススメです。自分はクエリの内容を見たかったので以下のようにしました。

$ curl -H "Authorization: Bearer $ACCESS_TOKEN" "https://www.googleapis.com/bigquery/v2/projects/[自分のプロジェクト名]/jobs?projection=FULL&maxResults=10000" | jq '.jobs[].configuration.query.query' >> ~/query.txt

さぁ、高額クエリを叩いた犯人を探しましょう！！