データ ウェアハウス使用者のための BigQuery

更新日: 2022 年 4 月

このドキュメントでは、データ ウェアハウスとしてBigQuery を使用する方法を説明します。一般的なデータ ウェアハウスのコンセプトと BigQuery のコンセプトをマッピングし、BigQuery で標準的なデータ ウェアハウジングのタスクの実行方法を説明します。このドキュメントは、データ ウェアハウスとビッグデータ システムを管理するユーザーが対象です。

サービスモデルの比較

次の表は、データ ウェアハウスの標準的なコンセプトと BigQuery のコンセプトとの対応付けを示したものです。

データ ウェアハウス BigQuery
データ ウェアハウス BigQuery サービスは、従来のデータ ウェアハウス向けの一般的なハードウェア設定に代わるものです。組織内のすべての分析データを集約するホームとして機能します。
データマート データセットは、事業分野や指定された分析領域によって分割できるテーブルの集合です。データセットはそれぞれ、Google Cloud プロジェクトに関連付けられています。
データレイク データレイクには、Cloud Storage または Google ドライブ内のファイルや、Bigtable または Cloud SQL 内のトランザクション データが含まれている場合があります。BigQuery では、スキーマを定義し、フェデレーション データソースとして外部データに対して直接クエリを発行できます。BigQuery Storage API は高帯域幅の並列読み取りが可能で、SparkPandas などの一般的な処理フレームワークと互換性があります。

BigQuery リソース

BigQuery には階層構造があります。そのレベルを次の図に示します。

プロジェクト A: データセット(テーブル、ビュー、スクリプト / 関数を含む)。プロジェクト B: ジョブ(クエリ、読み込み、コピー、エクスポートを含む)。

プロジェクト

Google Cloud のリソースを割り当てて使用するには、リソースがプロジェクトに含まれている必要があります。 プロジェクトは、Google Cloud で構築する対象をまとめて管理するエンティティです。BigQuery から見れば、プロジェクトはすべての BigQuery リソースのコンテナです。BigQuery ではストレージとコンピューティングが分離されているため、データを保存するプロジェクトとクエリを実行するプロジェクトが異なる可能性があります。

データセット

データセットは、BigQuery のテーブルとビューを体系化するために使用する最上位のコンテナです。データセットは、標準のリレーショナル データベースとデータ ウェアハウスのスキーマによくマッピングされます。

データセットは Cloud プロジェクトにスコープを設定されます。SQL クエリまたはコードで、コマンドラインからテーブルを参照する場合は、次のように参照します。

project.dataset.table

データセットはロケーションにバインドされています。データセットのロケーションは次のとおりです。

  • リージョン: ロンドンなどの特定のロケーション。
  • マルチリージョン: 米国などの、複数の地理的場所を含む広範囲な地理的領域。

データセットのロケーションは、作成時にのみ設定できます。クエリには、同じロケーションにあるさまざまなデータセットのテーブルまたはビューを含めることができます。

これらの複数のスコープ(プロジェクト、データセット、テーブル、ロケーション)を使用すると、情報を論理的かつ地理的に構造化する際に有用です。

BigQuery のテーブルは、データを保持する行と列の構造です。すべてのテーブルは、列名、データ型、その他の情報を記述するスキーマによって定義されます。テーブルのスキーマは作成時に指定できます。または、スキーマなしでテーブルを作成し、クエリジョブまたは読み込みジョブでスキーマを指定して、テーブルにデータを入力します。BigQuery には次のタイプのテーブルがあります。

  • ネイティブ テーブル: ネイティブ BigQuery ストレージでサポートされるテーブル。
  • 外部テーブル: BigQuery の外部にあるストレージでサポートされるテーブル。
  • ビュー: SQL クエリによって定義される仮想テーブル。

詳細については、ストレージ管理をご覧ください。

ジョブ

ジョブとは、データの読み込み、データのエクスポート、データのクエリ、データのコピーなど、BigQuery がユーザーに代わって実行するアクションのことです。ジョブは、データが保存されているプロジェクトと同じプロジェクトにリンクされません。ただし、ジョブを実行できるロケーションはデータセットのロケーションにリンクされています。たとえば、Cloud Storage バケットからシンガポールにある BigQuery データセットにデータを読み込む場合、Cloud Storage バケットはシンガポールまたはアジアを含むロケーション(アジアのマルチ リージョンなど)に存在する必要があります。逆に、データセットが欧州連合のマルチリージョンにある場合は、米国のマルチリージョンなど、EU 以外のロケーションからクエリを実行できません。これにより、データの局所性に関する要件を確実に満たすことができます。

プロビジョニングとシステムサイズ設定

多くの RDBMS システムとは異なり、BigQuery を使用する前にリソースをプロビジョニングする必要はありません。BigQuery は、使用パターンを基準に、ストレージ リソースとクエリリソースを動的に割り当てます。

  • ストレージ リソースはリソースの消費量に応じて割り当てられ、データまたはテーブルを削除したときに割り当てが解除されます。
  • クエリリソースはクエリのタイプと複雑さの度合いに応じて割り当てられます。クエリごとに複数のスロットが使用されます。スロットとは、一定量の CPU と RAM で構成される計算能力の単位です。

BigQuery を使用する際、最小使用量のコミットメントを設定する必要はありません。このサービスでは、実際の使用量に基づいてリソースが割り当てられ、課金されます。すべてのお客様にデフォルトで 2,000 スロットがクエリ オペレーション用として割り当てられます。また、プロジェクトのスロット予約を行うこともできます。使用方法の詳細については、費用をご覧ください。

ストレージ管理

BigQuery の内部では、Capacitor という独自のカラム型でデータが保存されます。Capacitor には、データ ウェアハウスのワークロードに対する多くの利点があります。BigQuery が独自の形式を使用する理由は、データ レイアウトに関する深い知識を活用してクエリの実行を最適化するクエリエンジンと連携して発展できる可能性があるためです。BigQuery はクエリ アクセス パターンを使用して、物理シャードの最適な数とそのエンコード方法を決定します。

