BigQuery Scheduled Queries まとめ

初めに

BigQueryのScheduled Queriesに関して、自分用にまとめる

目次

1. そもそもScheduled Queriesってなんぞ
2. Scheduled Queriesの初め方
3. 各設定項目
4. Schedule
5. 設定確認
6. Manual Runs
7. クエリパラメータ

0. そもそもScheduled Queriesってなんぞ

BigQuery上にて、クエリを定期的に実行して結果をテーブルに納めていくもの。
定期実行が失敗しても Manual Runs を使用して過去分を復旧することができる。

個人的にはデータポータルに表示するデータ類の中間テーブル作成用で使用中。

1. Scheduled Queriesの初め方

旧WEB-UIにて [New Query] テキスト ボックスが出ている状態で[Show Options]の一つ左にある
Schedule Query
ボタンを押すだけ。

新WEB-UIだとボタンが存在しなくて辛い。

2. 各設定項目

●通常設定

クエリ自体の編集は画面上部のテキストエディタ部分で行う。

項目	解説
Display name	表示名 そのまま
Schedule	クエリを実行するスケジュール設定 詳細は次で
Destination dataset	結果の書き込みを行うデータセット
Destination table	結果の書き込みを行うテーブル 存在しない場合は作られる
Write preference	WRITE_TRUNCATE: テーブルがすでに存在する場合、テーブルのデータを上書きする WRITE_APPEND: テーブルがすでに存在する場合、データをテーブルに追加する
Partitioning field	分割テーブル用 分割無しのテーブルと取り込み時間を使った分割テーブルの場合は空白でOK 分割テーブルを使用する場合は　TIMESTAMP または DATE　のPartitioning fieldを指定

●Advanced設定

項目	解説
Cloud Pub/Sub topic	クエリの実行が終わった後に通知するPub/Subの設定
Send email notifications	エラー時に管理者にメール通知するかどうかの設定
Disabled	チェック入れるとスケジュール実行されなくなる

3. Schedule

基本的には Repeats で指定した周期で At の時間にクエリが実行される。
繰り返す期間は Starting (UTC) から Ending (UTC) までとなる。

Repeats を Custom にするとある程度自由に設定することができる。
設定例）

30

every wed,fri of jan,jun 13:15
first sunday of quarter 00:00
every 4 hours

4. 設定確認

saveしたScheduled Queriesは旧WEB-UI上の左上の項目
Scheduled Queries
をクリックすると確認できる。

Display nameをクリックして詳細画面に入れば、editなども可能なので必要であればここからいじりましょう。
直近の実行履歴なども確認できます。
Manual Runsもこのページから実行可能なので忘れないように。

5. Manual Runs

一番めんどくさいところ。

[Start Manual Runs]をクリックすると Starting (UTC) と Ending (UTC) の設定画面が出てくる。
ここで指定した範囲に対して Scheduleで設定したRepeats周期でAtの時間が存在すれば 実行してくれる。
毎日0時に実行という設定で 1/2 15:00:00 ～1/5 3:00:00 を指定すると、1/3の0時、1/4の0時、1/5の0時の3回実行されることになる。

注意として、 Ending (UTC) は必ず現在より過去を指定しないとエラーになるということ。
現在バグ（？）のせいで、[Start Manual Runs]をクリックするとEnding (UTC)に、使用しているPCの現在時刻がUTC扱いで入ってしまい、そのまま使用すると上記のエラーになってしまう。

ということで、過去の実行もできるようになったが、過去の実行を行って過去のデータを出すには、クエリにいつのデータを取るのかということを教えなければいけない。
そこで出てくるのがクエリパラメータ。

6. クエリパラメータ

表記	型
@run_time	TIMESTAMP型
@run_date	DATE型

という形で二種類のパラメータが用意されている。
それぞれクエリが実行される日、つまりManual Runsを使用すれば過去の日付がそれぞれの型で送られてくる。
使い方は下記の通り

SELECT
    COUNT(*)
FROM
    hoge
WHERE
    datestamp = @run_date

@ではなく{}で囲ってあげるとテーブル名などにも使える

#最後に
以上駆け足ですがBigQueryのScheduled Queriesに関してまとめてみた。
ちょっとずつ弄ったり情報を追加する気持ちではいる。

早く新WEB-UIで使えるようにしてくれー！