〜SQLバッチフレームワークBricolage〜トレンド調査ラボの青木峰郎(id:mineroaoki)です。最近はRubyよりCoffeeScriptとSQLのほうが書く量が多くて悩んでいます。巨大バッチの構成について話したいと思います。処理時間が1日で4時間程度かかる、そこそこの規模のプログラムです。このバッチ処理プログラムをBricolage(ブリコラージュ)というフレームワークで構造化する手法について説明します。「たべみる」とはまず最初に、「たべみる」がどういうものなのかごく簡単にお話ししておきましょう。クックパッドのレシピ検索の分析をすることができます。それから急激に検索が増えている語などを知ることができます。次は「バレンタイン」という語の検索頻度のグラフです。▼「バレンタイン」の検索頻度グラフ(2014年〜2015年)このグラフを見ると、「バレンタイン」という語の検索頻度は12月26日から上昇を始めていることが見てとれます。クリスマスが終わったとみるや次のイベント(バレンタイン)に向けて準備を始めているのですね。「たべみる」バッチの構成たべみるは上記の画面を500ミリ秒未満で表示することができます。対して分析を行っていることを考えると、実は非常に高速と言える速度なのです。背後では事前に集計を済ませた、いわゆる「サマリーテーブル」を大量に作成しています。このサマリーテーブルを作るのが、たべみるバッチの主な仕事です。次のような仕組みでサマリーテーブルを更新します。Data(Hadoop)から取得してRedshiftに入れます。そのあとのキーワードの名寄せや集計はすべてRedshift上で行っています。Redshiftの並列処理の恩恵を十分に受けることができます。selectで行います。これは処理を羃等にし、vacuumを不要にするための工夫です。:;巨大なバッチをジョブに分割するたべみるバッチのSQLが3000行とは言っても、もちろん1つのSQLが3000行ではありません。バッチは構成されています。この大量のSQLをどうやって実行していくべきでしょうか。実行するだけでいいのであれば簡単ですね。このコードでひたすら実行していけばいいわけです。conn.closeendこれではまずいのでしょうか?当然、まずいのです。まずいと言っても、メソッドに分けるべきとかそういうレベルの問題ではありません。次のような疑問・要望が湧いてきます。失敗したところから再起動できるか?SQLの単体テストはどうすればいいのか?「運用しやすいバッチ」とは、「失敗したときに簡単に対処できるバッチ」のことです。問題を解決したら失敗したところから実行を再開できるのがよいバッチです。そのうえで開発しやすく、テストしやすい仕組みならベストでしょう。バッチジョブの組み合わせで全体を構成するのが一般的です。そのようなジョブをたくさん作ることで全体を組み立てるわけです。たべみるでは独自開発したBricolageというフレームワークを使っています。「SQL」バッチフレームワークとは言っていますが、現在は主にRedshiftを想定した実装になっています。Bricolageは先日GitHubでOSSとして公開しました(下記URL)ので、誰でも自由に使うことができます。後述するような様々な機能を活用することで容易に開発や運用ができるようになっています。たべみるの日次バッチは現在200程度のBricolageジョブで構成されています。その200くらいのジョブの依存関係を図で表すと次のようになります。複数のジョブを連動させて上記のようなジョブフローとして動かす仕組みはあまり作り込んでいません。いまのところ、ジョブフローをCUIで非並列で動作させる簡単な仕組み(bricolage-jobnetコマンド)だけを用意しています。ジョブフローを実行する仕組みはジョブ管理システムに任せるのがベストでしょう。Airflow(AirBnB)などがあります。機能だけで言えば個人的にはJP1/AJS3が使いたいのですが、ちょっと高いのと、運用が大変なので導入していません。何かしらのジョブ管理システムを導入する予定です。ジョブフローのGUIは絶対にほしいので、まずはHinemosから試そうと思っています。Bricolageの特長Bricolageには次のような特長があります。SQLにパラメーターを埋め込めるBricolageでは「$変数名」のような記法でSQLに任意のテキストを埋め込めるようにしています。ソースファイルには次のように書かれています。式やテーブル名、カラム名もパラメーターにすることができます。上記のSQLでもテーブル名を変数にしています($dest_tableと$keyword_recipe_sets)。実行時に上書きすることも可能です。そのため、本番で一時的にターゲットテーブル名を差し替えてコピーを作る、など柔軟に運用することができます。eRubyのタグを使ってRubyの式を埋め込むこともできます。本番用のサマリーテーブルを複数まとめて切り替えるためのジョブでは次のようなコードを使っています。手でテーブルリストをメンテナンスするのは事故のもとです。この方式ならばそのような変化するテーブル群をまとめて扱うことができます。「publish」属性を付けることで宣言できるようになっています。「--attributes」で始まっている行が属性の宣言です。SQLの定型パターンを自動生成できる2つめの利点は、よく使うSQLの定型パターンをオプションだけで自動生成できることです。table)ですりかえるという手法をわたしは好んで使います。table文なども必要になります。それに権限を与えるgrantも合わせて実行したいところです。全体では次のようなSQLになるわけですね。これらについていちいちdrop、create、rename......と書きたくはありません。そこでBricolageでは「ジョブクラス」という仕組みを使ってこれらを生成します。ジョブクラスは、ジョブで実行されるSQL文のテンプレートです。さきほど述べたdrop、create、renameはBricolageが生成してくれます。select文だけです。onlyが実行されます。指定するようになっています。「ジョブクラス」として用意してあります。そのとき、次のように--dry-runオプションを付けることで、SQLを実行せずに表示だけさせることができます。パラメーターとeRubyタグも展開されて表示されます。バッチを運用したことのある人ならよくわかるでしょう。実行されるSQLが事前にわかるのは精神衛生上たいへんいい効果があります。selectの部分だけexplainを付けて実行することもできます。文法と意味解析チェック(型チェックとか)の代わりとしても使うことが可能です。Bricolageのその他の機能ここで説明したBricolageの機能は一部にすぎません。ストリーミングロードの仕組みなどが用意されています。気になったかたはぜひGitHubのWikiを眺めてみてください。今後、Bricolageでは次の2つの機能の導入を予定しています。それはそれとして選ぶのも面倒になってきたので、自分で書いてしまおうかなとも思っています。いつまでたっても手頃なものが出てこないので痺れを切らしました。それから、他にぜひ導入したいのがジョブのユニットテスト機能です。入力データと正解データを書いておいたら自動的に検証してくれるような機能を予定しています。『10年戦えるデータ分析入門』6月30日発売!最後に個人的な宣伝です。来週6月30日に発売されます。池袋ジュンク堂など一部の書店ではすでに先行販売を実施中です。作為的なものすら感じますが、なんとまったくの偶然です。わたし自身もびっくりしました。SQLによるデータ分析は適用範囲を増しています。システム連携するパターンも増えていくでしょう。今回紹介した仕組みはそのような場面で役立つはずです。手元のツールボックスの1つとして分析バッチの仕組みを加えておいてください。技術部の青木峰郎です。〜舞台裏を支...2016-07-01ActiveRecordを使ってRedshiftから大量のデータを効率的に読み出すこんにちは、トレンド調査ラボの井上寛之(@inohiro)です。使ったセキュリティログ検索基盤の構築サービス特性にあった検索システムの設計戦略クックパッド採用説明会「クックパッドはサービスの作り手を採用したいんです。/*}*/スターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。