[レポート] Best Practices from Experts to Maximize BigQuery Performance (featuring Twitter) – Google Cloud Next ’20: OnAir #GoogleCloudNext

現在、2020年7月14日から9月8日までの数週間にわたってGoogle Cloudのデジタルイベント『Google Cloud Next ’20: OnAir』が開催されています。

このイベントでは、2020年7月14日から毎週、異なるテーマで様々なセッションや催しのコンテンツが公開されています。(コンテンツは PDT[米国太平洋標準時(夏時間)]での火曜日午前9時→JST[日本時間]の水曜午前1時に配信される形になっています)

• 1.[2020/07/14〜] Industry Insights
• 2.[2020/07/21〜] Productivity & Collaboration
• 3.[2020/07/28〜] Infrastructure
• 4.[2020/08/04〜] Security
• 5.[2020/08/11〜] Data Analytics
• 6.[2020/08/18〜] Data Management & Databases
• 7.[2020/08/25〜] Application & Modernization
• 8.[2020/09/01〜] Cloud AI
• 9.[2020/09/08〜] Business Application & Platform

当エントリでは、その中から「Data Analytics」のセッションとして公開された『Best Practices from Experts to Maximize BigQuery Performance (featuring Twitter)』の内容について紹介していきたいと思います。

目次

• セッション概要
• セッションレポート
  • 各種事例の紹介
  • BigQueryのアーキテクチャでパフォーマンス促進
  • クエリ実行例
  • 可能であれば『近似集計関数』を使用する
  • JOINの効率性を改善させる
  • クエリプラン情報を利用したクエリ分析
  • パーティショニングとクラスタリング
  • リアルタイムクエリのためのマテリアライズドビューとBIエンジン
  • データロード(取り込み)の最適化
• まとめ

セッション概要

公式ページで紹介されているセッションの概要情報は以下の通り。

セッションレポート

当エントリでは、セッションの中からBigQueryに関するベストプラクティスに言及している部分のみ抜粋してまとめます。

各種事例の紹介

• Stotify
  • Spotify Customers Google Cloud
  • ストリーミング音楽サービスの中心は「データ」：毎日500TB以上のデータがロードされている/BigQueryの規模は数百PB級
  • 何百万人ものユーザーのリスニング習慣に基づいて曲やプレイリストを推薦
  • レガシープラットフォームで16分掛かっていた分析クエリが、BigQueryでは33秒に短縮
• HSBC
  • Adopting cloud, with new inventions along the way, charges up HSBC | Google Cloud Blog
  • 100PBクラスの及ぶ金融データをBigQueryに移行
  • 日々の流動性の計算、金融リスクのモデル化、金融犯罪の検出
  • BigQuery への移行後、国別の流動性計算の処理時間が数時間から数分に
  • 金融犯罪分析を10倍速で実行
• UPS
  • UPS uses Google Cloud to build the global smart logistics network of the future | Google Cloud Blog
  • 荷物、配送、ドライバー、ルートに関する10億ポイントのデータを毎日収集
  • 機械学習を利用して需要を予測し、トラックを最適化
  • 年間4億ドルと1,000万ガロンの燃料を節約

BigQueryのアーキテクチャでパフォーマンス促進

• コンピュートとストレージが分離されている
• ストレージにはPB級のデータを格納する事が出来る
• 暗号化されたレプリケーション
• スロットと呼ばれる計算能力の単位があり、全てのクエリに必要なステージの数を計算、各ステージに必要なスロットを動的に計算
• データはストレージからクエリサーバへ移動、クエリステージ間でデータを保存する必要がある複雑なクエリでもメモリ上で永続的に保存したりシャッフルしたり出来る
• ...というような仕組みが、BigQueryにおける驚異的な高速パフォーマンスを実現している

クエリ実行例：

タイトルを含むWikipediaの10億ページ分のビューログに対するSELECT文のクエリ実行例。

• 最初にクエリを多くのワーカーに送信、それぞれのワーカーがタイトル列を読み取り、一致しないデータをフィルタリング
• GROUP BY句で指示された部分的な集計を実施、タイトルのハッシュを使って結果をシャッフルに書き込む
• 次のステージは前ステージのシャッフルされた内容を読み込んで作成
• 最後のステージでは結果のタイトルを読み込んでアルファベットの降順でソートして格納：この操作はクライアントに結果をまとめるための単一のワーカーで行われる

SELECT文には必須のカラムのみを含める

• SELECT *の記法はコスト効率が悪く、パフォーマンスも低下する可能性がある。特に内部クエリでは、必要なカラムのみを選択すること。
• 返されるカラムの数が多い場合は、SELECT * EXCEPTを使用して不要なカラムを除外することを検討。

可能であれば『近似集計関数』を使用する

• 標準 SQL の近似集計関数  |  BigQuery  |  Google Cloud
• 使用しているSQL集約関数が等価な近似関数を持っている場合、近似関数の方がより高速なクエリ性能を得ることが出来る
  • 例：COUNT→ APPROX_COUNT_DISTINCT
• 近似関数は、一般的に正確な数値の1%以内に収まる結果を生成する

WHERE句を使用して大きなテーブルからのデータを早めにフィルタリングしておく

• WHERE句は特に結合内ではできるだけ早く実行されるべきであり、結合されるテーブルはできるだけ小さくする必要がある
• 標準SQLはフィルターを押し下げるために最善を尽くすので、WHERE句は必ずしも必要ではないかもしれない
  • クエリプランを見直して、できるだけ早くフィルタリングが行われているかどうかを確認し、条件を修正するか、サブクエリを使用して事前にフィルタリングを行うようにする
  • クエリプランとタイムライン  |  BigQuery  |  Google Cloud

