Google PDE: BigQuery による分析とウェアハウスエンジニアリング — 学習ガイド

By Laurynas Brazys · 21 August 2026 · #google #PDE #certification #exam-prep #study-guide

こちらの一部です： Google Professional Data Engineer — 学習ガイド. 検証済みの解答で練習： Google試験ハブ, または時間制限付き模擬試験に挑戦： ExamRoll.io.

概要

BigQueryは、ストレージとコンピューティングを分離した、サーバーレス、カラムナ、MPP（超並列処理）分析ウェアハウスであり、ほぼ無限のスケール、ANSI SQL、統合されたガバナンスを提供します。BigQueryでのウェアハウスエンジニアリングは、スキーマ設計（パーティショニング、クラスタリング、非正規化と正規化の比較、ネストされたレコード）、取り込みパターン（バッチロード、ストリーミング、Storage Write API）、およびワークロード管理（オンデマンド vs. 容量ベースのエディションと予約）のバランスを取ることが重要です。堅牢なセキュリティ（承認済みビュー、行/列レベルのポリシー、ポリシータグ）は、コスト管理およびパフォーマンスツールと共存し、スキャンされるバイト数を最小限に抑え、レイテンシを削減します。このセクションでは、本番環境で予測すべきコア設計、運用、および障害モードについて説明します。

ストレージとセマンティクス：データセット、テーブル、ビュー、レイクアクセス

• データセット、テーブル、ビュー：

  • データセットはIAMとガバナンスのスコープを定義します。分離と請求の明確化のために、テナントごとにデータセットを維持してください。
  • 標準テーブルはデータをネイティブに保存します。パーティションとクラスタリングがレイアウトとプルーニング（読み飛ばし）を管理します。
  • ビューはデータを保存せずにSQLロジックをカプセル化します。承認済みビューを使用すると、ビューの所有者は基になるテーブルを隠しながら、制限されたサブセットを他のプロジェクトやテナントに公開できます。
  • マテリアライズドビュー（MV）は、事前計算された結果を永続化し、自動的に更新します。クエリリライトは、互換性がある場合にMVを透過的に使用します。互換性のない述語や関数はそれらをバイパスします。
  • 外部テーブルは、Cloud Storage、Google Drive、またはGoogle Sheetsのデータを参照します。データの取り込みは不要ですが、利便性と引き換えにスループットと関数のサポートが制限されます。繰り返し分析する場合は、ネイティブテーブルに取り込むことを推奨します。

• パーティショニング、クラスタリング、ネストされたレコード：

  • 取り込み時間、DATE/TIMESTAMP/DATETIME、または整数範囲でパーティション分割して、スキャンをプルーニングします。パーティション列に対するWHEREフィルターや、_PARTITIONDATEのようなデコレータを使用することで、プルーニングが有効になります。
  • カーディナリティが高く、頻繁にフィルタリングまたは結合される列（最大8つ）でクラスタ化します。BigQueryは自動的に再クラスタリングを行いますが、小さなDMLを繰り返すと、クラスタリングの品質が一時的に低下する可能性があります。
  • ネストされたレコードと繰り返しレコード（STRUCT、ARRAY）は、結合のオーバーヘッドなしで1対多の関係をモデル化します。UNNESTは慎重に使用してください。大きな配列でUNNESTを繰り返すと、劇的にファンアウト（行数が爆発的に増加）する可能性があります。

• 非正規化 vs. 正規化：

  • 結合を最小限に抑え、カラムナスキャンを活用するために、ディメンション属性をファクトテーブルに非正規化します。これは読み取り負荷の高い分析に最適です。
  • 書き込み増幅、更新ホットスポット、または自己結合が競合や複雑さを引き起こす場合（例えば、自己結合による行数の爆発を避けるためにマスター患者テーブルと訪問テーブルを分離する場合など）は、正規化します。ハイブリッドアプローチも検討できます：正規化されたコアエンティティと、ワイドで非正規化されたファクトまたはネストされた子を持つ構成です。

• マテリアライズドビュー：設計上の考慮事項

  • パーティション化されたベーステーブルに対する、安定した増分集計に最適です。MVの更新は非同期です。ダウンストリームのユーザーは、ステイルネスウィンドウ（データの鮮度の遅れ）を許容するか、フォールバックとしてベーステーブルをクエリする必要があります。
  • 増分更新のためには、パーティション列でフィルタリングおよびグループ化します。非決定論的関数、サポートされていない結合、またはUDFは、MVをリライトの対象から除外する可能性があります。