データは、Google の分散ファイル システム(Colossus)に物理的に保存されます。Colossus では、消失訂正符号を使用して複数の物理ディスクに冗長なデータのチャンクを保存することで、耐久性を確保しています。また、データは複数のデータセンターに複製されます。

フェデレーション データソースを使用すると、BigQuery ストレージ外部のデータ(Cloud Storage、Google ドライブ、Bigtable に保存されたデータなど)に BigQuery クエリを実行することもできます。ただし、これらのソースは BigQuery オペレーション向けに最適化されていないため、BigQuery ストレージに保存されているデータと同じように動作しない可能性があります。

メンテナンス

BigQuery はフルマネージド サービスです。つまり、BigQuery エンジニアリング チームが更新やメンテナンスを行います。通常、アップグレード時にダウンタイムは発生せず、システム パフォーマンスの低下もありません。

多くの従来型のシステムでは、データブロックの入れ替えや並べ替えと容量の復元のために、リソース使用量の高いバキューム プロセスを、さまざまな間隔で実行する必要があります。BigQuery の場合、ストレージ エンジンがデータの保存と複製の方法を継続的に管理して最適化するため、バキュームまたはインデックス管理に相当するものはありません。また、BigQuery はテーブルのインデックスを使用しないため、インデックスを再構築する必要がありません。

バックアップと復元

データベース管理者にとって、バックアップと可用性の管理は複雑でコストがかかるタスクです。ライセンスやハードウェアの追加が必要になると、コストが大幅に増える可能性があります。BigQuery では、サービスレベルで、バックアップと障害復旧が行われます。BigQuery では、テーブルに対する変更について 7 日間の完全な履歴を保持することで、テーブル デコレータまたはFROM内のSYSTEM_TIME AS OFのいずれかを使用して、データの特定の時点のスナップショットをクエリできます。バックアップからの復元をリクエストする必要とせず、変更を簡単に元に戻すことができます(テーブルが明示的に削除されると、その履歴は 7 日後にフラッシュされます)。また、cp コマンドにより、リージョン内のテーブルのスナップショットが即座に提供されます。

BigQuery データセットは、リージョンまたはマルチリージョンになります。リージョン データセット(us-central1 リージョンに配置されているデータセットなど)の場合、データセットのコピーはリージョン外で維持されません。1 つのリージョン以外でのバックアップがビジネスにとってリスクが高いと思われる場合は、BigQuery Data Transfer Service を使用してリージョン間のコピーを作成し、スケジュールを設定できます。ヨーロッパ(EU)などの大規模な地理的領域に配置されているマルチリージョン データセットの場合、コピーは自動的に別の Google Cloud リージョンに保存されます。

リージョンに障害が発生すると、一部の直近データがなくなる可能性があります。詳細については、可用性と耐久性 に関する BigQuery のドキュメントをご覧ください。

ワークフローの管理

このセクションでは、データセットの整理、権限の付与、BigQuery でのオンボーディング作業など、管理タスクについて説明します。また、同時ワークロードの管理方法、データ ウェアハウスの状態のモニタリング方法、ユーザー アクセスの監査方法についても説明します。

データセットの整理

データセットは、データのクラスや事業単位に基づいて別々のプロジェクトに分割したり、共通のプロジェクトに統合したりすることでシンプルにできます。

データ アナリストを招待して限定的なロールを付与すると、既存データセットの共同作業をすることができます。データ アナリストが コンソール にログインすると、データ アナリストには、プロジェクト間で共有されている BigQuery リソースのみが表示されます。データ アナリストがデータセットに対して実行できるアクティビティは、データセットに対するロールによって異なります。

権限の付与

従来の RDBMS システムでは、テーブルを表示または変更する権限の付与は、SQL 付与を作成し、これをデータベース システム内の所定のユーザーに適用することによって行います。さらに、一部の RDBMS システムでは、LDAP などの外部ディレクトリのユーザーにも権限を付与することができます。BigQuery でのユーザーと権限を管理するモデルは、後者のモデルに似ています。

Cloud Identity は、Google Cloud に組み込まれた中央 ID プロバイダであり、ユーザー認証を可能にします。これは Identity and Access Management(IAM)の一部です。認証に加えて、IAM では、BigQuery やそのデータセットに対する具体的な権限が付与された ID を承認するための集中管理が提供されます。アクセスを制御するには、事前定義ロールまたはカスタムロールを作成します。人間以外のユーザーが BigQuery リソースにアクセスするには、サービス アカウントを作成し、必要なロールを割り当てます。このアプローチの一例は、スケジュールされたデータ読み込みスクリプトへのアクセス権を付与することです。

データ ウェアハウスを運用するうえで重要なことは、異なるユーザーのグループに同じデータの共有を許可しつつ、その同じデータに対するアクセス権を制御することです。たとえば、財務部門、人事部門、マーケティング部門はすべて同じテーブルにアクセスしますが、アクセスレベルは異なります。従来のデータ ウェアハウス ツールの場合は、行レベルのセキュリティを適用することで、これを実現します。BigQuery の場合は、承認済みビュー行レベルの権限を定義することで、同じ結果を得ることができます。 特定の列に機密データがあるテーブルの場合は、データポリシー タグを IAM ロールとともに使用して、列レベルのセキュリティを適用できます。 データ ガバナンスの詳細については、データ ウェアハウスの BigQuery への移行: データ ガバナンスをご覧ください。

オンボーディング

従来のデータ ウェアハウスを使うと、新しいデータ アナリストのオンボーディングには、かなりのリードタイムが必要でした。アナリストが簡単なクエリを実行できるようにするには、データソースがどこにあるかを示し、ODBC の接続、ツール、アクセス権を設定する必要がありました。Google Cloud を使用すると、アナリストが生産にかける時間を大幅に短縮させられます。

Google Cloud でアナリストをオンボーディングするには、関連するプロジェクトへのアクセスを許可して Google Cloud Console に招待し、データの把握に役立つクエリを共有します。

