データ ウェアハウス使用者向け BigQuery

この記事では、Google BigQuery をデータ ウェアハウスとして使用する方法を説明します。一般的なデータ ウェアハウスの概念が BigQuery ではどのように扱われるかをマップで示し、さらに標準的なデータ ウェアハウス作業が BigQuery でどのように実行できるかについて説明します。

サービスモデルの比較

コンセプト

以下の表に、標準データ ウェアハウスの概念が Google BigQuery でどのように扱われるかを示します。

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

データセット

BigQuery は、データテーブルをデータセットと呼ばれる単位で整理します。これらのデータセットは、GCP プロジェクトを範囲とします。コマンドラインまたはコードでテーブルを参照するには、次の形式になります。

project.dataset.table

この複数の範囲は、情報を論理的に構成するのに役立ちます。データセットを複数使用して、別々の解析領域に関連するテーブルに分けることができます。また、プロジェクト レベルでの範囲設定を利用して、ビジネスニーズに応じてデータセットを分割することもできます。

BigQuery 構造の概要
図 1: BigQuery 構造の概要

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

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

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

すべてのお客様にデフォルトで 2,000 スロットがクエリ オペレーション用として割り当てられます。BigQuery の利用が増えると、スロット割り当ての制限が自動的に増大します。クエリが短期間に集中して発生するというお客様や、固定料金モデルを希望するお客様には、BigQuery のスロットを明示的に割り当てるオプションも用意されています。詳細については費用をご覧ください。

BigQuery サービスの通常の使用パターンでは、リソース使用量の最低コミットメントは不要です。リソースは、実際の使用量に基づいて動的に割り当てられ、課金されます。スロットの明示的な割り当てを希望するお客様は、定額料金をご利用いただけます。

ストレージ管理

BigQuery の内部では、取り込んだデータを Capacitor ストレージ形式で保存します。Capacitor はデータを列型で保存します。これはデータ ウェアハウスのワークロードに最も適したものです。BigQuery は、データのエンコードを決定し、それ以降もデータの保存に使用するシャーディングの数とエンコードの評価を継続的に行い、データが最適な形式で保存されるようにします。

データ自体は Google の Colossus ストレージ システムに保存されます。高い可用性と耐久性を確保するため、Colossus に保存されたデータは複数のデータセンターに複製されます。操作面では、異なるストレージ層間でのデータ移動、データのバックアップ、データ分割サイズの決定、データの手動での再エンコードといった局所的なストレージ操作は必要はありません。このような管理タスクのすべては BigQuery で管理され、継続的に最適化されます。さらに、BigQuery 内に保存されるすべてのデータは保存時に暗号化されます。

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

メンテナンス

BigQuery はフルマネージド サービスです。これは、BigQuery エンジニアリング チームがお客様の代わりにアップデートと保守を担当するという意味です。アップグレードの際にダウンタイムは発生せず、システム パフォーマンスの低下もありません。

処理の観点においても、多くのシステムではデータブロックを入れ替えて分類を最適化するために、リソース消費が高くシステム動作に影響するバキューム プロセスをさまざまな間隔で実行する必要があります。このバキューム プロセスは、削除されたスペースの回復にも寄与します。BigQuery では、ストレージ エンジンがデータの保存方法と複製方法を継続的に管理し、最適化しているため、バキューム プロセスに相当するものはありません。同様に、インデックスの断片化が問題となっているシステムでは、インデックスの再作成が頻繁に必要となりますが、BigQuery はテーブルでインデックスを使用しないため、インデックスを定期的に再作成する必要もありません。

権限

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

BigQuery でのユーザーと権限の管理モデルは、この後者の構造に似ています。BigQuery では、一連の権限を定義する Cloud IAM の役割を作成して、ユーザーまたはグループに割り当てます。役割は個々の Google メールアドレスに割り当てることができますが、G Suite のグループに割り当ててユーザーのグループに権限を付与することもできます。