• フェデレーションクエリ、BigLake、プッシュダウン：

  • フェデレーションクエリは、外部システム（例：Cloud SQL）をSQLで直接読み取ります。軽い結合や一回限りの探索には便利ですが、レイテンシが高く、クォータも厳しくなります。負荷の高い分析には、データをBigQueryに抽出してください。
  • BigLakeテーブルは、Cloud Storageやオープンテーブルフォーマット（Parquetなど）のデータに対する列レベルおよび行レベルの制御により、レイクとウェアハウスのガバナンスを統合します。述語と射影のプッシュダウンにより、ダウンロードされるバイト数が削減されますが、大規模なスキャンでは、パフォーマンスを最大化するために、依然としてネイティブテーブルへの取り込みが推奨されます。

• ワイルドカードテーブルとレガシーシャード：

  • ワイルドカードクエリは、日付でシャーディングされたテーブルに対するレガシーなパターンです。ネイティブパーティショニングの使用を推奨しますが、必要な場合は次のようにします：

undefined

WHERE _TABLE_SUFFIX >= ‘2010’。

クエリの最適化とワークロード管理

• パーティションプルーニングとクラスタリング:

  • コールドパーティションのスキャンを避けるため、常にパーティション列でフィルタリングします。狭い範囲のウィンドウでBETWEENを使用します。
  • クラスタリングキーを選択度の高い順に並べます。先頭のキーは、頻繁に使用されるフィルタや結合に一致させるべきです。カーディナリティが非常に低い列でのクラスタリングは避けてください。

• クエリプランの最適化:

  • EXPLAINと実行詳細を使用して、偏りのある結合、大規模なシャッフル、またはプルーニングされていないスキャンを見つけます。
  • SELECTリストやサブクエリで早期に列を減らします。BigQueryはカラム型であり、未使用の列を効率的に削除します。
  • 大規模データに対する速度とコストのトレードオフのために、近似集計（例: APPROX_QUANTILES）を優先します。
  • 取り込み元がイベントを繰り返す可能性がある場合は、ウィンドウ関数で重複排除を適用します。
    • SELECT * EXCEPT(rn) FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY event_ts DESC) rn FROM my_table t) WHERE rn = 1;

• 結合と非正規化:

  • シャッフルを減らすために、結合キーをクラスタリングキーとして同じ場所に配置します。極端な偏りには、ブルームフィルタや事前集計が役立ちます。
  • ホットジョインを避けるため、小さく、変化の遅いディメンションをファクトに非正規化します。頻繁に更新される非常にワイドなディメンションの場合は、正規化し、クラスタリングキーとマテリアライズド結合に依存します。

• ワークロード管理、スロット、エディション、オートスケーリング:

  • オンデマンド: BigQueryはクエリごとにコンピューティングを弾力的にスケーリングし、スキャンされたTB単位で支払います。課金される最大バイト数とパーティションプルーニングでコストを管理します。
  • BigQueryエディション（Standard、Enterprise、Enterprise Plus）による容量ベースでは、スロット予約を使用します。ベースラインのコミットメントを購入し、予約を作成し、プロジェクトやフォルダを割り当てます。オートスケーリングは、ピーク時にスロットを追加し、需要が減少したときに解放できます。ETLとBIの干渉を防ぐために、別々の予約を使用します。
  • ジョブの優先度: 低レイテンシにはインタラクティブ（デフォルト）、バックフィルやスケジュールされたクエリにはバッチ。バッチジョブは、予約またはサービスでアイドル容量が利用可能になるまでキューに入れられ、その後通常のコストで実行されます。

• 同時実行と割り当て:

  • 重要なワークロードを分離するために、予約と割り当てを使用します。混合テナントの場合は、個別の予約またはカスタマイズされた同時実行制限を持つプロジェクトに配置します。
  • 帰属分析のためにジョブにラベルを付けます。スロット使用率とキューイングの遅延については、INFORMATION_SCHEMA.JOBSとCloud Monitoringのメトリクスを監視します。

• BIのキャッシュと鮮度:

  • クエリ結果キャッシュは、同一クエリのレイテンシ/コストを改善します。1時間未満の鮮度が必要なクライアントでは無効にします。一部のBIツールは独自にデータをキャッシュするため、最新の結果を表示するにはレポートのキャッシュを無効にします。