コンソール では、Google Cloud 環境内のすべてのアセットを一元管理できます。データ アナリストに最も関連性の高いアセットは、ファイルを共同編集できる Cloud Storage バケットでしょう。 コンソールの BigQuery ページには、アナリストがアクセスできるデータセットのリストが表示されます。アナリストは、メタデータの表示、データのプレビュー、クエリの実行、保存、共有など、付与されたロールに応じたタスクをコンソールで実行できます。

データ検出は、企業にとって、新規ユーザーと経験豊富なユーザーのオンボーディングにとって大きな懸念事項です。必要なデータが見つかることが重要です。同様に、機密データを保護してデータへのアクセスを承認することが重要です。Data Catalog を使用して、メタデータ検索やデータ損失防止などの機能を自動的に提供できます。データ検出の詳細については、データ検出をご覧ください。

ワークロードと同時実行の管理

このセクションでは、ワークロードの管理、同時実行が可能なクエリ数、ジョブ スケジューリングに利用できる制御について説明します。

サービス割り当て

サービス割り当ては、BigQuery の使用時に一貫したサービス品質を維持するために使用され、BigQuery 割り当てポリシーに記載されています。各割り当て上限には、すべてのコンシューマのデフォルト値があります。たとえば、同時実行クエリの最大数を 100 にデフォルトで設定できます。この数値を変更する必要がある場合は、割り当てオーバーライドを行います。

カスタム割り当て

複数の BigQuery プロジェクトとユーザーが存在している場合、1 日に処理されるクエリデータの量に対する上限を指定するカスタム割り当てを要求することで、コストを管理できます。毎日の割り当ては、午前 0 時(太平洋時間)にリセットされます。

クエリの優先順位付けとスケジューリング

BigQuery には、インタラクティブとバッチの 2 種類のクエリ優先度があります。デフォルトで、BigQuery はインタラクティブ クエリを実行します。つまり、クエリはすぐに実行されます。インタラクティブ クエリは、同時実行レート上限割り当てに対してカウントされます。バッチクエリはキューに入れられ、アイドル状態のリソースが使用可能になると(通常は数分以内に)実行されます。24 時間以内にクエリが開始されなかった場合、BigQuery はジョブの優先度をインタラクティブに変更します。バッチクエリは同時実行のレート上限割り当てにカウントされません。

BigQuery では、同時クエリでプロジェクトや予約に使用できるスロットよりも多くのスロットを使用できる場合は、フェア スケジューリング アルゴリズムを実装します。BigQuery が動作する速度と規模を考えると、従来のワークロードの問題(ワークロードごとに異なるキューを維持するなど)の多くは該当しません。明確なクエリの優先順位付けが必要な場合は、機密性の高いワークロードを、個別の予約を持つプロジェクトに分割できます。

監視と監査

Monitoring を使用して BigQuery をモニタリングできます。Monitoring では、BigQuery の指標に基づいてさまざまなグラフやアラートが定義されます。たとえば、Query Time 指標を使用してシステムのスループットをモニタリングすることや、Slots Allocated 指標に基づいてクエリの需要の傾向を可視化できます。要求の厳しいクエリに対して事前に計画する必要がある場合は、Slots Available 指標を使用できます。システムの健全性を予測して対策を行うには、お客様が定義するしきい値に基づいてアラートを作成します。Monitoring には、セルフサービスのウェブベース ポータルが用意されています。

BigQuery はユーザー アクションの監査ログを自動的に作成します。監査ログを別の BigQuery データセットに一括で、またはデータ ストリームとしてエクスポートし、分析ツールを使用してログを可視化できます。詳細については、BigQuery を使用した監査ログの分析をご覧ください。

BigQuery には、データセット、テーブル、ジョブなどの BigQuery リソースのメタデータにアクセスするために使用できる INFORMATION_SCHEMA 読み取り専用ビューも用意されています。これらのビューは、テーブルの有効期限の追跡クエリのスロット使用率の把握など、さまざまな目的に使用できます。

データの管理

このセクションでは、スキーマ設計の考慮事項、パーティショニングとクラスタリングの仕組み、BigQuery にデータを読み込む方法について説明します。このセクションの最後では、分析ダウンタイムをゼロに抑えながら行うウェアハウスでの変更の処理について説明します。

スキーマの設計

BigQuery では、データを読み込むときや空のテーブルを作成するときにテーブルのスキーマを指定できます。BigQuery では、整数などの単純な型とARRAYSTRUCT などのより複雑な型を含む、標準 SQL のデータ型がサポートされます。

BigQuery は、スタースキーマスノーフレーク スキーマに基づく従来のデータモデルをサポートしています。これらのモデルでは、ファクト テーブルがディメンション テーブルと結合されます。BigQuery は、INNER[FULL|RIGHT|LEFT] OUTER、および CROSS JOIN 演算子もサポートしています。

場合によっては、データを非正規化するために、ネスト フィールドと繰り返しフィールドを使用する必要が生じることがあります。これを行うには、スキーマを定義するために ARRAYSTRUCT のデータ型の組み合わせを使用できます。

テーブルのパーティショニング

パーティション分割テーブルは、パーティション 列の値に基づいてセグメントに分割されます。クエリによってパーティション列のフィルタを指定すると、対応するセグメントのみがスキャンされます。このように配置することで、クエリの実行が高速化され、クエリの実行コストが削減されます。BigQuery テーブルは、次の方法で分割できます。

  • 取り込み時間: BigQuery は、データの取り込み時間または受信時間を反映した、日付に基づく日単位のパーティションにデータを自動的に読み込みます。
  • 列ベースのパーティショニング: テーブルが指定された列の値に基づいてパーティション分割されます。列で使用できるデータ型は次のとおりです。
    • 時間単位の列パーティション: テーブルは、DATE 列、DATETIME 列、TIMESTAMP 列でパーティション分割できます。
      • DATE: パーティションは日単位、月単位、年単位で作成できます。
      • TIMESTAMP DATETIME: HOUR を含む、任意の時間単位の粒度のパーティションを許可します。
    • 整数範囲: テーブルは整数列に基づいて分割されます。

テーブルの作成プロセス中にパーティショニングを有効にします。さらに、パーティション内のデータの有効期限を指定できます。パーティション分割テーブルに挿入される新しいデータは、挿入時にRAWパーティションに書き込まれます。データを読み込むパーティションを制御するには、読み込みジョブで特定のパーティションを指定できます。

