中間テーブルを作成し、分析するクエリを中間テーブルに対して実行するという操作は、パフォーマンス向上や料金の節約のためなどにBigQueryでそれなりによくやる操作です。
一度やるだけならBigQuery Web UIで適当にやればいいんですが、何度も実行する場合や、毎日一回自動でやるという処理をしたい場合、どこからクエリを投げるのか、ちょっと悩むところです。
Compute Engineのcronで設定すると、Compute Engine管理するのが面倒だし、App EngineやGoogle Scriptでやる場合は、そこそこロジック組んであげる必要があります。
ごりごりロジック書ける人は好きな方法でやればいいんですが、今回は適当にブロック繋いでいけば適当にやってくれる MAGELLAN BLOCKS でやるやり方を紹介します。
実行するクエリ
bigquery-public-data:samples.natality のテーブルに対してクエリを複数回実行します。
似たようなカラムに対するクエリを複数回実行する前に、必要なデータを抽出して中間テーブルを作ることで、読み込むデータサイズを小さくして、パフォーマンス向上とクエリ料金を節約する感じのものです。
# 中間テーブル用のクエリ
SELECT
year, state, mother_age, mother_race, drinks_per_week
FROM
[bigquery-public-data:samples.natality]
WHERE
state = 'WI' OR
state = 'CA' OR
state = 'CO'
# COのデータを取得する
SELECT year, state, AVG(mother_age) AS avg_mother_age
FROM cpb200demo1:work.state_data
WHERE state = 'CO'
GROUP BY state, year
ORDER BY year ASC
# CAのデータを取得する
SELECT year, state, mother_race, count(*) AS num_mothers
FROM cpb200demo1:work.state_data
WHERE state = 'CA'
GROUP BY state, year, mother_race
ORDER BY year ASC
# WIのデータを取得する
SELECT year, state, avg(drinks_per_week) AS avg_drinks_wk
FROM cpb200demo1:work.state_data
WHERE state = 'WI'
GROUP BY state, year
ORDER BY year ASC
ブロックの設置
ボードの作成までの手順は 公式のドキュメント に書いてあるので、BigQueryのブロックの設置から説明します。
まず、中間テーブルを作成するブロックを設置します。
クエリを実行するので、BigQueryの クエリーの実行 を使います。
次に分析用クエリのブロックを繋げていきます。
この時便利なのが、拡張設定で、 実行に必要なテーブル のところに、中間テーブルを指定しておくと、そのテーブルがあるのを確認して、クエリを実行してくれます。
後は、投げたいクエリの数だけブロックを置いておけばOKです。
おまけ
MAGELLAN BLOCKSの クエリーの実行 ブロックはクエリ実行のプライオリティのデフォルトがバッチモードになっています。
おそらくDaily Batchとかで人がいない時に実行するのがデフォルトの思想にあるからだと思いますが、ブロック置いてテストしてる時は、インタラクティブモードの方がとっとと実行されて便利なので、拡張オプションからインタラクティブに変えてテストして、できあがったら最後にバッチモードにすれば良いと思います。
