こんにちは。SCSKの山口です。
今回はBigQuery の外部テーブル機能についてのブログです。
BigQuery の外部テーブルとは？
概要
外部テーブル機能は、簡単に言うと、Google Cloud Storage (GCS) や Google ドライブなどの外部データソースに保存されているデータを、BigQuery のテーブルであるかのように直接クエリできる機能です。
実際のデータは BigQuery の管理下に置かず、外部のストレージサービス上のデータを参照します。これにより、データを BigQuery にロードする手間なくデータ分析が可能といったメリットがあります。
サポートされているデータ形式
BigQuery の外部テーブルは、以下の主要なデータ形式をサポートしています。
- CSV (Comma Separated Values)
- JSON (Newline-delimited JSON)
- Avro
- Parquet
- ORC
- Cloud Datastore のエクスポート
- Cloud Firestore のエクスポート
本記事のデモでは CSV 形式を使用します。
外部テーブルを作成する
今回は、Cloud Storage上のCSV形式のファイルを外部テーブルとして作成します。
あらかじめ、Cloud StorageのバケットにCSVファイルを配置しておきます。
BigQuery 画面で外部テーブルを作成
実際に外部テーブルを作成します。
- 対象のデータセットを選択し、「テーブルを作成」をクリックします。
- 下記を入力し、「テーブルを作成」をクリックします
- テーブルの作成元：Google Cloud Storage
- ファイルの形式：CSV
- プロジェクト：（プロジェクト名）
- テーブル：（テーブル名）
- テーブルタイプ：外部テーブル
これで作成完了です。
詳細を見ると、ソースのURIなどの詳細情報が表示されます。
外部テーブルのクエリ実行
ここから、外部テーブルに対して様々なクエリを実行してみます。
全データを取得してみる
SELECT * FROM `yamaguchi_test_exttable.ext_table_GCS`;
問題なくデータが閲覧できました。
フィルタリングしてみる
SELECT id, name, value FROM `yamaguchi_test_exttable.ext_table_GCS` WHERE value > 20 AND name LIKE '%e';
数値型、文字型ともにフィルタリングできました。
集計関数を使ってみる
SELECT AVG(value) AS average_value, MAX(value) AS max_value, MIN(value) AS min_value, COUNT(*) AS total_records FROM `yamaguchi_test_exttable.ext_table_GCS`;
集計関数も問題なく使用できました。
外部テーブルでできない事
ここまで外部テーブルでできることを列挙してきましたが、ここからはできない事を書きます。
具体的には、以下のデータ操作言語 (DML) ステートメントは外部テーブルに対して実行できません。
INSERT(データの挿入)UPDATE(データの更新)DELETE(データの削除)MERGE(データの結合と更新/挿入)TRUNCATE TABLE(テーブル内のすべての行を削除)
外部テーブルのデータに対する変更は、元の外部データソースに対して行う必要があります。例えば、GCS 上の CSV ファイルの内容を変更したい場合は、BigQuery ではなく GCS のファイルを直接編集する必要があります。
外部テーブルの主な用途は、外部に存在するデータを BigQuery の強力なクエリエンジンを活用して分析することです。データの永続的な保存や頻繁な更新を伴う用途には、BigQuery のマネージドストレージ（通常のテーブル）の利用が推奨されます。
実際に外部テーブルに対してDMLステートメントを実行しようとすると、
エラーではじかれます。
まとめ
BigQuery の外部テーブル機能は、GCS 上のデータを BigQuery にロードすることなく、手軽に分析できる非常に強力なツールです。データの柔軟性、迅速な分析開始、コスト効率などのメリットがあり、データレイクとの連携や ETL パイプラインの一部としても有効に活用できます。
今回のデモを通じて、外部テーブルの作成からクエリ実行までの基本的な流れをご理解いただけたかと思います。ぜひ、BigQuery の外部テーブル機能を活用して、より効率的なデータ分析を試してみてください。