BigQuery に外部テーブルを定義して、GCS のファイルデータを SQL で参照してみた

2020.04.13

こんにちは、みかみです。

AWS では S3 上のデータに直接 SQL クエリを実行できる Athena や、S3上のデータと Redshift のテーブルを結合できる Redshift Spectrum がありますが、GCP でも同様に BigQuery の external テーブルを使って GCS 上のデータを SQL で参照できるようなので、確認してみました。

やりたいこと

GCS に格納したファイルに対して、BigQuery から SQL クエリを実行してデータを参照したい。

制限事項

BigQuery の external テーブルを作成する場合、外部データソースと BigQuery のデータセットは、同一リージョンで作成する必要があります。

また、データ整合性(クエリ実行中に外部データソースが更新された場合の挙動)は保証されなかったり、クエリ結果のキャッシュが効かないなどの制限事項があります。

external テーブルを作成

GCS に、以下のような CSV 形式のファイルデータをアップロードしました。

Jennifer,F,58375
Amanda,F,35821
Jessica,F,33921
Melissa,F,31636
Sarah,F,25755
(省略)

続いて、Python クライアントライブラリを使用した以下のコードで、GCS 上のファイルデータを参照する external テーブルを作成します。

from google.cloud import bigquery

client = bigquery.Client()
dataset_id = 'dataset_1'

dataset_ref = client.dataset(dataset_id)
table_id = "table_external"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("gender", "STRING"),
    bigquery.SchemaField("count", "INTEGER"),
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://test-mikami/yob1980.txt"
]
table.external_data_configuration = external_config
table = client.create_table(table)
print("Created table {}".format(table_id))
(test_bq) [ec2-user@ip-10-0-43-239 ~]$ python create_external_table.py
Created table table_external

GCP 管理コンソールから、テーブルが作成できたことが確認できました。

(が、サイズと行数が「0」? ちゃんとデータ参照できるのかな?

external テーブルに対して SQL クエリを実行

作成した external テーブルに、SQL を実行してデータを参照してみます。

通常のテーブル同様、データが参照できました。

別のデータセットの通常テーブルとの JOIN も問題なく実行できるので、SQL で SELECT 文を実行する分には、通常のテーブルと同じように使えそうです。

なお、外部データソースなので、external テーブルに対して更新や削除などの DML は実行できません。

INFORMATION_SCHEMA とメタテーブルから external テーブル情報を取得する

まずは INFORMATION_SCHEMA から、テーブル情報を参照してみます。

external テーブルなので、is_insertable_intoNO で、table_typeEXTERNAL になっていることが確認できます。

テーブルメタ情報( __TABLES__ )も確認してみます。

type3 の external テーブルのレコードは確認できましたが、row_countsize_bytes0 になっています。 データ本体を BigQuery にロードしていない外部テーブルの場合は、サイズやレコード数をメタテーブルで取得することはできないようです。

外部テーブルに対するクエリの料金

データの実態は外部ストレージに格納されているため、BigQuery 側のストレージ課金は発生しませんが、BigQuery で実行したクエリの処理データサイズに対するクエリ課金は発生します。

クエリ課金では、たとえ LIMIT で結果件数を絞ったとしても、クエリ実行に必要なデータ量全体に対して課金されるため、データ量が多いテーブルに対して SELECT * などのクエリを実行すると料金が増加してしまいます。 外部データソースでストレージ課金が発生しないとはいえ、実行クエリのコストチューニングは必要です。

また、外部テーブルに対するクエリ課金の処理データサイズは、外部ストレージの格納サイズではなく、BigQuery が実際に処理する際のデータ型のサイズで算出されるということなので、特に外部ストレージに圧縮データを格納している場合などには注意が必要です。

まとめ(所感)

テーブルスキーマも通常のテーブルと同じように定義できますし、データソースの指定もシンプルで、BigQuery の External テーブルは簡単に作成できることが確認できました。

ただし、データソースを BigQuery で管理していない分、課金に関して注意が必要かと思いました。 課金情報の監視などのコスト管理を考慮しておけば、安心して便利に利用できそうです。

参考