こんにちは、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　

About AM 213 Articles

プロフェッショナルサービスチームの中の人。

Twitter YouTube