Help us understand the problem. What is going on with this article?

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

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

BigQueryのjobの実行履歴は普通にWebからも見れます。
スクリーンショット 2020-01-22 16.26.38.png
ここ押してから
スクリーンショット 2020-01-22 16.26.50.png
ここに表示されます。が、ここは 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を動かす

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

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

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

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

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

$ 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です!

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

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした