G-gen の杉村です。当記事は BigQuery について徹底的に解説する記事の応用編です。BigQuery に初めて触れる方はまずは基本編の記事を、ぜひご参照ください。

基本編の記事
外部データ連携の概要図
外部テーブル
連携クエリ (Federated query)
BigQuery Omni
BigLake
非構造化データの分析
チューニング
同時実行とリソース
高度なセキュリティ
コスト削減
- コスト削減のベストプラクティス
- 上限設定
BigQuery BI Engine
BigQuery ML
- 利用例
- 料金
BigQuery DataFrames

基本編の記事

当記事は BigQueryを徹底解説する記事の「応用編」です。基礎的な内容は「基本編」で解説しています。基本編で扱った内容は、以下のとおりです。

概要
料金
コンポーネント
データのロード
データのクエリ
エクスポート
可用性と耐久性
バックアップ
データパイプライン (ELT)
アクセス制御
セキュリティ関連機能
テーブル設計

blog.g-gen.co.jp

ここからは、応用編としてさらに詳細な内容に踏み込んでいきます。

外部データ連携の概要図

BigQuery は、内部ストレージに持っているデータだけでなく、外部サービスのデータをクエリすることが可能です。応用編記事ではいくつかの仕組みについて解説します。概要は以下の図のようになっています。

この図に表されている外部テーブル、連携クエリ (Federated query)、BigQuery Omni、BigLake については、それぞれ当記事内で解説します。

外部テーブル

外部テーブルとは

外部テーブルは BigQuery ストレージの外にあるデータを BigQuery から直接クエリできる仕組みです。以下のようなデータソースに対応しています。

Cloud Storage
Cloud Bigtable
Google ドライブ

外部テーブルではスキーマ定義とメタデータだけを持ち、データの実体は外部に置いたままです。そのため BigQuery 内部のデータをクエリするよりも、パフォーマンスは劣ります。その代わり、データを定期的に BigQuery にロードすることなく最新のデータが得られたり、ELT パイプライン構築の必要性が無いというメリットがあります。

ただし、特に Cloud Storage への外部テーブル定義の場合、外部テーブルの発展系である BigLake テーブル (後述) の利用が推奨されています。BigLake テーブルのほうが、よりきめ細かい制御が可能なうえ、テーブルのアクセス制御とデータソースへのアクセス制御を分離できるため、運用オーバヘッドの削減にもなります。

参考 : 外部テーブルの概要

用途

用途としては、ELT 処理の中で外部テーブルに対して CREATE TABLE xxx AS SELECT 〜 や INSERT INTO xxx SELECT 〜 を行い BigQuery 内部へのデータロードを行ったり、あるいは頻繁に変更がある外部データを都度 BigQuery に取り込むことなく BigQuery での分析に使うなどが挙げられます。

Cloud Storage 外部テーブル

Cloud Storage に対する外部テーブルでは、以下のファイル形式に対応しています。

CSV
JSON (改行区切り)
Avro
ORC
Parquet
Datastore エクスポート
Firestore エクスポート

例として、業務システムから定期的に Cloud Storage へデータをアップロードするような仕組みにしておき、BigQuery からは定期的に Cloud Storage 上のファイルを外部テーブルから INSERT INTO xxx SELECT 〜 するパイプラインを構築すれば、疎結合なデータ受け渡し場所として Cloud Storage が利用可能です。

参考 : Cloud Storage 外部テーブルを作成する

Google ドライブ外部テーブル

Google ドライブに対する外部テーブルでは、以下のファイル形式に対応しています。

CSV
JSON (改行区切り)
Avro
Google Sheets (スプレッドシート)

CSV や JSON のほか、Google Sheets (スプレッドシート) のデータもテーブルとして認識できます。そのため、従業員が普段使っているスプレッドシートの台帳をテーブルとして使い、ELT や分析に使うことが容易に可能になります。外部テーブルへクエリすると、常に最新のデータを見に行くため、リフレッシュのためのバッチ処理が不要です。

参考 : ドライブデータをクエリする

Bigtable 外部テーブル

Bigtable は Google Cloud のマネージドな NoSQL データベースサービスです。

Bigtable は行と列の概念を持ちますが NoSQL であるため独特のスキーマ構造をしています。詳細は以下のドキュメントをご参照ください。

参考 : Bigtable の外部テーブルを作成する

連携クエリ (Federated query)

連携クエリ (Federated query) は BigQuery から他のデータベースエンジンへクエリ文を送信し、結果を返してもらう機能です。

