MySQLへの書き込みとスケジュール登録

こんにちは、SSTDの大村です。いよいよ『Treasure Dataが拡げるBIGDATA最新動向』が今週末に開催となります。会場の下見などを行い準備を進めています。当日はトレジャーデータ社の芳川CEOによるビジョンと戦略に関する講演や先進事例の紹介もあります。会場は東京駅からすぐの丸の内 三菱ビル エムプラス/サクセスで、とても交通の便がよい便利な場所にあります。下は会場の写真です。

さて、前回はTreasure Data内のデータを外部ファイルに出力しました。Treasure DataはBIツールなどで分析を行うためのデータを外部DBにすることができます。今回はMySQLにデータを定期的に出力する方法を紹介します。

準備

まずはじめにMySQL側の準備をします。

MySQLの説明はしませんが、以下のDB、テーブル、ユーザを設定します。

  • DB:web_access
  • テーブル:www_access(access_day,code,access_count)
  • ユーザ:td_user

出力用DBとテーブルの準備

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://{DBのユーザ}:{パスワード}@{DBが存在するhost}/{DB名}/{テーブル名}' -w -d {TreasureDataのDB名} "実行クエリ"

実行

実際に書き込んでみましょう。

$ 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オプションに設定しましたが、今回はDB名まで指定します。td result:createの後ろに任意のResult名を指定し、その後ろに'mysql://{DBのユーザ}:{パスワード}@{DBが存在するhost}/{DB名}'を記述します。

$ td result:create res_mysql "mysql://td_user:hogehoge@xxx.host.com/web_access"

スケジュール設定

Resultの設定を行ったら、スケジュールの設定を行います。{cron}部分はcrontabの時間設定と同じように指定します。文法は以下になります。

$ td sched:create {スケジュール名} {cron} --result {Result名}:{テーブル名} -d {DB名} {クエリ}

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に貯めたデータを定期的に外部DBに出力することができます。この機能を利用することによって、日々のデータの様子を外部DBにひもづいたBIツールで確認することができます。発生する大量のデータを直接DBに保存するのではなく、Treasure Dataに保存し、必要な情報だけをDBに蓄積していきましょう。

執筆:大村@SSTD