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

最終更新日: 2017 年 9 月

この記事では、BigQuery をデータ ウェアハウスとして使用する方法を説明します。まず、一般的なデータ ウェアハウスのコンセプトを BigQuery のコンセプトに対応付けして、次に BigQuery で標準的なデータ ウェアハウジングのタスクをどのように行うかについて説明します。

サービスモデルの比較

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

データ ウェアハウス BigQuery
データ ウェアハウス BigQuery サービスは、従来のデータ ウェアハウス向けの一般的なハードウェア設定に代わるもので、組織内のすべての分析データを集約するホームとして機能します。
データマート データセットは、事業分野や指定された分析領域によって分割できるテーブルの集合です。データセットはそれぞれ、Google Cloud プロジェクトに関連付けられています。
データレイク データレイクには、Cloud Storage または Google ドライブ内のファイルや、Cloud Bigtable 内のトランザクション データが含まれている場合があります。BigQuery では、スキーマを定義し、フェデレーション データソースとして外部データに対して直接クエリを発行できます。
テーブルとビュー テーブルとビューは、BigQuery でも従来のデータ ウェアハウスと同様に機能します。
付与 BigQuery で特定のアクションを実行する権限を付与するには、Identity and Access Management(IAM)を使用します。

データセット

BigQuery は、データテーブルをデータセットと呼ばれる単位で整理します。このデータセットは、Google Cloud プロジェクトを対象範囲とします。コマンドライン、SQL クエリ、またはコードでテーブルを参照する場合は、次の構文を使用します。

project.dataset.table

プロジェクト、データセット、テーブルという複数の範囲を対象とすると、情報を論理的に構造化するのに役立ちます。複数のデータセットを使用して、それぞれの分析領域に関連するテーブルに分けることができます。また、プロジェクト レベルで範囲を設定し、ビジネスニーズに応じてデータセットを分割することも可能です。

BigQuery の構造の概要は次のとおりです。

BigQuery の構造の概要

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

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

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

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

ストレージ管理

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

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

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

メンテナンス

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

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

バックアップと復元

BigQuery は、バックアップと障害復旧をサービスレベルで行います。また、テーブルに対する変更について、7 日間の完全な履歴を保持することで、BigQuery では、FROMテーブル デコレータまたは SYSTEM_TIME AS OF を使用して、データの特定の時点のスナップショットをクエリできます。バックアップからの復元をリクエストせずに、変更を簡単に元に戻すことができます(テーブルが明示的に削除されると、その履歴は 7 日後にフラッシュされます)。

ワークフローの管理

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

データセットの整理

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

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

権限の付与

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

BigQuery には、リソースへのアクセス権を制御するための事前定義ロールが用意されています。定義済みの権限セットで構成されるカスタムの IAM ロールを作成し、ユーザーまたはグループに割り当てることもできます。ロールは、Google のメールアドレスまたは Google Workspace グループに割り当てることができます。

データ ウェアハウスを運用するうえで重要なことは、異なるユーザーのグループに同じデータの共有を許可しつつ、その同じデータに対するアクセス権を制御することです。たとえば、財務部門、人事部門、マーケティング部門はすべて同じテーブルにアクセスしますが、アクセスレベルは異なります。従来のデータ ウェアハウス ツールの場合は、行レベルのセキュリティを適用することで、これを実現します。BigQuery の場合は、承認済みビュー行レベルの権限を定義することで、同じ結果を得ることができます。

オンボーディング

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

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

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

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

BigQuery では、受信するリクエストの最大レートに上限が設定され、プロジェクト単位で適切な割り当てが適用されます。具体的なポリシーは、リソースの可用性、ユーザー プロファイル、サービス使用履歴などの要因によって異なります。詳細については、BigQuery の割り当てポリシーをご覧ください。

BigQuery には、インタラクティブとバッチの 2 種類のクエリ優先度があります。デフォルトで、BigQuery はインタラクティブ クエリを実行します。つまり、クエリはすぐに実行されます。インタラクティブ クエリはクエリ割り当てにカウントされます。バッチクエリはキューに入れられ、アイドル状態のリソースが使用可能になると直ちに(通常は数分以内に)実行されます。

BigQuery では、インタラクティブ クエリやバッチクエリの細かい優先順位付けはサポートされていません。BigQuery が動作する速度と規模を考えると、従来のワークロードの問題の多くは該当しません。明確なクエリの優先順位付けが必要な場合は、機密性の高いワークロードを、明確な数の予約スロットを持つプロジェクトに分割できます。定額料金でのご利用を検討されている場合は、Google の担当者にお問い合わせください。