取り込み、フェデレーション、リカバリ

• ロードジョブ:

  • Cloud Storageからのバッチロード（Avro/Parquet推奨）は、信頼性が高くコスト効率が良いです。スキーマとエンコーディングを明示的に設定します。CSVエンコーディングの不一致は、バイト単位の不一致の一般的な原因です。
  • マージを避けるために、パーティションデコレータを使用するか、パーティション分割テーブルにロードします。大規模なロードの場合は、パーティションごとに並列化します。

• ストリーミング取り込みとStorage Write API:

  • レガシーなストリーミング挿入はシンプルですが、より厳しい割り当てがあり、数秒間、結果整合性を示すことがあります。非常に最近のデータに対するタイムトラベルは遅れる可能性があります。
  • Storage Write APIは、より優れた重複排除制御を備えた高スループット、低レイテンシの書き込みに推奨される方法です。重複を防ぐために、べき等性（ストリームオフセット）を使用します。
  • アプリケーションの設計では、処理中のイベントを許容する必要があります。インタラクティブなクエリを予想される可用性（例: 観測されたレイテンシの2倍）だけ遅延させるか、取り込み時間パーティションにウォーターマークを使用します。

• Dataflowとデッドレターの設計:

  • 不正な行を含むパートナーから提供されたCSVの場合、Dataflowを使用して解析と検証を行い、有効なレコードをStorage Write API経由でBigQueryに書き込み、エラーを調査のためにデッドレターテーブルにルーティングします。
  • 大規模にBigQueryを読み取る場合、必要なフィールドのみを選択し、シャッフルを減らすために、クエリベースの読み取り（fromQuery）を優先します。

• スケジュールされたクエリと変換:

  • ELT変換、増分ロールアップ、テーブルメンテナンスには、スケジュールされたクエリを使用します。パーティション分割され、クラスタリングされたターゲットへの書き込みを優先します。スケジュールされたクエリはデフォルトでバッチ優先度になり、予約と統合されます。

• 通知と可観測性:

  • 特定のテーブル挿入ジョブでアラートをトリガーするために、ログシンクを使用してBigQuery監査ログをPub/Subにエクスポートします。
    • フィルタ例: resource.type=“bigquery_resource” AND protoPayload.methodName=“jobservice.jobCompleted” AND jsonPayload.jobChange.job.jobConfiguration.load.destinationTable.tableId=“target_table”
  • Cloud Loggingの監査ログとINFORMATION_SCHEMAビューを使用して、使用パターンを発見し、ガバナンスを強制します。

• タイムトラベル、スナップショット、クローン:

  • タイムトラベルを使用すると、以前のタイムスタンプ（デフォルト7日間）でテーブルをクエリできます。過去の状態を読み取るには、FOR SYSTEM_TIME AS OFを使用します。
  • テーブルスナップショットは、コピーオンライトで特定の時点のビューをキャプチャします。一貫性のあるバックフィルやリカバリに使用します。テーブルクローンは、開発やwhat-if分析のために、ほぼ瞬時にメタデータコピーを提供し、分岐するまで最小限のストレージで済みます。
  • リカバリの選択肢:
    • 小さな間違い: タイムトラベルを使用してクエリし、INSERT…SELECTで復元します。
    • 大規模な復元: スナップショットまたはクローンから作成し、その後スワップします。
  • 保持期間を強制するために、テーブルとパーティションの有効期限を設定します。保持期間がタイムトラベルの要件と一致していることを確認します。

• フェデレーションソース:

  • 軽い結合にはCloud SQLフェデレーションを使用します。持続的な分析や大規模なスキャンの場合は、ネイティブテーブルへの抽出・ロードをスケジュールします。
  • Cloud Storage上のParquet/ORCに対するBigLakeテーブルは、ポリシータグを強制し、フィルタや列の射影をプッシュダウンできます。それでも、ネイティブストレージよりも高いレイテンシが予想されます。

セキュリティ、ガバナンス、コスト管理

• IAMと最小権限:

  • 承認されたユーザーにのみ、データセットレベルのロール（BigQueryデータ閲覧者、データ編集者など）を最小限に付与します。APIアクセスはサービスアカウントと厳選されたグループに制限します。
  • クライアントと環境をデータセットとプロジェクトで分離し、アイソレーションを確保します。プロジェクトまたはフォルダごとに予約を割り当て、ノイジーネイバー（他のワークロードからの影響）を防ぎます。

