Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

BigQueryAdvent Calendar 2024

Day 8
@cygkichi

BigQuery外部テーブルのスキャン量を検証！

Last updated at Posted at 2024-12-07

　この記事はBigQuery Advent Calendar 2024 8日目の記事です。

はじめに

　BigQueryにおける外部テーブルとは、BigQueryの外部に保存されたデータへクエリを実行できる便利な機能です。しかしスキャン量の仕様では通常のテーブルとは異なる点もあります。ドキュメントを見てもあまり情報がなかったため、今回実験してわかった事をまとめます。

  • 本記事の内容は、公式ドキュメントに記載された情報ではなく、実験した結果となります
  • 外部テーブルの中でも、Cloud Strage上のCSVファイルを対象とするケースに限ります

外部テーブルとは

　外部テーブルとは、Cloud StorageやGoogleDriveなどBigQueryの外部に保存されているデータに対して、まるでBigQueryテーブルが存在しているかのようにクエリすることができる機能です。データの二重管理を避けられる点などがメリットと考えます。

　BigQueryにおいて、クエリ時の課金額はスキャン量に比例して決まるためスキャン量を意識することはとても重要です。しかし、外部テーブルのスキャン量の仕様は通常のテーブルとは異なり、謎に包まれています(？)。コンソール上ではスキャン量を事前に把握することが出来ないことから、スキャン量(課金額)を意識せずに使用している人も多いのではないでしょうか？

スキャン量を事前把握できない図
※画像は、コンソール上でスキャン量を把握できていない図

スキャン量の実験

ドキュメントを調べても、具体的な仕様が見当たらなかったため実験します。

実験の準備

CSVファイルの準備

まずはデータを準備します。pythonを使用して1.4GB程度のCSVのファイルを生成します。

import pandas as pd
n = 30_000_000
pd.DataFrame({
    'id':range(n),
    'post_code':'100-0001',
    'address':'東京都千代田区千代田'
}).to_csv('address.csv', index=None)

Cloud Strageの準備

次にCloud Storageにバケットを作成して、先程のCSVファイルを20個分アップロードします。合計は27~8GBです。

# バケットの作成
gcloud storage buckets create gs://your_bucket_name --location=asia-northeast1
# 20ファイル分アップロード
for i in $(seq -w 20)
do
    gcloud storage cp address.csv gs://your_bucket_name/address_${i}.csv
done

BigQuery外部テーブルの準備

最後にBigQueryに外部テーブルを構築します。データセットの作成、スキーマの検出、テーブル作成の順番で、外部テーブルを作成します。

# BigQueryデータセットを作成する
bq mk  --location=asia-northeast1 --dataset your_dataset_name

# Cloud Storage上のファイルを指定して、スキーマを検出する
bq mkdef \
  --autodetect \
  --source_format=CSV \
  'gs://your_bucket_name/*.csv' > table_def.json

# 検出したスキーマの確認
cat table_def.json
#{
#  "autodetect": true,
#  "csvOptions": {
#    "encoding": "UTF-8",
#    "preserveAsciiControlCharacters": false,
#    "quote": "\""
#  },
#  "sourceFormat": "CSV",
#  "sourceUris": [
#    "gs://your_bucket_name/*.csv"
#  ]
#}

# BigQueryに外部テーブルを作成する
bq mk --table --external_table_definition=table_def.json \
  your_dataset_name.csv_table

実験

実験1:フルスキャン

まずフルスキャンしてみます。27.17GB程度のようです。

bq --job_id=abc001 query  --nouse_cache 'select * from   your_dataset_name.csv_table'
bq --location=asia-northeast1 show -j=true abc001
#Job <your-project-id>:abc001
#
#  Job Type    State      Start Time         Duration         User Email      Bytes Processed   Bytes Billed   Billing Tier   Labels
# ---------- --------- ----------------- ---------------- ------------------ ----------------- -------------- -------------- --------
#  query      SUCCESS   07 Dec 15:52:51   0:00:23.696000   <email_address >   29177777800       29178724352    1

実験2:LIMIT句で絞る

先頭100,000,000件のみスキャンしてみます。4.76GBのようです。フルスキャンと比べて減っていますね。

bq --job_id=abc002 query  --nouse_cache 'select * from   your_dataset_name.csv_table limit 100000000'
bq --location=asia-northeast1 show -j=true abc002
#Job <your-project-id>:abc002
#
#  Job Type    State      Start Time         Duration         User Email      Bytes Processed   Bytes Billed   Billing Tier   Labels
#
# ---------- --------- ----------------- ---------------- ------------------ ----------------- -------------- -------------- --------
#  query      SUCCESS   07 Dec 15:59:05   0:01:47.714000   <email_address >   5106111077        5106565120     1

先頭50,000,000件のみ。2.49GBのようです。さらに減っています。

