BigQuery で GCS外部テーブルのパフォーマンス・コスト比較

BigQuery は BigQuery 外のデータソースにアクセスできます！
Public Datasets からテストデータを作成する
- csv、gz ファイルのエクスポート
csv、gz ファイルから外部テーブルを作成する
パフォーマンス測定の条件を決める
パフォーマンス測定結果と考察
- 実行時間の比較
まとめ

BigQuery は BigQuery 外のデータソースにアクセスできます！

あまり知られていない機能かもしれませんが、BigQuery では以下のデータソースに外部テーブルとしてアクセスすることができます。

Google Cloud Bigtable
Google Cloud Storage
Google ドライブ

外部テーブルとしてアクセスする場合は BigQuery 内にデータを保存する必要がありませんので、簡単に外部データリソースとの連携が可能になります。
ただし、外部データリソースを使用する場合、BigQuery 内に保存されるネイティブテーブルとは異なる制限事項があります。また、BigQuery のクエリによる課金と、参照先サービス利用による課金が発生します。詳細については以下のページを参照ください。

【外部データソースの概要】
https://cloud.google.com/bigquery/external-data-sources?hl=ja

本記事では BigQuery 内に保存されるネイティブテーブルと GCS 外部テーブルのパフォーマンスとコストを比較します。GCS に保管するファイルは csv 形式と、csv ファイルを圧縮した gz 形式の 2つの形式のファイルを試してみます。また、BigQuery ではレガシーSQL と標準SQL がサポートされていますが、今回は推奨されている標準SQL を使用します。

Public Datasets からテストデータを作成する

BigQuery にはサンプルとして利用できるデータ(Public Datasets) が用意されているので、本記事での検証ではそのサンプルデータを利用します。

csv、gz ファイルのエクスポート

それでは外部テーブルとなる csv、gz のファイルを GCS にエクスポートします。BigQuery の WebUI 画面左のリストから [Public Datasets] を開いてみましょう。その中に [nyc-tlc:green] というデータセットの中に [trips_2014] というテーブルが見つかると思います。これはニューヨークのタクシーの移動記録として説明されているデータで、レコード数15,836,973件、サイズが 2.27GB のテーブルです。今回はこのデータを使用してみます。
[trips_2014] を選択して、

[Export Table] をクリックします。

[Export format] は csv を指定しますが、圧縮しない場合は [Compression] で [None] を選択し、gzip に圧縮する場合は [GZIP] を選択します。[Google Cloud Storage URI] では任意のバケットとファイル名を指定します。バケットの場所は、外部テーブルを作成するデータセットの Data Location と合わせる必要があるので、今回はストレージクラスが Multi-Regional で US のバケットを使用しました(ストレージクラスは Regional でも構いません)。また、エクスポートするテーブルサイズが 1GB を超える場合は分割する必要があるため、ファイル名を [trips_2014_*.csv] としてワイルドカード(“*”) を含めています。圧縮する場合のファイル名は拡張子を変えて [trips_2014_*.gz] とします。

分割してエクスポートすると、“trips_2014_000000000000.csv” のように通番が付加されたファイルが作成されます。

csv、gz ファイルから外部テーブルを作成する

次に、出力した csv ファイル、gz ファイルにアクセスする外部テーブルを作成します。
BigQuery WebUI のデータセットリストから、任意のデータセット名の右にある三角アイコンをクリックして [Create new table] を選択します(ただし、データセットは csv、gzファイルが保存されているバケットと同じ Data Location のものを使用します)。すると Create Table の画面が開くので、以下のように設定して csv と gz の 2つの外部テーブルを作成します。以下の表では設定が必要な項目のみ記載しています。

Location	Google Cloud Storage
Location	エクスポートしたファイルの GCS パス
File format	CSV
Table name	任意のデータセット
Table name	trips_2014_csv、trips_2014_gz
Table type	External table
Schema ([Edit as text] をクリックしてテキストで入力します)	[Automatically detect] チェックなし
Schema ([Edit as text] をクリックしてテキストで入力します)	pickup_datetime:TIMESTAMP,dropoff_datetime:TIMESTAMP,store_and_fwd_flag:STRING,rate_code:INTEGER,pickup_longitude:FLOAT,pickup_latitude:FLOAT,dropoff_longitude:FLOAT,dropoff_latitude:FLOAT,passenger_count:INTEGER,trip_distance:FLOAT,fare_amount:FLOAT,extra:FLOAT,mta_tax:FLOAT,tip_amount:FLOAT,tolls_amount:FLOAT,ehail_fee:FLOAT,total_amount:FLOAT,payment_type:INTEGER,distance_between_service:FLOAT,time_between_service:INTEGER,trip_type:INTEGER
Field delimiter	Comma
Header rows to skip	1

[Automatically detect] をチェックするとカラムの型を自動検出できますが、今回の trips_2014 のデータでは自動検出でうまくいかないカラムがあるので手動で設定します。また、テーブルのエクスポート機能で出力した csvファイルにはヘッダーが付加されるので、[Header rows to skip] で 1 を設定してヘッダー行をスキップします。
全てを設定したら下にある [Create table] をクリックして外部テーブルを作成します。

パフォーマンス測定の条件を決める

