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

しんや

2020.08.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

現在、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)』の内容について紹介していきたいと思います。

目次

　

セッション概要

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

Title(タイトル)：
Best Practices from Experts to Maximize BigQuery Performance (featuring Twitter)
(BigQueryのパフォーマンスを最大化するための専門家によるベストプラクティス feat.Twitter)

Speakers(講演者):
Jagan Athreya (Product Manager / Google Cloud)
Gary Steelman (Sr. Software Engineer / Twitter)

Description(説明):
You’ve made the decision to run your data analytics on BigQuery’s serverless platform. As you deploy complex workloads on your data, you want to maximize the performance of all data operations from data loading to data analytics. Join this session to learn the performance best practices from speeding up your data ingest into BigQuery to learning the tips and tricks from the BigQuery engineering team to maximize query performance of your data warehouse.
(データ分析をBigQueryのサーバーレスプラットフォームで実行することを決定しました。データに複雑なワークロードを展開する際には、データの読み込みからデータ分析まで、すべてのデータ操作のパフォーマンスを最大化したいと考えています。このセッションに参加して、BigQueryへのデータインジェストの高速化から、データウェアハウスのクエリパフォーマンスを最大化するためのBigQueryエンジニアリングチームからのヒントやコツまで、パフォーマンスのベストプラクティスを学びましょう。)

　

セッションレポート

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

　

各種事例の紹介

  • Stotify
    • Spotify Customers Google Cloud
    • ストリーミング音楽サービスの中心は「データ」：毎日500TB以上のデータがロードされている/BigQueryの規模は数百PB級
    • 何百万人ものユーザーのリスニング習慣に基づいて曲やプレイリストを推薦
    • レガシープラットフォームで16分掛かっていた分析クエリが、BigQueryでは33秒に短縮
  • HSBC
  • UPS

　

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句は必ずしも必要ではないかもしれない

　

JOINの効率性を改善させる

　

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

　

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

  • パーティショニング 
例)
・疑似列を使った時間分割
・ユーザー定義の日付・時間列を使った時間分割
　　・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は割とこの辺、インフラ側の方で良い感じに吸収してくれるのかなとも思いましたが、ユーザー側の方でも改善できるポイントが結構あるのだな、とも思いました。適切なクエリ記述・設定を行うことでスムーズな処理実行を出来るようにしていきたいですね！

AWS

関連記事

【CDI】Informatica Cloud Data Integrationで、ソースのスキーマを自動的に読み込めるという「スキーマ変更処理」を検証してみた

渡部晃季

2023.05.02