2013-12-17 06:30:05
この記事は MySQL Casual Advent Calendar 2013 の 17 日目です。
前日は @Yuryu さんの MySQLにMHA を導入してハマったところでした。
今日は、私が CIEN Pattern (シャンパターン) と名付けた SQL のイディオムをご紹介します。
これは複雑な集計をカジュアルに行うためのもので、私は日常的に使っています。
イディオムの紹介とか言いつつ、大半は前に社内で書いた便利ツール (オープンソースでない) の紹介だったりします。
本日ご紹介するイディオム
ここでいう「複雑な」というのは、例えば「掲示板に書き込んでいるユーザのうち 20 台男性のユニークユーザ数・30 台女性のユニークユーザ数・全体のユニークユーザ数・全体の投稿数を SQL 一発で全部集計したい」みたいなヤツです。
「サブクエリ 7 重にもネストしている上相関サブクエリだ」とか「なんかテーブルを 30 個ぐらい JOIN している」とかそういうのではありませんが、そういう地獄の SQL にも応用は可能です。
SELECT
COUNT(1) AS `全体の投稿数`,
COUNT(DISTINCT p.user_id) AS `全体の UU`,
COUNT(DISTINCT IF(u.age BETWEEN 20 AND 29 AND u.sex = 'm', u.id, NULL)) AS `20 台男性の UU`,
COUNT(DISTINCT IF(u.age BETWEEN 30 AND 39 AND u.sex = 'f', u.id, NULL)) AS `30 台女性の UU`
FROM
bbs_posts AS p
LEFT JOIN
users AS u
ON
p.user_id = u.id
;
+--------------+-----------+----------------+----------------+
| 全体の投稿数 | 全体の UU | 20 台男性の UU | 30 台女性の UU |
+--------------+-----------+----------------+----------------+
| 12220 | 1032 | 83 | 211 |
+--------------+-----------+----------------+----------------+
1 row in set
こんな感じの結果が得られます。
ここで注目して欲しいのは SELECT 対象のフィールドです。
最初のふたつはどこのご家庭でも使われているであろうごく普通の COUNT(expression) や COUNT(DISTINCT expiression) です。
今回ご紹介するイディオムで特徴的なのは、COUNT(DISTINCT expression) の expression 中に、何やら複雑な IF() を突っ込んでいるところです。
ちなみに GROUP BY を使った場合も同様のイディオムが使えます。
仕組みとしての詳細は後で紹介するとして、こういったクエリが必要になった背景について紹介します。
背景
そんな MECE でない集計にどんな意味が？？？みたいなツッコミ等あると思いますが、現実は複雑なものです。
耐えるしかない。
なんか「あれとあれのデータ集計出してもらえる？来週いっぱいまで。毎日」みたいなリクエストに対していちいちクエリ叩いて Excel を更新するには人間の一生はあまりに短い。
かといっていちいち専用のツール実装にかまけてられるほど人生暇でもない。
ソリューション、SQL Executor
というわけで作ったのが「あらかじめ SQL を登録しておくことでいつでもその結果をカジュアルに確認できておまけに結果を保存しておける」みたいなツールです。
仮に SQL Executor と呼称することにします。
予め言っておくと、再利用可能な実装になってないので今のところ公開してませんが、大したものでもないのでオープンな形で公開できればと思ってますし、あわよくば誰か作ってくれないかと強く願っています。
まぁとても簡単なツールなので、新卒エンジニアにでも作らせると良いのではないでしょうか。一晩で。
もうちょっと詳しく機能を紹介すると
- 開発者ユーザは SQL を登録できる
- ユーザ (非開発者含む) は登録された SQL をいつでも実行できる
- 実行結果は実行時間とともに保存することができる (JSON で保存)
- 実行結果は様々なフォーマットで出力可能 (HTML テーブル・テキストテーブル・TSV・morris.js による時系列グラフ)
- すべてのクエリと全ての実行結果は固有のパーマリンクを持つ
というものです。
多分 phpMyAdmin なんかにもそういう機能はありそうですが、如何せん何でもできてしまうところが最大のネックです。
僕はおっかなくて業務内で利用したことはありません。
その点を、このツールでは接続先を、集計専用に用意したスレーブに限定することで、うっかり UPDATE とかでデータが壊されないようになっています。
出力フォーマットは必要に応じてたまに増やしてます。
morris.js で時系列グラフを出力するやつがお気に入りです。
一番目の列を時刻として、以降の列を全て値というルールで、SELECT クエリひとつ書けばすきなメトリクスの時系列グラフがすぐに得られます。
あとは TSV 出力もお気に入りです。
とりあえず SELECT さえ書けば「あとは EXCEL にでもコピペして適当にこねくり回しておいて」という具合に丸投げできます。
何よりもお気に入りなのはパーマリンク機能です。
URL さえ渡せばみんなでクエリや集計データを共有できるので、メールやチャットでカジュアルにやり取りできます。
データを収集するツールにはパーマリンクが欠かせない、ということ事実に Cacti はいい加減気付いて欲しいところです。
SQL Executor を作って見て
この SQL Executor を作って見てなんですが、日々のストレスのうちごく一部ではありますが、確実に減りました。
SQL 叩いて Excel に貼りつけてメールして、みたいな業務をかなり減らすことができました。
もちろんこれまでも何でもかんでも手動で出していたわけではなく、よく使われるものは何かしらのツールを実装して自動化していました。
ですが、例えば数日間しか必要無いもののような、専用ツール化するほどでもないものについて、実装の手間をかける必要が無くなりました。
また、今時のフルスタックでイケてるエンジニアの皆様は Fluentd で収集したログを MongoDB なり ElasticSearch につっこんで Kibana で眺めてはニヤニヤしていることかと思われますが、そういった素敵なツールに組み込む前の段階のプロトタイピングとしても、なかなかの威力を発揮します。
とりあえず SELECT 文をこねくりまわして非エンジニアの人にも見せつつ、本当に必要なデータがわかってきたところで、上記のような素敵ツールに組み込んでいくと便利だと思います。
Treasure Data はそういった用途にも対応できる機能が揃っていると思いますが、如何せんそんなに安いものではない (個人の感想です) ですし、データ規模によっては Hadoop/Hive に頼るのは大袈裟だったりすると思うので、適材適所使い分けていければ良いんじゃないかと思います。
あとよかったのは、今までそれぞれの手元で眠りがちだった SQL が共有されるようになったことです。
これまでも Wiki に便利 SQL 集みたいな形で共有していたりしましたが、その場で実行できると凄く楽なので、情報共有が加速したんじゃないかと思います。
まぁそんなこんなで「とりあえず何でも SQL 一発でゴチャゴチャと集計しちゃおうぜ！」みたいな文化が醸成され、このイディオムが発見されるに至りました。
とは言え、なんでもかんでもゴチャ混ぜにした集計は神 EXCEL 問題と同様のアレを孕むものなので、使いどころは限定するのが良いんでしょうが。
というわけでようやくイディオムの解説に戻ります。
COUNT() とは何だったのか
まず COUNT(expression) や COUNT(DISTINCT expression) について振りかえる必要があります。
なお、COUNT(DISTINCT expression, [expression...]) という具合に、集計対象の expression は複数指定することもできますが、ここでは単純化のために考えないことにします。
COUNT(expression) はどういう関数だったかというと、「expression が NULL でない行の数を数える」みたいな感じだと思います。
つまり user_id が 1, 2, 2, NULL となっている場合、COUNT(user_id) の結果は 3 となります。
実装を読んだわけじゃないしマニュアルも意外とそこまで詳しく書いてないのですが、手元でいろいろ試した結果そういう感じっぽかったので、そういうものとして話を進めます。
次に COUNT(DISTINCT expression) はというと、expression のユニーク数を数えます。もちろん NULL は除外します。
user_id が 1, 2, 2, NULL となっている場合、COUNT(DISTINCT user_id) の結果は 2 となります。
IF() の結果を COUNT() する
これで「COUNT(expression) は expression が NULL でない行の数を数える」ということがわかりました。
つまり、集計対象から除外したい行は NULL にすれば良いのです。
というわけで、20 台の男性だけを集計したいときはこうです。
IF(DISTINCT u.age BETWEEN 20 AND 29 AND u.sex = 'm', u.id, NULL)
こうすることで 年齢が 20 ~ 29 でかつ性別が m の行以外は NULL として除外されるようになります。
また、u.id による DISTINCT な COUNT なので、該当ユーザのユニーク数が集計されるというわけです。
多分このイディオムを使っている方は少なからずいると思うんですが、如何せん呼び名が無いので、ノウハウとして共有されていない気がしています。名前重要。
COUNT IF EXPRESSION NULL を略して CIEN Pattern (シャンパターン) とか呼ぶと格好が良いのではないでしょうか。
並び変えたら NICE になりますし、CIEN パターンで NICE かつカジュアルな集計！とかどうでしょうか。
だめでしょうか。
まとめ
カジュアルにクエリ叩ける Web ベースのツールあると便利なのでオススメという話でした (イディオムはオマケ)。
明日は @RKajiyama さんです。
2013-11-23 19:02:31
DevOps 系の技術や話題にキャッチアップすべく、ここ 1 ~ 2 ヶ月でいろいろ新しいことを始めました。
さくら VPS 上に手作業で構築していた Apache x mod_php な環境で運用してましたが、全て Chef (というか knife solo) で構築するようにして、構成も Nginx x PHP-FPM に変更。
あとはアクセスログなんかは LTSV 形式で出力するようにし、それを Fluentd で吸い上げ Elasticsearch に突っ込み、Kibana を眺めてニヤニヤする、みたいな感じになってます。
なお、Chef に入門するにあたっては伊藤直也さんの書籍入門Chef Solo
と WEB+DB PRESS Vol.75
の記事が大変参考になったのでおすすめです。
で、今度は AWS に引っ越そうとしているんですが、あんまり AWS に関係ないところでハマったので、それについて書きます。
正直まだよくわかってない部分が多いので、ツッコミ大歓迎です。
起きた問題
EC2 の Ubuntu Linux 12.04 LTS に knife solo によるプロビジョニングを行ったところ、MySQL のビルドでコケました。
長いですが、 make コマンドが見つからなくてコケていることがわかります。
なお、 MySQL は Opscode Community の mysql クックブック 4.0.6 を Berkshelf を利用してインストールし、make のインストールは自前のクックブックでインストールするようにしています。
そして run_list 上は MySQL より先に make がインストールされるように指定していました。
対処
make をインストールするクックプックのレシピに、以下のような修正を行いました。
元はこれ。
これを、run_action メソッドを呼ぶように修正しています。
run_action メソッドとは何か
いろいろ調べた結果、以下の記事にたどり着きました。
Evaluate and Run Resources at Compile Time
Chef によるレシピの実行はコンパイルと実行の 2 フェーズに分かれていて、まずコンパイルフェーズではレシピを Ruby コードとして実行し、取り扱うべきリソースや、それに対して行うアクションを識別します。
それを一気に実行するのが実行フェーズです。
ですが、run_action メソッドを呼ぶことで、コンパイルフェーズでそのアクション (例えばパッケージのインストール) を実行することができてしまいます。
これにより、本来 make のインストールが先に行われるべきところが、コンパイルフェーズにおいて MySQL のインストール (厳密には Ruby の mysql gem のビルド) が行われるも、make はまだインストールされてないのでコケる、ということになっていました。
それならば、ということで make のインストールも run_action でコンパイルフェーズに行ってしまえば、MySQL のビルドよりも make のインストールが先行するので、問題なくプロビジョニングが完了する、というわけです。
本当にそれでよいのか
どうにもバッドノウハウ感があります。
おそらく、run_action は本来できる限り使うべきでない禁じ手で、Opscode の mysql クックブックは run_action を使わない形に修正されるべきなんじゃないかと思ってます。
mysql クックブックを自前でそのように作ってしまってもいいのかもしれません。
その他、いい方法をご存知の方は是非教えてください。
まぁ面倒なので MySQL は Amazon RDS に移行する予定です。
ブログ書いたらお腹空いたので目黒においしいトンカツでも食べにいこうと思います。