Cloud StorageからExternal Tableの作成

BigQuery
Author

Ryo Nakagami

Published

2024-12-11

このノートのスコープ

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

External Tableとは？

BigQueryのExternal Table（外部テーブル）は，BigQuery内にデータをコピーまたはインポートすることなく， 外部データソースに直接アクセスしてクエリを実行できる機能です．

BigQueryにおけるExternal Tableの利用形式として，permanent tableとtemporary tableの２つがあります．

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

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

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

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

以上のデータストアがBigQueryからアクセス可能な外部データソースとしてサポートされています

▶  Pricing

BigQueryのPricingは

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

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

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

が発生します．

📘 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: 営業段階でのアドホック分析

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

という場面を考えます．

▶  Key Points

  • わざわざテーブルスキーマを定義する時間はあまりない→早く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`
WITH PARTITION COLUMNS (
partition_dt DATE, -- column order must match the external path
)
OPTIONS (
uris = ['gs://project-make-dedede-great-again/pupupuland_store_pos/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://project-make-dedede-great-again/pupupuland_store_pos',
require_hive_partition_filter = false);

PARTITION COLUMNSのフィールドに partition_dtと入力していますが，これは外部ファイルのパスのパターンがgs://project-make-dedede-great-again/pupupuland_store_pos/partition_dt=yyyymmdd/data.parquet であることを前提にしています．そのため，upload時のdirectory構造を

└── partition_dt=2023-09-30
    └── purchase_history_20230930.parquet

という形にしてcloud storageにuploadしたという背景があります．DATETYPEにDATEと指定していますが，/<partition field>=2018-10-18/という構造になっていないと失敗することに注意してください． 他にも INTEGER, STRING, TIMESTAMPが指定できます．TIMESTAMPの場合は，/<partition field>=2018-10-18 16:00:00+00/という構造であることが必要です．

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

References