GCP は監査ログを自動的に作成し、ユーザーの操作を追跡します。詳細については監査ログをご覧ください。

BigQuery への接続

BigQuery には、次のようなさまざまな方法で接続できます。

  • bq コマンドライン ユーティリティ
  • BigQuery ウェブ コンソール
  • BigQuery コネクタ

上記の方法はそれぞれ、BigQuery API を基盤としたラッパーを提供します。この API を直接使用することもできます。BigQuery API へのすべての接続は、HTTPS を使用して暗号化されます。

bq コマンドライン ユーティリティ

bq コマンドライン ユーティリティは Google Cloud SDK の一部として提供されています。

BigQuery ウェブ コンソール

BigQuery コンソールを使用すると、データセットの対話型クエリが可能となり、アクセス可能なデータセットを一元的に管理できるようになります。このコンソールには、アドホック クエリの保存と共有、過去のクエリの調整と編集、テーブルとスキーマの確認、テーブル メタデータの収集などの有用な機能も用意されています。

BigQuery ウェブ コンソール
図 2: BigQuery ウェブ コンソール

BigQuery REST API

BigQuery の上に操作性を構築する必要があるアプリケーションとプラットフォームを開発している場合は、BigQuery API を直接使用することもできます。BigQuery には最も一般的なプログラミング言語に対応するクライアント ライブラリがあります。独自のクライアントを使用して API に直接接続する方法については、API ドキュメントをご覧ください。

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

API レベルで BigQuery とネイティブに統合されていないアプリケーションから BigQuery に接続する場合、BigQuery JDBC ドライバと ODBC ドライバを使用できます。このドライバは、従来のアプリケーションや、Microsoft Excel などの、容易に変更できないアプリケーションに対応できるように、BigQuery と対話するブリッジを提供します。ODBC ドライバと JDBC ドライバは SQL による BigQuery との対話をサポートしますが、API を直接使用する場合と比べて構文がやや複雑になります。

データの読み込み

BigQuery にデータをインポートする方法には、読み込みジョブを使用する方法と、ストリーミングを使用してレコードごとにインポートする方法があります。

読み込みジョブの使用

大規模な移行の場合、データのインポートには読み込みジョブを使用してください。

最初に、テーブル別のファイル セットにデータをエクスポートまたはアンロードし、BigQuery への読み込みに適切な形式とサイズにします。BigQuery で有効な入力データ形式は、Avro、JSON、CSV です。各データ形式と、形式の選択において考慮すべき要件と機能の詳細については、BigQuery のデータ形式をご覧ください。

作成したエクスポート ファイルを Google Cloud Storage バケットに転送します。バケットから BigQuery テーブルにデータを読み込む方法が、読み込みアクティビティを完了する上で最も信頼できる方法です。Cloud Storage バケットへのファイルの転送には、gsutil コマンドライン ユーティリティか Cloud Platform Console を使用できます。エクスポート ファイルが、Amazon S3 や HTTP/HTTPS ロケーションなどの別のストレージ プロバイダに配布できる状態になっている場合や、これらのロケーションのいずれかにすでに存在している場合は、フルマネージド Google Cloud Storage Transfer Service を使用できます。このサービスを利用すると、Cloud Storage バケットにファイルを移動するプロセスが自動化され、効率性が向上します。

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

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

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

  • 自己記述型である Avro 形式のデータの場合、BigQuery がスキーマを直接指定できます。
  • JSON または CSV 形式のデータは、BigQuery がスキーマを自動検出します。
  • CSV ファイルにヘッダー情報が含まれている場合、BigQuery はそのヘッダー内で指定されているスキーマを使用します。

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

初期の読み込みとテーブル作成後は無視するべきヘッダー行が CSV ファイルに常に含まれている場合は、--skip_leading_rows フラグを使用するとその行は無視されるようなります。

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

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

データ読み込みの別の方法、あるいは補完的な方法として、BigQuery に直接ストリーミングする方法もあります。ストリーミング データには、リアルタイムで既存のテーブルデータと一緒にクエリが実行されます。クエリパスを別に実装する必要はありません。

