Elasticsearchで複数フィールドにまたがるGROUP BY的なAggregationsを指定する

Elasticsearch でちょっと複雑な集計をしようと思った場合、 SQL とは若干感覚が違うので書いておきます。正直 Elasticsearch はボチボチ触り始めた程度なので、もっとスマートなやり方があるとかそのままだと問題あるよとかあったら教えて欲しいです。

先に言っておくとサンプルコードの類は直接は実行していないためシンタックスの不備とか細かいミスあるかもしれませんが備忘録ってことで......

以下は参考にしたサイトなど:

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

マッピングの準備

今回はブログの記事ごとにデバイス別 PV / UU 数を取得する例を用います。

ここでは article_access_logs というタイプがあって article_id に記事 ID が入っていて、 session_id で UU をカウントするようにします。 device_type には pc / tablet / smartphone が入ってくるとしましょう。

article_access_logs:

{
  "_source": {
    "enabled": true
  },
  "properties": {
    "timestamp": {
      "type": "date"
    },
    "article_id": {
      "type": "integer"
    },
    "session_id": {
      "type": "string",
      "index": "not_analyzed"
    },
    "device_type": {
      "type": "string",
      "index": "not_analyzed"
    }
  }
}

SQL で集計する

先に馴染み深い SQL で集計するサンプルを書いておきます。一応 MySQL での書き方ですが、こんな感じになるかと思います。 timestamp は Elasticsearch 側にはミリ秒で入ってるんで若干異なりますが細かいところは置いといて。

2日前から現在までの記録を日別で集計しています。

SELECT
    FROM_UNIXTIME(`timestamp`, '%Y-%m-%d') AS `day`, `article_id`, `device_type`, COUNT(`article_id`), COUNT(DISTINCT `session_id`)
FROM
    `article_access_logs`
WHERE
    `day` >= CURRENT_DATE() + INTERVAL -2 DAY
GROUP BY
    `day`, `article_id`, `device_type`
;

Elasticsearch で集計する

前述の SQL でやったようなこととだいたい同じようなことを Elasticsearch のクエリにしたものがこちら。 aggs は aggregations の略でこれが集計用の指定になっているのでこれを用いるのですが、 Aggregations はどうやら1度に1つのフィールドしか指定できない模様。複数フィールドでグルーピングしたい場合、さらに別条件で Aggregations をネストしていくのが Elasticsearch 流らしいです。