外部テーブルとの違いは、クエリの相手先がデータベースエンジンであり、相手先のコンピュートリソースを使って処理された結果を BigQuery が受け取るという点です。

以下のデータソースに対応しています。

Cloud Spanner
Cloud SQL

BigQuery クエリを実行するロケーションと Cloud Spanner / Cloud SQL のロケーションは同じ (BigQuery 側がマルチリージョンの場合、同じ国内) である必要があります。

参考 : 連携クエリの概要

BigQuery Omni

BigQuery Omni とは

BigQuery Omni は他のクラウドサービスとデータ連携を行い、BigQuery でのデータ分析を可能にする仕組みです。

データは Amazon S3 や Azure Blob に置いたまま、BigQuery のコンピュート能力を活かしてデータを分析したり、あるいは BigQuery へデータをロードすることもできます。

他クラウドへの認証は後述の BigLake の仕組みを用います。AWS の IAM ロール等の認証情報を接続 (Connection) に登録して認証します。

参考 : BigQuery Omni の概要

対応サービス

BigQuery Omni は以下のサービスに対応しています。

Amazon S3 (Amazon Web Services)
Azure Blob Storage (Microsoft Azure)

注意点

業務システムを Amazon Web Services (AWS) に配置し、データ分析基盤を Google Cloud (BigQuery) に置いているようなユーザであれば特に有用と見えるこの機能ですが、特に日本のユーザに対する注意点としては、Amazon S3 / Azure Blob ともに日本国内のリージョンに未対応であることです。以下の公式ドキュメントををご参照ください。

参考 : BigQuery Omni locations

また BigQuery Omni では通常のクエリとは異なる料金体系が適用されることに注意が必要です。

参考 : BigQuery Omni pricing

さらに、データソース側のクラウド (AWS や Azure) 側の通信料金にも注意が必要です。各クラウドサービスでは、データが外に出ていく時にデータサイズに応じて課金が発生することが一般的です。

BigLake

BigLake とは

BigLake は、アクセス権限の委任を使用して、BigQuery 外部のストレージにあるデータをクエリするための仕組みです。BigLake の仕組みで作成されたテーブルは BigLake テーブルと呼ばれ、外部テーブルの発展系とされています。

BigLake テーブルでは「ユーザからテーブルへのアクセス権限」と「テーブル (BigQuery) からデータソースへのアクセス権限」を分けて考えます。テーブルをクエリするユーザは、BigLake テーブルへのアクセス権限だけがあればよく、データソースからテーブルへデータを取得する権限は接続 (Connection、基本編参照) に紐付けられたサービスアカウントの権限で行われます。

なお BigQuery Omni を使うテーブルは常に BigLake テーブルです。接続 (Connection) に、AWS の IAM ロールの認証情報等を持たせます。

参考 : BigLake テーブルの概要

データソース

BigLake テーブルに対応しているデータソースは、以下です。

Cloud Storage
Amazon S3 (BigQuery Omni)
Azure Blob Storage (BigQuery Omni)

通常の外部テーブルとの違い

通常の外部テーブルでは、テーブルをクエリするユーザは、テーブルへのアクセス権限と同時にデータソースへのアクセス権限も持つ必要がありました。

すなわち、通常の外部テーブルと BigLake テーブルの違いは、クエリを行うユーザがデータソースへのアクセス権限を持っている必要があるか、ないか、という点になります。

利点

BigLake の最大の利点は、権限管理運用の簡素化です。

通常の外部テーブルでは、テーブルを新規作成したり、あるいは既存の外部テーブルへアクセスできる人を増やす・減らす場合などに「Cloud Storage 等のデータソースへのアクセス権限」と「テーブルへのアクセス権限」の両方を編集する必要があります。BigLake ではこれらの権限が分離され疎結合になるため、運用の簡素化に繋がります。

また、BigLake テーブルではデータソースへのアクセス権限とテーブルレベルのアクセス権限を分けられるため、以下のような機能が使えるようになります。

行レベル・列レベルセキュリティ
動的データマスキング (Cloud Storage のみ)

BigLake テーブルでなければ、ユーザはデータソースのファイルへのアクセス権限をまるごと持つため、上記のような細かい制御は意味を成しません。ユーザのアクセス権限をテーブルレベルで制御できるため、上記のような細かい粒度でのアクセス制御ができるようになります。

これらの利点から、特に Cloud Storage への外部テーブルを作成する場合は、BigLake テーブルを利用することが推奨されています。

参考 : オブジェクトストア上の BigLake テーブル

権限の持たせ方

