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

  • 697
    いいね
  • 2
    コメント

pronama.png

※ 代役:プロ生ちゃん(暮井 慧)

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

しかしこのタイトル。僕は約150万円分使いました。まずは関係各位に改めてお詫びを。これは「主婦でも簡単に稼げる」「今ならTポイントが貰える」という謳い文句で、実際には誰もが損をするリスクのあるFXにも似ています。あびゃ〜

(追記) BigQueryを検討している方へ

BigQueryの料金

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

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

経緯

BigQueryの用途

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

schema.json
[
  {
    "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のコンソール上でもまとめて表示してくれるので見やすくなります。

point.png

ログとして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はSELECT tag, time FROM [dataset_name.table_name_20151206]のように必要な列だけを選択した場合にはスキャンの幅を狭めることは可能ですが、LIMITWHERE句には何を書いてもテーブルをフルスキャンしてしまうということです。テーブルデコレータという機能もありますが、過去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の「今月の見込み課金額」は即時反映じゃない

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

pay.png(料金部分は消しました)

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

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

精神的対策

世の中には時間が解決してくれる問題と解決してくれない問題がありますが、後者の問題でなおかつ自分ではどうしようもない場合、絶望感が生まれることもあります。そうなってしまわないように実際に起こった問題は過ぎた事として切り離して考えて、次の対策を考えなければなりません。

なお、この切り離しが出来ずに絶望してしまった例が載っているリンクを貼っておきます。

参考になるまとめ:株やFXの失敗談コピペが怖すぎる

政治的対策

…とにかく自分の立場から言えることは、この作業自体は必要なものであったことを理解してもらうことと、チームリーダー・上司が寛大であったことに感謝しなければならないということです。

こういう事例があってもBigQueryは使える?

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

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

あたりまえの教訓

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

終わりに

以上、やらかしてしまった事案でした。今回のようなことを誰かが起こさないように共有として、そして自分の反省として書いてみました。クラウドサービスもハイレバ金融商品と同様、正しい知識を持って扱わないと危ないということですね。

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

(追記) BigQueryを検討している方へ

Table Partitions

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

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

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

キャッシュの存在

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

カスタム割り当て

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

この投稿は gumi Advent Calendar 20156日目の記事です。