こんにちは、SSTDの大村です。前回はTreasure Data内のデータを外部ファイルに出力しました。Treasure DataはＢＩツールなどで分析を行うためのデータを外部ＤＢにすることができます。今回はMySQLにデータを定期的に出力する方法を紹介します。
準備
まずはじめにMySQL側の準備をします。
MySQLの説明はしませんが、以下のＤＢ、テーブル、ユーザを設定します。
- ＤＢ：web_access
- テーブル：www_access(access_day,code,access_count)
- ユーザ：td_user
出力用ＤＢとテーブルの準備
mysql> create database web_access;
mysql> create table web_access.www_access(access_day date, code int(3),access_count int(10));
ユーザの権限設定
Treasure Dataから書き込むにはSELECT, INSERT, DELETE, CREATE, DROP , ALTERの権限が必要です。この権限を持ったユーザを作成します。
mysql> GRANT SELECT, INSERT, DELETE, CREATE, DROP, ALTER ON web_access.* TO td_user@’%’ IDENTIFIED BY ‘hogehoge’
ポートの確認
MySQLのポートを外部からアクセス可能に設定します。
出力
今回は以前作成したtestdbのwww_accessテーブルの日別、コード別のカウント数を集計して書き込みます。
文法
一度だけ書き込むには下記の設定を行います。
$ td query –result ‘mysql://{ＤＢのユーザ}:{パスワード}@{ＤＢが存在するhost}/{ＤＢ名}/{テーブル名}’ -w -d {TreasureDataのＤＢ名} “実行クエリ”
実行
実際に書き込んでみましょう。
$ td query –result ‘mysql://td_user:hogehoge@xxx.host.com/web_access/www_access’ -w -d testdb “SELECT TD_TIME_FORMAT(time, ‘yyyy-MM-dd’) as access_day,v[‘code’] as code, COUNT(1) as access_count FROM www_access GROUP BY v[‘code’],TD_TIME_FORMAT(time, ‘yyyy-MM-dd’) “
実行結果が標準出力に表示されます。
Status : success
Result :
+————+——+————–+
| access_day | code | access_count |
+————+——+————–+
| 2013-04-15 | 404 | 3 |
| 2013-04-16 | 500 | 2 |
| 2013-04-16 | 404 | 14 |
| 2013-04-15 | 200 | 2128 |
| 2013-04-16 | 200 | 2853 |
+————+——+————–+
5 rows in set
確認
MySQL側のテーブルを確認します。
mysql> select * from www_access order by access_day,code;
+————+——+————–+
| access_day | code | access_count |
+————+——+————–+
| 2013-04-15 | 200 | 2128 |
| 2013-04-15 | 404 | 3 |
| 2013-04-16 | 200 | 2853 |
| 2013-04-16 | 404 | 14 |
| 2013-04-16 | 500 | 2 |
+————+——+————–+
5 rows in set (0.00 sec)
集計したデータが書き込めていることが確認できました。
スケジュール登録
次に定期的にデータをMySQLに出力する設定をします。
Result設定
まずは出力先の設定をします。先ほどはテーブル名までresultオプションに設定しましたが、今回はＤＢ名まで指定します。td result:createの後ろに任意のResult名を指定し、その後ろに’mysql://{ＤＢのユーザ}:{パスワード}@{ＤＢが存在するhost}/{ＤＢ名}’を記述します。
$ td result:create res_mysql “mysql://td_user:hogehoge@xxx.host.com/web_access”
スケジュール設定
Resultの設定を行ったら、スケジュールの設定を行います。{cron}部分はcrontabの時間設定と同じように指定します。文法は以下になります。
$ td sched:create {スケジュール名} {cron} –result {Result名}:{テーブル名} -d {ＤＢ名} {クエリ}
1日分のアクセス数をMySQLに出力する設定をしてみましょう。
$ td sched:create sched_mysql “59 23 * * *” -t Asia/Tokyo –result res_mysql:www_access -d testdb “SELECT TD_TIME_FORMAT(time, ‘yyyy-MM-dd’) as access_day,v[‘code’] as code, COUNT(1) as access_count FROM www_access WHERE TD_TIME_FORMAT(time, ‘yyyy-MM-dd’) = TD_TIME_FORMAT(unix_timestamp(), ‘yyyy-MM-dd’) GROUP BY v[‘code’],TD_TIME_FORMAT(time, ‘yyyy-MM-dd’) “
長いのですが、毎日23時59分にその日のアクセス数をコード別に出力するsched_mysqlというスケジュールを設定しました。先ほどのクエリに「当日分」という条件を加えています。
動作確認
動作するか確認してみましょう。
まず試験データを作成します。Treasure Dataに用意されているサンプルデータを利用します。
$ td sample:apache sample.json
これでローカルにsample.jsonというファイルができますので、先頭の2行を抜き出します。
$ head -n 2 sample.json > head.json
現在のunix時間を表示し、head.jsonの中の2箇所の「”time”:XXXXXXXXXXXX」の部分の数値を現在のunix時間に修正します。
$ date ‘+%s’
$ vi head.json
修正後、Treasure Dataにインポートします。
$ td table:import testdb www_access –json head.json
それでは1日待って、確認してください。と言いたいところですが、次の日になると忘れそうなのですぐに確認するためにスケジュールを実行します。td sched:runで実行します。時間は過去の時間を書けばすぐに実行されます。
$ td sched:run sched_mysql “2013-05-15 00:00:00”
Scheduled 1 jobs from 2013-05-15 01:50:00 +0900.
+———+—————————+
| JobID | Time |
+———+—————————+
| 2876518 | 2013-05-15 13:00:00 +0900 |
+———+—————————+
1 row in set
$ td job:show 2876518
(中略)
Result :
+————+——+————–+
| access_day | code | access_count |
+————+——+————–+
| 2013-05-15 | 200 | 2 |
+————+——+————–+
1 row in set
Use ‘-v’ option to show detailed messages.
MySQL側のテーブルを確認します。
mysql> select * from www_access order by access_day,code;
+————+——+————–+
| access_day | code | access_count |
+————+——+————–+
| 2013-04-15 | 200 | 2128 |
| 2013-04-15 | 404 | 3 |
| 2013-04-16 | 200 | 2853 |
| 2013-04-16 | 404 | 14 |
| 2013-04-16 | 500 | 2 |
| 2013-05-15 | 200 | 2 |
+————+——+————–+
6 rows in set (0.00 sec)
2件の当日分のデータが増えていることが確認できました。
このようにtd-agentでTreasure Dataに貯めたデータを定期的に外部ＤＢに出力することができます。この機能を利用することによって、日々のデータの様子を外部ＤＢにひもづいたＢＩツールで確認することができます。発生する大量のデータを直接ＤＢに保存するのではなく、Treasure Dataに保存し、必要な情報だけをＤＢに蓄積していきましょう。
<
p align=”right”>執筆：大村@SSTD 
