BigQueryを徹底解説!(基本編)

記事タイトルとURLをコピーする

G-gen の杉村です。Google Cloud のフルマネージドな分析用データベースである BigQuery について、徹底的に解説します。当記事は基本編であり、当記事を読み終わったあとは応用編もご参照ください。

概要

BigQuery とは

BigQuery とは Google Cloud (旧称 GCP) の分析用データベースです。BigQuery はフルマネージドサービスであり、そのため「従量課金 (初期投資がゼロ)」「フルマネージド (インフラ構築・管理が一切不要)」であることが最大の特徴です。

また自動的にスケーリング (拡張・縮小) するため、非常に高度なパフォーマンスを容易に得ることができます。

分析用データベース (あるいはデータウェアハウス等とも呼ばれます) として企業や官公庁、あるいは個人によって世界中で広く使われており、Google Cloud を特徴づける最大のプロダクトでもあります。同種の他社製品としては、Amazon Redshift や Snowflake、Azure Synapse Analytics が挙げられます。

BigQuery のデータは表形式で格納され、Google SQL (旧称 : Google 標準 SQL) という SQL や API 経由でデータを操作可能です。扱うデータは表形式ではありますが一般的な業務アプリ用の RDBMS とは異なり「列指向ストレージ」「キー制約の不在」「インデックスの不在 (一部機能として存在)」「非正規化のプラクティス」などの特性があります。

利用方法

BigQuery は Google Cloud のプロダクトです。利用開始するにはログイン用の Google アカウントGoogle Cloud プロジェクトが必要です。

BigQuery を操作するインターフェイスはいくつか存在します。Web ブラウザで操作できる Web コンソール (Google Cloud コンソール)、 CLI コマンドラインである bq コマンドライン、Python など各言語用のクライアントライブラリなどです。

また、BigQuery 用の ODBC/JDBC ドライバも用意されています。

BigQuery コンソール画面

まだ Google Cloud プロジェクトをお持ちでない場合、手軽に Google Cloud を利用開始する方法は、以下もご参照ください。

blog.g-gen.co.jp

フルマネージド (サーバーレス)

BigQuery はフルマネージドであり、サーバーレスです。これは BigQuery のインフラ管理が Google によって行われており、我々ユーザーは関与しなくてよいことを示しています。

次の図は、BigQuery の基盤アーキテクチャを模式的に示した図です。

BigQuery のアーキテクチャ

BigQuery のインフラは Google によって管理されています。コンピュート処理を行うワーカーはコンテナ群で形成されており (裏では Borg と呼ばれる技術が使われています)、ユーザーによるジョブ投入に応じて自動的にスケーリングします。

また、ストレージレイヤも仮想化されており、コンピュート能力とは切り離されています。

我々ユーザーは、BigQuery API を経由して BigQuery にデータを投入したり、SQL を投入するなどして、Google の持つ大量のリソースを柔軟に使うことができます。課金は、使った分だけの従量課金です (プラン選択で固定化も可能です)。

他の Google Cloud サービスとの連携

BigQuery の優れている点は、高度なスケーリング能力や保守性に留まりません。Google Cloud の他のサービスと連携させることで、容易に業務アプリケーションのデータを BigQuery にロード (読み込み) し、分析に繋げることができるのです。

例えば後述の 外部テーブルBigLake テーブル (応用編で解説) を用いると、オブジェクトストレージサービスである Cloud Storage に保存してある CSV や Parquet などのファイルや Google ドライブ上のスプレッドシートを BigQuery にロードすることなくそのままクエリできたり、連携クエリ (Federated query、応用編で解説) により Cloud SpannerCloud SQL など業務用 RDBMS のテーブルを直接クエリすることができます。

またメッセージングサービスである Cloud Pub/Sub から BigQuery にデータを直接投入することで、IoT 機器や Web アプリからニアリアルタイムでデータを BigQuery に投入することが可能です。

このように、BigQuery と他の Google Cloud サービスを組み合わせることで、全ての情報システムからデータを途切れなく BigQuery に流し込むことができます。スピーディ・低工数でデータを利用まで繋げられるため、BigQuery がデータ戦略の中心と成り得ると言えます。

BigQuery を中心としたデータ分析基盤

他クラウドサービスとの連携