監視と監査

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

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

データの管理

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

スキーマの設計

BigQuery に最適なスキーマを設計するには、次の一般的なガイドラインに従ってください。

  • データ操作、UPDATE および DELETE 処理、コストの重要性が、最適なクエリのメリットを上回っているという確かな根拠がある場合を除き、10 GB を超えるディメンション テーブルを非正規化する。
  • テーブルで UPDATE 処理と DELETE 処理をほとんど経由しない場合を除き、10 GB 未満のディメンション テーブルを正規化する。
  • 非正規化されたテーブルでは、ネストされたフィールドや繰り返しフィールドを活用する。

非正規化

データを非正規化する従来の方法では、ファクトをそのすべてのディメンションとともに、フラットなテーブル構造に書き込みます。たとえば、販売取引の場合は、各ファクトをオーダーや顧客情報などの関連するディメンションとともに、レコードに書き込みます。

対照的に、データを非正規化する望ましい方法は、JSON または Avro 入力データのネスト構造と繰り返し構造に対する BigQuery のネイティブ サポートを利用することです。ネスト構造と繰り返し構造を使用してレコードを表記すると、基盤データをより自然な表現で提供できます。販売注文の場合、JSON 構造の外側の部分には注文と顧客情報が含まれ、JSON 構造の内側の部分には注文の個別の項目が含まれ、ネストされた要素と繰り返し要素として表されます。

{
  "orderID": "ORDER",
  "custID": "EMAIL",
  "custName": "NAME",
  "timestamp": "TIME",
  "location": "LOCATION",
  "purchasedItems": [
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    },
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    }
  ]
}

ネスト フィールドと繰り返しフィールドを使用してレコードを表現すると、JSON ファイルまたは Avro ファイルを使用したデータ読み込みが簡素化されます。このようなスキーマを作成した後、ドット表記(Order.Item.SKU など)を使用して個別のフィールドで SELECTINSERTUPDATEDELETE の処理を実行できます。例については、BigQuery のドキュメントをご覧ください。

非正規化のメリット

本質的に、BigQuery は分析エンジンです。DML アクションをサポートしていますが、オンライン トランザクション処理(OLTP)ストアとして使用するものではありません。データの変更についての説明では、分析ダウンタイム ゼロを維持し、最適なオンライン分析処理(OLAP)を実現しつつ、変更を処理するためのガイドラインを示しています。スタースキーマやスノーフレークなどの正規化されたデータ構造や部分的に正規化されたデータ構造は、更新処理や削除処理には適している一方、OLAP ワークロードには最適ではありません。正規化されたテーブルで OLAP オペレーションを実行する場合、必要な集計を実行するには複数のテーブルを JOIN する必要があります。BigQuery では JOIN が可能なため、小さなテーブルで使用することが推奨される場合もあります。ただし、通常は非正規化された構造ほどパフォーマンスは高くありません。

次のグラフは、JOIN と単純なフィルタを使用したクエリのパフォーマンスとテーブルのサイズとの関係を比較したものです。JOIN を使用したことで、クエリのパフォーマンスが、急激に悪化していることを示しています。

JOIN とフィルタを使用したクエリ時間の比較

非正規化のデメリット

非正規化スキーマはストレージに最適ではありませんが、BigQuery の低コストのストレージは、ストレージの非効率性に関する懸念に対応しています。クエリ速度の向上とコストを比較して、ストレージが重要な要因ではない理由を確認できます。

非正規化スキーマを使用する場合の課題の 1 つは、データの整合性を維持することです。変更の頻度とその範囲によっては、データの整合性を維持するために、テストと検証のためにマシンの稼働時間と、場合によっては人の対応時間が増加することがあります。

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

BigQuery は日付別によるテーブルのパーティショニングをサポートしています。テーブルの作成プロセス中にパーティショニングを有効にします。BigQuery は、新しい日付ベースのパーティションを自動的に作成します。追加のメンテナンスは必要ありません。さらに、パーティション内のデータの有効期限を指定できます。

パーティション分割テーブルに挿入される新しいデータは、挿入時に生のパーティションに書き込まれます。データを読み込むパーティションを明示的に制御するには、読み込みジョブで特定の日付パーティションを指定します。

データの読み込み