接続 (Connection) を作成する際に、BigLake の種類を選択します。接続先環境によって、権限を持たせるための手順は異なります。

Cloud Storage 用の BigLake 接続を作成すると、サービスアカウント ID が払い出されるので、そのサービスアカウントに、対象の Cloud Storage の読み取り権限を持たせます。

Amazon S3 用の BigLake 接続では、IAM ロールの ARN を指定し、IAM ロール側の信頼関係ポリシーで接続の ID を信頼します。

Azure Blob では Federated Identity の利用有無により、方法が異なります。

それぞれ、詳細は公式ドキュメントをご参照ください。

BigQuery コネクタ

BigQuery コネクタ (BigLake コネクタ) は、BigLake の追加機能の一つです。

BigQuery コネクタにより Apache Spark、Apache Hive、TensorFlow、Trino、Presto など、BigQuery 以外のデータ処理ツールから、BigLake テーブルへのアクセスが可能になります。

参考 : コネクタ

非構造化データの分析

オブジェクトテーブル

オブジェクトテーブルは Cloud Storage 内の非構造化データを分析するための仕組みです。オブジェクトテーブルは Cloud Storage 内のオブジェクトのメタデータを格納し、1行が1オブジェクトに対応します。

非構造化データとは、RDB のテーブルのように型 (type) を含むスキーマが決まった構造化データや、CSV や JSON のように型が決まっていないが構造を有する半構造化データとは異なり、スキーマが決まっていないデータを指します。すなわち、画像、動画、PDF などを指します。

オブジェクトテーブルはこれらのデータのメタデータを管理します。また仮想列である data にアクセスすることで、そのデータの RAW バイトにアクセスすることができます。これは BigQuery ML (後述) によって利用されます。

参考 : オブジェクトテーブルの概要

実現できること

オブジェクトテーブルの利用により、Cloud Storage 内の非構造化データに対して以下のようなことが実現できます。

BigQuery ML による画像の推論
リモート関数との組み合わせ
- Cloud Vision API による画像の推論
- Cloud Translation API による翻訳
- Cloud Natural Language API による感情分析
- Apache Tika を用いた PDF のメタデータ抽出

アクセス制御

オブジェクトテーブルはデータソースへのアクセスに BigLake の仕組みを利用します。BigQuery は接続 (Connection) に紐付けられたサービスアカウント権限を使って Cloud Storage にアクセスします。

チューニング

クエリプラン(実行計画)

BigQuery でも、他の DBMS では EXPLAIN で取得できるようなクエリプラン (実行計画) の確認が可能です。

クエリジョブが投入されると、BigQuery は SQL を複数のクエリステージに分割し、さらにクエリステージは細かいステップに分割されます。複数のワーカーがクエリステージを並列実行するため、高速に処理されます。またクエリジョブの進捗はタイムラインで表現され、保留中・実行中・完了済の作業単位が確認できます。

クエリプラン・タイムラインは Google Cloud コンソールでジョブごとに確認できます。「実行の詳細」タブでは詳細を、「実行グラフ」タブではグラフィカルなフロー図を確認できます。

参考 : クエリプランとタイムライン
参考 : In-memory query execution in Google BigQuery

クエリ・テーブルの最適化

想定よりもクエリが遅い場合は、クエリプラン・タイムラインを参考に、SQL をチューニングします。また SQL の記述方法やテーブル設計のベストプラクティスに沿うことも重要です。例として、以下のようなベストプラクティスがあります。

テーブルを非正規化してネスト (STRUCT) 列・REPEATED 列を使う
WHERE 句は STRING や BYTE より BOOL INT FLOAT DATE の方が高速
REGEXP_CONTAINS() ではなく LIKE を使用
不要な列を SELECT しない
パーティションやクラスタリングを使用
JOIN の前にデータを減らす
複雑で長大なクエリをマルチステートメントクエリに分割し一時テーブルを活用
自己結合よりウインドウ関数

以下のドキュメントを参照してください。

参考 : クエリパフォーマンスの最適化の概要

主キー制約・外部キー制約

BigQuery には実は主キー制約 (Primary Key Constraints) と外部キー制約 (Foreign Key Constraints) が存在します。

しかし、これらの制約に強制力は無く (NOT ENFORCED)、主キー制約を入れた列は重複できますし、外部キー制約を入れた列も行の追加・削除等に制限がかかりません。これらの制約の存在理由は、実行計画 (クエリプラン) の最適化にあります。

BigQuery で主キー制約・外部キー制約を定義すると、それらに基づいてクエリ・オプティマイザがクエリプランを最適化し、JOIN 処理が高速化やスキャンボリュームの最適化に繋がる可能性があります。クエリプランの最適化は、コンピュート料金の最適化にも繋がります。