BigQuery には、Amazon Web Services (AWS) の Amazon S3 や Microsoft Azure の Azure Blob Storage など、他のクラウドサービスのデータを取り込むための機能も備わっています。

当記事で紹介する BigQuery Data Transfer や、応用編の記事で紹介する BigQuery Omni を使うことでそれらの外部データソースから BigQuery にデータを取り込むことが可能です。ただし各クラウドサービスはデータが外部に出るときにデータサイズ (GB 数) に応じて課金が発生するので、その点には注意が必要です。

料金

料金体系の基本

BigQuery の料金体系の原則は使った分だけ支払いが発生する従量課金です。ただし、設定により固定料金とすることもできます。

BigQuery の利用料金は以下の2軸で決定されます。

  1. ストレージ料金 (格納したデータサイズに応じた課金)
  2. コンピュート料金 (コンピュート処理能力に対する課金)

BigQuery 利用料金の構成

料金表や課金の仕組みの詳細は、以下公式ページもご参照ください。

ストレージ料金

ストレージ料金の基本

ストレージ料金は、シンプルに格納したデータサイズで決定し、月次で請求されます。

ディスクの拡張や追加といった概念はなく、ユーザーとしてはデータを挿入したり削除するだけで、利用したストレージ分だけが課金される仕組みです。また最初の 10 GB は無料枠となっています。

料金表は、以下の通りです。

Physical Storage と Logical Storage

データセット (後述) ごとに Logical Storage 課金か Physical Storage 課金(Compressed Storage とも呼称)かを選択することができます。

BigQuery に格納したデータは透過的に圧縮されています。Logical Storage 課金を選択すると、圧縮の額面データサイズに課金されます。Physical Storage 課金だと、圧縮の実データサイズに対して課金されます。 単価は Physical Storage 課金のほうが高いのですが、BigQuery では通常それを超える圧縮率で圧縮されますので、Physical Storage 課金のほうが安価になる傾向があります。

Active Storage と Long-term Storage

Physical Storage と Logical Storage の両方で Active Storage と Long-term Storage と呼ばれる保存領域があります。

格納してすぐのデータは Active Storage の単価で課金されます。その後、90 日間連続で変更がされなかったデータは Long-term Storage という扱いになり、より安価な単価で課金されます。

コンピュート料金

一方のコンピュート料金の課金方法は「オンデマンド」と「BigQuery Editions」という2つの仕組みからどちらかを選択することができます。デフォルトはオンデマンドです。

オンデマンドの場合、BigQuery が1ヶ月にスキャンしたデータサイズに応じて料金が決定します。無料枠があり、毎月最初の 1TB のスキャンは無料です。料金表は、以下の通りです。

一方の BigQuery Editions では、利用したスロットの量に応じて課金されます。スロットとは、BigQuery がクエリの実行に使う CPU のリソース量を図るための単位です。料金表は、以下の通りです。

BigQuery Editions は3つのエディションで構成されており、使用可能な機能がエディションにより制限されています。一方のオンデマンドは、ほとんどの機能が利用可能です。しかしながら無料枠を超える場合は、適切な Edition を選択したほうが、オンデマンド課金よりも安価になる可能性があります。Editions の概念のより詳細な解説は、以下の記事をご参照ください。

blog.g-gen.co.jp

無料枠

ストレージ課金は、最初の 10GB は無料となります。

コンピュート料金は、オンデマンド課金を選択している場合のみ無料枠があります。月の最初の 1TB のスキャンまでは無料です。

無料枠は Google Cloud のプロジェクトごとではなく、請求先アカウントごとに適用される点にご注意ください。

オンデマンド課金に制限をかける

突発的な大量課金を防ぐために、API の割り当て (Quota) 機能を利用する方法があります。以下の記事でご紹介していますので、ご参照ください。

blog.g-gen.co.jp

コンポーネント

BigQuery の構成要素

BigQuery の論理的な構成要素 (コンポーネント) を解説します。

なお、Google Cloud プロジェクトや組織といった概念についてはこの記事では解説しません。代わりに、以下の記事もご参照ください。

blog.g-gen.co.jp

BigQuery の構成例を図にすると、以下のようになります。

構成例

データセット

BigQuery のデータセットとは、テーブルをグルーピングするための論理的な入れ物です。データセットは一つの Google Cloud プロジェクトに所属します。

