はじめに
Google BigQuery (以下、BigQuery)という**Google Cloud Platform(GCP)のサービスを、本記事にアクセスした方なら聞いたことがありますよね？現在使っている方もおられると思います。
本記事は申し訳ございませんが、BigQuery を触ったことがない初心者用に書いていません。BigQuery を触ったことがない方は是非触っていただき、BigQuery の常軌を逸したスケールの大きさを体感してみてください。Qiita にBigQuery 入門編の良記事が多数投稿されています。
本記事は、実際にBigQuery を利用したデータ分析ソリューションなど提案、構築、ご利用している方々に、「ちょっとちょっと！この辺のこと知ってる？知らない？、ちゃんと考えてる？考えてるね」**って言いたい記事です。
BigQuery のイメージは？
BigQuery のイメージは、なんか間違えたら Google から高額請求がくるイメージですが、本記事は初心者向けではないので、この辺りは問題ない方々と思って、課金についての基本説明や溶かさない対策はスルーします。Qiitaにある有名なXXX万円〇〇した時の顔の記事をお読みください。他にはPB(ペタバイト)サイズのテーブルに対するクエリーが数十秒で返ってくるイメージですね。Google 様の金にモノを言わせた力技で、例えば10億レコードのテーブルを対象にするクエリーを、数千台のサーバーを使って高速に実行してしまいます。BigQuery は正に、ビッグデータ爆速解析プラットフォームと言えます。
BigQuery の仕組み
BigQuery を利用している方は、仕組みについて一度は聞いたことがあると思います。
一応簡単に説明しますが、詳細は本記事の参考リンクの分かり易い記事やググって探して読んでください。
BigQuery は、カラム型データストアとツリーアーキテクチャで構成されています。カラム型データストアは、クエリーで指定されたカラム(列=フィールド)のみアクセスします。必要なカラムのみアクセスするので高速な処理を実現できます。また、カラム型はデータの圧縮に効率がよく、高い圧縮率を実現できるとのこと。カラム(列)に入っているデータは、カーディナリティが低く(値が少ない種類という意味)重複していることが多いというのが理由です。データサイズを小さくでき、高速なアクセスにも保存スペースにもメリットがあります。
次にツリーアーキテクチャですが、ツリーの根(root)である root server がクエリーを受け取ったSQL文を解析し、より単純に実行できる複数のクエリーに分割します(クエリステージ)。このクエリーステージを、並列アーキテクチャであるツリーの葉、leaf serverで実行します。クエリーステージ間は、分散シャッフルアーキテクチャでお互いに通信しています。ツリーの枝はMixerと言い、leaf server の処理を集約する役目となります。
BigQuery は高速実行を実現するために、上記クエリー実行処理を全てメモリ上で行います。
以上、簡単な説明でしたので、細かくは抜けがあるかもしれませんので、参考リンクの記事をご参照ください。
BigQuery の Slot とは
BigQuery の Slot、BigQuery を使っている方はStackDriver で Slot の割り当て(allocated for Project)、利用可能総数(total Available)を確認できることをご存知と思います。Slotとは、公式ドキュメントに記載があります通り、クエリーの実行に必要な演算能力の単位です。演算能力ですから、上記のleaf server のnode(CPU Core)とイコールと思いきや、必ずしもそうではないようで、leaf server のスレッドと考えたらよいと思います。前述の分割した複数クエリーをSlotに割り当て、並列処理を行います。
Slotですが、GCPのプロジェクトに対して最大2,000 Slot割り当てられます。公式ドキュメントに記載の通り、2,000 Slotあればほとんどの処理は十分足ります。
BigQuery の弱点
ここからが本題です。
BigQuery のSLA読みました？
このように現在最強爆速データ分析プラットフォームである BigQuery ですが、BigQuery のSLAにはクエリー速度に関するSLO(サービスレベル目標)の記載がありません。クエリーの速度をあれだけ自慢気に謳っているにもかかわらず。どういうことでしょうか・・・
実は速度はベストエフォート
前述しましたSlotですが、デフォルトでプロジェクトに対して最大2,000 Slot与えられます。クエリーの爆速はSlot の並列処理により生み出していると言えます。Slotは、BigQuery のその時点で余っているリソースから割り当てるわけです、考えてみたら当たり前ですけど。Slotは基本的にグローバルリソースです。
ということは、最大2,000 Slot使用できると言っていますが、使いたくても2,000 Slot同時に使用できるとは限らないのです。料金のサイトにオンデマンド料金と記載があります。
Slot が不足した場合
BigQuery のSlotが不足した場合、Slotに空きがでるまでクエリー処理はWait状態になります。クエリー実行後6時間でタイムアウトします。
※クエリ実行時間の制限を参照
対策を考える
Slotのリソースが枯渇する可能性があり、クエリーの速度は必ずしも高速とは限らないと考えられますので、あまり考えずにソリューション構築してしまうと、GCPのデータ分析は需要があり、BigQuery の利用者が増えてる状況ですので、ある日急にリソースが無くなり、遅くなるということもあり得ます。
クエリーの実行において、最初からSlotの使用を極力少なく使用する作りを目指しましょう。Slot を極力使用しない作りは、結果パフォーマンスも良くなる可能性があります。
BigQuery のソリューション設計では、以下の点を考えるべきです。
1. SQL文の書き方はベストプラクティスに従う
若干肩透かし感がありますが、BigQuery のSQL文の基本はしっかりベストプラクティスに従いましょう。
2. テーブル設計はクエリー実行時にデータサイズが少なくなるように
- BigQuery のテーブル設計は、クエリー実行時のデータ量を減らすような設計を考えましょう。クエリーでのWhere句など行を絞る条件は処理するデータ量は減りません。
- パーテーションを有効に使えるなら使いましょう。
- カラムは極力非正規化しましょう。
- できれば、BigQuery 特有のネストフィールドを使用できるところは積極的に使用しましょう。
- トランザクションレベルのレコードを持っているテーブル同士をJoinすることは避けましょう。結構複雑なクエリーを書けるので凄いんですけどね。テーブル結合は、マスターテーブルのような小さいデータサイズのテーブルとするような設計にしましょう。
3. ジョブ実行は極力直列に実行
BigQuery のジョブを複数並列実行すると、自プロジェクト内の割り当て分 Slot を食い合うことになります。できる限り並列実行を避け、シンプルにジョブ実行するような設計にしましょう。
4. 対象データの取り込みはシンプルに
Google Cloud Storage (GCS) に保存したファイルを直接リンク(外部テーブルと言う)して、クエリー発行が可能ですが、基本的には BigQuery のテーブルに取り込みましょう(bq loadで)。外部テーブルはパフォーマンスに影響がありますし、料金も若干高くなります。
5. 複数リージョンでテスト実行
どのリージョンの BigQuery のリソースが空いているか、なんて Google は教えてくれませんから、念のため、試験して試してみましょう。日本からならUSリージョン、東京リージョン、シンガポールリージョンなど。(リージョン選択は料金も考慮する必要があります。)
試験して、StackDriver で使用Slot数を確認しましょう。
6. リージョン移行を考慮した設計にする
バッチ処理のクエリー速度が遅延したり、業務に支障が出てしまった場合、対策できるように、簡単にリージョン移行できるような作りが理想です。
例えば・・・
- 最適なGCS のバケットタイプの選択
- 別リージョンにすぐインポートできるように元データファイルをリカバリできるように保存
- 移行対象テーブルのエクスポート手順やインポートのデータサイズを少なくできるような作りにする
- バッチの対応はリージョン指定を変更するだけで変更可能とするなど
理想を並べただけですが…
7. 最悪はFlat Rate契約・・・
プロジェクトに割り当てられる最大Slot数は、2,000 Slotベストエフォートと言っていましたが、実は定額料金にしたら、2,000 Slotがなんと専用になります。しかも追加料金を払えば500 Slot単位で増加することが可能です。月額料金は以下の通りとなります。(クエリー料金のみ、ストレージ料金他は別途）
| リージョン | Flat Rate月額料金 | 追加料金 |
|---|---|---|
| US・EU | $40,000 | 500 slot追加毎 $10,000 |
| 東京 | $48,000 | 500 slot追加毎 $12,000 |
東京リージョンは、なんと月額 5,424,000円(税抜)です。($1 = 113円で算出)
しかし、遅いからと専用にしても、本当に2,000 Slot超えていた場合は速度が速くなる可能性は低く、ただ高額料金を払うだけになってしまいますので、十分設計時点で確認すべきでしょう。（基本は速くなると思いますが金額が金額だけに）
8. 最悪の最悪、別のサービス検討
どうしても2,000 slotを超え、処理遅延が起こってしまい、業務に影響してるけど、Flat rate契約が難しいという状況になってしまったら、もうどうしようもないので、VM インスタンスでスケールアップ可能なCloud DataProc の検討をするしかありません。(Red Shiftとは言えない)
個人的にはこのパターンには持っていかないようにしたいです。
[おまけ] リージョンによる実行速度差の検証
リソース(Slot)の空き状況によって、BigQuery の速度が変わってくることから、東京リージョンとUSリージョンの BigQuery にて同一クエリーを実行し、クエリー速度(経過時間)と消費したスロット時間を取得・比較します。東京リージョンとUSリージョンのどちらが現在リソースに問題ないリージョンか確認します。ただし、この検証ではオンデマンドの割り当て Slot数を使い果たす複雑かつデータサイズの大きなクエリーは(予算の都合上)実行しませんので、あくまでも参考としていただけたらと思います。
- 比較するデータ：Loto6 第1回(2000/10/5)から第1330回(2018/11/15)までの当たり数字6個＋ボーナス数字1個、9カラム・1330レコード、データサイズ：93.5 KB
- 測定方法：リージョン毎交互の５回実行、単純に全列・全レコード取得、no cache設定
測定結果
- 測定時刻：日曜AM 3時台(東京リージョン）、土曜PM 12時台(USマルチリージョン)
- ネットワーク速度は含めない。
| 回数 | リージョン | 経過時間 | 消費したスロット時間 | リージョン | 経過時間 | 消費したスロット時間 |
|---|---|---|---|---|---|---|
| 1 | US | 1.493s | 0.173s | 東京 | 0.812s | 0.060s |
| 2 | US | 1.47s | 0.187s | 東京 | 0.623s | 0.068s |
| 3 | US | 1.595s | 0.257s | 東京 | 0.793s | 0.052s |
| 4 | US | 1.453s | 0.350s | 東京 | 0.793s | 0.039s |
| 5 | US | 1.464s | 0.213s | 東京 | 0.658s | 0.051s |
| AVG | US | 1.495s | 0.236s | 東京 | 0.7358s | 0.054s |
見解
検証日はUSリージョン、東京リージョンの両地域とも休日であり、利用者が少ないと考えられますが、消費したスロット時間は東京リージョンは、USリージョンの1/4の消費時間であり、消費したスロット時間が短いことは、Slotが問題なく空いていてUSリージョンよりも効率的な並列処理が行われたためと推測されます。経過時間はクエリー実行時間ですが、当然東京リージョンが速いです。
この検証結果として、現状は東京リージョンでクエリーを実行する方が、クエリー速度において優位であると結論付けられます。
BigQuery ソリューションの構築は、現在なら東京リージョンがお薦めということになります。ただし、今後状況は変わりますので東京リージョンでも安心はできません。(いきなり Google 様がリソースを増やすこともあり得ますので、USリージョンは絶対駄目ということもありません・・・)
※もし、業務でBigQuery を利用している方で速度に懸念があり、リージョン移行を検討したい場合、本番環境に近しいデータを使用し検証してください。本検証結果によって判断されても責任は持てません。
終わりに
BigQuery はどんなことをしても速いわけではありません。Google 側のリソース状況によって左右されます。BigQuery はフルマネージドサービスであり、そのメリットは多々ありますが、何か問題が発生しても BigQuery サービス自体に私達は何も対策できないデメリットもあることを認識していただき、事前にできることはやっておくという意識を持っていただけたらと思います。
クラウドサービス特有の制約の中で、問題が起こってもすぐに対処でき、ユーザーが問題なく利用できる BigQuery ソリューションの構築を目指していただくことに、本記事が少しでもお役に立てたら幸いです。