• 承認済みビューと行レベルセキュリティ:

  • 承認済みビューは、外部プロジェクトに選択された列/行のみを公開し、ビューのプロジェクトはテーブルへのアクセスを保持します。ビューとソースは同じデータセットに保持するか、ターゲットプロジェクトへのデータセットレベルの承認を使用します。
  • 行レベルセキュリティは、行アクセスポリシーを使用してクエリ時にユーザーまたはグループごとに行をフィルタリングします。承認済みビューと組み合わせて、階層的な制御を実現します。

• 列レベルセキュリティとポリシータグ:

  • Data Catalogのポリシータグを使用して機密性の高い列を保護し、データマスキングを有効にします。データ分類に合わせて、テーブルではなくタグにアクセス権を割り当てます。パートナーアクセスの場合は、タグを介してPII（個人を特定できる情報）列をマスクまたは拒否します。

• BigQuery MLとインウェアハウス分析:

  • CREATE MODELとML.PREDICTを使用して、BigQuery内で直接モデル（例：線形/ロジスティック回帰、XGBoost、K-means、時系列）をトレーニングおよびサービングします。特徴量はパーティション分割テーブルに保存し、スケジュールされた再トレーニングを使用します。
  • リモートモデルを使用すると、SQLからVertex AIまたは外部エンドポイントを呼び出して、フェデレーションガバナンス内でスコアリングを実行できます。出力をテーブルにキャッシュして、繰り返しクエリのレイテンシを償却します。

• コスト管理とパフォーマンスのトラブルシューティング:

  • スキャンされるバイト数を削減する:
    • パーティション分割とクラスタリングを行い、常にこれらのキーでフィルタリングします。
    • 必要な列のみをSELECTし、SELECT *を避けます。
    • 該当する場合は、マテリアライズドビューと結果のキャッシュを使用します。
    • maximum_bytes_billedを設定してコストの上限を設けます。
  • パフォーマンスのトラブルシューティング:
    • ジョブの実行詳細を調べて、スキュー、プルーニングされていないパーティション、またはシャッフルのホットスポットがないか確認します。シャッフルを減らすために、結合キーを再設定するか、事前集計を行います。
    • MV（マテリアライズドビュー）の書き換えを検証します。互換性のある述語と決定論的な関数であることを確認します。
    • 新しいストリーミングデータが欠落しているダッシュボードについては、整合性の遅延を考慮するか、クライアント側のキャッシュを無効にします。
  • ガバナンスの可視性: Cloud Logging、INFORMATION_SCHEMA、および詳細なジョブラベルを使用して監査し、チャージバックや外れ値の特定を行います。

実践的な問題シナリオ

NovaCare Healthは、地域的な遠隔医療プラットフォームを運営しています。パイロット版では単一テーブルのpatient_and_visit設計で対応していましたが、規模が100倍になるとレポートがタイムアウトし、ストリーミングのupsertによって重複が発生し、パートナーからは厳格なデータ分離が要求されるようになりました。

アプローチ:

1. スキーマとレイアウトの再設計

   • 正規化されたコアテーブルを作成します: patients(patient_id, demographics, updated_at) と visits(visit_id, patient_id, visit_ts, metrics, updated_at)。
   • visitsテーブルをDATE(visit_ts)でパーティション分割し、patient_idとvisit_idでクラスタリングします。ダッシュボードの速度を上げるため、小さな参照ディメンションはvisitsに非正規化して保持します。
   • 理由: 正規化により、単一のホットなテーブルでの高コストな自己結合や重い行の更新を回避できます。パーティション分割は履歴データのスキャンをプルーニングし、クラスタリングはpatient_idでの結合とフィルタを同じ場所に配置することでシャッフルを削減します。

2. Storage Write APIによる取り込みとべき等性の強制

   • Dataflowパイプラインを使用して、受信イベントを解析、検証し、べき等なオフセットを持つ名前付きストリームでStorage Write APIに書き込みます。
   • 不正な形式のイベントは、トリアージ用のデッドレターBigQueryテーブルにルーティングします。
   • 理由: Storage Write APIは、従来のストリーミングよりも高いスループット、低いレイテンシ、および優れた重複排除の保証を提供します。デッドレタリングにより、パートナーのデータ品質問題に対する可視性が維持されます。

