LoginSignup
121
128

More than 5 years have passed since last update.

@jtodo(junya todo)

Elasticsearchの"Aggregations"を使って SQLの"Group by"を置き換える

Last updated at Posted at 2014-02-22

はじめに

Elasticsearchのv1.0.0 が無事リリースされ、Aggregations APIの利用が可能になりました。

Elasticsearchはこれまで、検索結果を集約して解析する仕組みとしてFacets APIを提供していましたが、実質SQLのGroup byでのcountに相当する機能しかなかったため、maxやavgといった複雑な条件で集約を行いたい場合、クエリを分けて集約したり解析したりするしかありませんでした。

v1.0.0でAggregations APIが提供されたことで、maxやavgはもちろん、日付でヒストグラムを取得するといった複雑な集約も楽に行えるようになり、SQL型検索エンジンからの置き換えも行いやすくなってきたのではないかと思います。

内容

Aggregationsで何ができるのか、SQLのGroup byとの置き換えを例に検証してみましたので、その共有です。

Aggregations

詳しい説明は公式に書いてありますので省きますが、Facets APIに代わる次世代データ分析クエリという位置づけで、以下のような集約や複雑なフィルターができます。
将来、Facets APIが非推奨になるかどうかはまだ決まってないようです。

Aggregationクエリ 内容
min 最小値
max 最大値
sum 合計値
avg 平均値
stats count,max,sum,avg 全部
terms 値の種類ごとに集約
range 範囲ごとに集約
histogram 一定範囲ごとに集約
...

[公式URL] http://www.elasticsearch.org/guide/en/elasticsearch/reference/1.x/search-aggregations.html

検証環境

$ cat /etc/redhat-release
centos release 6.4 (final)

データフォーマット

(株)ドワンゴと国立情報学研究所さんのニコニコデータセットを利用しました。
http://www.nii.ac.jp/cscenter/idr/nico/nicocomm-apply.html

Elasticsearchのデータは、Fluentdのtail inputで一括投入すると楽に作れます。
その辺りの記事もElasticsearchタグでいずれ書きます。

Aggregationsを使ったSQL Group byとの置き換えの例

1. 集計関数の為にGROUP BYを実行する

  • SQLの場合

movie_type毎の件数・再生時間(最長・最短・平均)を取得するSQL

SELECT
    movie_type, COUNT(movie_type), MAX(length), MIN(length), AVG(length)
FROM
    nicodata
GROUP BY
    movie_type;
  • ESの場合
curl -XGET http://localhost:9200/nicodata/_search?pretty -d '
{
  "aggs": {
    "my_movie_type_term": {
      "terms": {
        "field": "movie_type",
        "order": { "_count" : "desc" },
        "size": 3
      },
      "aggs": {
        "my_max_length": {
          "max": { "field": "length" }
        },
        "my_min_length": {
          "min": { "field": "length" }
        },
        "my_avg_length": {
          "avg": { "field": "length" }
        }
      }
    }
  }
}'

解説
"aggs"は"aggregations"の略でどちらでも使えます。
"my_xxx" はSQLのAS(別名)のようなもので、一つのaggregationクエリを表します。

最初のaggregationクエリは"terms"によってmovie_typeごとに集約したカウントを取得しています。
更に"aggs"の入れ子と"max","min","avg"によって、movie_typeごとに最大値や平均値を取得しています。

_countは結果のdoc_count順の並べ替えです。代わりにアルファベット順並べ替えを行う場合は、order: _termとするとよいでしょう。

結果