テーブルのクラスタリング

クラスタ化テーブルは、指定された 1 つ以上の列に基づいて編成されます。BigQuery は、パーティション分割テーブルと分割されていないテーブルの両方で、クラスタリングをサポートします。クラスタリングにより、テーブル セグメントはクラスタリング フィールドで並べ替えられたブロックに分割されます。クラスタ化列でデータをフィルタリングするクエリの場合、スキャンされるデータの量が減り、クエリのパフォーマンスが向上します。スキャンされるデータの量は、ランタイム時にしか判断できないため、クエリの正確な実行コストは事前にわかりません。

BigQuery では、新しく挿入されたデータがバックグラウンドで自動的に再クラスタリングされます。自動再クラスタリングはクエリの容量や価格には影響しません。

次のフローチャートは、パーティショニング、クラスタリング、テーブルでのパーティショニングとクラスタリングに最適なユースケースの概要を示しています。

次の表でオプションが繰り返されるフローチャート。

前述のフローチャートは、次のオプションの概要を示しています。

使用例 推奨
オンデマンド料金を選択していて、クエリを実行する前に厳密な費用保証が必要な場合。 分割テーブル
テーブルのパーティショニング後のセグメントのサイズは 1 GB 未満です。 クラスタ化テーブル
BigQuery の上限を超える多数のパーティションが必要です。 クラスタ化テーブル
データでミューテーションが頻繁に発生すると、多数のパーティションが変更されます。 クラスタ化テーブル
クエリを頻繁に実行して、特定の固定列のデータをフィルタリングします。 パーティションに加えてクラスタリング

マテリアライズド ビュー

マテリアライズド ビューは事前に計算されたビューであり、パフォーマンスと効率の向上を目的としてクエリの結果を定期的にキャッシュに保存します。BigQuery では、マテリアライズド ビューは常にベーステーブル(BigQuery ストリーミング テーブルを含む)の内容と一致します。マテリアライズド ビューは、データ ウェアハウスにサマリー表を作成する場合に役立ちます。

地理空間データ

多くの場合、データ ウェアハウスには位置情報が含まれています。この種のデータは、より効率的なサプライ チェーン ロジスティクス システムの提供から、風力タービンのハリケーン発電所におけるハリケーンの計画まで、さまざまな目的で使用できます。 地理空間分析(地理情報システム)を使用すると、BigQuery で標準の SQL 地理関数を使用して地理空間データを分析および可視化できます。BigQuery は、GEOGRAPHYデータ型を変換します。これにより、GeoJSONWell-Known Binary(WKB)Well-Known Text(WKT)形式で空間データをロードすることができます。BigQuery には、GIS データを解析、変換、操作できるいくつかの地理関数もあります。地理空間データの操作の詳細については、地理空間分析の操作をご覧ください。

データの読み込み

BigQuery には、データを読み込むモードとバッチモードの両方が用意されています。また、BigQuery Data Transfer Service を使用して、特定の SaaS アプリケーションからデータを直接インポートすることもできます。バッチ読み込みを使用すると、追加料金なしでクエリのパフォーマンスに影響を与えることなく、大量のデータを読み込めます。データをリアルタイムで利用可能にする必要がある不正行為検出用の変更データの読み込みなどのユースケースでは、BigQuery にデータをストリーミングできます。

バッチ読み込み

バッチ読み込みの場合、データファイルは Cloud Storage バケットでステージングされ、読み込みジョブを使用して BigQuery テーブルにインポートされます。BigQuery は、CSV、JSON、AvroORCParquet などの多数のオープン形式をサポートしてます。また、BigQuery には、DatastoreFirestore のサポートも組み込まれています。

BigQuery では、プロジェクトごと、テーブルごとに実行できる読み込みジョブの数とサイズに 1 日あたりの上限が設定されます。また、個々の読み込みファイルとレコードのサイズにも上限が設定されています。詳細については、割り当てと上限をご覧ください。

Console から読み込みジョブを起動できます。このプロセスを自動化するには、特定のバケットへの新しいファイルの到着に関連付けられた Cloud Storage イベントをリッスンするように Cloud Functions を設定し、BigQuery 読み込みジョブを起動します。データ パイプラインは、多くの場合、データ ウェアハウスの外部で実行される抽出、変換、読み込み(ETL)手順を実行するために使用されます。次の図は、パイプライン内のイベントのフローを示しています。

BigQuery の ETL 手順

ETL プロシージャの代わりに、抽出、読み込み、変換(ELT)の手順を使用することもできます。次の図に示すように、ELT 手順では、データが最初にデータ ウェアハウスに読み込まれ、SQL オペレーションを使用して目的のスキーマに変換されます。

BigQuery の ELT パイプライン.

Dataflow で実行される ETL パイプラインを使用し、Apache Beam SDK で提供されている BigQuery I/O コネクタ を利用することで、自動的にデータを BigQuery に読み込むことができます。また、Apache Spark で構築されたパイプラインを使用し、Spark BigQuery コネクタを利用することで、自動的にデータを BigQuery に読み込むこともできます。

ストリーミング挿入

BigQuery テーブルにデータをストリーミングする場合は、BigQuery API を使用して BigQuery にレコードを直接送信します。Cloud Logging を使用する場合は、App Engine からのリクエストログや Cloud Logging に送信されるカスタムログ情報などの Cloud プロジェクト ログを BigQuery に直接ストリーミングすることもできます。

また、Apache Beam SDK で提供される BigQuery I/O コネクタの STREAMING_INSERTS 方式を使用した Pub/Sub および Apache Kafka などの Dataflow で稼働するパイプラインを使いメッセージング システムからイベントデータをストリーミングすることもできます。

企業では、多くの Google Cloud サービスを使い始めるにつれて、BigtableCloud SQL または Cloud Spanner にソースデータを直接キャプチャすることがよく選ばれます。また、Dataflow を使用してデータの抽出、変換、読み込みが、BigQuery にバッチまたはストリームで行われます。次の図は、Dataflow を使用した、バッチ ELT パイプラインとストリーム ELT パイプラインの設定方法を示しています。

