スタースキーマと列指向データベース

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)

僕はWebエンジニアではないけれども上記の本を読み、とりわけ11-3 分析系処理と列指向データベースを興味深く読みました。

そこで分析用途のデータを作る場合にどうやるのがいいのか調べてみたのでメモっておきます。

「おむつを買った人はビールを買う傾向がある」みたいな分析を行うためには時系列に大量の業務データを蓄積してDWHを作ります。

その際にスタースキーマと呼ばれる形式でテーブル設計をします。

中心のファクトテーブルは分析のキー(例:商品ID、顧客ID)と分析対象の数値データ(例:販売数量、販売金額)を持ちます。
このテーブルは時系列でひたすら蓄積されていき大量データとなります。例えば売り上げテーブルですね。トランザクションテーブルといってもいいでしょう。

ディメンジョンテーブルは分析の切り口となるデータが格納され、分析の際にはファクトテーブルと結合します。マスターテーブルといってもいいでしょう。

中心のファクトテーブルと、それと関連するディメンジョンテーブルとが星形に表現されるため「スター・スキーマ」と呼ばれます。

詳しくはこの辺参照

分析の際にはディメンジョンテーブルとファクトテーブルを結合して集計します。この場合にファクトテーブルで参照する列は限られています。そのため列指向データベースのほうがマッチします。

Sybase IQ、Netezza、Vertica、Exadata、SQL Server 2012といった商用製品はこのようなDWH用途なものです。

詳しくはこの辺参照
最近話題の「カラム型データベース」とはどんな仕組みのデータベースか? - Publickey


さてここでHiveでWebのアクセスログを集計する場合はどんな感じになるのか。脳内イメージを書いてみます。ただSqoopを使って集計はRDBMSでやるとかもありそう。

ファクトテーブル、トランザクションテーブルの1レコードは1PV相当でしょう。

例えば、

IPアドレス リターンコード 日付 URL

といった感じになるでしょう。

ここでサッカーの記事を配信するWebサイトのアクセスログを解析することを考えてみたいと思います。

WebサイトのURLはhttp://xxx.net/チーム名となっているとします。

例えば、
http://xxx.net/chelsea/aaa.html
アンドレ・ビラス・ボアス解任の記事が、
http://xxx.net/arsenal/bbb.html
ロビン・ファン・ペルシ△という記事が、
http://xxx.net/bolton/ccc.html
に宮市イケメンという記事があるというイメージです。プレミアリーグ以外の記事もあるとします。

ディメンジョンテーブル、マスターテーブルをこんな感じに持っておきます。

chelsea プレミアリーグ
arsenal プレミアリーグ
bolton プレミアリーグ
barcelona リーガエスパニョーラ
dortmund ブンデスリーガ

そうすればファクトテーブルとディメンジョンテーブルを結合して集計することにより例えばプレミアリーグ関連の記事の月間PV数を知ることが出来ます。ドリルアップですね。

今シーズンは2部(チャンピオンシップ)にいる李忠成が所属するウェストハムが来シーズン1部に上がってきた場合は

westham チャンピオンシップ

から

westham プレミアリーグ

への変更というマスターメンテナンスを行えばOKです。

こういう用途の場合はSequenceFileよりも列指向のRCFileでHiveのテーブルを作るほうがいいのかなあと思い始めています。みなさんどうしてるんでしょ。

http://www.cse.ohio-state.edu/hpcs/WWW/HTML/publications/papers/TR-11-4.pdf
を見るとFacebookはRCFileをデフォルトとして使っているようです。

とりあえず
in-mapper combining - wyukawa’s blog
で使った郵便番号データで軽くベンチマーク取ってみました。擬似分散での参考値です。

こんなテーブルです。

CREATE TABLE IF NOT EXISTS postdata (
    chihou_code string,
    post_code string,
    choiki_name string,
    choson_name string,
    todoufuken_name string,
    flag1 int,
    flag2 int,
    flag3 int,
    flag4 int,
    flag5 int,
    flag6 int
)

クエリはこんな感じ。

SELECT todoufuken_name, count(todoufuken_name) FROM postdata GROUP BY todoufuken_name;

SequenceFileだと70秒かかっていたのがRCFileだと56秒になりました。
HDFS上のデータサイズは1.2MB→859KBになりました。GZIP圧縮してます。圧縮前の元データは8.38MBです。
HDFS_BYTES_READは1,256,981→879,838になりました。

使ったクエリは
https://github.com/wyukawa/InMapperCombining/tree/master/hive-work
にあります。