{
  ...

  "aggregations" : {
    "my_movie_type_term": {
      "buckets" : [ {
        "key" : "mp4",
        "doc_count" : 426778,
        "my_max_length" : {
          "value" : 37600.0
        },
        "my_min_length" : {
          "value" : 1.0
        },
        "my_avg_length" : {
          "value" : 711.5485451452512
        }
      }, {
        "key" : "flv",
        "doc_count" : 11848,
        "my_max_length" : {
          "value" : 9313.0
        },
        "my_min_length" : {
          "value" : 1.0
        },
        "my_avg_length" : {
          "value" : 664.2181802835921
        }
      }, {
        "key" : "swf",
        "doc_count" : 9672,
        "my_max_length" : {
          "value" : 7379.0
        },
        "my_min_length" : {
          "value" : 1.0
        },
        "my_avg_length" : {
          "value" : 246.87065756823822
        }
      } ]
    }
  }
}

2. 月別集計の為のGROUP BYを実行する

  • SQLの場合

月毎の投稿件数を取得するSQL

SELECT
    DATE_FORMAT(upload_time, '%Y-%m') AS upload_time, COUNT(*)
FROM
    nicodata
GROUP BY
    DATE_FORMAT(upload_time, '%Y-%m');
  • ESの場合
curl -XGET http://localhost:9200/nicodata/_search?pretty -d '
{
  "aggs" : {
    "my_upload_time_histogram" : {
      "date_histogram" : {
        "field" : "upload_time",
        "interval" : "1M",
        "format" : "yyyy-MM-dd"
      }
    }
  }
}'

解説
"date_histogram"は先程まで使っていた"terms"と同じAggregationクエリの一つです。"histogram"のdate版で、日時範囲ごとに集約を行います。

"field"は対象としたいindexのfield名、"interval"は範囲で"1d","24h"..のように指定します。
"format"は結果のタイムスタンプを加工して"key_as_string"として取得したいフォーマットを指定します。

結果

{
  ...

  "aggregations" : {
    "my_upload_time_histogram" : {
      "buckets" : [ {
        "key_as_string" : "2012-04-01",
        "key" : 1333238400000,
        "doc_count" : 10651
      }, {
        "key_as_string" : "2012-05-01",
        "key" : 1335830400000,
        "doc_count" : 153742
      }, {
        "key_as_string" : "2012-09-01",
        "key" : 1346457600000,
        "doc_count" : 113462
      }, {
        "key_as_string" : "2012-10-01",
        "key" : 1349049600000,
        "doc_count" : 170443
      } ]
    }
  }
}

0件の月も結果に含めるには、"min_doc_count"を使うとよいでしょう。

curl -XGET http://localhost:9200/nicodata/_search?pretty -d '
{
  "aggs" : {
    "my_upload_time_histogram" : {
      "date_histogram" : {
        "field" : "upload_time",
        "interval" : "1M",
        "format" : "yyyy-MM-dd",
        "min_doc_count" : 0
      }
    }
  }
}'

結果

{
  ...

  "aggregations" : {
    "my_upload_time_histogram" : {
      "buckets" : [ {
        "key_as_string" : "2012-04-01",
        "key" : 1333238400000,
        "doc_count" : 10651
      }, {
        "key_as_string" : "2012-05-01",
        "key" : 1335830400000,
        "doc_count" : 153742
      }, {
        "key_as_string" : "2012-06-01",
        "key" : 1338508800000,
        "doc_count" : 0
      }, {
        "key_as_string" : "2012-07-01",
        "key" : 1341100800000,
        "doc_count" : 0
      }, {
        "key_as_string" : "2012-08-01",
        "key" : 1343779200000,
        "doc_count" : 0
      }, {
        "key_as_string" : "2012-09-01",
        "key" : 1346457600000,
        "doc_count" : 113462
      }, {
        "key_as_string" : "2012-10-01",
        "key" : 1349049600000,
        "doc_count" : 170443
      } ]
    }
  }
}

3. サブクエリとしてGROUP BYを実行する　⇒未解決

[追記]
サブクエリを置き換える形ではありませんが、Elasticsearchのバージョンアップによって、例示したSQLの置き換えは可能になりました。

v1.3.0で追加された Top Hits Aggregation により、グループ毎の並べ替えやメタ情報の取得が可能になり、置き換えられるSQLの幅がより広がったのではないかと思います。