データは、分析ワークロードを行うために BigQuery に読み込む前に、通常は読み込み元にネイティブな形式で Cloud Storage プロダクトに保存されます。Google Cloud への移行の初期段階で、既存の抽出、変換、読み込み(ETL)ツールを使用して、データを BigQuery の理想的なスキーマに変換することが一般的なパターンです。変換したデータは、CSV、JSON、Avro ファイルとして Cloud Storage に転送し、そこから読み込みジョブまたはストリーミングを使用して BigQuery に読み込みます。または、既存のオンプレミス データ ストレージにネイティブなスキーマで Cloud Storage に転送し、BigQuery の一連のステージング テーブルに読み込みます。その後 BigQuery SQL コマンドを使用して BigQuery の理想的なスキーマに変換します。この 2 つのアプローチを図で表すと以下のようになります。

データを読み込む 1 番目の方法

データを読み込む 2 番目の方法

Google Cloud でフットプリントを拡張する際には、Cloud BigtableDatastore、または Cloud Spanner でソースデータを直接キャプチャし、Dataflow を使用して BigQuery にバッチまたはストリームでデータを ETL する方法が考えられます。

ソースコードを直接キャプチャする

読み込みジョブの使用

このセクションでは、データがサポート対象のファイル形式で、ファイルのコレクションとして Cloud Storage 内にあることを前提としています。各データ形式と、形式の選択において考慮すべき特定の要件と機能の詳細については、BigQuery のデータ形式をご覧ください。

CSV に加えて、--field_delimiter フラグを使用してカンマ以外の区切り文字でデータファイルを使用することもできます。詳細については、bq 読み込みフラグをご覧ください。

BigQuery は、gzip 圧縮ファイルの読み込みをサポートしています。ただし、圧縮ファイルの読み込みは、圧縮されていないファイルの読み込みほど高速ではありません。時間が問題となるシナリオや、Cloud Storage への非圧縮ファイルの転送に帯域幅や時間の制約があるシナリオの場合、簡単な読み込みテストを実施して、最適な方法を見つけてください。

読み込みジョブは非同期であるため、ジョブの実行中はクライアント接続を維持する必要はありません。また、読み込みジョブが他の BigQuery リソースに影響しないことも重要です。

宛先テーブルが存在しない場合、読み込みジョブによって宛先テーブルが作成されます。

BigQuery は、以下のようにしてデータスキーマを指定します。

  • 自己記述型である Avro 形式のデータの場合、BigQuery がスキーマを直接指定できます。
  • データが JSON 形式または CSV 形式の場合、BigQuery はスキーマを自動検出できますが、手動検証をおすすめします。

スキーマを引数として読み込みジョブへ渡すことで、スキーマを明示的に指定できます。進行中の読み込みジョブは、初期読み込みと同じプロシージャを使用して同じテーブルに追加できますが、ジョブごとにスキーマを渡す必要はありません。

CSV ファイルに、初期読み込みとテーブル作成後に無視する必要があるヘッダー行が常に含まれている場合は、--skip_leading_rows フラグを使用してその行を無視できます。詳細については、bq 読み込みフラグをご覧ください。

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

BigQuery ウェブ UI から読み込みジョブを起動できます。このプロセスを自動化するには、特定のバケットへの新しいファイルの到着に関連付けられた Cloud Storage イベントをリッスンするように Cloud Functions を設定し、BigQuery 読み込みジョブを起動します。

ストリーミング挿入の使用

別の補完的なアプローチとして、データを BigQuery に直接ストリーミングすることもできます。ストリーミングされたデータはすぐに利用可能になり、既存のテーブルデータと一緒にリアルタイムでクエリの実行が可能です。

不正の検出やシステム指標のモニタリングなど、リアルタイムの情報が有力になるような状況では、ストリーミングは重要な差別化要因となりえます。ただし、BigQuery の無料の読み込みジョブとは異なり、データのストリーミングには料金がかかります。そのため、費用を上回るメリットがある状況でストリーミングを使用することが重要です。

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

変更の処理

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

BigQuery でのテーブル変更はすべて ACID に準拠しています。これは、DML オペレーション、宛先テーブルを含むクエリ、読み込みジョブに適用されます。ユーザークエリの提供中に挿入、更新、削除を行うテーブルは、同時実行を正常に処理し、ある状態から次の状態にアトミックな方法で移行します。したがって、テーブルを変更してもダウンタイムは発生しません。ただし、新しく更新されたデータを分析で利用できるようにする前に、内部プロセスでテストと検証のフェーズが必要になることがあります。また、DML オペレーションはスロットを介して分析ワークロードと競合するため、それらを分離することが好ましい場合があります。これらの理由から、ダウンタイムが発生する可能性があります。この記事では、「分析ダウンタイム」という用語を使用して、BigQuery サービスのダウンタイムとの混同を避けます。

