BigQueryで150万円溶かした人の顔

※ かなり前の記事ですが、未だに引用されるので一応追記しておきます。タイトルと画像がキャッチーなのはちょっと反省していますが、これを見てBigQuery使うのを躊躇している人は多分あまり内容を読んでいないので気にする必要はないです。自分は当時の会社でも今の会社でも個人でも普通にBigQuery使っていて解析用データなどはBigQueryに入れる設計をよくしています。また、アドベントカレンダーだったのでネタっぽく書きましたが事前に想定できる金額です。

※ 代役：プロ生ちゃん（暮井 慧）

巷のBigQueryの噂と言えば「とにかく安い」「数億行フルスキャンしても早い」などなど。とりわけ料金に関しては保存しておくだけであれば無視できるほど安く、SQLに不慣れなプロデューサーがクエリを実行しても月数ドルで済むなど、賞賛すべき事例は枚挙に暇がありません。

しかし、使い方によってはかなり大きな金額を使ってしまう可能性もあります。

（追記） BigQueryを検討している方へ

BigQueryの料金

料金の詳細な説明については下記のリンク先にあるので省きますが、今回重要なのはデータの取得に対して比較的大きな料金が発生する点です。

公式：https://cloud.google.com/bigquery/pricing
参考になる記事：BigQuery の課金仕様と注意点をまとめてみた（2015-07 時点）

経緯

BigQueryの用途

今回作業の対象としたBigQueryのテーブルは、ログの保存用として利用しているテーブルです。

[
  {
    "name": "time",
    "type": "INTEGER"
  },
  {
    "name": "tag",
    "type": "STRING"
  },
  {
    "name": "message",
    "type": "STRING"
  }
]

上記は実際のスキーマではありませんが、大体こんな感じのテーブルです。STRING型でJSONを保存しておくとスキーマレスっぽい運用ができてログの保存などに向いています。

SELECT JSON_EXTRACT_SCALAR(message, '$.name') as name FROM [table_name]

参考になる記事：
BigQuery をスキーマレスっぽく運用する方法
Fluentd 経由で BigQuery に Schema-less なログを入れる

作業の思惑

BigQueryにはTABLE_DATE_RANGEという関数があり、それを使うと日ごとに分割したテーブルをうまく結合して利用できます。テーブルを分割しておくことで大きなテーブルをフルスキャンすること無く、一回のクエリにかかるコストを下げることが可能です。

例えばdataset_name.table_name_20151206のような日ごとのテーブルを作っていた場合、下のように書くと今日と昨日の日付のテーブルからデータを取得することが出来ます。

SELECT *
FROM TABLE_DATE_RANGE(
    dataset_name.table_name_,
    DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),
    CURRENT_TIMESTAMP()
)

また、このようなテーブル名にしておくと、BigQueryのコンソール上でもまとめて表示してくれるので見やすくなります。

ログとしてBigQueryを利用し始めた際、テーブルは月ごとに分割しており、途中から日ごと・タグごとのテーブルに設計し直してログを保存するようになりました。そのため、初期に作ったいくつかのテーブルを日ごと・タグごとのテーブルに分ける必要がありました。それが今回の作業です。

作業の実際

月ごとのデータを保存してあるテーブルを日ごと・タグごとに分けるということで、今回はスクリプト内でbqコマンドを使って処理をしました。実行したコマンドは下記のような感じです。

テーブルの作成

bq mk -t <project_id>:<dataset>.<new_table_name> tag:string,time:integer,message:string

データの移行

bq --project_id <project_id> query \
--allow_large_results \
--destination_table=<project_id>:<dataset>.<new_table_name> \
"SELECT tag, time, message FROM <old_table> WHERE tag = '<tag>' AND time BETWEEN <start> AND <end>"

何をしているかというと、新しいテーブルを作った後にもともとのテーブルに対して必要な条件のクエリを実行し、その結果を先ほど作ったテーブルに入れています。

ちなみに--allow_large_resultsをつけないと、データ量が多い場合に"Error: Response too large to return in big query"というメッセージとともにエラーになります。通常、分析などで利用する際このエラーが出るほどのデータ量を必要とすることは少ないので、これは本来ユーザーに取ってありがたい動作です。

作業の結果

それぞれのテーブルには約60種類のタグがあります。ひと月は29日〜31日なので、今回の方法で移行作業をするとひと月分で大体60×30回コマンドが実行されることになります。そしてそのテーブルは数ヶ月分あります。