ただし「主キー制約をかけた列は NULL でなく一意であること」また「外部キー制約をかけた列は参照先テーブルで主キーであること」などの本来の制約条件はシステム的には強制されないため、これを守る義務はユーザー側にあります。データが制約に違反している場合、誤った結果が返される恐れがあるとされています。

詳細な概念として Inner Join Elimination 、Outer Join Elimination、Join Reordering が以下の公式ブログで紹介されています。

参考 : Join optimizations with BigQuery primary keys and foreign keys

同時実行とリソース

クエリの同時実行

BigQuery はサーバーレスのサービスであり分散アーキテクチャを採用しているため、高度な並列処理が可能です。

クエリの同時実行数は、オンデマンドモードの場合はプロジェクトごとに、BigQuery Editions を使っている場合は予約 (Reservation) ごとに決まります。

オンデマンドモードのプロジェクトでは、クエリの最大同時実行数は動的に決まります。

一方で予約 (Reservation) を使っているプロジェクト、すなわち Editions が割り当てられているプロジェクトでは、最大同時実行数を明示的に設定できます。

参考 : Use query queues

フェアスケジューリング

クエリが並列で実行される際、リソース (スロット) の割り当ては BigQuery によって動的に決まります。この割り当ての仕組みをフェアスケジューリングと呼びます。

フェアスケジューリングにより、プロジェクト間やジョブ間で自動的にスロットが分配されます。スロットを大きく要するクエリには大きなスロット数が、そうでないクエリには少ないスロット数が動的に割り当てられます。

参考 : BigQuery のフェアスケジューリング

クエリキュー

オンデマンド / Editions に関わらず、クエリキューの概念があります。前述の仕様で決定した最大同時実行数を超えたクエリは、キューに溜まります。

インタラクティブクエリの場合は最大 1,000、バッチクエリの場合は最大 20,000 クエリがキューに滞留することができ、それを超えたクエリ投入はエラーになります。

参考 : Use query queues

高度なセキュリティ

基本編で解説したセキュリティ機能

基本編でもいくつかのセキュリティ機能を解説しています。基本編の以下の見出しをご参照ください。

Cloud DLP

Cloud DLP (Cloud Data Loss Prevention) は機密性の高いデータを自動的に検出し、保護するためのフルマネージドサービスです。BigQuery とは別の Google Cloud サービスですが、BigQuery とは綿密に連携できます。

Cloud DLP を使うと、以下の二通りの方法で機密情報の位置を特定できます。

データプロファイラの作成 (Cloud DLP が BigQuery テーブルを自動でスキャンして作成)
オンデマンド検査 (明示的に指示してテーブルまたは列をスキャン)

データのプロファイルを行うと「リスクレベル」「機密性」などのインサイトがプロジェクトレベル、テーブルレベル、列レベルで得られます。

単一プロジェクトに対してプロファイルを行うことも、組織やフォルダレベルで指定して配下プロジェクト全体にプロファイルを行うことも可能です。

参考 : Cloud DLP を使用した BigQuery データのスキャン
参考 : BigQuery データのデータプロファイル

動的データマスキング

動的データマスキングは、セキュリティ目的で、クエリ結果を列レベルで動的にマスキングする機能です。

列レベルでアクセス制御をするという点で「列レベルのセキュリティ」と似ていますが、同機能では権限が無いアカウントがアクセス制御がかかった列にアクセスしようとすると権限エラーとなる一方で、動的データマスキングではエラーとならずに難読化 (null 化、マスキングで意味のない値にする、ハッシュ化など) された値が返ってきますので、既存の SQL を変更する必要がないという利点があります。

この機能は列レベルのアクセス制御と同様に分類 (Taxonomy) やポリシータグを利用しますので、これらの概念については参考ドキュメントをご参照ください。

参考 : 動的なデータマスキングの概要
参考 : BigQueryの列レベル・行レベルのセキュリティを解説

列レベル暗号化

Cloud KMS で管理する暗号鍵を利用して、透過的な暗号化とは別に、列レベルの暗号化をすることができます。

これは、データを暗号化する鍵、すなわち Data Encryption Key (DEK) とその鍵自体を暗号化する鍵である Key Encryption Key (KEK) を分けることで、キーの漏洩リスクを下げる手法で行われます。KEK は Cloud KMS で管理され、そのアクセス管理は IAM によって行われます。