bq --job_id=abc03 query  --nouse_cache 'select * from   your_dataset_name.csv_table limit 50000000'
bq --location=asia-northeast1 show -j=true abc03
#Job <your-project-id>:abc03
#
#  Job Type    State      Start Time         Duration         User Email      Bytes Processed   Bytes Billed   Billing Tier   Labels
#
# ---------- --------- ----------------- ---------------- ------------------ ----------------- -------------- -------------- --------
#  query      SUCCESS   07 Dec 16:06:53   0:00:52.926000   <email_address >   2674629600        2674917376     1

先頭10,000,000件のみ、どういうわけか0GBとなっています。こちら理由はわからなかったのですが、わかる方いましたら教えて下さい。<(_ _)>

bq --job_id=abc004 query  --nouse_cache 'select * from   your_dataset_name.csv_table limit 10000000'
bq --location=asia-northeast1 show -j=true abc004
#Job <your-project-id>:abc004
#
#  Job Type    State      Start Time         Duration         User Email      Bytes Processed   Bytes Billed   Billing Tier   Labels
#
# ---------- --------- ----------------- ---------------- ------------------ ----------------- -------------- -------------- --------
#  query      SUCCESS   07 Dec 16:08:46   0:00:09.961000   <email_address >   0                 0              0

実験2:SELECT句で列を絞る

つぎにid列に絞ります。select *のときとスキャン量は変わっていません。通常のテーブルとは異なる挙動です。

bq --job_id=abc005 query  --nouse_cache 'select id from   your_dataset_name.csv_table'
bq --location=asia-northeast1 show -j=true abc005
#Job <your-project-id>:abc005
#
#  Job Type    State      Start Time         Duration         User Email      Bytes Processed   Bytes Billed   Billing Tier   Labels
# ---------- --------- ----------------- ---------------- ------------------ ----------------- -------------- -------------- --------
#  query      SUCCESS   07 Dec 16:14:40   0:00:27.991000   <email_address >   29177777800       29178724352    1

実験3:WHERE句+limit句で行を絞る

WHERE句で制限したデータの先頭を検索してみます。id < 1000 というすぐに見つかるデータを1000件検索しています。この場合は、スキャン量は0のようです。

bq --job_id=abc006 query  --nouse_cache 'select * from your_dataset_name.csv_table where id < 1000 limit 100'
bq --location=asia-northeast1 show -j=true abc006
#Job <your-project-id>:abc006
#
#  Job Type    State      Start Time         Duration         User Email      Bytes Processed   Bytes Billed   Billing Tier   Labels
# ---------- --------- ----------------- ---------------- ------------------ ----------------- -------------- -------------- --------
#  query      SUCCESS   07 Dec 16:16:49   0:00:00.804000   <email_address >   0                 0              0

次にid>100,000,000という、該当する行がないものを1000件検索します。こちらの場合はスキャン量はフルスキャンと同じになりました。 1000件見つかるまでデータを探して、見つかったタイミングで終了しているのでしょうか？

bq --job_id=abc007 query  --nouse_cache 'select * from your_dataset_name.csv_table where id > 100000000 limit 100'
bq --location=asia-northeast1 show -j=true abc007
#Job <your-project-id>:abc007
#
#  Job Type    State      Start Time         Duration         User Email      Bytes Processed   Bytes Billed   Billing Tier   Labels
# ---------- --------- ----------------- ---------------- ------------------ ----------------- -------------- -------------- --------
#  query      SUCCESS   07 Dec 16:17:31   0:00:16.830000   <email_address >   29177777800       29178724352    1

まとめ・所感

BigQuery外部テーブルのスキャン量を実験しました。結果をまとめると以下のとおりです。

  • LIMIT句で絞ると、スキャン量は減る
  • SELCT句で列を絞っても、select *とスキャン量は変わらない
  • LIMIT句+WHERE句で条件を満たす行の一部を検索する場合、
    • WHERE句で該当する行が見つかる場合は、スキャン量が減る
    • WHERE句で該当する行が見つからない場合は、全てスキャンすることになるので減らない

　外部テーブルでスキャン量を節約する方法はあります。例えば、パーティションに分割することで、特定の列でのスキャン量を削減する方法もあります。

　私個人としては、フルスキャンを気軽にできない程度のデータ量のときは、外部テーブルは使用しないと思います。例えば数100個のCSVを外部テーブルとしたが一部のCSVが破損しており、うまく取り込まれていないことがありました。そのとき、どのデータが欠落しているのかを調べるには結局フルスキャンをしなければならず、その点がイマイチと感じたためです。

　今回はCSVしか試せていませんが、Parquetのよう圧縮できるデータも使用できるようです。データの保管量が大幅に削減できるなど、外部テーブルの良いユースケースがあるのかもしれません。

参考

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?