BigQueryを扱う際に注意しなければならないのは、BigQueryはSELECT tag, time FROM [dataset_name.table_name_20151206]のように必要な列だけを選択した場合にはスキャンの幅を狭めることは可能ですが、LIMITやWHERE句には何を書いてもテーブルをフルスキャンしてしまうということです。テーブルデコレータという機能もありますが、過去7日以内という制限があります。

今回の作業で思った通りの結果を実現するには何度もテーブルをスキャンする必要がありました。つまりおおよそ60×30×n回フルスキャンを行うことになります。(nは対象の月の数)

ひと月分のテーブルサイズは大体300GBくらいあり、結果としてクエリ発行で使用した容量は 数PB(ペタバイト)になりました。

参考になるスライド：BigQueryの課金、節約しませんか
参考になる記事：Table Decoratorを使って処理するレコード数を削減する

どうすれば節約できるか

例） 他のサービスに移してからデータを選別する

今回のように大量の大きなクエリを流す際は、一旦Redshiftなどに移してからクエリを実行するのが良いかもしれません。Redshiftはデータ量と時間に対して課金されますが、BigQueryの大量フルスキャンよりは料金を押さえられるでしょう。

BigQueryからGoogle Cloud Storageにexportし、それをgsutilを使ってs3にコピー。RedshiftのCOPYを使ってs3からimport。そして上手くRedshift内でテーブルを分割した後、今度は逆の手順を追っていけばBigQueryに分割したデータを戻すことが出来ます。

他に注意すること

Google Cloud Platformの「今月の見込み課金額」は即時反映じゃない

ひと月分スクリプトを実行した後、今月の見込みか金額を見て「このまま実行しても大丈夫かな」と思っていたのですが、当然のことながらリアルタイムに反映される訳はありません。

（料金部分は消しました）

150万円だと思っていても後から見直すと実は1ドル122円換算で200万円だったなんていうこともあるのかもしれないので、タイムラグがあることを頭に入れておく必要があります。

システム管理者のもとには「使いすぎじゃないですか？」みたいなメールが届きます。

結局BigQueryは使える？

結論から言うとBigQueryはビッグデータ解析に十分な威力を発揮します。データ量が多い場合は日付ごとのテーブルを設計しておけばSELECTでお金を使いすぎてしまう事も防げます。

また、直接クエリを実行せずに管理画面などを作って発行されるクエリを制限する方法も考えられます。TableauやModeを使っても良いでしょう。

あたりまえの教訓

クラウドサービスで大きなデータを扱う際は特に料金体系やデータの扱われ方をしっかり調べないと料金が跳ね上がる。

終わりに

クラウドサービスもハイレバ金融商品と同様、正しい知識を持って扱わないと危ないということですね。

今回代役を演じていただいたプロ生ちゃんのアドベントカレンダーもあるようです。
プロ生ちゃん Advent Calendar 2015

（追記） BigQueryを検討している方へ

Table Partitions

BigQueryではTable Partitionsの機能が発表されています。これを利用すれば、この記事に書かれているような操作は不要になります。
参考：Google BigQuery でヒストリカルデータ保存の料金を半分に、 クエリの速度を 10 倍に

Clustered tables

BigQueryではクラスタ化テーブルの機能が発表されています。1 度フルスキャンしてクラスタ化テーブルを作った後は、ベストエフォートではありますがクラスタ外のデータスキャンを防ぐことができます。

Dry runを使って料金は計算できる

クエリの実行時に--dry-runをつけると実際にクエリを実行せずにスキャンの量を得ることができます。そこから実際に利用する料金を計算すれば思いもよらない料金が発生することは防げます。

キャッシュの存在

BigQueryは同じクエリを投げる場合はキャッシュされるため料金は制限されます。今回のようにバッチで回し続けたり、アドホックなクエリを大量に投げない限りは150万円を使うのは逆に難しいと思われます。

カスタム割り当て

コメントでも指摘いただいたとおり、この記事の書かれた直後（だったと思う）にBigQueryカスタム割り当て機能が利用できるようになりました。これにより想定以上に使ってしまうことを制限できます。
参考：費用管理 - BigQuery

定額料金

スキャンの量に依らない契約を秒額、月額、年額から選択できるようになりました。これりにより想定以上に使ってしまうことを制限できます。また、スキャンの量が多く、スロット利用が少ない場合には、節約になるかもしれません。
参考：定額料金 - BigQuery の料金 - BigQuery