Dataflow を使用して設定されたバッチ ELT パイプラインとストリーム ELT パイプライン

SaaS アプリケーションからインポートする

BigQuery Data Transfer Service では、Google 広告、キャンペーン マネージャー、Google アド マネージャー、YouTube などの Google アプリケーションのソースからデータをインポートできます。また、Amazon S3 などの外部データソースTeradataAmazon Redshift などのデータ ウェアハウスにも対応しています。 Google Cloud Marketplace の他のシステムに、パートナーが提供するコネクタを使用することもできます。

変更の処理

多くのデータ ウェアハウスは、厳密なサービスレベル契約(SLA)のもとで運用されており、ダウンタイムはほとんど発生しません。BigQuery サービスは 稼働時間が 99.99% の SLA を保持していますが、データの変更を反映する方法でデータセットの可用性と応答性が制御されます。

BigQuery でのすべてのテーブル変更(DML オペレーション、宛先テーブルを含むクエリ、読み込みジョブなど)は ACID に準拠しています。したがって、テーブルを変更してもダウンタイムは発生しません。ただし、新しく更新されたデータを分析で利用できるようにする前に、内部プロセスでテストと検証のフェーズが必要になることがあります。また、分析データベースでは DML オペレーションの効率が悪いため、DML をバッチ処理することをおすすめします。データの変更を処理するには、よく知られている手法のほとんどを適用できます。このセクションでは、既知の課題とソリューションについて説明します。

スライディング タイム ウィンドウ

従来のデータ ウェアハウスでは、データレイクとは異なり、データを一定期間(たとえば過去 5 年間)保持します。更新サイクルごとに、新しいデータがウェアハウスに追加され、最も古いデータが破棄され、期間が固定されます。一般に、このコンセプトは古いテクノロジーの制限を回避するために利用されていました。

BigQuery はスケーリングされるように構築されています。ウェアハウスのサイズ拡大にあわせてスケールアウトできるため、古いデータを削除する必要はありません。すべての履歴を保持することで、ビジネスの詳細な分析が可能になります。ストレージのコストが懸念される場合は、古いデータをアーカイブし、必要に応じて特別な分析に使用することで、BigQuery の長期保存料金を利用できます。古いデータを削除する正当な理由がある場合は、日付分割テーブルパーティションの有効期限の BigQuery の組み込みサポートを使用できます。 この場合、BigQuery が古いデータを自動的に削除できるようになります。

スキーマの変更

データ ウェアハウスは設計されて、開発されますが、通常は列を追加、更新、削除するか、テーブル全体を追加または削除してテーブル スキーマを調整します。変更が追加された列またはテーブルの形式でない限り、削除されたテーブル、名前が変更された列、その他の関連要素を参照する保存クエリやレポートは機能しません。

データ ウェアハウスが本番環境に移行した後、そのような変更は厳密な変更管理を経ます。ほとんどの場合、スキーマの変更はバージョンのアップグレードとしてスケジュールされます。以前のバージョンのデータ ウェアハウスが分析ワークロードを処理している間に、並行してアップグレードの設計、開発、テストを行います。BigQuery データ ウェアハウスにスキーマの変更を適用する場合も、同じアプローチに従います。

変化が緩やかなディメンション

正規化されたデータスキーマは、ディメンション テーブルで変更を分離することで、変化が緩やかなディメンション(SCD)の影響を最小限に抑えます。一般に、SCD がフラット ファクト テーブルを広範囲に更新するのは、非正規化スキーマより正規化スキーマの方が適しています。

緩やかに変化するディメンションのすべてのケースで、一般的なソリューションはありません。変更の性質を理解し、最も関連性の高いソリューションまたはソリューションの組み合わせを、自身の問題に適用することが重要です。このセクションの残りの部分では、いくつかのソリューションと、それらを SCD タイプに適用する方法について説明します。

SCD タイプ 1: 上書き

タイプ 1 の SCD は、履歴を保持せずに属性の値を新しいデータで上書きします。このアプローチは、ディメンション テーブルが稼働中のプライマリ テーブルをミラーリングする場合に特に便利です。たとえば、「すばらしい保湿クリーム」という商品が「健康と美容」のカテゴリに含まれていましたが、新たに「化粧品」に分類された場合、次のようになります。

変更前:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC すばらしい保湿クリーム - 100 オンス 健康と美容

変更後:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC すばらしい保湿クリーム - 100 オンス 健康と美容
化粧品

属性が正規化されたディメンション テーブルにある場合、変更は独立しています。ディメンション テーブルの該当する行を、単に更新するだけです。

特定の行に対する変更の頻度が低い場合は、UPDATE DML ステートメントを使用できます。

update mydataset.dimension_table set PRD_CATEGORY="cosmetics" where PRD_SK="123"

ディメンションを稼働中のプライマリ テーブルと定期的に同期しなければならない場合もあります。一般的なパターンでは、ダンプをオペレーショナル データベースから BigQuery ディメンション テーブルに定期的にマージします。新しいデータを一時テーブルに読み込むか、そのデータにポイントする外部テーブルを作成できます。

ディメンション テーブル:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC すばらしい保湿クリーム - 100 オンス 健康と美容
124 PQR すばらしい化粧水 - 50 オンス 健康と美容

一時テーブル

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC すばらしい保湿クリーム - 100 オンス 化粧品
124 PQR すばらしい化粧水 - 50 オンス 化粧品
125 XYZ 究極の T シャツ - XL 衣類

これで、ディメンション テーブルを更新するマージクエリを実行でき、一時テーブルを削除できます。

MERGE my-dataset.dimension_table as MAIN using
my-dataset.temporary_table as TEMP
on MAIN.PRD_SK = TEMP.PRD_SK
when matched then
UPDATE SET
MAIN.PRD_CATEGORY = TEMP.PRD_CATEGORY
when not matched then
INSERT VALUES(TEMP.PRD_SK, TEMP. PRD_ID, TEMP. PRD_SK, TEMP.
PRD_CATEGORY)