分析ダウンタイムの処理には、以前からの実績ある手法のほとんどを適用できます。このセクションでは、既知の課題と対処方法について説明します。

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

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

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

スキーマの変更

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

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

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

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

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

手法 1: ビューの切り替え

この手法は、データの「メイン」と「シャドウ」の 2 つのビューに基づいています。実際のテーブルを非表示にして、「メイン」ビューをユーザーに公開するのがコツです。更新サイクルでは、ユーザーが「メイン」ビューに対して作業する間に、「シャドウ」ビューが作成 / 更新され、データの正確性テストが行われます。切り替え時に、「メイン」ビューが「シャドウ」ビューに置き換えられます。古い「メイン」と現在の「シャドウ」は、次の更新サイクルまで破棄されるか、組織で定義されたルールとプロセスに応じて一部のワークフローで保持されます。

2 つのビューは、共通のテーブルに基づいて「view_type」などの列で区別するか、異なるテーブルに基づきます。前者の方法はおすすめしません。テーブルの「シャドウ」ビューに対する DML オペレーションは、「メイン」ビューに対するユーザークエリの速度を低下させる可能性があり、実際のメリットが何もないためです。

ビューの切り替えでは分析ダウンタイムは発生しませんが、更新サイクル中にデータのコピーが 2 つ存在するため、コストが高くなります。さらに重要なのは、更新サイクルが 90 日よりも高い割合で発生すると、この方法では、ご自身の組織で長期保存の料金を利用できなくなる可能性があることです。90 日という日数は、この記事の作成時点での価格設定ポリシーに基づいています。必ず最新のポリシーをご確認ください。

さまざまなデータのセグメントが、それぞれのペースで変化することがあります。たとえば、北米の販売データは毎日更新されますが、アジア太平洋のデータは 2 週間ごとに更新される場合です。このような場合は、変更の要因(この例では「国」)に基づいてテーブルを分割することをおすすめします。ビューの切り替えは、データ ウェアハウス全体ではなく、影響を受けるパーティションに適用されます。この記事の作成時点では、データを明示的に複数のテーブルに分割することで、「国」などのカスタムデータ属性に基づいてのみ分割が可能です。

手法 2: インプレース パーティションの読み込み

データの変更がパーティションによって分離され、短い分析ダウンタイムが許容される場合、ビューの切り替えが過剰になる可能性があります。その代わりに、影響を受けるパーティションのデータを他の BigQuery テーブルにステージングするか、分析ダウンタイムの間にファイルの置き換えが可能な Cloud Storage にファイルをエクスポートできます。

対象のパーティションのデータを、別のテーブルのクエリのデータで置き換えるには:

bq query --use_legacy_sql=false --replace \
         --destination_table 'flight_data.fact_flights_part$20140910' \
         'select * from `ods.load_flights_20140910`

Cloud Storage からの読み込みによって、対象のパーティションのデータを置き換えるには:

bq load  --replace \
         --source_format=NEWLINE_DELIMITED_JSON
         'flight_data.fact_flights_part$20140910' \
         gs://{bucket}/load_flights_20140910.json
手法 3: データ マスキングの更新

小さくて頻繁に変化するディメンションは、正規化の主要な候補です。この手法では、このようなディメンションの更新が、残りのデータと条件付きで結合されている独立したテーブルまたはビューでステージングされます。

SELECT f.order_id as order_id, f.customer_id as customer_id,
    IFNULL(u.customer_first_name, f.customer_first_name) as customer_first_name,
    IFNULL(u.customer_last_name, f.customer_last_name) as customer_last_name
FROM fact_table f
LEFT OUTER JOIN pending_customer_updates u
ON f.customer_id = u.customer_id
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 オンス 健康と美容
化粧品

属性が正規化されたディメンション テーブルにある場合、変更は極めて独立しています。ディメンション テーブルの該当する行を、単に更新するだけです。タイプ 1 の更新が頻繁にある小さなディメンション テーブルの場合は、手法 3: データ マスキングの更新を使用します。

属性がファクト テーブルに非正規化された方法で埋め込まれている場合、変更はかなり広範囲になります。属性が繰り返されているすべてのファクト行を更新する必要があります。この場合は、手法 2: インプレース パーティションの読み込み、または手法 1: ビューの切り替えのいずれかを使用します。

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