データセット作成時に、データを配置するロケーションを選択します。東京リージョンなどの個別リージョンや、あるいは US マルチリージョンなどが選択できます。

またデータセットの単位で IAM 権限の設定やテーブル有効期限の設定、暗号鍵の指定が可能です。データセットで設定した設定値が配下のテーブルのデフォルトの設定値になります。ほとんどの設定値はテーブルの設定でオーバーライド (上書き) が可能ですが、ロケーションだけはオーバーライドできません。

データセットにはテーブルだけでなく、ビュー、関数、プロシージャなどが所属します。

テーブル

テーブルとは

BigQuery のテーブルは、データを格納するためのコンポーネントです。テーブルはスキーマ定義を持っており、通常のリレーショナル・データベースと同じように、型 (type) が決まった列 (カラム) と、行 (レコード) を持ちます。テーブルは単一のデータセットに所属します。

標準テーブル

後述の外部テーブル等と区別するため、通常のテーブルは標準テーブルもしくは単にテーブルと呼ばれます。

標準テーブルは、業務用の RDBMS とは異なり列指向 (カラムナ) でデータを保持します。そのため集計処理など、カラム単位で行う処理を高速に行うことができます。なお列指向は多くの分析用データベースが持つ特徴であるほか、一部のファイルフォーマット (Apache Parquet や ORC) でも採用されています。

BigQuery のテーブルは列指向であるため、クエリ時は SELECT 文で必要な列だけを選択することで、料金に関わる要素であるスキャン量を節約し、パフォーマンスを向上させることが可能であるなどの特性があります。

外部テーブル

外部テーブル はテーブルの一種ですが、BigQuery のストレージの外にある外部データソースをクエリするための仮想的なテーブルです。

Cloud Storage をはじめ、Cloud Bigtable、Google ドライブを外部テーブルのソースとして定義することが可能です。

外部テーブル定義を利用することで外部データソースからデータを BigQuery にロードすること無くクエリが実行できるのが利点です。ただしデータソースへのストレージ I/O が発生するほか、例えば Cloud Storage であれば、オブジェクトストレージへの I/O となるため、BigQuery 内のデータをクエリするよりもパフォーマンスは劣ります。

また外部テーブルを BigLake テーブルとして定義することで、アクセス権限管理を高度化・簡素化することもできます。外部テーブルの詳細や、BigLake の概要は「応用編」記事で解説します。BigLake テーブルでは BigQuery Omni 機能により Amazon S3 や Azure Blob Storage 上のデータをクエリすることもできます。

ビュー

ビュー (通常)

ビューは実データを持たない、読み取り専用の仮想的なテーブルです。ベースとなるテーブルへの SELECT 文を書くことで定義します。副問合せを含む複雑なクエリを単純化して可読性をよくしたり、再利用性を高めるために利用します。

ビューと、そのビューのベーステーブルは同じロケーションに存在している必要があるなど、いくつかの制限が存在します。

マテリアライズド・ビュー

ビューでありながら実データを持つマテリアライズド・ビューも存在します。マテリアライズド・ビューには使える JOIN 句に制限がある等しますが、内部にデータを持つためベーステーブルへのクエリが発生しません。自動リフレッシュが設定できるため、管理コストの低い ELT (データ変換) とみなすこともできます。

ルーティン

ルーティンとは

BigQuery では以下のリソースを総称してルーティン (routines) と読んでいます。

  • ストアド・プロシージャ
  • ユーザー定義関数 (UDF)
  • テーブル関数

これらは SQL やプログラミング言語によって事前に処理を定義しておき、後から呼び出せるようにしておくためのコンポーネントです。ルーティンは単一のデータセットに所属します。

ストアド・プロシージャ

ストアド・プロシージャ は SQL で記述するステートメント群です。他のクエリや他のストアド・プロシージャから呼び出すことができ、引数として値をインプットし、戻り値としてアウトプットを返すことができます。

システムプロシージャと呼ばれる、組み込みのストアド・プロシージャも存在します。

ユーザー定義関数 (UDF)

ユーザー定義関数 (User Defined Functions, UDF) は SQL または JavaScript で記述するステートメント群です。

永続 UDF と一時 UDF があり、永続 UDF は一度定義すると複数のクエリから使い回せる一方、一時 UDF は一つのクエリの中で完結し、クエリが完了すると消滅します。