※追記前とデータが変わっているので、取得内容はニコニコデータセットと一致しません。

  • SQLの場合

動画種別(flv,mp4,etc)毎に、もっともIDが古い動画のメタ情報を取得するSQL

SELECT
    n.video_id, title, thumbnail_url, movie_type
FROM
    nicodata n,
    (SELECT MIN(video_id) AS video_id FROM nicodata GROUP BY movie_type) n1
WHERE
    n.video_id = n1.video_id;
  • ESの場合
curl -XGET http://localhost:9200/nicodata/_search?pretty -d '
{
  "aggs": {
    "my_movie_type_term": {
      "terms": {
        "field": "movie_type"
      },
      "aggs": {
        "my_top_hit": {
          "top_hits": {
            "sort": [{ "video_id": { "order": "asc" } }],
            "size": 1
          }
        }
      }
    }
  }
}'

解説
"terms"は、グループ化されたデータ（Elasticsearchでは、bucketと呼ばれている）を作る役割があり、"top_hits"は、それぞれのbucketから並べ替えた上位のドキュメントを選び取る役割を持っています。

後者のようにbucketから条件や値に基づいて抽出を行うAggregationクエリを、Elasticsearchではmetric aggregationと呼んでいます。

結果

{
  ...

  "aggregations": {
    "my_movie_type_term": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "flv",
          "doc_count": 11848,
          "my_top_hit": {
            "hits": {
              "total": 2,
              "max_score": null,
              "hits": [
                {
                  "_index": "nicodata",
                  "_type": "video",
                  "_id": "sm16",
                  "_score": null,
                  "_source": {
                    "video_id": "sm16",
                    "movie_type": "flv",
                    "thumbnail_url": "http://tn-skr1.smilevideo.jp/smile?i=16",
                    "title": "LIVE! THE IDOLM@STER アイドルマスター relations 演歌ver"
                  },
                  "sort": [
                    "sm16"
                  ]
                }
              ]
            }
          }
        },
        {
          "key": "mp4",
          "doc_count": 426778,
          "my_top_hit": {
            "hits": {
              "total": 1,
              "max_score": null,
              "hits": [
                {
                  "_index": "nicodata",
                  "_type": "video",
                  "_id": "so20753557",
                  "_score": null,
                  "_source": {
                    "video_id": "so20753557",
                    "movie_type": "mp4",
                    "thumbnail_url": "http://tn-skr2.smilevideo.jp/smile?i=20753557",
                    "title": "【聞いてみたシリーズ】吉本彩編"
                  },
                  "sort": [
                    "so20753557"
                  ]
                }
              ]
            }
          }
        },
        {
          "key": "swf",
          "doc_count": 9672,
          "my_top_hit": {
            "hits": {
              "total": 1,
              "max_score": null,
              "hits": [
                {
                  "_index": "nicodata",
                  "_type": "video",
                  "_id": "nm2522142",
                  "_score": null,
                  "_source": {
                    "video_id": "nm2522142",
                    "movie_type": "swf",
                    "thumbnail_url": "http://tn-skr3.smilevideo.jp/smile?i=2522142",
                    "title": "初心者による初心者のための初心者のNMM使い方講座"
                  },
                  "sort": [
                    "nm2522142"
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  }
}

おわりに

現状最新はv1.4.0ですが、この時点でAggregationsにはfromに相当する機能がない為、ページングをするようなケースでは思ったように運用ができないこともありそうです。

ただ、ページングが必要になるほどbucketsを分割するようなケースでは、parent/childを利用したほうがよいと思われますので、件数に応じて使い分けるとよいでしょう。

最後に、ここで紹介した以外にも、バージョンアップのたびに新しいAggregationクエリは増えてきているので、ぜひ公式ドキュメントも参照してみてください。
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations.html

121
128
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
121
128