Cloud StorageからExternal Tableの作成

BigQuery
Author

Ryo Nakagami

Published

2024-12-11

Modified

2025-07-19

このノートのスコープ

  • Google Cloud StorageからBigQuery上にExternal tabaleを作成する
  • Partition Fieldが定義されたExternal tabaleを作成するための条件を確認する

External Tableとは？

Definition 1 External Table

  • BigQueryのExternal Table（外部テーブル）は，BigQuery内にデータをコピーまたはインポートすることなく， 外部データソースに直接アクセスしてクエリを実行できる機能
  • BigQueryにおけるExternal Tableの利用形式として，permanent tableとtemporary tableの２つがあります

permanent tableとtemporary table

  • permanent table: BigQuery dataset内に作成され，外部データソースにリンクされるテーブル
  • temporary table: 外部データソースに対して一時的にクエリを実行する場合に利用されるテーブル
Note

一時的なテスト処理などでtemporary tableを用いることは有用ですが，分析者間でクエリやコードを共有して分析を進める場合はpermanent tableを用いないと テーブルアクセスを共有できません．

サポートされているデータストア<

BigQueryからアクセス可能な外部データソースとしてサポートされているデータストアは以下:

  • BigLake
  • Cloud Storage
  • Bigtable
  • Google Drive
  • Amazon S3
  • Azure Blob Storage

Pricing

BigQueryのPricingは

  • Compute pricing: クエリの処理にかかる費用
  • Storage pricing: データ保存費用

の２つから構成されています．External Tableを用いると，後者の方はCloud Storage換算の費用となるため，一般的には ストレージコストを抑えることができます．ただし，External Tableにアクセスして分析するたびに

  • Query cost: クエリ計算処理時間に比例するコスト
  • Bytes cost: External tableからのデータ読み取りサイズに比例するコスト

が発生します．

Pricing費用低減のTips

External Tableを用いた分析が一時的なものであるならばExternal Tableを用いたほうが良いと思いますが， 頻繁にBigQueryからアクセスする場合は

  • Partitioning/Clusteringを用いてアクセスサイズを抑える
  • SELECT以下で*を使用せず，分析に用いるカラムを明示的に指定して，スキャンサイズを抑える

という工夫，またはBigQuery native tableとしてデータを保持することを検討したほうが良いと思います．

External Table use cases

External Tableの特徴

  • BigQuery native tableとしてデータは保持しない
  • External Tableと紐づく外部ストアの最新データに対して，クエリ処理が走る

があります．そのため，PoCや営業段階での分析や頻繁にupdateされるデータに対してのオンライン分析といった場面で活用されるケースが多いです．

Example 1 営業段階でのアドホック分析

  • BtoC向けサービスを展開している企業Aに対して営業をしているとする
  • NDAを結んだ後に，企業Aが保有しているデータのサンプルとして２年間分の連携してもらった
  • このデータを用いて簡易的な分析を行い，どのようなインサイトが導けそうか？を次の営業会議で企業Aに伝え説得したい

という場面を考えます．このとき，

  • わざわざテーブルスキーマを定義する時間はあまりない→早くEDAをやりたい
  • サンプルデータなので，プロジェクト受注後に連携されるであろうデータセットよりも情報量が少ない→わざわざテーブルスキーマしても再利用可能性は低い

External Tableを利用することで以下のようなメリットを享受することができます:

  • テーブルスキーマをわざわざ定義しなくても，BigQueryを用いた大容量のデータに対しての分析が可能
  • 複数の分析者間でサンプルデータをCloud Storageベースで簡単に共有できる
  • Pythonなどの言語を用いた分析でも，予めBigQueryで前処理して小さいdatamartを作成し，それに対してEDAが実施できる

External Table作成

ここでは，EXTERNAL PARTITION TABLEの作成を目的に

  1. google cloud storageへのupload
  2. EXTERNAL TABLEの作成

の２段階の流れで説明します．

Cloud Storageへのupload

~/pupupuland_store_posというdirectory以下に，yyyymmdd形式の日付でpartitionされたPARQUET形式のデータがあるとします．

pupupuland_store_pos
├── partition_dt=2023-09-23
│   └── purchase_history_2023-09-23.parquet
├── partition_dt=2023-09-24
│   └── purchase_history_2023-09-24.parquet
├── partition_dt=2023-09-25
│   └── purchase_history_2023-09-25.parquet
├── partition_dt=2023-09-26
│   └── purchase_history_2023-09-26.parquet
├── partition_dt=2023-09-27
│   └── purchase_history_2023-09-27.parquet
├── partition_dt=2023-09-28
│   └── purchase_history_2023-09-28.parquet
├── partition_dt=2023-09-29
│   └── purchase_history_2023-09-29.parquet
└── partition_dt=2023-09-30
    └── purchase_history_2023-09-30.parquet

このディレクトリ構造を保持したまま，gs://project-make-dedede-great-again/というcloud storage bucketに格納したいとします．このとき

gcloud storage cp -r ~/pupupuland_store_pos/ gs://project-make-dedede-great-again/

以上で下準備は完了です．

External Tableの作成

Google Cloud project pupupuland-businessdata の dedede-holdings dataset以下に先程上げた pupupuland_store_pos tableを作成したいと思います．

CREATE EXTERNAL TABLE `pupupuland-businessdata.dedede-holdings.pupupuland_store_pos`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://project-make-dedede-great-again/pupupuland_store_pos/*'],
  hive_partition_uri_prefix = 'gs://project-make-dedede-great-again/pupupuland_store_pos/', -- 末尾にスラッシュを追加
  require_hive_partition_filter = false
);

require_hive_partition_filter フィールド

  • require_hive_partition_filter フィールドはクエリ時にpartition_dtによるWHERE句フィルタリングを強制するか否かの設定フィールドです．
  • デフォルトではfalseとなっており，この場合はフィルタリングしなくてもクエリできることを意味します．

Appendix: Hive形式

Definition 2 Hive形式

  • <ベースパス>/<列名>=<値>/<ファイル名>という構造を用いたファイルパスベースのパーティション構造
gs://bucket/retail_data/partition_month=2022-07-01/part-0001.parquet
gs://bucket/retail_data/partition_month=2022-08-01/part-0001.parquet

という形式でparquetが格納されているときに, hive_partitioning_mode=AUTO を指定して外部テーブルを作成すると

  • partition_month 列が自動で追加される
  • WHERE partition_month = '2022-07-01' のようなクエリが使える
  • クエリ効率もよくなる（パーティションプルーニング）

というメリットがあります．

References