またリモート関数という種類の UDF も存在します。リモート関数では Cloud Functions または Cloud Run に関数をデプロイできることから、SQL や JavaScript 以外で関数を記述することができます。

ストアド・プロシージャと UDF の違い

ストアド・プロシージャと UDF はどちらも事前に処理を記述しておき、再利用するための仕組みです。

ストアド・プロシージャは主に ELT 処理など、データに対して処理を行った後、その結果を DML でテーブルに書き込む用途が想定されます。

一方の UDF はユーザー定義の「関数」ですので、組み込み関数である SUM()AVG() のように、SQL 中で呼び出され、インプットに応じた値を返すことに使うことが想定されています。

テーブル関数

テーブル関数 (TVF) は、戻り値として値ではなくテーブルを返すユーザー定義関数です。テーブルを返すという点ではビューとも類似していますが、ビュー定義ではパラメータを受け取ることができない一方で TVF ではパラメータを受け取れるというのが違いです。

ジョブ

ジョブ とは、BigQuery が実行するアクションの総称で「データのロード」「データのクエリ」「データのエクスポート」「データのコピー」などがこれに当たります。

利用者は、Google Cloud コンソールや bq コマンドラインなどから非同期にジョブを投入し、あとからステータス・結果を取得できます。

コンピュート課金はジョブが投入されたプロジェクトにおいて発生します。そのため、コンピュート処理課金を負担するプロジェクトとストレージ課金を負担するプロジェクトを別にすることができます。これにより、例えば自社の BigQuery データセットをグループ会社の Google Cloud プロジェクトに共有した場合、ストレージ料金を負担するのは自社ですが、データの利用 (クエリ) によって発生する課金はグループ会社、という立て付けにすることが可能です。

なおジョブの実行にもロケーションの概念が存在します。ジョブが対象とするデータセットに基づいて、クエリを実行するロケーションも決定されます。データセットの参照が無いクエリの場合は US マルチリージョン で実行されます。明示的にクエリの実行ロケーションを指定することもできますが、ジョブと同じロケーションのデータセットしか参照・更新できません。

接続 (Connection)

Biglake テーブルや連携クエリなど、BigQuery の外部のデータへのクエリを行うための接続設定や認証情報を管理するために接続 (Connection) というコンポーネントが存在します。接続には、以下の種類があります。

  • Biglake テーブル
  • 連携クエリ
  • リモート関数
  • Apache Spark ストアドプロシージャ

接続 (Connection) により、接続先データベース (連携クエリにおける Cloud SQL 等) への接続設定や認証情報を管理したり、リモート関数として実装された Cloud Functions や Cloud Run の情報を管理します。なお、接続 (Connection) はデータセットにではなくプロジェクトに所属します。

データのロード

バッチロード

BigQuery にデータを投入することをロード (読み込み、積み込み) と言います。いくつかの方法がありますが、以下は代表的なものです。

  1. Cloud Storage 等からファイルで一括ロード
  2. SQL の結果をテーブルに書き込み
  3. BigQuery Data Transfer Service
  4. BigQuery Storage Write API
  5. 他のマネージドサービスやサードパーティ製品

これらは、ファイルの単位である程度のデータをまとまりでロードするため、バッチロード (一括ロード) とも呼ばれます。

最も多く使うであろう 1. では Avro / CSV / JSON / ORC / Parquet などのファイルを、Cloud Storage やローカル環境から読み込ませることができます。CSV や JSON のような半構造化データ (スキーマが自己記述されていないデータ) のロード時には、スキーマを明示的に BigQuery に指示することもできますし、スキーマの自動検知も利用可能です。

ストリーミング

BigQuery にはバッチロードのほか、ストリーミングでデータをロードすることもできます。

BigQuery に用意されている Storage Write API 経由でデータをストリーミングしたり、Google Cloud のフルマネージドサービスである Dataflow、Datastream、Pub/Sub 経由でデータを投入することができます。

IoT 機器や Web アプリの行動履歴データなど、ニアリアルタイムにデータを BigQuery に投入して分析したい場合にストリーミングロードが利用されます。

データのクエリ

クエリと宛先テーブル

BigQuery には Google SQL (旧称 : Google 標準 SQL) を使い、クエリを投入できます。

Google Cloud コンソールや bq コマンドライン、あるいは各種クライアントライブラリ (Cloud SDK) などからクエリを実行可能です。