暗号化は INSERT/UPDATE 時の暗号化関数によって行われます。確定的 (deterministic) な暗号化関数と非確定的 (non-deterministic) な暗号化関数があり、前者はインプットが同じであれば暗号文が同一になり、後者はインプットが同じでも、出力される暗号文が異なるものになります。前者の確定的暗号化を用いると、暗号化済みのテキストを使って集約や JOIN などの分析処理を行うことができます。

復号は SELECT 時に復号関数を用いることで行われます。クエリするユーザは、対象のテーブル・列に対するアクセス権限と同時に、Cloud KMS 鍵に対する権限も持っていなければ、データを復号することはできません。

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

blog.g-gen.co.jp

参考 : Cloud KMS を使用した列レベルの暗号化
参考 : AEAD 暗号化のコンセプト

コスト削減

コスト削減のベストプラクティス

BigQuery にはコスト削減のためのベストプラクティスも存在します。以下の記事をご参照ください。

blog.g-gen.co.jp

上限設定

BigQuery のオンデマンド課金が一定以上にならないように、クエリ量に上限設定をすることが可能です。以下をご参照ください。

blog.g-gen.co.jp

BigQuery BI Engine

BI Engine とは

BigQuery BI Engine (または単に BI Engine) とは、Looker Studio や Looker、Tableau などの BI ツールを主な対象として、クエリ結果をメモリにキャッシュすることで高速化する機能です。

BI Engine は BigQuery API と統合されており、BI ツールや API などから BigQuery が利用された際には自動的に BI Engine のキャッシュが適用されるので、アプリケーション側に変更が必要ありません。

参考 : BI Engine とは

料金

BI Engine では、予約というオブジェクトをプロジェクト内作成することでメモリ容量が確保されます。この確保した容量の GB 数に応じて課金が発生します。

2023年6月現在の東京リージョンでは $0.0499 / GB / hour です。これは、1ヶ月に $37.1256/GB に当たります。

また BigQuery Editions でコミットメントを購入した場合、購入スロット数に応じて無料の BI Engine の GB 枠が利用可能になります。

最新の料金は以下の公式ページをご参照ください。

参考 : BI Engine pricing

活用のテクニック

BI Engine では特にキャッシュを利用させたいテーブルを指定して優先テーブルとしてマークできます。

また BI Engine を活用するテクニックとして、BI Engine を使用させたいクエリだけを別プロジェクトに切り出し、そのプロジェクトで BI Engine 予約を作成し、キャッシュを使わせたいクエリをそのプロジェクトに集中させるという方法もあります。これを優先テーブルと組み合わせることで、確保した BI Engine 予約を有効に活用することができます。

参考 : BI Engine に関する考慮事項

注意点

注意点として、BI Engine ではワイルドカードテーブルを参照するクエリをサポートしていません。

また他にも外部テーブルへのクエリやユーザ定義関数など、サポートされていない機能もあります。

参考 : サポートされていない機能

BigQuery ML

BigQuery ML とは BigQuery の SQL インターフェイスを使って機械学習モデルをトレーニングしたり、推論を実行できる機能です。もちろん、トレーニングや推論には BigQuery 内のデータをシームレスに利用できます。

機械学習の専門知識がなくても、SQL による呼び出しで、以下のような組み込みモデルをトレーニング可能です。

線形回帰
ロジスティック回帰
K-means クラスタリング
行列分解 (Matrix factorization)
主成分分析 (PCA)
時系列予測

また Vertex AI や AutoML でトレーニングしたモデルを利用することも可能です。こちらではディープニューラルネットワークやランダムフォレスト、ブーストツリーなども利用できます。

BigQuery ML のモデルを Vertex AI Model Registry に登録して、オンライン推論用のエンドポイントにデプロイすることも可能です。

参考 : BigQuery ML とは

利用例

以下のように SQL 構文で BigQuery ML を利用することができます。以下は、モデル作成の例です。

#standardSQL
CREATE MODEL `bqml_tutorial.sample_model`
OPTIONS(model_type='logistic_reg') AS
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'

上記は bqml_tutorial データセットの中に sample_model というモデルを作成しています。

パブリックデータセット上のデータ bigquery-public-data.google_analytics_sample.ga_sessions_* を利用してロジスティック回帰モデルをトレーニングしています。

推論は以下のように行います。

#standardSQL
SELECT
  country,
  SUM(predicted_label) as total_predicted_purchases
FROM
  ML.PREDICT(MODEL `bqml_tutorial.sample_model`, (
SELECT
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(geoNetwork.country, "") AS country
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY country
ORDER BY total_predicted_purchases DESC
LIMIT 10