MySQLからデータをダンプしてHiveにデータをロードするまでの流れメモ。
MySQLからデータをダンプしてHiveにデータをロードする
大きく分けて以下の手順を踏むことになります。
- mysqlからcsvデータをダンプする
- hiveテーブルへcsvデータをロードする
順番に方法を見ていきます。
MySQLから数百万レコード程度のデータをダンプする
データがせいぜい数百万レコードしかないならどんな方法でも大丈夫です。代表的な方法は下記の通りです。
mysql -e "SELECT ..." > file_name
クエリの結果をそのままファイルにリダイレクトする方法です。リモートDBにクライアントからつないでいる場合に使えます。
SELECT ... INTO OUTFILE 'path/to/file.csv'
myqlが稼働しているサーバーのローカルFS上にクエリの結果を出力する方法です。別途、NFSやrsync等でデータを転送する必要があります。
mysqlからデータをダンプする、という点だけに限れば上記の方法でいけます。csvにするためにコンマを入れたり、ファイルを圧縮したりする必要があるため、実際にはもうちょっと細かい手順が必要になります。
InnoDB Hot Backupとかと絡めればもっとスマートな方法があるかもですが、そういうのは人数が増えてからおいおいやればいいと個人的には思います…(^^)
MySQLから数千万～数億レコードのデータをダンプする
基本的には上記の手順と同じなんですが、レコード数が多い場合は相応の負荷がかかるため、ちょっとした工夫が必要になります。
sleepを入れながらちょっとずつSELECTする
スマートな解決策とは言えませんが、現実的にはこれでなんとかなると思います。ページングでクエリの結果を絞りつつ、クエリ間にsleepを少しだけ入れれば負荷はなんとかなります。
上記の方法は、本番稼働しているDBサーバーからcsvをダンプするための工夫です。ダンプ用のDBを用意できるなら単純にSELECTしていいと思います。
どれくらいデータが増えてきたらこういう工夫が必要なのかどうかですが、実際に自分のデータでSELECTの時間を計測してみるのが一番分かりやすいと思います。
ある程度でかいサービスだと、DBサーバーが水平分散されていますが、csvにダンプする段階では各サーバーごとにcsvが分かれていて大丈夫です。hiveへロードする際の工夫でカバーできます。
Hiveテーブルへcsvデータをロードする
クエリで簡単にロードできます。テーブル定義は一例として下記の通りです。
CREATE TABLE IF NOT EXISTS user.user ( user_id STRING, admin STRING, birthday STRING, gender STRING, created STRING, updated STRING ) PARTITIONED BY (dt STRING, node STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
全部のカラムをSTRING型にしていますが、だいたいの場合はこれでOKです。MySQLだとストレージ効率のために細かく型を変えますが、hiveで使いたいだけなら実践的にはこれで大丈夫です。
注意点があります。csvデータ内に日本語文字列を含まないようにしておきましょう。ごちゃごちゃした問題がおきます。省いておくに越したことはないです。
また、ダブルクォートで英数字を囲っている場合は、そのダブルクォートまでカラム内に入ってしまう点に注意しましょう。上記のテーブル定義は、「ロードするデータはコンマ区切りです」という意味でしかないです。
apacheのaccess_logをロードする時みたいな正規表現を書けば、上記のダブルクォートの問題は回避できます。その方法は「hive access_log」とかで検索してみてください。
ロードするcsvデータは一例として下記のようになります。createdとupdatedにはダブルクォートごと文字列が入ることになります。
001,true,2012-04-01,female,"2010-04-01 10:00:00",,"2013-04-01 10:00:00" 002,true,2012-04-01,female,"2010-04-01 10:00:00",,"2013-04-01 10:00:00" 003,true,2012-04-01,female,"2010-04-01 10:00:00",,"2013-04-01 10:00:00"
テーブルへのデータロードは下記のクエリで行えます。
LOAD DATA LOCAL INPATH '/tmp/user_user.2012-04-01.csv.gz' OVERWRITE INTO TABLE user.user PARTITION (dt='2012-04-01', node='1');
dtにはcsvをダンプしたのと同じ日付を指定しておけばOKです。少なくとも今のバージョンのhiveでは日付をSTRING型として扱うため、上記の例のように文字列を直書きしてしまいます。
DBサーバーが複数台に分かれている場合を想定してnodeパーティションを作りましたが、DBが一台しかないならこのパーティションはなくてもOKです。
上記のロードクエリを、dtとnodeの値を変えながら、ダンプしたcsvファイルの回数分繰り返しましょう。そうすればデータのロードは完了です。
ロードしたデータをHiveQLで集計してみる
今回のcsvデータは、「毎日の登録ユーザー一覧」みたいなのを想定しています。日毎の総登録ユーザー数は下記のクエリで集計できます。
use user;
SELECT
dt,
count(distinct user_id)
FROM
user.user
WHERE
dt IN ('2013-02-01', '2013-02-02')
GROUP BY
dt
ORDER by
dt
;
hiveqlで注意するのは、「パーティションを指定すること」だけです。indexがどうたらとか細かいことはほとんど気にする必要はないです。
もし、あなたのロードしたデータがhadoopクラスタを構成するサーバースペックに対してでかすぎる場合は、dtを一日ずつ指定する必要があるかもしれません。逆にデータが少ないなら、「dt regexp '2013-04'」みたいな感じで一ヶ月分ずつ集計できるかもしれません。
でかすぎるパーティションを指定すると、クエリが全部止まったり、データノードが応答しないのでクラスタから外れたり、色々問題が起きます…(^^) この辺のさじ加減は自分で試しながらつかんでみてください。
csvへのダンプからhiveテーブルへのロードまでの一連の流れを毎日繰り返せば、hive解析環境の完成です(^^)
まとめ
csvへデータをダンプして、hiveテーブルへデータをロードするまでの手順を書きました。
実際には細かい問題が色々と起きます。hadoopクラスタの構築からやっている場合はパラメータチューニングもむっちゃ大変です。が、百聞は一見にしかずなので、自分であれこれ試行錯誤してみてください。
ちなみに、hadoopクラスタを自前で用意するのは全くおすすめしません…。TREASURE DATAとか、せめてAmazon EMRを利用するのが無難だと個人的には思います。
