BigQueryどハマり珍道中
こんにちは私です。
前回のブログエントリから結構な間が空いてしまいましたが、存命です。
前回のエントリはathenaの話でしたが、今回はbigqueryです。 またもや大量のログと格闘しております。
アド関係やってると大量ログとの格闘は避けられないようです。
まあ、SSPとかDSPの事業者さんと比べると屁でもない量なんですけど、それでもathena(presto)やbigquery使わないとキツイ感じですね
今回はbigquery触っててちょっとハマった点のメモです。
なお、standard sqlの話なので注意
(正直bigqueryのnewbieなので、standard sqlをデフォルトにしてほしいと思ってる)
文字列をSPLITして返るARRAYのインデックス指定方法でハマる
SPLITは名前そのまんまで
SPLIT(value, separater)
みたいな引数を指定すると、valueをseparaterを区切り文字として分割する関数。 valueが文字列の場合はARRAYが返る。
で、ARRAYのインデックスを指定する場合
array[offset(0)]
と指定してやればいい。インデックス番号を直接指定するのではなく、offsetを挟まないとダメ。
(0スタートの場合、offsetで、1スタートの場合ordinalを使う)
今回のケースでは、FROM句のテーブル名にワイルドカードを指定した場合に参照できる疑似カラム_TABLE_SUFFIX(ワイルドカードにマッチした文字列が入る)をアンダースコアで分割して、末尾を参照したかったので、以下のように指定した
SPLIT(_TABLE_SUFFIX, '_')[OFFSET(1)]
何故かこれだとsyntax errorになる
正しくは以下のようにする
SPLIT(_TABLE_SUFFIX, '_')[OFFSET(1) ]
offsetのあとに半角スペースを入れないとsyntax errorになるってわけ
なんでやねん:D
公式のマニュアルを何度見ても、半角スペース入ってないんだけどなあ。。。(もしかしたら今は直ってるかも)
テーブルの更新回数上限を超過する
以下のような処理のバッチを作っていた。
- MySQLに保存してある条件を基に、BigQuery用のクエリを生成(複数クエリになる)
- 上記クエリを実行して、返ってくるレコードをBigQueryの一時保管用テーブルに書き込み(以下テンポラリテーブルとする)
- 全クエリ実行し終わったら、テンポラリテーブルをgzipにしてエクスポート
このバッチをhourlyで動かしてて、まあ当初はなんの問題もなく動いていたんだけど、つい先日バッチがコケるようになって、エラーメッセージを見ると、「quota exceeded」とかいう文字列が。。。
生成されたクエリの数が多すぎて、テーブルの1日あたりの更新回数上限を超えてもうた
ここで疑問があって、バッチ実行毎にテンポラリテーブルは作り直してる(初回クエリにreplaceオプション付けてる)んだけど、更新回数上限は引継いでしまってるっぽいこと
テーブル名を別名にしないとダメなんだろうか?( バッチ実行時のタイムスタンプをテンポラリテーブルのサフィックスにつけるとか)
この場合、回避策としては以下の2つが考えられる。
- 全クエリをUNION ALLでガッチャンコしてしまう
- テンポラリテーブルを複数用意して、更新先を散らし、最後にマージする
UNION ALLでガッチャンコ作戦はクエリ長制限にひっかかることが予想されるので、普通にボツ(というかバッチ実装当初にこれでやってひっかかったわ)
なので、2番の複数テンポラリテーブル最後にマージ作戦でいった。
まあ問題なく動くんだけど、この回避策は一つ問題があって、MySQLに保存されてる条件レコードが増えていって、生成されるクエリ数が増えていくと、テンポラリテーブルを3つ、4つと追加しないといけない泥縄式なところ。
生成されたクエリの数から自動的にテンポラリテーブルの数を調節するようなコードを書けば済む話ではあるけども。
感想
BigQuery便利
athenaはクエリの失敗多すぎんのよね。最近リソース増強したのか失敗頻度が少なくなってるけど
それにしても、少人数でやってるとフロントエンドからバックエンドからインフラ、ログ基盤の整備までこなさないといけないのでツライタノシイ!!