SELECT 文の実行結果はコンソールやコマンドラインに返されますが、裏では同じ結果が一時テーブルに書き込まれています。一時テーブルはクエリ結果の書き込み先として使われる、料金のかからない、24時間だけ持続するテーブルで、作成したアカウントからのみアクセスできます。

なおコンソールや bq コマンドで SELECT 文を実行する際に、クエリ結果の書き込み先を既存の標準テーブルへ向けることができます。このとき「宛先テーブルが空の場合にだけ書き込む」「洗い替え」「追記」から選択できます。それぞれ、SQL でいうところの CREATE TABLE xxx AS SELECT 〜 CREATE OR REPLACE TABLE xxx AS SELECT 〜 INSERT INTO xxx SELECT 〜 を実行した場合と同等です。

クエリ結果のキャッシュ

先述の通り SELECT 文の結果はデフォルトで一時テーブルに書き込まれますが、この一時テーブルはクエリ結果のキャッシュとしても使われます。

クエリの文字列が完全一致する場合のみ、BigQuery は結果を一時テーブルから返します。

また一時テーブルは、前述の通り、クエリ実行者のユーザー(Google アカウント、サービスアカウント)からしかアクセスできないため、原則的には別のユーザーのキャッシュを参照することはできません。ただし Enterprise または Enterprise Plus edition の課金体系の場合のみ、別のユーザーのキャッシュを参照することができます。

クエリのドライラン

クエリのドライランによって、どれくらいのサイズのデータにスキャンがかかるかを、クエリ実行前に知ることができます。

Google Cloud コンソールを利用している場合、SQL を記述すると自動的に想定スキャンバイト数が表示されます。

bq コマンドラインであれば --dry_run オプションを明示的に指定します。

パラメータ化クエリ

パラメータ化クエリ (Parameterized query) を使うことで SQL 内にプレイスホルダを置いて、動的な SQL 生成が可能です。

@param_name のように @ 記号でプレイスホルダを表現します。

SQL へパラメータを引き渡すには bq コマンドの --parameter オプションでパラメータ名と値を指定します。Google Cloud コンソールでのクエリ実行では、残念ながらパラメータ化クエリは利用できません。

エクスポート

テーブルデータのエクスポート

データは Cloud Storage 上のファイルにエクスポートすることができます。1ファイル最大 1GB となり、それを超える場合は複数ファイルに分割されます。

ファイル形式は Avro / CSV / JSON / Parquet に対応しており、それぞれ対応した形式で圧縮されます。

エクスポートは Google Cloud コンソールや bq コマンドラインで行えるほか、SQL の EXPORT DATA ステートメントでも実行可能です。

クエリ結果のエクスポート

Google Cloud コンソールを利用してクエリを実行している場合、クエリ結果をエクスポートすることも可能です。

コンソールからは、ローカル PC のファイル、Google スプレッドシート (英名 : Google Sheets)、Google ドライブにクエリ結果をダウンロードできます。

bq コマンドやクライアントライブラリからクエリを実行した場合は、上記のような結果ダウンロードはできませんが、前述の通りクエリ結果は一時テーブルまたは通常のテーブルに書き込めるため、それらの宛先テーブルから 前述の Cloud Storage へのエクスポートが実施できます。

クエリ結果のエクスポート

可用性と耐久性

概要

BigQuery は Compute Engine や Amazon Redshift 等とは異なり、ユーザごとにインスタンスを構築して利用するようなサービスではありません。Borg と呼ばれる分散コンテナ管理基盤や Colossus という分散ストレージ基盤といった Google の技術を用いており、ユーザはバックエンドのインフラを全く気にすることなく利用することができます。

これにより 99.99% の可用性 SLA が提供されています。一定の条件下で可用性がこれを下回った場合、Google Cloud で利用可能なクレジットの払い戻しがあります。詳細な条件は公式ページをご参照ください。

BigQuery のストレージに保存したデータは、指定したリージョン内の2つのゾーンに複製されます。これにより高可用性と耐久性の両方を実現しています。

マルチリージョンの意味

データセット作成時に、配置先のロケーション (リージョン) を選択します。このとき US や EU といったマルチリージョンを選択することもできます。