不正の検出やシステム指標のモニタリングなど、リアルタイムの情報が有力になるような状況では、ストリーミングは重要な差別化要因となりえます。ただし、BigQuery では無料で行われる読み込みジョブとは異なり、データのストリーミングは課金対象であるため、適切かつ有益な状況で利用することが重要です。

データを BigQuery テーブルにストリーミングするときは、BigQuery API を使用してレコードを BigQuery に直接送信してください。GCP のロギング サービスである Stackdriver Logging を使用している場合は、Google App Engine からの要求ログと、Stackdriver Logging に送られるカスタムログ情報を含む、GCP プロジェクトのログを BigQuery に直接ストリーミングすることもできます。

データの管理

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

BigQuery は、テーブルの日付によるパーティショニングをネイティブ サポートします。テーブル作成プロセス時にパーティショニングを有効にすると、BigQuery は新しい日付ベースのパーティションを自動的に作成します。追加のメンテナンスは不要です。パーティション内のデータに特定の有効期限を指定することもできます。

パーティション テーブルに挿入される新しいデータは、挿入時に生のパーティションに書き込まれます。読み込みジョブにパーティション デコレータを使用して明確な日付を指定すると、データが読み込まれるパーティションを明示的に制御できます。

データの非正規化

一般的なデータ ウェアハウスのシナリオとして、ディメンション モデルに従ってデータを挿入するシナリオがあります。ここでは、ファクトが 1 つ以上のディメンション テーブルと結合しています。BigQuery は結合をサポートしますが、最適なパフォーマンスを得るために、結合を実行するのは、小規模なディメンション テーブルを処理する場合に限定するべきです。

非正規化スキーマを使用した場合と、結合を使用した場合のパフォーマンスの比較
図 3: 非正規化スキーマを使用した場合と、結合を使用した場合のパフォーマンスの比較

データを非正規化するには、通常、ファクトをそのすべてのディメンションとともに、フラットなテーブル構造に書き込みます。たとえば、販売取引を処理している場合、注文やお客様情報などの付随ディメンションとともに個々のファクトを 1 つのレコードに書き込みます。この方法を採用すると、ストレージ サイズが大きくなる可能性がありますが、パフォーマンスが向上するためトレードオフの価値が十分にあります。

データを非正規化する別の方法は、JSON 入力データまたは Avro 入力データ内のネスト構造と繰り返し構造に対する BigQuery のネイティブ サポートを活用します。ネスト構造と繰り返し構造を使用してレコードを表記すると、基盤データをより自然な表現で提供できます。販売注文の場合、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"
    }
  ]
}

BigQuery は、小規模なデータセットではすばやく結合を実行できますが、より大きなディメンション テーブルでの結合はパフォーマンスに影響を及ぼす可能性があるため、パフォーマンスが重視される状況では不適切な方法と見なさるべきです。クエリのパフォーマンスを最適化するには、ファクト テーブルとディメンション テーブルを非正規化してから、データを BigQuery に読み込んでください。BigQuery 向けのデータの非正規化について詳しくは、読み込み用のデータの準備をご覧ください。

データの更新

データ ウェアハウスは OLAP ワークロード用として意図されているので、不変ストアとして扱い、新たに到着したデータは追記専用で書き込まれるようにするべきです。しかし、BigQuery は挿入、更新、削除などの DML アクションをサポートしています。

データのクエリ

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 はクエリを実行するたびに、高速で列のフルスキャンを実施します。BigQuery はインデックスを使用せず、サポートもしません。BigQuery のパフォーマンス コストとクエリコストは、クエリ中にスキャンされるデータ量を基準としているため、クエリに関係する列だけが参照されるようにクエリを計画する必要があります。さらに、日付によるパーティションをベースとしたテーブルを使用するときは、テーブル デコレータを使用して、クエリの対象である時間枠に関係するデータを含むパーティションのみスキャンするようにします。

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

