itkr.net

blog summaries

Blog Entry  (Dec. 5, 2015, 9:01 a.m.)

Tilo Mitra's avatar

【画像あり】BigQueryで150万円溶かした人の顔

Qiita |

pronama.png

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

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

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

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] のように必要な列だけを選択したり LIMIT を使用したりという場合にはスキャンの幅を狭めることは可能ですが、 WEHRE 句には何を書いてもテーブルをフルスキャンしてしまう ということです。テーブルデコレータという機能もありますが、過去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 でお金を使いすぎてしまう事も防げます。

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

あたりまえの教訓

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

終わりに

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

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

元の記事へ