BigQueryでマーケティングデータ基盤などを構築している場合に、クエリのキャッシュが動作しているせいで「最新のデータに更新されない」という問題が起きるケースがあります。このようなときの対処法をお話致します。
キャッシュが使われるとどうなる？
BigQueryのドキュメント「キャッシュに保存されているクエリ結果を使用する」には下記のような記載があります。
BigQuery はすべてのクエリ結果をテーブルに書き込みます。テーブルはユーザーが明示的に指定したテーブルか（宛先テーブル）、一時的なキャッシュ結果テーブルのいずれかです。一時的なキャッシュ結果テーブルは、ユーザー別、プロジェクト別に保持されます。一時的なテーブルにはストレージの費用がかかりませんが、永続的なテーブルにクエリ結果を書き込むと、そのデータの保存に対して課金されます。
https://cloud.google.com/bigquery/docs/cached-results?hl=ja
処理スピード及び課金面で良かれと思って、同じ結果をいちいち再度処理しないで良いように一時的なキャッシュ結果が利用されるのですが、制限事項をきちんと理解していないとキャッシュが効いているのか、効いていないのかわかりにくいので注意しましょう。
キャッシュが使われない例外を理解する
ドキュメントに明記されいている「キャッシュが使われない例外」の中で引っ掛かりやすいなともったのがこちらです。
- 結果がキャッシュに保存された後で、参照するテーブルまたは論理ビューが変更された
CURRENT_TIMESTAMP()やCURRENT_DATEといった日時関数やSESSION_USER()関数など、実行タイミングによって異なる値を返す非決定性関数がクエリで使用されている場合
私の経験上、特にここが重要です。問題が起きるケースとその解決策が詰まっています。
課題：テーブルは変更されているが、それを統合しているビューは変更されていないケース
「結果がキャッシュに保存された後で、参照するテーブルまたは論理ビューが変更された」の逆、つまり「参照するテーブルまたは論理ビューが変更されていない」場合にはキャッシュが利用されるということになります。テーブルが変更されていない、ならキャッシュを使うというのは当然問題ないです。問題になるのが、「論理ビュー」が変更されていないけれどもビューが参照しているテーブルが変更されているようなケースです。
私の場合は、コンソールでの実行ではなくSupermetricsなどBigQueryをGoogleスプレッドシートに展開するようなツールでクエリをビューに対して叩くときのよくこのキャッシュの問題に当たっています。
解決策：クエリに無理やり変数を入れる
例外にあった、「CURRENT_TIMESTAMP() や CURRENT_DATE といった日時関数や SESSION_USER() 関数など、実行タイミングによって異なる値を返す非決定性関数がクエリで使用されている場合」を使って、クエリ内部に変数を入れることでクエリ自体が実質同じ内容にならないようにします。
SELECT
name,
count,
CURRENT_TIMESTAMP()
FROM
`my-project`.mydataset.mytableというようなクエリです。私がよく使うのは日本時間での更新時間を入れると便利なことがデータ基盤を作る場合には多いので、下記のような感じにします。
#standardSQL
SELECT
date,
sum(cost) AS cost,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'Asia/Tokyo') as time_updated
FROM
`project.dataset.table`
group by
dateちなみに、冒頭に下記のような記述があるのは、
#standardSQL今はほとんど通常時は利用されることはないのですが、Supermetricsなど外部のツールからBigQueryを叩く場合はこの記述で標準SQLということを宣言しておかないとエラーが出ることがあるためです。
株式会社 GLASS代表。広告からSEOなどあらゆるデジタルマーケティングに精通するデータアナリスト。徹底的に現場に根付いた、スピーディーでシンプルなデータ分析環境の構築と施策推進を得意とする。
＜経歴＞
株式会社マイネットでモバイルCRMサービス（後にYahoo! JAPANに売却）のマーケティング部長、モバイルアプリ事業を立ち上げ。Googleで広告代理店営業マネージャーとして国内50社以上の広告代理店のコンサルティング。通信テクノロジー業界シニアアカウントマネージャーとして大手企業向けのマーケティング支援に関わる。
