この記事はモチベーションクラウドシリーズ Advent Calendar 2020の2日目の記事になります。
ElasticsearchでRDBのjoinのようなことを試す機会があったので、そこまでの道のりを備忘録的にまとめてみました。
この記事のゴール
以下のようなテストの結果を持つユーザーテーブルと、ユーザーの属性（性別や学年などをイメージして欲しい）を管理するテーブルがあるとします。
このテーブルをRDBでいうところのjoinをして、属性ごとにグルーピングしテストの結果を集計することをゴールとします。
users table
| id | name | favorite | english | math | chemistry |
|---|---|---|---|---|---|
| 1 | モチベ太郎 | 開発 | 100 | 100 | 100 |
| 2 | モチベ次郎 | 筋トレ | 50 | 60 | 85 |
| 3 | モチベ三郎 | モチベーションの向上 | 70 | 90 | 80 |
user_attributes table
| id | user_id | attribute_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 6 | 3 | 2 |
| 7 | 3 | 3 |
RDBで実行
select attribute_id, AVG(english), AVG(math), AVG(chemistry)
from users
inner join user_attributes on users.id = user_attributes.user_id
group by user_attributes.attribute_id
実行結果
attribute_id AVG(english) AVG(math) AVG(chemistry)
1 75.0000 80.0000 92.5000
2 73.3333 83.3333 88.3333
3 85.0000 95.0000 90.0000
Elasticsearchの環境
Amazon Elasticsearch Serviceを利用しました。
ドメインの作成方法はAWSの公式ドキュメント（Amazon Elasticsearch Service ドメインの作成と管理）を参考にしてます。
途中でマスターユーザーの作成を行いますが、後述のKibanaで利用します。
この記事の内容を実行するだけなら、docker上に構築した環境でも問題ありません。
Elasticsearchバージョン
7.8
Kibanaログイン
Amazon Elasticsearch Serviceドメインを作成するとKibanaも使用できるようになります。
Kibana は Elasticsearch で稼働するように設計された、ポピュラーなオープンソースの可視化ツールです。
今回はKibanaを使ってクエリの実行を行います。
ログイン手順
作成したドメインのダッシュボードにKibanaの項目があるのでリンクをクリックします。
Kibanaのログイン画面に遷移したらドメイン作成時に作成したマスターユーザーとパスワードでログインします。
ログインに成功したらサイドバーを開きDev Toolsを開きます。
Console画面でクエリを実行していきます。
左のエリアにクエリを入力し実行すると右のエリアに結果が表示されます。
レスポンスタイムも表示されるのでパフォーマンス検証にも活用できます。
ちょっと練習
まずはKibanaでクエリの実行を試したいと思います。
document作成
まずはdocument作成していきます。
1件ずつ登録
indexの作成をしなくてもdocument作成時に指定したindexが無ければ自動で作成されるので省略します。
PUT hoge/_doc/1
{
"name": "モチベ太郎",
"favorite": "開発",
"english": 100,
"math": 100,
"chemistry": 100
}
PUT hoge/_doc/2
{
"name": "モチベ次郎",
"favorite": "筋トレ",
"english": 50,
"math": 60,
"chemistry": 85
}
PUT hoge/_doc/3
{
"name": "モチベ三郎",
"favorite": "モチベーションの向上",
"english": 70,
"math": 90,
"chemistry": 80
}
それぞれ以下が返れば成功
結果
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "1",
"_version" : 1,
"result" : "created",
"_shards" : {
"total" : 2,
"successful" : 1,
"failed" : 0
},
"_seq_no" : 0,
"_primary_term" : 1
}
一括で登録
bulkを使用すれば一括で登録することも可能です。
POST hoge/_bulk
{"index":{"_index":"hoge","_id":"1"}}
{"name":"モチベ太郎","favorite":"開発","english":100,"math":100,"chemistry":100}
{"index":{"_index":"hoge","_id":"2"}}
{"name":"モチベ次郎","favorite":"筋トレ","english":50,"math":60,"chemistry":85}
{"index":{"_index":"hoge","_id":"3"}}
{"name":"モチベ三郎","favorite":"モチベーションの向上","english":70,"math":90,"chemistry":80}
以下が返れば成功
結果
{
"errors" : false,
"items" : [
{
"index" : {
"_index" : "hoge",
"_type" : "_doc",
"_id" : "1",
"_version" : 1,
"result" : "created",
"_shards" : {
"total" : 2,
"successful" : 2,
"failed" : 0
},
"_seq_no" : 0,
"_primary_term" : 1,
"status" : 201
}
},
{
"index" : {
"_index" : "hoge",
"_type" : "_doc",
"_id" : "2",
"_version" : 1,
"result" : "created",
"_shards" : {
"total" : 2,
"successful" : 2,
"failed" : 0
},
"_seq_no" : 1,
"_primary_term" : 1,
"status" : 201
}
},
{
"index" : {
"_index" : "hoge",
"_type" : "_doc",
"_id" : "3",
"_version" : 1,
"result" : "created",
"_shards" : {
"total" : 2,
"successful" : 2,
"failed" : 0
},
"_seq_no" : 2,
"_primary_term" : 1,
"status" : 201
}
}
]
}
documentの取得
次に作成したdocumentの取得をします。
全件取得
GET hoge/_search
{
"query": {
"match_all": {}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "1",
"_score" : 1.0,
"_source" : {
"name" : "モチベ太郎",
"favorite" : "開発",
"english" : 100,
"math" : 100,
"chemistry" : 100
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "2",
"_score" : 1.0,
"_source" : {
"name" : "モチベ次郎",
"favorite" : "筋トレ",
"english" : 50,
"math" : 60,
"chemistry" : 85
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "3",
"_score" : 1.0,
"_source" : {
"name" : "モチベ三郎",
"favorite" : "モチベーションの向上",
"english" : 70,
"math" : 90,
"chemistry" : 80
}
}
]
}
検索して取得
GET hoge/_search
{
"query": {
"term": {
"name.keyword": {
"value": "モチベ太郎"
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 0.9808291,
"hits" : [
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "1",
"_score" : 0.9808291,
"_source" : {
"name" : "モチベ太郎",
"favorite" : "開発",
"english" : 100,
"math" : 100,
"chemistry" : 100
}
}
]
}
}
集計クエリを使う
Elasticsearchには便利な集計クエリが用意されています。
group byやavgは集計クエリを使うことで簡単にできます。
平均値取得
avgを使います。
GET hoge/_search
{
"aggs": {
"english_avg": {
"avg": {
"field": "english"
}
},
"math_avg": {
"avg": {
"field": "math"
}
},
"chemistry_avg": {
"avg": {
"field": "chemistry"
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "1",
"_score" : 1.0,
"_source" : {
"name" : "モチベ太郎",
"favorite" : "開発",
"english" : 100,
"math" : 100,
"chemistry" : 100
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "2",
"_score" : 1.0,
"_source" : {
"name" : "モチベ次郎",
"favorite" : "筋トレ",
"english" : 50,
"math" : 60,
"chemistry" : 85
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "3",
"_score" : 1.0,
"_source" : {
"name" : "モチベ三郎",
"favorite" : "モチベーションの向上",
"english" : 70,
"math" : 90,
"chemistry" : 80
}
}
]
},
"aggregations" : {
"english_avg" : {
"value" : 73.33333333333333
},
"chemistry_avg" : {
"value" : 88.33333333333333
},
"math_avg" : {
"value" : 83.33333333333333
}
}
}
平均値のみ取得
"size": 0を指定します。
GET hoge/_search
{
"size": 0,
"aggs": {
"english_avg": {
"avg": {
"field": "english"
}
},
"math_avg": {
"avg": {
"field": "math"
}
},
"chemistry_avg": {
"avg": {
"field": "chemistry"
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"english_avg" : {
"value" : 73.33333333333333
},
"chemistry_avg" : {
"value" : 88.33333333333333
},
"math_avg" : {
"value" : 83.33333333333333
}
}
}
グルーピングして取得
termsを使います。
GET hoge/_search
{
"size": 0,
"aggs": {
"name_aggs": {
"terms": {
"field": "name.keyword"
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"name_aggs" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "モチベ三郎",
"doc_count" : 1
},
{
"key" : "モチベ太郎",
"doc_count" : 1
},
{
"key" : "モチベ次郎",
"doc_count" : 1
}
]
}
}
}
Join field type を使う
いよいよ本題に入っていきます。
ElasticsearchでjoinするにはJoin field typeを使用します。
Join field typeとは同じindexのdocument内に親子関係を作成する特別なフィールドです。
親子関係の定義
まずはdocumentが親子関係になるように親子関係の定義をします。
userが親、user_attributeが子になるように親子関係を定義します。
PUT hoge/_mapping
{
"properties": {
"join_field": {
"type": "join",
"relations": {
"user": "user_attribute"
}
}
}
}
以下が返れば成功
結果
{
"acknowledged" : true
}
GET hoge/_mapping
以下が返れば成功
結果
{
"hoge" : {
"mappings" : {
"properties" : {
... # 省略
"join_field" : {
"type" : "join",
"eager_global_ordinals" : true,
"relations" : {
"user" : "user_attribute"
}
},
... # 省略
}
親になるdocumentの作成
まず親になるdocumentを作成します。
親になるdocumentには親子関係の定義で設定したjoin_fieldにname: userを指定します。
PUT hoge/_doc/1
{
"name": "モチベ太郎",
"favorite": "開発",
"english": 100,
"math": 100,
"chemistry": 100,
"join_field": {
"name": "user"
}
}
PUT hoge/_doc/2
{
"name": "モチベ次郎",
"favorite": "筋トレ",
"english": 50,
"math": 60,
"chemistry": 85,
"join_field": {
"name": "user"
}
}
PUT hoge/_doc/3
{
"name": "モチベ三郎",
"favorite": "モチベーションの向上",
"english": 70,
"math": 90,
"chemistry": 80,
"join_field": {
"name": "user"
}
}
子になるdocumentの作成
次に子になるdocumentを作成します。
子になるdocumentにはjoin_fieldにname: user_attributeとparent: {親になるdocumetn_id}を指定します。
また、URLパラメータにrouting=親になるdocumetn_idを指定します。
PUT hoge/_doc/4?routing=1
{
"user_id": 1,
"attribute_id": 1,
"join_field": {
"name": "user_attribute" ,
"parent": 1
}
}
PUT hoge/_doc/5?routing=1
{
"user_id": 1,
"attribute_id": 2,
"join_field": {
"name": "user_attribute" ,
"parent": 1
}
}
PUT hoge/_doc/6?routing=1
{
"user_id": 1,
"attribute_id": 3,
"join_field": {
"name": "user_attribute" ,
"parent": 1
}
}
PUT hoge/_doc/7?routing=2
{
"user_id": 2,
"attribute_id": 1,
"join_field": {
"name": "user_attribute" ,
"parent": 2
}
}
PUT hoge/_doc/8?routing=2
{
"user_id": 2,
"attribute_id": 2,
"join_field": {
"name": "user_attribute" ,
"parent": 2
}
}
PUT hoge/_doc/9?routing=3
{
"user_id": 3,
"attribute_id": 2,
"join_field": {
"name": "user_attribute" ,
"parent": 3
}
}
PUT hoge/_doc/10?routing=3
{
"user_id": 3,
"attribute_id": 3,
"join_field": {
"name": "user_attribute" ,
"parent": 3
}
}
user_attributeを子に持つ親を取得
データの投入ができたのでデータの取得を行なっていきます。
has_childクエリを使用して子documentから親documentを取得します。
全件取得
GET hoge/_search
{
"query": {
"has_child": {
"type": "user_attribute",
"query": {
"match_all": {}
}
}
}
}
以下が返れば成功
結果
..., # 省略
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "1",
"_score" : 1.0,
"_source" : {
"name" : "モチベ太郎",
"favorite" : "開発",
"english" : 100,
"math" : 100,
"chemistry" : 100,
"join_field" : {
"name" : "user"
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "2",
"_score" : 1.0,
"_source" : {
"name" : "モチベ次郎",
"favorite" : "筋トレ",
"english" : 50,
"math" : 60,
"chemistry" : 85,
"join_field" : {
"name" : "user"
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "3",
"_score" : 1.0,
"_source" : {
"name" : "モチベ三郎",
"favorite" : "モチベーションの向上",
"english" : 70,
"math" : 90,
"chemistry" : 80,
"join_field" : {
"name" : "user"
}
}
}
]
}
}
子のフィールドを検索
GET hoge/_search
{
"query": {
"has_child": {
"type": "user_attribute",
"query": {
"term": {
"attribute_id": {
"value": 1
}
}
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "1",
"_score" : 1.0,
"_source" : {
"name" : "モチベ太郎",
"favorite" : "開発",
"english" : 100,
"math" : 100,
"chemistry" : 100,
"join_field" : {
"name" : "user"
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "2",
"_score" : 1.0,
"_source" : {
"name" : "モチベ次郎",
"favorite" : "筋トレ",
"english" : 50,
"math" : 60,
"chemistry" : 85,
"join_field" : {
"name" : "user"
}
}
}
]
}
}
userを親にもつ子を取得する
反対に親documentから子documentを取得する場合はhas_parentクエリを使用します。
userを親にもつ子documentを全件取得
GET hoge/_search
{
"query": {
"has_parent": {
"parent_type": "user",
"query": {
"match_all": {}
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 7,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "4",
"_score" : 1.0,
"_routing" : "1",
"_source" : {
"user_id" : 1,
"attribute_id" : 1,
"join_field" : {
"name" : "user_attribute",
"parent" : 1
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "5",
"_score" : 1.0,
"_routing" : "1",
"_source" : {
"user_id" : 1,
"attribute_id" : 2,
"join_field" : {
"name" : "user_attribute",
"parent" : 1
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "6",
"_score" : 1.0,
"_routing" : "1",
"_source" : {
"user_id" : 1,
"attribute_id" : 3,
"join_field" : {
"name" : "user_attribute",
"parent" : 1
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "7",
"_score" : 1.0,
"_routing" : "2",
"_source" : {
"user_id" : 2,
"attribute_id" : 1,
"join_field" : {
"name" : "user_attribute",
"parent" : 2
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "8",
"_score" : 1.0,
"_routing" : "2",
"_source" : {
"user_id" : 2,
"attribute_id" : 2,
"join_field" : {
"name" : "user_attribute",
"parent" : 2
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "9",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"user_id" : 3,
"attribute_id" : 2,
"join_field" : {
"name" : "user_attribute",
"parent" : 3
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "10",
"_score" : 1.0,
"_routing" : "3",
"_source" : {
"user_id" : 3,
"attribute_id" : 3,
"join_field" : {
"name" : "user_attribute",
"parent" : 3
}
}
}
]
}
}
親のフィールドを検索
もちろん検索もできます
GET hoge/_search
{
"query": {
"has_parent": {
"parent_type": "user",
"query": {
"term": {
"name.keyword": {
"value": "モチベ太郎"
}
}
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "4",
"_score" : 1.0,
"_routing" : "1",
"_source" : {
"user_id" : 1,
"attribute_id" : 1,
"join_field" : {
"name" : "user_attribute",
"parent" : 1
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "5",
"_score" : 1.0,
"_routing" : "1",
"_source" : {
"user_id" : 1,
"attribute_id" : 2,
"join_field" : {
"name" : "user_attribute",
"parent" : 1
}
}
},
{
"_index" : "hoge",
"_type" : "_doc",
"_id" : "6",
"_score" : 1.0,
"_routing" : "1",
"_source" : {
"user_id" : 1,
"attribute_id" : 3,
"join_field" : {
"name" : "user_attribute",
"parent" : 1
}
}
}
]
}
}
グルーピングして平均値を出す
いよいよゴールです。
子のフィールドでグルーピング
GET hoge/_search
{
"size": 0,
"aggs": {
"attr_aggs": {
"terms": {
"field": "attribute_id"
}
}
}
}
以下が返れば成功
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 10,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"attr_aggs" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 2,
"doc_count" : 3
},
{
"key" : 1,
"doc_count" : 2
},
{
"key" : 3,
"doc_count" : 2
}
]
}
}
}
親のフィールドで集計
子ドキュメントのフィールドでグルーピングして親ドキュメントのフィールドを集計するには、集計クエリをネストすることで実現できます。
GET hoge/_search
{
"size": 0,
"aggs": {
"attr_aggs": {
"terms": {
"field": "attribute_id"
},
"aggs": {
"test_result": {
"parent": {
"type": "user_attribute"
},
"aggs": {
"english_avg": {
"avg": {
"field": "english"
}
},
"math_avg": {
"avg": {
"field": "math"
}
},
"chemistry_avg": {
"avg": {
"field": "chemistry"
}
}
}
}
}
}
}
}
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 10,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"attr_aggs" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : 2,
"doc_count" : 3,
"test_result" : {
"doc_count" : 3,
"english_avg" : {
"value" : 73.33333333333333
},
"chemistry_avg" : {
"value" : 88.33333333333333
},
"math_avg" : {
"value" : 83.33333333333333
}
}
},
{
"key" : 1,
"doc_count" : 2,
"test_result" : {
"doc_count" : 2,
"english_avg" : {
"value" : 75.0
},
"chemistry_avg" : {
"value" : 92.5
},
"math_avg" : {
"value" : 80.0
}
}
},
{
"key" : 3,
"doc_count" : 2,
"test_result" : {
"doc_count" : 2,
"english_avg" : {
"value" : 85.0
},
"chemistry_avg" : {
"value" : 90.0
},
"math_avg" : {
"value" : 95.0
}
}
}
]
}
}
}
子のフィールドで集計
おまけで子のフィールドでも集計してみましょう。
parentクエリではなくchildrenクエリを使用します。
GET hoge/_search
{
"size": 0,
"aggs": {
"user_aggs": {
"terms": {
"field": "name.keyword"
},
"aggs": {
"test_result": {
"children": {
"type": "user_attribute"
},
"aggs": {
"user_id_sum": {
"sum": {
"field": "user_id"
}
}
}
}
}
}
}
}
結果
{
..., # 省略
"hits" : {
"total" : {
"value" : 10,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"user_aggs" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "モチベ三郎",
"doc_count" : 1,
"test_result" : {
"doc_count" : 2,
"user_id_sum" : {
"value" : 6.0
}
}
},
{
"key" : "モチベ太郎",
"doc_count" : 1,
"test_result" : {
"doc_count" : 3,
"user_id_sum" : {
"value" : 3.0
}
}
},
{
"key" : "モチベ次郎",
"doc_count" : 1,
"test_result" : {
"doc_count" : 2,
"user_id_sum" : {
"value" : 4.0
}
}
}
]
}
}
}
最後に
無事に成し遂げることが出来ました。
Join field typeは親子だけではなく親子孫や親に子を二人つけることも可能です。
データ構造やクエリが複雑になりそうですがいろいろな検索に役立てそうなことがわかりました
ただし、has_childまたはhas_parentクエリはクエリのパフォーマンスに大きな負担をかけるので、パフォーマンス重視であれば別の方法を検討しましょう。
注意点としてはあくまでもRDBのjoinとは似て非なるものなので、RDBのjoinのように使用してはいけないということを覚えておきましょう。