ログってなんぼ

日々のメモです

TreasureData:td queryの結果をjsonでもらう

トレジャーデータ - Treasure Data はてなブックマーク - トレジャーデータ - Treasure Data

最近やっと業務でも使い始めました。おさらい、というか備忘録的に作業メモ。

普通にクエリを投げてみる

$ td query -w -d test "select * from kndl limit 3"
Job 4920670 is queued.
Use 'td job:show 4920670' to show the status.
queued...
  started at 2013-09-25T14:34:59Z
  Hive history file=/mnt/hive/tmp/2655/hive_job_log__729516546.txt
  finished at 2013-09-25T14:35:17Z
  OK
  MapReduce time taken: 0.28 seconds
  Time taken: 2.894 seconds
Status     : success
Result     :
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+
| v                                                                                                                                                                                                                                                                     | time       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+
| {"user":"-","method":"GET","referer":"-","code":"200","host":"5.10.83.38","agent":"Mozilla/5.0 (compatible; AhrefsBot/5.0; +http://ahrefs.com/robot/)","time":"1379725272","size":"35828","path":"/items/search/s/2292479051/1/"}                                     | 1379725272 |
| {"user":"-","method":"GET","referer":"-","code":"200","host":"69.30.238.26","agent":"Mozilla/5.0 (compatible; MJ12bot/v1.4.4; http://www.majestic12.co.uk/bot.php?+)","time":"1379725895","size":"12040","path":"/items/search/d/2275256051/1/Carole%20Bugg%C3%A9"}   | 1379725895 |
| {"user":"-","method":"GET","referer":"-","code":"200","host":"69.30.238.26","agent":"Mozilla/5.0 (compatible; MJ12bot/v1.4.4; http://www.majestic12.co.uk/bot.php?+)","time":"1379725898","size":"12048","path":"/items/search/d/2275256051/1/China%20Mi%C3%A9ville"} | 1379725898 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+

jsonで受け取る

$ td query -w -d test -o ./result.json -f json "select * from kndl limit 3" && cat ./result.json
Job 4920676 is queued.
Use 'td job:show 4920676' to show the status.
queued...
  started at 2013-09-25T14:38:15Z
  Hive history file=/mnt/hive/tmp/2655/hive_job_log__764856367.txt
  OK
  MapReduce time taken: 0.245 seconds
  finished at 2013-09-25T14:38:30Z
  Time taken: 2.589 seconds
Status     : success
Result     :
written to ./result.json in json format
[[{"user":"-","method":"GET","referer":"-","code":"200","host":"5.10.83.38","agent":"Mozilla/5.0 (compatible; AhrefsBot/5.0; +http://ahrefs.com/robot/)","time":"1379725272","size":"35828","path":"/items/search/s/2292479051/1/"},1379725272],[{"user":"-","method":"GET","referer":"-","code":"200","host":"69.30.238.26","agent":"Mozilla/5.0 (compatible; MJ12bot/v1.4.4; http://www.majestic12.co.uk/bot.php?+)","time":"1379725895","size":"12040","path":"/items/search/d/2275256051/1/Carole%20Bugg%C3%A9"},1379725895],[{"user":"-","method":"GET","referer":"-","code":"200","host":"69.30.238.26","agent":"Mozilla/5.0 (compatible; MJ12bot/v1.4.4; http://www.majestic12.co.uk/bot.php?+)","time":"1379725898","size":"12048","path":"/items/search/d/2275256051/1/China%20Mi%C3%A9ville"},1379725898]]

jsonファイルが出来て、中身も確認できました。

jqで見やすく

ついでに、せっかくなのでjq はてなブックマーク - jqで見やすくしてみます

$ td query -w -d test -o ./result.json -f json "select * from kndl limit 3" && cat ./result.json | jq "."
Job 4920691 is queued.
Use 'td job:show 4920691' to show the status.
queued...
  started at 2013-09-25T14:42:15Z
  Hive history file=/mnt/hive/tmp/2655/hive_job_log__21142916.txt
  OK
  MapReduce time taken: 0.281 seconds
  Time taken: 2.904 seconds
  finished at 2013-09-25T14:42:31Z
Status     : success
Result     :
written to ./result.json in json format
[
  [
    {
      "path": "/items/search/d/2275256051/1/%E8%B3%80%E6%9D%B1%20%E6%8B%9B%E4%BA%8C",
      "user": "-",
      "method": "GET",
      "code": "200",
      "referer": "-",
      "host": "119.235.237.93",
      "time": "1378861656",
      "agent": "Yeti/1.0 (NHN Corp.; http://help.naver.com/robots/)",
      "size": "37451"
    },
    1378861656
  ],
  [
    {
      "path": "/items/detail/B0053CT2FO",
      "user": "-",
      "method": "GET",
      "code": "200",
      "referer": "-",
      "host": "109.87.126.146",
      "time": "1378863414",
      "agent": "Mozilla/5.0 (compatible; MJ12bot/v1.4.4; http://www.majestic12.co.uk/bot.php?+)",
      "size": "15837"
    },
    1378863414
  ],
  [
    {
      "path": "/items/detail/B005DNQQPW",
      "user": "-",
      "method": "GET",
      "code": "200",
      "referer": "-",
      "host": "198.74.231.14",
      "time": "1378863415",
      "agent": "Mozilla/5.0 (compatible; MJ12bot/v1.4.4; http://www.majestic12.co.uk/bot.php?+)",
      "size": "18835"
    },
    1378863415
  ]
]

実際にはこんなかんじで色ついてます

f:id:Okisanjp:20160714124440p:plain