結果ディメンション テーブル:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC すばらしい保湿クリーム - 100 オンス 健康と美容
化粧品
124 PQR すばらしい化粧水 - 50 オンス 健康と美容
化粧品
125 XYZ 究極の T シャツ - XL 衣類

SCD タイプ 2: 行履歴の保持

この方法では、個別のサロゲートキーがある特定の自然キーに複数のレコードを作成して、無制限の過去のデータをトラッキングします。たとえば、SCD タイプ 1 で説明した同じ変更が、次のように処理されます。

変更前:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC すばらしい保湿クリーム - 100 オンス 健康と美容 2009 年 1 月 31 日 NULL

変更後:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC すばらしい保湿クリーム - 100 オンス 健康と美容 2009 年 1 月 31 日 2017 年 7 月 18 日
124 ABC すばらしい保湿クリーム - 100 オンス 化粧品 2017 年 7 月 19 日 NULL

このテーブルの上部にビューまたはマテリアライズド ビューを作成して、分析クエリで使用できます。

create view products_current as
select PRD_SK, PRD_ID, PRD_DESC, PRD_CATEGORY, PRD_START_DATE
from my-dataset.dimension_table
where END_DATE IS NULL

属性が非正規化された方法でファクト テーブルに埋め込まれている場合は、明示的な値の開始日と終了日を保持せず、代わりにトランザクション日に依存する場合は、より望ましい状況になる場合があります。以前のトランザクションが発生した日時については、以前の値が保持されているため、以前のファクト テーブルの行を変更する必要はありません。ファクト テーブルは次のように表示されます。

TRANSACTION_DATE PRD_SK PRD_ID PRD_DESC PRD_CATEGORY UNITS AMOUNT
2017 年 7 月 18 日 123 ABC すばらしい保湿クリーム - 100 オンス 健康と美容 2 25.16
2017 年 7 月 19 日 124 ABC すばらしい保湿クリーム - 100 オンス 化粧品 1 13.50

SCD タイプ 3: 列の追加による履歴の保持

この方法では、個別の列を使用して制限付きの過去のデータをトラッキングして、制限付きの履歴を保持します。BigQuery はネスト フィールドと繰り返しフィールドに対応しているので、START_DATE 値で昇順に配列型を使用して、同じ列に履歴を保持できます。SCD タイプ 2 と同様に、テーブルの上部にビューまたはマテリアライズド ビューを作成することで、クエリが容易になります。

ベーステーブル:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC すばらしい保湿クリーム - 100 オンス CATEGORY_NAME START_DATE END_DATE
健康と美容 2009 年 1 月 31 日 2017 年 7 月 18 日
化粧品 2017 年 7 月 18 日 NULL

ビューを作成して、PRD_CATEGORY 配列の最後の製品カテゴリの名前を選択します。

create view my-dataset.products_current as
select PRD_SK, PRD_ID, PRD_DESC,
PRD_CATEGORY.ordinal[array_length(PRD_CATEGORY)] as PRD_CAT
from my-dataset.dimension_table;

表示:

PRD_SK PRD_ID PRD_DESC PRD_CAT
123 ABC すばらしい保湿クリーム - 100 オンス 化粧品

ほぼリアルタイムのレプリケーション

運用データベースの更新されたデータをほぼリアルタイムで分析可能にする必要がある場合は、変更データ キャプチャ(CDC)を使用した BigQuery へのデータベース レプリケーションを使用できます。

データのクエリ

BigQuery は標準 SQL クエリをサポートし、ANSI SQL 2011 と互換性があります。BigQuery の SQL リファレンスには、サポート対象のすべての関数、演算子、正規表現機能についてまとめられています。

BigQuery は、データモデルの一部としてネスト フィールドと繰り返しフィールドをサポートするため、BigQuery の SQL サポートは、これらのフィールド タイプを特にサポートするように拡張されました。たとえば、GitHub 公開データセットを使用すると、繰り返しフィールドを反復する UNNEST コマンドを発行できます。

SELECT
  name, count(1) as num_repos
FROM
  `bigquery-public-data.github_repos.languages`, UNNEST(language)
GROUP BY name
ORDER BY num_repos
DESC limit 10

インタラクティブ クエリ

コンソールを使用すると、データセットの対話型クエリが可能になり、アクセス可能なプロジェクト間でデータセットの統合ビューを利用できます。このコンソールには、アドホック クエリの保存と共有、過去のクエリの調整と編集、テーブルとスキーマの確認、テーブル メタデータの収集などの有用な機能も用意されています。詳細については、コンソールの使用をご覧ください。

ユーザー定義の関数

BigQuery では、SQL ステートメントで関数を記述することが現実的でないクエリについてはユーザー定義関数(UDF)もサポートされます。UDF を使用すると、組み込みの SQL 関数を拡張できます。この関数は、値のリスト(ARRAY 型または STRUCT 型)を受け取り、単一の値(ARRAY 型または STRUCT 型)を返します。UDF は標準 SQL と JavaScript で記述できます。JavaScript UDF では、暗号化や他のライブラリなどの外部リソースを含めることができます。標準 SQL UDF は JavaScript UDF よりもパフォーマンスが高いため、使用することをおすすめします。Google Cloud プロフェッショナル サービスチームが構築および保守する一般的な UDF の例については、bigquery-utils GitHub ページをご覧ください。

マルチクエリ ステートメントとストアド プロシージャ

多くの場合、企業のユーザーはデータ ウェアハウス内で複雑なロジックを実行します。BigQuery のプロシージャ言語を使用すると、変数と制御ステートメントを含む標準 SQL 複数ステートメント クエリを作成できます。BigQuery データ ウェアハウス内で複数ステートメント クエリを実行できます。

ストアド プロシージャを使用すると、マルチステートメント クエリを保存できます。ビューと同様に、ストアド プロシージャを組織内の他のユーザーと共有して、ストアド プロシージャの 1 つの正規バージョンを維持することもできます。マルチステートメントのクエリとストアド プロシージャのサンプルについては、bigquery-utils GitHub ページをご覧ください。

自動化されたクエリ