「マルチリージョン」という表現が誤解を呼びやすいのですが、これらのマルチリージョンを選択してもデータは1つのリージョン内にしか保存されません。選択されたマルチリージョンの中のいずれかのリージョンにデータが保存されます。マルチリージョンを選択するメリットは可用性ではなくより大きな割り当て (Quota) が利用可能になる点にあります。

クロスリージョンデータセットレプリケーション

BigQuery のデータは複数ゾーンに保存されているため、マシンレベルやゾーンレベルの障害は、サービスの可用性に影響しません。ただし、リージョンレベルの障害時に対してはクロスリージョンデータセットレプリケーションなどの利用を検討する必要があります。

クロスリージョンデータセットレプリケーションではデータセットを別のリージョンに非同期レプリケーションすることで、データの読み取り可用性を高めることができます。

2024年5月現在は Preview であることに留意してください。

以下の当社記事もご参照ください。

blog.g-gen.co.jp

災害対策(DR)

BigQuery では災害対策(DR)のために Managed disaster recovery 機能を利用することができます。ただし、当機能は BigQuery Enterprise Plus エディション以上のみで利用できることに注意してください。

当機能を使うと、BigQuery がリージョンレベルで機能停止した際に、コンピュートリソースやクエリを自動で他のリージョンにフェイルオーバーすることができます。詳細は、公式ドキュメントをご参照ください。

2024年5月現在は Preview であることに留意してください。

バックアップ

耐久性に関する考え方

前述の通り BigQuery では、必ず2つの異なるゾーンにデータを保存することから、ユーザー側で何も考慮しなくても、データの物理障害への耐久性は高いといえます。

ただし、大災害などによって Google Cloud のリージョンレベルでデータの消失が起きる可能性を考慮するのであれば、BigQuery Data Transfer Service のデータセットのコピー機能などを用いて、別のリージョンにデータセットの複製を作っておく、などの対策を検討します。

また誤削除、誤更新などの論理的な障害に備えて、後述のタイムトラベル、クローン、スナップショットなどの機能を利用することができます。

タイムトラベル

BigQuery にはタイムトラベルと呼ばれる機能が存在します。BigQuery のテーブルには、デフォルトで過去7日間のデータが保存されており、データが更新されたり削除されたりしても、保存期間内であればどの時点のデータにもアクセスすることができます。

過去データへのアクセスは、以下のような SQL によって行います。

SELECT *
FROM `mydataset.mytable`
FOR SYSTEM_TIME AS OF '2024-01-01 10:00:00'

フェイルセーフ

BigQuery にはタイムトラベルとは別に、フェイルセーフと呼ばれるデータ保存期間があります。タイムトラベル期間に加えて、さらに7日間、データが保存されます。

フェイルセーフは緊急復旧用のデータであり、復元するには Google Cloud カスタマーケア(サポート窓口)に問い合わせる必要があります。また、復元はテーブル単位になります。

保存期間はデータセットの単位で2〜7日間の範囲で設定できます。

スナップショットとクローン

スナップショットクローンという機能を使うことで、低価格で、テーブルの論理的なバックアップを作成したり、検証用の複製テーブルを作成することができます。

詳細は、以下の記事をご参照ください。

blog.g-gen.co.jp

データパイプライン (ELT)

データパイプライン・ELT とは

BigQuery を中心としたデータ分析基盤は、以下のように単純な模式図にすることができます。

データパイプライン

各種データを収集して BigQuery に投入・保存し、そのデータに SQL で変換をかけ、利用に適した形にしてから BI ツールや AI/ML 等に利用します。

この一連のデータの流れを、人手でやるのではなく、プログラムで自動化したものがデータパイプラインです。

またこの一連の処理を指して、Extract (データの抽出・収集) / Load (データベースへの読み込み) / Transform (データの変換) の頭文字を取り ELT と呼びます。アルファベットの並び順が違う ETL という用語もありますが、こちらはデータベースへの読み込み前にデータを変換するという、ELT とは順番の異なる処理を指します。BigQuery では高度な並列処理能力と SQL という学習コストの少ない言語が使えるメリットがあることから、まず BigQuery にデータを読み込んでから SQL で変換をかける「E "LT"」が採られることが多いと言えます。

この項では、BigQuery での ELT に使える各種機能や Google Cloud プロダクトを紹介します。

BigQuery Data Transfer Service

