タイトル通りです、沼りました。
2度と僕のような犠牲者を出したくないとの思いからブログにしたためさせていただきます。
TL;DR
- 【状況】hoge_{yyyymmdd}のように日付でシャーディングされたテーブルがあり、同じデータセット内にhoge_1_{yyyymmdd}のような名前で、一番目のテーブルとはカラム構造が異なるテーブルが存在するとき、
- 【試行】「from hoge_* where _table_suffix ~~」のようにワイルドカードを用いて、hoge_{yyyymmdd}の該当期間のテーブルを一括操作しようとすると、
- 【結果】「hoge_1_{yyyymmdd}」のテーブルもワイルドカードの対象範囲となってしまい、カラム構造がバグる（hoge_{yyyymmdd}じゃなくてhoge_1_{yyyymmdd}のカラム構造になる）。
- 【対策】紛らわしいテーブル名にしない。どうしても紛らわしいテーブル名にする場合はワイルドカードの対象が区別されるような命名にする。もしくはデータセットを分ける。
詳細
状況
例えば以下のようなGAの生ログを集計した中間テーブルがあったとします。毎日中間テーブルを作っており日付でシャーディングされてる感じです。名前は「ga_dataset_{yyyymmdd}」としておきましょう。
さて、ここで以下のような別の中間テーブルも欲しくなったとします。上のテーブルとはカラム構造が異なっていることに注意してください。そして何を思ったかこのテーブルの名前を「ga_dataset_1_{yyyymmdd}」としてます。（これが悲劇の始まりです）
試行
分析用にさらにテーブルを加工していくケースを想定します。該当期間のテーブルを一括で読み込んで操作をすることなどはよくあるでしょう。下のようなケースです。
結果
以下のような結果になります。これは欲しかったga_dataset_{yyyymmdd}ではなくga_dataset_1_{yyyymmdd}の方のカラム構造が抽出されてしまっています。当然本来存在しないカラムを抽出していることになるのでnullばっかになってます。
こちらのブログ（BigQueryのテーブル分割でのパーティションテーブルとシャーディングテーブルの使い分け）で詳細に解説してくれているように、ワイルドカードは単純な末尾マッチングなので、今回のケースで言うと狙った「ga_dataset_{yyyymmdd}」だけでなく「ga_dataset_1_{yyyymmdd}」の方も対象範囲となってしまったことがその原因かと思われます。
対策
- 紛らわしいテーブル名にしない。今回のケースで言えば、「ga_channel_{yyyymmdd}」と「ga_pagePath_{yyyymmdd}」などと明確に区別。
- 普通に管理の上でもオススメ。
- どうしても紛らわしいテーブル名にする場合はワイルドカードの対象が区別されるような命名にする。
- 上のブログでも紹介されているように一つ目のテーブルを「ga_dataset__{yyyymmdd}」
などとしてテーブル指定を
「from `PROJECT.DATASET.ga_dataset__*` where _table_suffix ~~ 」
とすれば回避可能。 - とはいえ、対処療法感が否めないのであまりオススメはできません。
- 上のブログでも紹介されているように一つ目のテーブルを「ga_dataset__{yyyymmdd}」
- もしくはデータセットを分ける。
- データセット名称を新たに区別するという観点では、一番目の対策とほぼ同じ。
以上です。誰かのお役にたてば幸いでございます。