JOINの効率性を改善させる

• JOINのタイプ - 標準 SQL クエリ構文  |  BigQuery  |  Google Cloud
• Self-Join(自己結合)
  • テーブル自身で結合
  • 大規模なテーブルではコストが高価になる可能性あり
    • 1回のパスでデータを取得しようとする
    • データを削減するために、より高いカーディナリティの列に結合する前に集計を行う
    • 従属するデータにウィンドウ分析関数（LEAD、LAG）を使用
• Broadcast Join
  • 左側の大きなテーブルが小さなテーブルと結合する場合
  • クエリ計算の改善  |  BigQuery  |  Google Cloud
  • BigQueryの内部処理について徹底解剖してみた| PLAID engineer blog
• Hash JOIN
  • 2つの大きなテーブルを結合
  • APPROX_TOP_COUNTを使用して不均衡な結合を監視
  • バランスの取れた結合上でクエリの結果をUNION

クエリプラン情報を利用したクエリ分析

• クエリプランとタイムライン  |  BigQuery  |  Google Cloud
• UI の「実行の詳細」の下を参照
• クエリが各段階で何をしているかを把握
• ステージ間の待ち時間の依存関係を探す
• 平均値と最大値の間の差異を探す

パーティショニングとクラスタリング

• パーティショニング
  • パーティション分割テーブルの概要  |  BigQuery  |  Google Cloud
  • カーディナリティの低い列のために小さなテーブルに分割されたデータ
  • クエリ実行前にストレージをフィルタリングし、データの読み込みを開始することでコストを削減
  • 指定されたパーティションへのテーブルのフル スキャンを削減

例)
・疑似列を使った時間分割
・ユーザー定義の日付・時間列を使った時間分割
　　・WHERE eventDate BETWEEN "2018-01-03" AND "2018-01-05"
・整数範囲を用いた分割
　　・WHERE regionCode BETWEEN 20 AND 29

• クラスタリング
  • クラスタ化テーブルの概要  |  BigQuery  |  Google Cloud
  • 高カーディナリティカラム用のカラムセグメント内のストレージを最適化し、フィルタリングとレコードのコロケーションを改善
  • より多様なタイプ（ネストされた列ではない）の最大4列までのクラスタリングに優先順位をつける
  • パーティショニングされたテーブルとパーティショニングされていないテーブルをサポート

例)
・クラスタ化されたテーブルを作成:
　　CLUSTER BY country, state, city
・クエリ述語を使う:
　　WHERE country = 'USA'
　　AND state = 'California'
　　AND city = 'Sunnyvale'

リアルタイムクエリのためのマテリアライズドビューとBIエンジン

• マテリアライズド・ビュー
  • 実体化されたビューの概要  |  BigQuery  |  Google Cloud
  • より高速で安価なクエリを実現するためのキャッシュ層
  • ユーザーはMVを定義し、BigQueryは自動的にMVSを維持して更新
  • クエリーは、該当する場合は自動的にMVにリダイレクトされる
  • データは自動的に新鮮な状態に保たれ、決して古くなることはない
• BIエンジン
  • BigQuery BI Engine の概要  |  Google Cloud
  • メモリ内キャッシュの最適化により、生データの問い合わせを回避
  • BigQuery UI内でのキャパシティの上下のスケーリング機能
  • アグリゲートリフレッシュ時間、キャッシュヒット率、クエリのレイテンシなどのメトリクスをStackdriver内で完全に可視化

データロード(取り込み)の最適化

• データの読み込みの概要  |  BigQuery  |  Google Cloud
• ストレージフォーマット:形式によって速度が異なる
  • [より早い]
  • Avro(データブロックで圧縮)
  • Avro
  • Parquet/ORC
  • CSV
  • JSON
  • CSV(圧縮)
  • JSON(圧縮)
  • [より遅い]
• バッチ処理
  • 様々なファイル形式を理解するバッチロードを使用して、GCSまたはHTTP POSTからインジェスト
  • DMLの変異には、大きなバッチサイズを使用します。
• ストリーミング
  • 1つまたは複数のソースからの継続的な摂取
  • リアルタイム情報の高速取り込み

例)
ストリーミングバッファ内のデータを _PARTITIONTIME IS NULL を使用して問い合わせる

スクリプトとストアドプロシージャでデータタスクを高速化

• スクリプト
  • データをBigQueryの外部に移動させることなく、BigQuery内でのデータクレンジングとELT操作を自動化
  • データ移行サービスと組み合わせて、データやクエリの移行を行う
• ストアドプロシージャ
  • 既存のデータ処理プロシージャをレガシーデータベースからBigQueryプロシージャに移行
  • ストアド・プロシージャを使用したBigQuery内のMLモデルのトレーニングと評価

まとめ

以上、Google Cloud Next '20のセッション「Best Practices from Experts to Maximize BigQuery Performance (featuring Twitter)」のレポート紹介でした。BigQueryは割とこの辺、インフラ側の方で良い感じに吸収してくれるのかなとも思いましたが、ユーザー側の方でも改善できるポイントが結構あるのだな、とも思いました。適切なクエリ記述・設定を行うことでスムーズな処理実行を出来るようにしていきたいですね！

• Google Cloud Next ’20: OnAir ｜ シリーズ ｜ Developers.IO