一般的な手法として、スケジュールやイベントに基づいてクエリの実行を自動化し、後で使用するために結果をキャッシュに保存することもできます。BigQuery のスケジュール設定されたクエリを使用すると、データ定義言語(DDL)データ操作言語(DML)のステートメントを定期的に実行できます。

Cloud Storage バケットからの読み込みジョブの自動化などの単純なオーケストレーションでは、Cloud Storage トリガーを使用して、BigQuery ジョブを行う Cloud Functions を実行できます。スケジュール設定されたジョブの場合は、Cloud Scheduler から Cloud Functions をトリガーできます。より複雑なワークフローの場合は、Cloud Composer を使用します。Airflow BigQuery 演算子で、他の自動アクティビティをオーケストレーションできます。

BigQuery Storage API

通常、企業には BigQuery から大量のデータを読み取る必要があるパイプラインがあります。BigQuery Storage API を使用すると、シリアル化された構造化データの並列ストリームを読み取ることができます。このアプローチは、ページ分けされた行の読み取りのパフォーマンス上限を over 服し、Cloud Storage にデータをエクスポートする際のオーバーヘッドの回避に役立ちます。

Apache Beam または Apache Spark を使用して構築された既存のパイプラインでは、BigQuery Storage API をほとんど追加設定の必要なしで使用できます。

クエリの最適化

クエリ実行後のパフォーマンス特性を理解するには、クエリプランの説明をご覧ください。この説明は、クエリが進んだステージ、各ステージで処理される入出力行の数、各ステージ内のタイミング プロファイルに分類されます。この説明から得られる結果の活用は、クエリの理解と最適化に役立ちます。

データスキャンを削減する

BigQuery はインデックスの使用やサポートをしません。クエリを実行するたびに、列全体のスキャンが実行されます。BigQuery のパフォーマンスとクエリコストは、クエリ中にスキャンされたデータ量に基づくため、クエリに関連する列のみを参照するようにクエリを設計することをおすすめします。パーティション分割テーブルを使用する場合は、関連するパーティションのみをスキャンします。パーティション列に基づくフィルタを使用することで、不要なスキャンを回避できます。特定の列で頻繁にフィルタリングするクエリがある場合は、テーブルのクラスタリングを検討します。さらなる処理のために集計クエリを頻繁に実行する必要がある場合は、クエリを実体化することを検討してください。この方法により、コンピューティング要件とスキャンするデータの量が削減されます。

コンピューティング要件を削減する

JavaScript のユーザー定義関数の使用を避けることをおすすめします。可能であれば、標準 SQL UDF を使用します。クエリを高速化するもう 1 つの方法は、COUNT(DISTINCT) の代わりに APPROX_COUNT_DISTINCT などの近似集計を使用することです。

結合のパフォーマンスを向上させる

特にデータ ウェアハウスにスタースキーマやスノーフレーク スキーマがある場合、企業は複数のテーブルを結合する必要があります。ファクト テーブルは通常、ディメンション テーブルよりも大きくなります。スノーフレーク スキーマでは、ディメンションが正規化されているため、ディメンション テーブルがさらに小さくなる場合があります。左側のファクト テーブルをまず開始し、右側の小さいディメンション テーブルとサイズの降順で結合することをおすすめします。大きいテーブルを JOIN の左側に配置し、小さいテーブルを JOIN の右側に配置した場合は、ブロードキャスト結合が形成されます。ブロードキャスト結合は、小さいテーブル内のすべてのデータを大きいテーブルを処理する各スロットに送信します。

詳細については、データ ウェアハウスの BigQuery への移行: パフォーマンスの最適化をご覧ください。

外部ソース

小規模で頻繁に変化するオペレーション テーブルを BigQuery テーブルと結合しなければならないユースケースの場合、BigQuery ではCloud BigtableおよびCloud SQLなどの外部データソースがサポートされます。 この方法により、データが更新されるたびに再読み込みする必要が確実になくなります。

BigQuery は、Avro、Parquet、ORC などのさまざまな形式のデータのクエリをサポートしているため、データを変換して、Google ドライブまたは Cloud Storage から BigQuery にワンパスで読み込むために使用できます。デフォルトの Hive パーティション レイアウトに従った BigQuery から Cloud Storage の既存のデータレイクのデータをクエリすることもできます。たとえば、企業のデータレイク内のテーブルは、次の Hive パーティショニング パターンを使用して Parquet 形式で Cloud Storage バケットに保存されます。

gs://my_bucket/my_table/{dt:DATE}/{val:STRING}

クエリを行うには、ユーザーは、Hive パーティショニング パターンを使用して BigQuery で外部テーブルを作成できます。ユーザーがこのテーブルでクエリを実行すると、BigQuery によって Hive パーティション スキーマが履行され、スキャンされるデータが削減されます。

データを移動せずにすべてのクエリを BigQuery に移行できるため、データ ウェアハウスを BigQuery に段階的に移行する場合に役立ちます。

BigQuery の外部データソースについての詳細は、外部データソースの概要をご覧ください。

クエリの共有

BigQuery を使用すると、共同編集者はチームメンバー間でクエリを保存して共有できます。この機能は、データ探索の演習や、新しいデータセットまたはクエリパターンを試すときなどに、特に有用です。詳細については、クエリの保存と共有をご覧ください。

データの分析

このセクションでは、BigQuery に接続してデータを分析するさまざまな方法について説明します。BigQuery を分析エンジンとして最大限に活用するには、データを BigQuery ストレージに保存する必要があります。ただし、特定のユースケースでは、外部ソースを単独で分析することや、BigQuery ストレージのデータと JOIN して分析できる場合があります。

既製のツール

Google データポータルLooker、および BigQuery に統合されている多くのパートナー ツールを使用して、BigQuery から分析を引き出すほか、高度な対話型のデータ可視化を構築できます。スプレッドシートのインターフェースに精通している場合は、コネクテッド シートを使用して、スプレッドシートから BigQuery のデータへのアクセス、分析、可視化、共有が可能です。