実行する SQL は以下の4パターンとしました。外部テーブルでは DML(INSERT、UPDATE、DELETE、MERGE) がサポートされておらず、クエリ実行時にエラーとなるため、SELECT での測定を行ないます。

全件	SELECT * FROM `各テーブル`;
WHERE句	SELECT * FROM `各テーブル` WHERE 1 < passenger_count;
1カラム	SELECT pickup_datetime FROM `各テーブル`;
集計	SELECT passenger_count, SUM(COUNT()) OVER() AS total_count, COUNT() AS data_count, COUNT() / SUM(COUNT()) OVER () AS data_count_ratio, AVG(trip_distance) AS trip_distance_avg, AVG(tip_amount) / AVG(total_amount) AS tip_amount_avg_ratio FROM `各テーブル` GROUP BY passenger_count ORDER BY passenger_count;

また、実行時の設定として、時間を測定するためにキャッシュ(Use Cached Results) を無効し、標準SQL を使うのでレガシーSQL(Use Legacy SQL) を無効にします。
この設定で、ネイティブテーブル [nyc-tlc.green.trips_2014] と、2つの外部テーブル [trips_2014_csv]、[trips_2014_gz] に対して実行します。

[RUN QUERY] をクリックしてクエリを実行し、完了すると画面上に実行時間とデータサイズが表示されるのでこの時間を測定します。

実行時間にはばらつきがあるため、今回はそれぞれの条件で5回実行した結果の平均値を取得します。

パフォーマンス測定結果と考察

上述した条件で実行した測定結果を以下にまとめます。

実行時間の比較

各テーブルに対するそれぞれの条件での実行時間平均(秒) は以下のようになりました。

パフォーマンス測定結果

	ネイティブテーブル	csv外部テーブル	gz外部テーブル
全件	42.2	52.6	60.9
WHERE句	36.2	55.7	64.8
1カラム	14.0	31.6	42.4
集計	2.6	23.6	32.0

実行時間に関する上図のパフォーマンス測定結果からは、予想通りではありますが、4パターンとも、

ネイティブテーブル < csv外部テーブル < gz外部テーブル

という順で実行時間が長くなっている結果が確認できます。特に集計クエリに関しては、ネイティブテーブルと gz外部テーブルでは10倍以上の差が出ています。
ここで、ネイティブテーブル全件クエリと csv外部テーブル全件クエリの実行計画を下図に示します(実行計画のグラフについては、WebUI にてクエリ実行後に [Details] をクリックすると表示できます)。2つの実行計画を比較すると、csv外部テーブル全件クエリでは Input に時間がかかっている(S00:Input の赤い部分が長くなっている) ことがわかります。

ネイティブテーブル全件クエリの実行計画

csv外部テーブル全件クエリの実行計画

また、パフォーマンス測定結果のグラフにおいて、集計クエリのほうが他のクエリより実行時間が短くなっているのは、出力するデータが10件しか無いためだと考えられます。下図はネイティブテーブル集計クエリの実行計画ですが、S02: Output の時間が、上述のネイティブテーブル全件クエリの S02: Output より短くなっていることが確認できます。

ネイティブテーブル集計クエリの実行計画

コストの比較
各クエリ実行時の参照データ量と課金額(US/EU の金額で算出) は以下のようになりました。ネイティブテーブルの場合は SELECT で対象とするカラムによってデータ量が変わるので、以下の表の課金額は全カラムをスキャンする全件パターンで計算しています。対して GCS外部テーブルの場合は、カラムを絞った場合でも全カラムを読み込んだ場合とデータ量が変わらない結果となりました。また、gzファイルの場合でも解凍後のデータサイズ(csvファイルと同じデータサイズ) で読み込まれるようです。従って、通常 BigQuery ではカラムを絞ってクエリを実行するので、ネイティブテーブルのほうがクエリ実行時のコストは安くなると予想できます。

	ネイティブテーブル	csv外部テーブル	gz外部テーブル
全件	2.27 GB	2.44 GB	2.44 GB
WHERE句	2.27 GB	2.44 GB	2.44 GB
1カラム	121 MB	2.44 GB	2.44 GB
集計	483 MB	2.44 GB	2.44 GB
課金額	$ 0.01135 以下	$ 0.0122	$ 0.0122

また、データの保存量に対する料金は以下のようになります。課金額(US/EU Regional にて算出) は各データ保存量を1ヶ月保存した場合の金額になります。

	ネイティブテーブル	csv外部テーブル	gz外部テーブル
データ保存量	2.27 GB	125 MB * 20ファイル = 2.5 GB	35 MB * 20ファイル = 700 MB
課金額	$ 0.0454	$ 0.05	$ 0.014

そして、データ保存量は上記のままとして、クエリ実行回数を増やしていった場合の金額合計($) は以下のようになりました。

クエリ実行回数	1	2	・・・	36	37	38	・・・	99	100
ネイティブテーブル	0.057	0.068	・・・	0.454	0.465	0.477	・・・	1.169	1.180
csv外部テーブル	0.062	0.074	・・・	0.489	0.501	0.514	・・・	1.258	1.270
gz外部テーブル	0.026	0.038	・・・	0.453	0.465	0.478	・・・	1.222	1.234