{
  "aggs": {
    "article_access_counting": {
      "filter": {
        "range": {
          "timestamp": {
            "gte": "now-2d",
            "lte": "now",
            "time_zone": "+9:00"
          }
        }
      },
      "aggs": {
        "by_day": {
          "date_histogram": {
            "field": "timestamp",
            "interval": "day",
            "format": "yyyy-MM-dd",
            "time_zone": "+9:00"
          },
          "aggs": {
            "by_article_id": {
              "terms": {
                "field": "article_id",
                "size": 0
              },
              "aggs": {
                "by_device_type": {
                  "terms": {
                    "field": "device_type"
                  },
                  "aggs": {
                    "uu": {
                      "cardinality": {
                        "field": "session_id"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

aggs: article_access_counting

基本的に aggregations を指定する際はキーも指定する必要がある模様。とりあえずルート的なものはそれっぽい名前をつけてあります。

ここではまず filter を用いて対象範囲の絞り込みを行ってます。今のサンプルだと相対的な指定なのでタイムゾーンはいらないかもしれませんが、 yyyy-MM-dd 形式などで絶対指定するならタイムゾーンがあったほうが確実。

実際にグルーピングのキーとなるフィールドの指定は terms で行うのですが、この Aggregations にはすでに filter で絞り込みが行われているため同時に指定することはできず、ネストする必要があります。

aggs: by_day

期間での絞り込みができた状態で、日付ごとにグルーピングする Aggregations がこちら。タイムスタンプを元に日付形式でグルーピングするには date_histogram を用います。この Aggregations だけの結果を見ると、日別の PV が集計可能な状態です。

キーの名前ですが、絞り込みの意図が分かりやすいように by_* 形式にしています。

aggs: by_article_id

さらに記事別にグルーピングします。 terms を用いてますが、これが Elasticsearch の集計でもっともシンプルな指定です。

それと、Elasticsearch はsize (SQL でいう LIMIT 句) を指定しないと 10 件しか結果を返してこないため、 size を 0 にして上限をなくしています。

ただここのsize: 0 はちょっと自信なくて、これだと記事数とアクセス数が多い場合に結果自体が膨れ上がる可能性があって、それをどう対策していいのかがわからないんですよね。 Aggregations のページングはまだサポートされてないのでそれができたらちょっとクエリいじればいいんですけど、現時点だとどうするのがよいのかは調べ切れていません。 Parent-Child でやれとか書いてあったりするのを見たのですがまだよくわかっておらず。

Paging support for aggregations #4915

aggs: by_device_type

記事別の結果を更にデバイスでグルーピングします。ここは terms なので特に言うことないですが、 Aggregations は対象が何件あるかを自動的に計算してくれるので、これでデバイス別の PV は取得できた状態です。

aggs: uu

最後に uu ですが、 COUNT(DISTINCT ...) 的な指定は cardinality でフィールドを指定すればできます。これで記事ごとにデバイス別 PV / UU が取得できます。

これは単に集計した値を取るだけなので、値の名前としてキーは uu のみにしています。

結果を使いやすいように PHP で整形する

この結果はかなりネストが深い感じになるので、とりあえず必要な部分だけ PHP で抜き出してみます。 SQL は表形式で結果が返ってきますが Elasticsearch はネストされているので変数の初期化はしやすい気がします。

// use Elasticsearch\Client
$result = $elasticsearch->query($params);

$data = [];

foreach ($result['aggregations']['article_access_counting']['by_day']['buckets'] as $resultByDay) {
    $day = $resultByDay['key_as_string'];

    $data[$day] = [];

    foreach ($resultByDay['by_article_id']['buckets'] as $resultByArticleId) {
        $articleId = $resultByArticleId['key'];

        $data[$day][$articleId] = [];

        foreach ($resultByArticleId['by_device_type']['buckets'] as $resultByDeviceType) {
            $deviceType = $resultByDeviceType['key'];

            $data[$day][$articleId][$deviceType] = [
                'pv' => $resultByDeviceType['doc_count'],
                'uu' => $resultByDeviceType['uu']['value'],
            ];
        }
    }
}

var_dump($data);

結果

表示は多少いじっていますがこんな感じです。ここまでくればもうアプリケーションで使える状態になってますね。

[
    '2015-09-19' => [
        5 => [
            'pc' => [
                'pv' => 36,
                'uu' => 10,
            ],
        ],
        6 => [
            'pc' => [
                'pv' => 100,
                'uu' => 25,
            ],
            'tablet' => [
                'pv' => 38,
                'uu' => 4,
            ],
        ],
    ],
    '2015-09-20' => [
        5 => [
            'pc' => [
                'pv' => 38,
                'uu' => 7,
            ],
            'smartphone' => [
                'pv' => 23,
                'uu' => 11,
            ],
        ],
        6 => [
            'pc' => [
                'pv' => 46,
                'uu' => 31,
            ],
        ],
    ],
    '2015-09-21' => [
        6 => [
            'pc' => [
                'pv' => 24,
                'uu' => 11,
            ],
            'smartphone' => [
                'pv' => 18,
                'uu' => 7,
            ],
        ],
    ],
];

まとめ

Elasticsearch で複雑な集計をしたい場合は Aggregations をネストして行います。クエリは複雑っぽくなりますが、絞り込み条件を1つ1つ分解していく過程が可視化されるため、結果のイメージがつきやすいなとは感じました。

まだ不明なのが、インデックスが大きくなってきたときにこのようなクエリがどの程度のパフォーマンスで動作するのかと、ページング周りですね。

Elasticsearch 自体まだ大して使いこなせてないのですが中々使いやすくて素敵なので色々調べていこうと思います。