目次
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] を選択して、
分割してエクスポートすると、“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 |
---|---|
エクスポートしたファイルの GCS パス | |
File format | CSV |
Table name | 任意のデータセット |
trips_2014_csv、trips_2014_gz | |
Table type | External table |
Schema ([Edit as text] をクリックしてテキストで入力します) |
[Automatically detect] チェックなし |
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 |
全てを設定したら下にある [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] に対して実行します。
実行時間にはばらつきがあるため、今回はそれぞれの条件で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 の赤い部分が長くなっている) ことがわかります。
また、パフォーマンス測定結果のグラフにおいて、集計クエリのほうが他のクエリより実行時間が短くなっているのは、出力するデータが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 |
クエリ回数が少ない場合の合計は csv外部テーブルが高く、gz外部テーブルが一番安いのですが、今回の検証では、36回以上クエリを実行するとネイティブテーブルが一番安くなっています。カラムを絞ったクエリであれば、回数が増えるとネイティブテーブルのほうが安くなることが想定されます。
まとめ
本記事では BigQuery のネイティブテーブルと、GCS の csv および gz の外部テーブルのパフォーマンス、コストの比較を行ないました。その結果、パフォーマンスについては以下のようなことが確認できました。
- 予想通りネイティブテーブルが1番速く、次に csv外部テーブルが速い
- 通常、使用すると想定される集計クエリについてはネイティブテーブルは外部テーブルより10倍以上速い
また、コストについては、
- クエリ実行回数が少ない場合、GCS の gzファイルを使った外部テーブルが一番安く、次にネイティブテーブルが安い
- カラムを絞ったクエリであれば、実行回数が増えるとネイティブテーブルのほうが一番安くなる
などが確認できました。
これらの結果から、例えば、
- パフォーマンスを重視する、またはカラムを絞ったクエリの実行回数が多い場合はネイティブテーブルを使う
- パフォーマンスが悪くても良く、かつ、クエリ実行回数が少ない場合はコストを下げるために gz外部テーブルを使う
- 一定期間はネイティブテーブルとして利用し、ある程度利用頻度が下がったデータは gz外部テーブルとして切り出してしまう、というのもコスト重視ではあり
などの使い分けも考えられます。要件によって使い方はいろいろ考えられると思いますが、ご検討いただく際に本記事が参考になれば幸いです