データ処理のプロセスにおいてBigQueryとBIツールの使い分けに関して、どの操作をどのツールで行うべきかを詳しく検討します。
BigQueryの得意領域
BigQueryは大規模なデータセットを高速に処理しクエリの柔軟性に優れています。
そのため、データ処理においてはできるだけ多くの操作をBigQueryで完了するのが望ましいです。
理想的なシナリオは、データの加工、変換、集計などの操作をBigQueryで行い、その結果をBIツールで簡単に可視化することです。
BIツールでの操作が適しているケース
BigQueryの方がデータ加工に優れているとはいえ、一部のケースではBIツールの方が効率的で、特定の操作に適しています。
以下に、BIツールでの利用が適切なシナリオをいくつか紹介します。
複数行を集約したときの掛け算・割算
例えば、商品（product_id）ごとの日次売上（sales）と費用（cost）のテーブルがあるとします。
| date | product_id | sales | cost |
|---|---|---|---|
| 2023-1-1 | 11111 | 10000 | 2000 |
| 2023-1-1 | 22222 | 30000 | 8000 |
| 2023-1-1 | 33333 | 50000 | 10000 |
| 2023-1-2 | 11111 | 5000 | 1000 |
最終的に日ごとの利益率{ = ( 売上 – 費用 )÷ 売上}を時系列グラフで表現したいとき、BigQueryで各行に対して利益率を計算してもBIツールで日ごとに集約すると１日当たりの利益率になりません。
| date | product_id | sales | cost | rate |
|---|---|---|---|---|
| 2023-1-1 | 11111 | 10000 | 4000 | 0.6 |
| 2023-1-1 | 22222 | 30000 | 9000 | 0.7 |
| 2023-1-1 | 33333 | 50000 | 10000 | 0.8 |
| 2023-1-2 | 11111 | 5000 | 3000 | 0.4 |
2023-1-1全体の利益率は74.4%ですが、rate列をBIツールで集約しても正しい利益率を表示できません。
BIツールでは、日次データを集約し、{( 売上 – 費用 )÷ 売上}を適用するだけで、正確な結果を取得できます。BigQueryで同様の結果を得るためには、複雑なクエリやウィンドウ関数を使用する必要があり、手間がかかります。
仕様変更の可能性がある文字列操作
例えば、ウェブサイトのアクセスログをBIツールで表示する際に、各ページをURLで分類して表示する設計だとします。
新しいページが追加され、ページ分類の仕様が変更される場合、BigQueryでデータを変更するのは手間がかかります。
BIツールに接続してからページ分類処理することにしておくことで、柔軟に仕様変更に対応できます。
分割テーブルも同様
Google Analytics4のログを日次で処理し分割（シャーディング）テーブルとしいる場合、後で仕様が変更された場合、すべての分割テーブルを操作するのは手間がかかるうえ、クエリ実行に関する費用が加算されます。
BIツールを使用すれば、接続後のデータソースを編集することで仕様変更できるため最小限のコストで対応できます。
BigQueryで行うべき操作
データ処理において、BigQueryは多くの操作に適していますが、特に以下の操作はBigQueryで実行することが望ましいです。
データソースの結合
データソースの結合はBIツールでも可能ですが、BigQueryは詳細な設定が可能であり、結合後のデータを確認しやすく、結合に関する問題があっても迅速に特定できます。
特に、LookerStudioなどの一部のBIツールでは、結合後のフィールドの編集が制限されていることがあるため、BigQueryで結合することが推奨されます。
他の行を参照する操作（ウィンドウ関数）
BIツールの操作にはウィンドウ関数が含まれていない場合が多いため、特にウィンドウ関数を使用する場合はBigQueryを活用すべきです。
ウィンドウ関数をBIツールで再現しようとすると、複雑な処理が必要になるため推奨されません。
取得したいデータの事前設計が重要
使い分けを判断するために、最終的にBIツールで可視化したいグラフや表のイメージを事前に検討し、必要なデータを加工するか検討ることが重要です。
データ処理の事前設計の際には、以下の2ステップで考えることが役立ちます。
- 要件定義: 目的と使用方法を明確にする
- 必要な分析軸と表示したい指標を特定する
- 「必要になりうる」範囲を広く取得する
事前設計時に気を付けること
事前設計の段階で、以下の点に注意してデータ処理中の予期せぬ問題を回避しましょう。
表記の一貫性が保たれているか
表記の一貫性がないと、グラフの表示やデータソースの結合が難しくなります。
特に、全角半角や大文字小文字の違いによって別のデータとして扱われることがあります。
表記ゆれがあるかどうかを事前に確認し、正規化処理をして問題を排除しましょう。
想定外の行の増加がないか
テーブルを結合する際に、結合キーが1:nの関係にあると行数が増加し、集計結果の値が予想外になることがあります。
各テーブルの構造や列の定義を確認しましょう。
まとめ
データ処理と可視化は、それぞれBigQueryとBIツールが得意とする分野があります。
特性を理解し、使い分けることで、効率的なデータ分析プロジェクトを実現できます。BigQueryは大規模なデータ処理に優れ、BIツールは柔軟な可視化を提供します。
BigQueryでデータ処理の際には、スケジュールクエリ機能を使ってテーブルとして出力し、定期的に新鮮な状態にしておくとコストパフォーマンスが高く運用できます。
Magiqを使うことで、クエリビルダから直接スケジュールクエリを作成し実行スケジュールを設定できるのでワンストップでBigQueryのデータを処理できます。