Database JUNKY

MySQL,MariaDBを中心としたブログです

Treasure Dataからgoogleスプレッドシートにダイレクトに出力する

tre

前回、td-agentを利用してltsvのデータをトレジャーデータに書き込むなんてことをやりました。今回はそのトレジャーデータからGoogle スプレッドシートに直接書き込むなんてことをやってみようかと思います。まぁ結果としてできたことはできたのですが、スプレッドシートに書き込むところより、hiveqlでどのように描くのか悩んだ感じですけどね。(^_^;)

冒頭でも書きましたが、前回、td-agentを利用してltsvのデータをトレジャーデータに書き込むの話の続きだと思ってください。ちなみに前回Treasure Dataに出力したファイルはこんなファイルになります。

[ad#google-ad1]

  • 見づらくてすみません こんなデータが現在、数百万件入っている状態です。 [shell]

{"x_forwarded_for":"-","v_host":"www.s-quad.com","last_status":"200","time":"1375085092","remote_host":"XXX.YYY.ZZZ.AAA","size":"41645","user_agent":"Mozilla/5.0 (Linux; U; Android 2.2; fr-fr; Desire_A8181 Build/FRF91) App3leWebKit/53.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1","referer":"http://www.s-quad.com/","request":"GET /wordpress/?p=1655 HTTP/1.1"} [/shell]

このまま蓄えておいてもしょうがない。だって、Treasure Dataなんだから有効に活用しなくっちゃね。なんてことを考えながら、こんなお題目を持ってSQLを作ってみました。

さて、お題目は。。。

このサイトでどこのページを皆様は一番見てくれているのだろうか?・・

ちょっとどこかの初心者講座みたいなお題目ではありますが、そもそも私自身が初心者なので、つっこみなしでお願いします。

どの項目を参照すれば、該当ページのカウントが取れるのか?

私の出力されているwordpressのデータですと、requestの値をいい感じにとれば集計できそうです・・ということだけわかりました。でも、requestの項目に余分な項目が付きすぎです。本来はトレジャーデータに入れる前に加工していれるのが、きっとクールなやり方だとは思うのですが、もう数百万件も入っているし、いまさらこの部分の構成を変更する気がまったくしないので、意地でもこのフォーマットで集計をとるようにしたいと思います。

hiveqlで加工する

hiveqlは、RDBMSとよく似たSQLライクなもので、データを操作する言語です。詳しくは、 http://www.gmo.jp/report/technology/05/index_02.php

が参考になりましたので、ご一読をお願いします。 さきほども言ったとおり、とにかく、余計な文字列をカットし、最終的には、ページIDだけ残してそれを集計したいってことです。それを実現するためにはどうすればいいか?

split 関数を使えばいいのです!

たとえば、こんな値を分割したい場合、どう書けばいいのかというと・・・ * 加工前のデータ [shell]

td query \ -w \ -d apache \ " select 'wordpress/?p=12345' from access limit 1 "

+--------------------+ | _c0 | +--------------------+ | wordpress/?p=12345 | +--------------------+

[/shell] * splitをつかって分割する spilitを利用して、col1とcol2に値を分割しているのが結果から見てわかるかと思います。また、括弧内の引数で、'/'と記載している部分がありますが、これがいわゆる区切り文字になります。ここでは、/を区切り文字にしております。また、[0]とか[1]とかしている部分は、プログラム的にいいますと、配列です。分割後の一番最初の値は、[0]で、次が、[1]という感じです。以下の例で、wordpressという文字と、?p=12345という文字が分かれているのがわかるかと思います。 [shell]

td query \ -w \ -d apache \ " select split('wordpress/?p=12345','/')[0] as col1, split('wordpress/?p=12345','/')[1] as col2 from access limit 1 "

Result : +-----------+----------+ | col1 | col2 | +-----------+----------+ | wordpress | ?p=12345 | +-----------+----------+

[/shell]

ここでの主題は、hiveqlの話をするわけではないので、あとは割愛させていただきます。というのも、自分もまだあんまし詳しくありません。もっと効率の良いやり方があるかもしれませんので・・・

本題のgoogleスプレッドシートに書き込む!!

実はですね・・思ったより簡単にできてしまいました。ようは、tdコマンドに、--result パラメータをつければ良いだけでした。(^_^;)  書式は以下の通りです。

書式 --result 'gspreadsheet://googleアカウント:パスワード@gmail.com/BOOK名/シート名'

※ファイル及びシートが存在しない場合は、自動的に生成します。

  • クエリ発行 上記のオプションを追記して生成したのが、以下のコマンドになります。もちろんsplitも利用しております。 ただし、splitした結果を、サブクエリーで再度spiitしております。また、split関数に関しての補足ですが、区切り文字数の制限はありません。

[shell]

td query \

-w \ -d apache \ --result 'gspreadsheet://squad:password@gmail.com/Analysis/rank100' \ " select split(X.parm1,' ')[0] as seq_num, count(X.parm1) as cnt from ( select v['request'] as request, split(v['request'],'p=')[1] as parm1 from access acs where v['request'] like '%p=%' ) X group by split(X.parm1,' ')[0] order by cnt desc limit 10 " [/shell]

  • 実行結果 上記クエリの実行結果です。クエリーの実行状態なんか確認してもしょうがないのでスプレッドシートの出力結果をお見せします。

*出力結果1 おぉ!?なんでグラフが!?ってすみません、いきなりなんですが、これは予め加工したものです。あたりまえですが、トレジャーデータでできるのは、データをスプレッドシートに展開するだけです。(^_^;)

<<下記画像をクリックいただきますと、公開中のスプレッドシートにリンクします>>

[caption id="attachment_1679" align="alignnone" width="300"]2013-08-07_101644 クリックいただきますと、公開中のスプレッドシートにリンクします[/caption]

*出力結果2 実際に出力されるデータは、下記のデータです。上記のグラフはこのデータを元に表示させております

2013-08-07_101716 スプレッドシートの出力は自動で反映させたい!

まあ、普通にそう思いますよね。いちいちコマンドを叩いてられないですもん。ここからはちょっとおまけですが、WEB上で当該処理をスケジューリングできます。(ちょっと先ほどと手順が変わります・・)

  1. ブック情報の登録(コマンドラインのみ) [shell] # td result:create rank_gspreadsheet 'gspreadsheet://analysis.squad:password@gmail.com/Analysis' Result URL 'rank_gspreadsheet' is created. [/shell]
  2. WEB上で登録されていることを確認 2013-08-07_123046
  3. クエリーの登録、及びスケジュールの設定 [caption id="attachment_1687" align="alignnone" width="300"]2013-08-07_124938 画像をクリックすると原寸で表示されます[/caption] ① クエリーを登録します。先ほどと同様のSQLを登録しました [SQL] select split(X.parm1,' ')[0] as seq_num, count(X.parm1) as cnt from ( select v['request'] as request, split(v['request'],'p=')[1] as parm1 from access acs where v['request'] like '%p=%' ) X group by split(X.parm1,' ')[0] order by cnt desc limit 10 [/SQL] ② 出力先を登録します。 上記 1 で登録した定義名を入力しております [shell] rank_gspreadsheet:rank100 [/shell] ③スケジュール名を登録します。 [shell] push_access_ranking [/shell] ④実行時間を登録します。ここでは、@dailyを登録しております。@dailyは、毎日 0:0に実行されるという意味ですが、任意に時間を設定することも可能です。 詳しくは、こちらを参照してください
  4. 上記にて登録が完了しますと、スケジュールリンクに登録内容が表示されるかと思います2013-08-07_125051

以上、駆け足で説明しましたが、なんとなくわかりましたでしょうか?今回は、google のスプレットシートを例にあげましたが、出力先を、MySQLにしたり、PostgreSQLにしたり、amazon S3にしたりなんでもできます。こちらに関しての説明も、結局のところ、トレジャーデータのサイトに記載されておりますので、色々とためしてみるのがいいのかな?なんて思っております(汗)

また、この手のものって、自分もそうなのですが、漠然となんかデータが集計できるかも?だけだと苦戦します。まずは、目的を絞って試行錯誤していくのが、習得の早道だと思いますので、色々とお試しください。