要旨

jsonなどのデータを臨機応変に集計したいとき、あると思います。
もちろん自分でスクリプトを書くとかjqなどのコマンドを駆使して頑張るのもいいけど、
Apache Drillを使うと、SQLで集計できるので、SQLできる人ははかどりますよ！というお話。

本題

Story

「今稼働しているVMから、名前がかぶっているインスタンスがあるか調べてくれない？」
たとえば、ある日突然お客さんからこんな依頼が来たとします。
そんなときも安心、そうApache Drillならね。

やりたいこと

CloudStackのAPIで取得できるVMの情報から、同じドメインに同じなまえのインスタンスが存在するか？ を調査したい！

データ

JSONレイアウト

対象のデータは こんな感じのJSONが

1
2
3
4
5
6
7
8
9
10
11
12
13

{
  "listvirtualmachinesresponse": {
    "count": 99999,
    "virtualmachine": [
      {
       "VMのJSON"
      },
      {
        "VMのJSON"
      }
    ]
  }
}

こんな感じにラップされてます。
では、このJSONをApacheDrillで集計してみましょう。

集計

準備

インストールはMacならHomeBrewで

1

brew install drill

SQLの実行

準備は整ったので実際に集計してみましょう！

drillの起動

まず、Drillを起動します。

1

drill-embedded

起動すると、Drillのプロンプトが返ってくるので、そこにおもむろにSQLを叩き込みます。

SQLを実行する

あとは、SQLを実行するだけ。
いつものSQLと違うのはFROM句でテーブルを指定せず、ファイルを指定するくらいです。

1
2
3
4
5
6

select vms.vm.name, vms.vm.domain, count(*)
from ( select flatten(t.listvirtualmachinesresponse.virtualmachine) as vm from dfs.`/Users/yenjoji/Downloads/listVirtualMachines.json` as t) as vms
group by vms.vm.name, vms.vm.domain
having count(*) > 1
order by vms.vm.name
;

おまけ

私は、みんな大好きエクセルで後加工したかったので、こんなオプションつけました。

• ヘッダを最初だけにする

  1	!set headerinterval 0

• 出力フォーマットをTSVにする

  1	!set outputformat tsv

• 出力をファイルに保存する

  1	!record result.tsv

と、しておくとTSVになるので、Excelにはっつけてピボットテーブルでどうにかしたりなど加工し放題です。

感想

• JSONをSQLでサクッと検索できるのはちょっとうれしい。
• 1回使う手順を覚えてしまえば、2回め以降が楽になるし、ちょっと集計方法を変えたりなんかの対応が柔軟に出来る。