属性が正規化されたディメンション テーブルにある場合、変更は分離されます。単に前の行を更新して、新しい行をディメンション テーブルに追加するだけです。タイプ 1 の更新が頻繁にある小さなディメンション テーブルの場合は、手法 3: データ マスキングの更新を使用します。

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

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

データのクエリ

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

BigQuery ウェブ UI の画面キャプチャ

自動化されたクエリ

スケジュールやイベントに基づいてクエリの実行を自動化し、後で使用するために結果をキャッシュに保存することは、一般的な方法です。

他の自動化アクティビティをオーケストレートするために Airflow を使用していて、すでにそのツールに慣れている場合は、クエリの自動化のために Apache Airflow API for BigQuery を使用します。このブログ投稿では、Airflow をインストールして BigQuery に対するワークフローを作成する過程について説明しています。

オーケストレーションをよりシンプルにするために、cron ジョブに頼ることもできます。このブログ投稿では、クエリを App Engine アプリとしてカプセル化し、スケジュールされた cron ジョブとして実行する方法について説明しています。

クエリの最適化

BigQuery はクエリを実行するたびに、列全体のスキャンを実行します。BigQuery はインデックスの使用やサポートをしません。BigQuery のパフォーマンスとクエリコストは、クエリ中にスキャンされたデータ量に基づくため、クエリに関連する列のみを参照するようにクエリを設計します。日付で分割されたテーブルを使用する場合は、関連するパーティションのみをスキャンするようにします。これを実現するには、PARTITIONTIME または PARTITIONDATE に基づくフィルタを使用します。

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

BigQuery の結果画面のキャプチャ

外部ソース

フェデレーション データソースを使用して、BigQuery の外部に存在するデータでクエリを実行できますが、この方法はパフォーマンスに影響を与えます。データを外部で維持する必要がある場合に限り、フェデレーション データソースを使用してください。クエリ フェデレーションを使用して、外部ソースから BigQuery への ETL を実行することもできます。この方法では、使い慣れた SQL 構文を使用して ETL を定義できます。

ユーザー定義の関数

BigQuery は、SQL の複雑さを超えるクエリ用のユーザー定義関数(UDF)もサポートしています。UDF を使用すると、組み込みの SQL 関数を拡張できます。UDF は、値のリスト(配列または構造体)を受け取り、単一の値(配列または構造体)を返します。UDF は JavaScript で記述され、暗号化や他のライブラリなどの外部リソースを含めることができます。

クエリの共有

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

データの分析

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

既製のツール

この記事の作成時点でベータ版として提供されている Google データポータルと、BigQuery に統合された多くのパートナー ツールを使用して、BigQuery から分析を引き出すほか、高度な対話型のデータ可視化を構築できます。

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

パートナーロゴ

カスタム開発

BigQuery の上にカスタム アプリケーションとプラットフォームを構築するには、一般的なプログラミング言語で使用できるクライアント ライブラリを使用するか、BigQuery の REST API を直接使用します。

具体的な例については、このチュートリアルをご覧ください。Python ライブラリを使用して BigQuery に接続し、カスタムの対話型ダッシュボードを生成します。

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

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

費用

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

BigQuery には、読み込みコスト、ストレージ コスト、クエリコストの主要な 3 つのコストの要素があります。このセクションでは、それぞれのコストの要素について詳しく説明します。

データの保存

ストレージの料金は、MB/秒で案分されます。

テーブルが 90 日間連続して編集されていない場合、テーブルは長期ストレージとして分類され、そのテーブルのストレージの料金は自動的に 50% 値引きされ、1 か月あたりの料金は 1 GB あたり $0.01 になります。テーブルを長期にわたって保存していても、パフォーマンス、耐久性、可用性などの各種機能性が損なわれることはありません。テーブル内のデータが変更されると、BigQuery はテーブルのタイマーをリセットし、テーブル内のデータはすべて通常のストレージ料金に戻ります。ビューのクエリや作成など、データを直接操作しないアクションでは、タイマーはリセットされません。

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

データの読み込み

従来の読み込みジョブを無料で使用して、データを BigQuery に読み込むことができます。データが読み込まれたら、上記で説明したストレージの料金を支払います。

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

データのクエリ

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

オンデマンド料金

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

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

クエリ API

定額料金

毎月の費用を一定にしたいお客様は、定額料金を選択できます。詳細については、BigQuery 定額料金をご覧ください。

次のステップ