BigQuery Data Transfer Service は BigQuery に備え付きの、フルマネージドなデータ転送サービスです。設定したスケジュールに基づいて、各種取得元からデータを取得して BigQuery のテーブルにデータを取り込みます。容易に実装することができるうえ、料金も一部のコネクタを除いて無料です。

以下は、利用可能なデータソースの例です。

  • Cloud Storage
  • Google Ads (旧 AdWords)
  • Google Play
  • YouTube Channel / YouTube Content Owner
  • Amazon S3
  • Azure Blob Storage
  • Amazon Redshift

上記のように、Google Cloud 内の Cloud Storage はもちろん、Amazon S3 上のファイル等からデータを引き抜いて BigQuery にデータをロードすることができます。

この機能も、後述のスケジュールされたクエリと同様に、簡易的な ELT 処理に利用することが可能です。実行失敗通知等は、Pub/Sub へ通知したり、メールへ通知することもできます。

スケジュールされたクエリ

BigQuery では、簡単な設定で、事前定義したクエリを定期実行できます。この機能はスケジュールされたクエリ (Scheduled Queries) あるいはクエリのスケジューリングと呼ばれます。

組み込みパラメータである @run_time (実行時刻の TIMESTAMP) や @run_date (実行時刻の DATE) をクエリに渡すことができるため、簡易的な ELT 処理の実装にも役立てられます。また過去日付に基いて実行させるバックフィルも可能です。

デフォルトではクエリはスケジュールを作成した人の Google アカウント権限で実行されますが、サービスアカウント権限で実行するよう設定することも可能です。

クエリの自動実行が失敗した場合の通知方法については、以下の記事も参考にしてください。

blog.g-gen.co.jp

Dataform

Dataform は Google Cloud プロダクトの一つで、BigQuery における ELT の T (Transform、データ変換) を担うサービスです。Dataform core (SQLX) という SQL の拡張言語で変換処理を記述でき、無料で利用可能なサービスです。

詳細は以下の記事をご参照ください。

blog.g-gen.co.jp

アクセス制御

ネットワーク

BigQuery は Web API で操作するクラウドサービスであり、API エンドポイントはインターネットからアクセス可能です。そのため BigQuery は原則的にインターネット経由で利用するサービスであると言えます。API との通信は HTTPS プロトコルであり、必ず SSL/TLS で暗号化されます。

ただし限定公開の Google アクセス機能や Private Service Connect 機能を利用することで、VPC 内の VM や専用線 / IPSec VPN で Google Cloud と接続されたオンプレミス環境からプライベート IP アドレスで API を利用することも可能です。

限定公開の Google アクセスや Private Service Connect の詳細は、以下の記事をご参照ください。

blog.g-gen.co.jp

blog.g-gen.co.jp

IAM

BigQuery の認証・認可は他の Google Cloud サービスと同様、Cloud IAM の仕組みで制御されます。

BigQuery 自体はインターネットから利用可能なサービスですが、IAM により厳密な認証・認可制御がされますので、アクセスすべきでない人がデータへアクセスすることはできません。クラウドサービスは「内側」「外側」という境界型セキュリティではなく「あるべき人があるべき方法でアクセスしているか」を検査する方法でセキュリティが担保されます。

Cloud IAM 自体の仕組みは以下の記事をご参照ください。

blog.g-gen.co.jp

BigQuery のテーブルやデータセット、またジョブ投入に関する IAM 権限管理の仕組みについては、以下の記事をご参照ください。

blog.g-gen.co.jp

承認されたビューと承認されたデータセット

ビューにも IAM アクセス権限が存在します。通常は、利用者がビューへクエリを投げるとき、ビューへのアクセス権限を持っていたとしても、ビューのベーステーブルへのデータアクセス権限も持っていなければクエリは権限エラーで失敗します。

しかし承認されたビュー承認されたデータセットの仕組みを使うと、ベーステーブルへのアクセス権限が必要なくなり、アクセス権限管理をビューに集中させることができます。

機能の詳細は、以下を参照してください。

blog.g-gen.co.jp

セキュリティ関連機能

デフォルトの暗号化・CMEK 暗号化

Google Cloud では、保存される全てのデータが自動的・透過的に暗号化されます。BigQuery も例外ではなく、データは透過的に暗号化・復号されます。これをデフォルトの暗号化といいます。

デフォルトでは、暗号化は Google が管理する AES 暗号鍵で行われます。この鍵は厳密に監査された堅牢な鍵管理システムで管理されています。