3. クエリにおける鮮度と重複排除のための設計

   • インタラクティブな分析では、最新のパーティションをクエリする前に短いウォーターマーク（例：観測された可用性の2倍）を追加するか、_PARTITIONDATEでフィルタリングして（where partition_date <= CURRENT_DATE()）、処理中の行を除外します。
   • 上流でのリトライを許容する必要があるビューでは、ROW_NUMBER() OVER (PARTITION BY visit_id ORDER BY event_ts DESC) = 1 を使用します。
   • 理由: ストリーミングは短い間、結果整合性です。ウォーターマークとウィンドウベースの重複排除は、一時的なデータの欠落や重複からダッシュボードを保護します。

4. マテリアライズドビューによる共通集計の高速化

   • DATE(visit_ts)と患者コホートでグループ化された日次KPIのために、visitsテーブル上にパーティションに合わせたMVを作成します。述語が書き換えと互換性があることを確認します。
   • 理由: MVは、定期的なレポートのレイテンシとスキャンされるバイト数を削減します。BigQueryはクエリを透過的に書き換えてMVを使用します。

5. テナントの分離と詳細なセキュリティの強制

   • 各パートナーを専用のデータセットに配置します。パートナーグループに最小権限のデータセットロールを付与します。
   • 生のテーブルを公開することなく、共有のクロスパートナーベンチマークのために承認済みビューを公開します。
   • PII列にポリシータグを適用し、visitsテーブルに行アクセスポリシーを追加して、内部のマルチテナント分析のためにpartner_idによるアクセスを制限します。
   • 理由: データセットごとのテナントセグメンテーションに加えて、承認済みビューとポリシータグを使用することで、厳選された共有を可能にしながら最小権限を強制します。

6. エディション、予約、オートスケーリングによるワークロード管理

   • BigQueryエディションでキャパシティを購入し、2つの予約を作成します: etl（Dataflowシンク、スケジュールされた変換）とbi（アドホック/レポーティング）。プロジェクトを適宜割り当て、オートスケーリングを有効にしてピークを吸収します。
   • ELTクエリを明確なSLAを持つバッチとしてスケジュールします。インタラクティブなプロジェクトにはmaximum_bytes_billedを設定します。
   • 理由: 予約を分離することで、ETLがBIを枯渇させるのを防ぎます。オートスケーリングは、過剰なプロビジョニングなしに突発的な負荷に対応します。

7. コストの統制と使用状況の監視

   • visit_tsでのフィルタを必須にし、共有ビューでのSELECT *を拒否します。INFORMATION_SCHEMA.JOBSを使用して、プルーニングされていないスキャンやスキューのある結合を検出します。
   • ログシンクを使用してBigQueryの監査ログをPub/Subにエクスポートし、visitsテーブルへのinsertジョブでフィルタリングして、予期せぬ急増に対する監視アラートをトリガーします。
   • 理由: バイトプルーニングと列の射影はコストを管理します。監査ログは、アクセスパターンと異常をほぼリアルタイムで明らかにします。

8. リカバリとバックフィルの計画

   • コンプライアンスとタイムトラベルのニーズに合わせたデフォルトのテーブル有効期限ポリシーを有効にします。大規模な編集の場合は、スナップショットを作成し、変更を実行し、問題があれば迅速にロールバックします。ストレージを複製することなく、開発/テストのwhat-if分析にはテーブルクローンを使用します。
   • 理由: スナップショットとクローンは、高速でスペース効率の良いセーフティネットを提供します。タイムトラベルは、小さな修正リストアをカバーします。

9. インウェアハウスMLの統合

   • 加工された特徴量をパーティション分割テーブルに保存し、再入院リスクのためのBigQuery ML分類モデルをトレーニングします。Vertex AIでホストされている外部モデルについては、リモートモデルを作成し、予測をクラスタ化テーブルにキャッシュして低レイテンシの結合を実現します。
   • 理由: MLをデータの近くに置くことで、データの移動とガバナンスの複雑さを軽減します。リモート推論をキャッシュすることで、レイテンシとコストを償却します。

この設計により、NovaCareは100倍の負荷下でも予測可能なパフォーマンス、強力なテナント分離、および統制されたコストを達成し、同時に低レイテンシの分析と再現可能なリカバリを維持します。

← データストレージ、データレイク、ファイル形式 · すべてのドメイン · Dataflow と Apache Beam によるストリーム処理 →

これらの問題を練習する → · ExamRoll.ioで時間制限付き練習 →

Googleを閲覧 →