ツールを自分で選択する必要がある場合は、Gartner のマジック クアドラント レポートと G2 クラウドによる G2 スコアレポートで包括的なベンダー比較をご覧ください。Gartner のレポートは、Tableau などの多くのパートナー サイトから取得できます。

カスタム開発

BigQuery 上でカスタム アプリケーションとプラットフォームを構築するには、一般的なプログラミング言語で使用できるクライアント ライブラリを使用することも、BigQuery REST API を直接使用することもできます。例については、Python ライブラリを使用して BigQuery に接続し、カスタム インタラクティブ ダッシュボードを生成する Bokeh と BigQuery によるカスタム インタラクティブ ダッシュボードの作成をご覧ください。

サードパーティ製コネクタ

API レベルで BigQuery とネイティブに統合されていないアプリケーションから BigQuery に接続する場合、BigQuery JDBC ドライバと ODBC ドライバを使用できます。ドライバは、従来のアプリケーションや、Microsoft Excel などの簡単に変更できないアプリケーションのために、BigQuery とやり取りするためのブリッジを提供します。ODBC と JDBC は、SQL を使用した BigQuery とのやり取りはサポートしますが、このドライバは API を直接処理するほどの表現力がありません。

料金

ほとんどのデータ ウェアハウスは、組織内の複数のビジネス エンティティに対応しています。一般的な課題は、ビジネス エンティティごとに運用コストを分析することです。請求の分割と使用量へのコストの割り当てについては、BigQuery とデータポータルを使用して Google Cloud の課金を可視化するをご覧ください。

BigQuery には、読み込みコスト、ストレージ コスト、クエリコストの主要な 3 つのコストの要素があります。BigQuery データセットを所有するプロジェクトには、毎月、標準的なストレージの料金が請求されます。クエリまたは読み込みを開始したプロジェクトには、コンピューティングのコストが課金されます。このセクションでは、それぞれの特徴について詳しく説明します。

データの保存

ストレージの料金は、Mbps あたりで案分されます。

テーブルが 90 日間連続して編集されていない場合、テーブルは長期ストレージとして分類され、そのテーブルのストレージの料金は自動的に 50% 値引きされ、1 か月あたりの料金は 1 GB あたり $0.01 になります。テーブルを長期にわたって保存していても、パフォーマンス、耐久性、可用性などの各種機能性が損なわれることはありません。

テーブル内のデータが変更されると、BigQuery はテーブルのタイマーをリセットし、テーブル内のデータはすべて通常のストレージ料金に戻ります。ビューのクエリや作成など、データを直接操作しないアクションでは、タイマーはリセットされません。パーティション分割テーブルの場合、個々のパーティション セグメントに同じモデルが適用されます。

詳細については、BigQuery のストレージの料金をご覧ください。

データの読み込み

従来の読み込みジョブを無料で使用して、データを BigQuery に読み込むことができます。データが読み込まれると、前のセクションで説明したようにストレージの料金が発生します。

ストリーミング挿入は、ストリーミングされるデータの量に基づいて課金されます。詳細については、BigQuery ストレージの料金に記載されているストリーミング挿入の料金をご覧ください。

データのクエリ

クエリに関しては、BigQuery にはオンデマンドと予約を使用する定額の 2 種類の料金モデルが用意されています。

オンデマンド料金

オンデマンド モデルでは、BigQuery はクエリの実行中にアクセスされるデータの量に対して課金します。BigQuery はカラム型ストレージ形式を使用するため、クエリに関係する列のみがアクセスされます。レポートを週単位または月単位でのみ実行し、1 TB 未満のデータに対してクエリを実行した場合は、請求書のクエリのコストが非常に少額になります。

クエリの課金の仕組みについての詳細は、BigQuery クエリの料金をご覧ください。

既定のクエリでスキャンするデータの量を事前に判断するには、コンソールでクエリ バリデータを使用します。カスタム開発の場合は、API リクエストで dryRun フラグを設定し、BigQuery でジョブを実行しないようにできます。代わりに、ジョブに関する統計情報(処理されたバイト数など)を返します。詳細については、クエリ API をご覧ください。

BigQuery Reservations

毎月の費用を一定にしたい場合は、BigQuery Reservations を通じて定額料金を有効にできます。このオプションでは、組織に特定数の BigQuery スロットの容量コミットメントを購入し、特定のプロジェクトに割り当てることができます。

月間コミットメントか年間コミットメントにすることができます。また、最短 60 秒間の追加スロットを購入できる Flex Slots コミットメントにすることもできます。スロットの購入後、予約と呼ばれる別のバケットにスロットを割り当てることができます。予約では、名前をつけたスロットの割り当てが作成されます。購入したスロットを使用するには、プロジェクト、フォルダ、組織を予約に割り当てます。リソース階層内の各レベルでは、この設定をオーバーライドしない限り、上位のレベルから割り当てが引き継がれます。

BigQuery Reservations を使用して、予約を追加作成し、それらの予約にプロジェクトを割り当てることで、commit された容量をワークロード、チーム、部門間で分離できます。

次の図の最初の例のシナリオでは、データ サイエンス(DS)とビジネス インテリジェンス(BI)の 2 種類のワークロードに 1,000 スロットが必要です。2 つ目のシナリオでは、ELT ジョブを 1 時間ごとに 15 分間実行するのに 1,000 個のスロットが必要です。

スロット予約の例。

DS ジョブと BI ジョブの最初のシナリオでは、次のようにコミットメントと予約を使用します。

  • 1,000 スロットの月単位または年単位のコミットメントを作成します。
  • DS 500 スロットの予約を作成し、関連するすべての Google Cloud プロジェクトを DS の予約に割り当てます。
  • 500 スロットの BI 予約を作成し、BI ツールに接続されているプロジェクトを BI 予約に割り当てます。

ELT ジョブの 2 番目のシナリオでは、コミットメントと予約を次のように使用します。

  • 1,000 スロットの Flex Slots のスロットの予約を作成します。
  • 1,000 スロットを使用して ELT の予約を作成し、関連するプロジェクトを ELT の予約に割り当てます。
  • ELT ジョブが完了したら、割り当て、ELT 予約、コミットメントを削除します。

次のステップ