一方で、監査要件への対応やより厳密なセキュリティを求める場合、顧客管理の暗号鍵 (Customer-managed Encryption Key、略称 CMEK) を用いることができます。CMEK は Cloud KMS で管理されます。

Cloud KMS の詳細は、以下の記事を参照してください。

blog.g-gen.co.jp

列レベルの暗号化

列レベルの暗号化は、Cloud KMS で管理する暗号鍵を使って、データ自体を暗号化してテーブルに格納する方法です。

デフォルトの暗号化や CMEK 暗号化とは異なり、透過的なアクセスはできず、関数で復号しなければデータにアクセスできません。詳細は以下の記事をご参照ください。

blog.g-gen.co.jp

列レベルセキュリティ・行レベルセキュリティ

IAM でデータセットレベル、あるいはテーブルレベルでアクセス制御が可能なことは先に述べたとおりです。

列レベルセキュリティ機能や行レベルセキュリティ機能を使うと、さらに細かい粒度、すなわち列レベルおよび行レベルでアクセス制御を行うこともできます。

詳細は、以下を参照してください。

blog.g-gen.co.jp

VPC Service Controls

VPC Service Controls は Google Cloud の API アクセス制御とデータ移動の制限のためのサービスです。BigQuery にも対応しており、BigQuery にアクセス可能な主体を制限したり、プロジェクト外へのデータ移動を制限することができます。

以下の記事もご参照ください。

blog.g-gen.co.jp

テーブル設計

非正規化

BigQuery をはじめとする分析用データベースでは、第3正規形以上に正規化されたテーブル設計を基本とする業務用データベースの OLTP 処理とは対象的に、分析処理 (OLAP) を最適化するため非正規化したデータモデリングを行うこともあります。

当記事ではデータウェアハウス (データマート) の非正規化のベストプラクティスまでは踏み込みませんが、通常のトランザクション処理 (OLTP) 向けデータベースとは異なる考えでデータモデリングを行う必要がある点にご留意ください。

パーティショニング・クラスタリング

BigQuery のテーブル設計ではパーティショニングクラスタリングの概念が重要です。

テーブルへのクエリは、通常はフルスキャンとなります。数 TB の規模のテーブルをフルスキャンすると処理時間がかかるほか、スキャン容量 / コンピュート処理量が多くなり、利用料金も跳ね上がります。

これを避けるため、パーティショニング列やクラスタリング列を適切に設定すると、スキャン範囲を狭め、効率的な処理に繋がります。

詳細は以下の記事を参照してください。

blog.g-gen.co.jp

Search Index

BigQuery のテーブルには、通常の RDBMS におけるインデックスの概念に近いものとして Search Index (検索インデックス) が存在します。

ただし、RDBMS で使われるように汎用的なクエリでスキャン効率を良くしたりする目的ではなく、非構造化テキストや半構造化 JSON データから特定の文字列を検索・抽出するようなクエリの効率化の目的で用いられます。

いかのようなユースケースが挙げられます。

  • システムログ、ネットワークログ、アプリケーションログ等から特定文字列を検索
  • 法的規制に準拠するため削除対象データを特定
  • セキュリティ監査
  • 検索フィルタのダッシュボード用
  • 前処理されたデータの完全一致検索

以下の公式ドキュメントもご参照ください。

また、以下の当社記事でも紹介されています。

blog.g-gen.co.jp

応用編での内容

当記事は「基本編」です。続編記事である「応用編」では以下のような事項を扱います。

  1. 外部テーブル
  2. 連携クエリ (Federated query)
  3. BigQuery Omni
  4. BigLake
  5. 非構造化データの分析
  6. チューニング
  7. 同時実行とリソース
  8. 高度なセキュリティ
    • Cloud DLP
    • 動的データマスキング
    • 列レベル暗号化
  9. コスト削減
  10. BigQuery BI Engine
  11. BigQuery ML
  12. BigQuery Dataframes

blog.g-gen.co.jp

杉村 勇馬 (記事一覧)

執行役員 CTO / クラウドソリューション部 部長

元警察官という経歴を持つ現 IT エンジニア。クラウド管理・運用やネットワークに知見。AWS 12資格、Google Cloud認定資格11資格。X (旧 Twitter) では Google Cloud や AWS のアップデート情報をつぶやいています。