クエリプランの説明
図 4: クエリプランの説明

フェデレーション データソースを使用して、BigQuery の外部に存在するデータでクエリを実行できますが、この方法はパフォーマンスに影響が及ぶ可能性があります。データを外部で維持する必要がある場合に限り、フェデレーション データソースを使用してください。

BigQuery は、SQL の複雑性を超えるクエリのために ユーザー定義関数(UDF)もサポートします。UDF は、MapReduce の Map 関数のように動作します。UDF は 1 行を 1 つの入力として取り、JSON(ネスト構造)か、1 つまたは複数の行を出力として発行します。UDF は JavaScript で記述され、時系列やその他のライブラリなどの外部リソースを含めることができます。

ワークフローとコラボレーションの管理

新しいデータ解析担当者がチームに定着するまでには、かなりの時間を要する場合があります。通常、すべてのデータソースが存在する場所を指導し、ODBC 接続をセットアップし、ツールとアクセス権をセットアップするといった作業を経るまで、新任の解析担当者は単純なクエリを実行することさえできません。GCP のプロジェクト構造を活用することで、定着プロセスを効率化し、短期間で解析担当者の生産性を軌道に乗せることができます。

GCP ならば、解析担当者をプロジェクトに追加して、Google Cloud Platform Console と BigQuery コンソールに担当者を導入するだけで、定着プロセスが完了します。各コンソールには、BigQuery プロジェクト向けのさまざまなビューと機能が用意されています。

  • Google Cloud Console には、Google Compute Engine インスタンスや Cloud Storage バケットなどのクラウド環境に、資産の一元化されたビューが用意されています。
  • BigQuery コンソールには、プロジェクトに関連付けられているデータセット一覧と、SQL を実行するためのクエリ コンソールが含まれます。

これらの機能に沿って内部のワークフローを設計すると、解析担当者間の効率と連携が大幅に向上します。

統合データセット

データ ウェアハウス管理におけるデータ供給側の処理の一環として、新しいデータセットは、解析担当者がアクセス権を持つプロジェクトに追加されます。プロジェクトは、データまたはビジネス ユニットのクラスに基づいてセグメント化することもできますが、簡潔性を重視して複数のプロジェクトを 1 つに統合することもできます。また、他のプロジェクト内に存在するデータセットがある場合に、そのデータの解析担当者を招待して共同で作業するということも可能です。解析担当者が Cloud Console にログインすると、その担当者に共有されているすべてのデータセットが BigQuery コンソールに表示されます。

クエリの共有

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

費用

BigQuery には、ストレージ コストとクエリコストという主に 2 つのコスト要素があります。ここでは、それぞれのコストについて詳しく説明します。

データの保存

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

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

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

データの読み込み

通常の読み込みジョブを課金なしで使用して、データを BigQuery に読み込むことができます。ただし、読み込みジョブの結果として消費されるストレージには課金されます。

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

データのクエリ

クエリに関して、BigQuery はオンデマンドと定額の 2 種類の料金モデルを提供します。

オンデマンド料金

オンデマンド モデルでは、BigQuery はクエリの実行中にアクセスされるデータの量に対して課金します。BigQuery はカラム型ストレージ形式を使用するため、クエリに関係する列のみがアクセスされます。クエリの課金の仕組みについて詳しくは、BigQuery クエリの料金をご覧ください。

週単位または月単位でのみレポートを生成している場合、クエリコストの請求は少額であるか、あるいは 1TB 未満のデータでクエリを実行していると、課金なしのこともあります。クエリによってスキャンされるデータの量がどれくらいかを事前に判断するために、クエリ検証ツールを使用できます。

クエリ検証ツール
図 5: クエリ検証ツール

定額料金

月間経費を一定にしたいお客様は、定額料金をご利用いただくこともできます。詳細については、BigQuery 定額料金をご覧ください。

次のステップ

Google Cloud Platform の他の機能を試すには、チュートリアルをご覧ください。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...