Имеет ли Elastic Search эквивалент агрегации SQL Group By?
Я знаю, что Elastic Search может выполнять агрегацию, но вы знаете, что агрегация БД не совсем такая же, как в мире ES.
Как мы делаем ниже в БД:
select a, b, c, d, sum(e), sum(f)
from some_table nolock
group by a, b, c, d
Он вернется:
a, b, c, d, sum e, sum f
1, 2, 3, 4, 100 , 100
1, 2, 3, 5, 150 , 150
...
Таким образом, каждый a+b+c+d как отдельный ключ будет иметь возврат одной строки.
Но как я могу это сделать в агрегации ES? Он вернет вложенный результат, и если я агрегирую с другой последовательностью, например a->b->c->d, результат будет отличаться от результата d->c->b->a. Но в БД группа по a,b,c,d не будет отличаться от группы по d,c,b,a.
Любое предложение приветствуется, спасибо
Пробовали xpack и параллельную агрегацию, xpack сложно использовать, параллельно не так, как ожидалось
(поскольку агрегаты, которые вы показываете, являются sum()s, а сумма коммутативна, я также не знаю, что вы имеете в виду, когда говорите, что упорядочение меняет ваш результат; конкретный пример конкретных данных, для которых возможны неправильные результаты, может быть дальнейшим способ принести пользу, лучше обосновав этот вопрос и убедившись, что он правильно понят).
извините за краткое описание, я хочу показать здесь код, но перед публикацией необходимо заменить слишком много ключевых или конфиденциальных слов. Я думаю, что мне нужна составная агрегация, в любом случае спасибо, хорошего дня @CharlesDuffy
Ответы 2
Прежде всего, на самом деле нет никакой разницы между результатом a->b->c->d и d->c->b->a, если вы правильно обрабатываете ключи, единственной разницей будет порядок. Вот пример:
DELETE test
PUT test
{
"mappings": {
"properties": {
"a": {
"type": "integer"
},
"b": {
"type": "integer"
},
"c": {
"type": "integer"
},
"d": {
"type": "integer"
},
"e": {
"type": "integer"
},
"f": {
"type": "integer"
}
}
}
}
POST test/_bulk?refresh
{"index": {"_id": 1}}
{"a": 1, "b": 2, "c": 3, "d": 4, "e": 50, "f": 50 }
{"index": {"_id": 2}}
{"a": 1, "b": 2, "c": 3, "d": 4, "e": 50, "f": 50 }
{"index": {"_id": 3}}
{"a": 1, "b": 2, "c": 3, "d": 5, "e": 50, "f": 50 }
{"index": {"_id": 4}}
{"a": 1, "b": 2, "c": 3, "d": 5, "e": 50, "f": 50 }
{"index": {"_id": 5}}
{"a": 1, "b": 2, "c": 3, "d": 5, "e": 50, "f": 50 }
POST test/_search
{
"size": 0,
"aggs": {
"a": {
"terms": {
"field": "a"
},
"aggs": {
"b": {
"terms": {
"field": "b"
},
"aggs": {
"c": {
"terms": {
"field": "c"
},
"aggs": {
"d": {
"terms": {
"field": "d"
},
"aggs": {
"sum_e": {
"sum": {
"field": "e"
}
},
"sum_f": {
"sum": {
"field": "f"
}
}
}
}
}
}
}
}
}
}
}
}
POST test/_search
{
"size": 0,
"aggs": {
"d": {
"terms": {
"field": "d"
},
"aggs": {
"b": {
"terms": {
"field": "b"
},
"aggs": {
"c": {
"terms": {
"field": "c"
},
"aggs": {
"a": {
"terms": {
"field": "a"
},
"aggs": {
"sum_e": {
"sum": {
"field": "e"
}
},
"sum_f": {
"sum": {
"field": "f"
}
}
}
}
}
}
}
}
}
}
}
}
Однако вам может быть удобнее использовать агрегации composite, которые дадут более ровные результаты, близкие к тем, которые вы ожидаете от SQL.
POST test/_search
{
"size": 0,
"aggs": {
"my_groups": {
"composite": {
"sources": [
{
"a": {
"terms": {
"field": "a"
}
}
},
{
"b": {
"terms": {
"field": "b"
}
}
},
{
"c": {
"terms": {
"field": "c"
}
}
},
{
"d": {
"terms": {
"field": "d"
}
}
}
]
},
"aggs": {
"sum_e": {
"sum": {
"field": "e"
}
},
"sum_f": {
"sum": {
"field": "f"
}
}
}
}
}
}
Но вы также можете позволить Elasticsearch написать это агрегирование за вас и получить именно то, что вы ожидали:
POST /_sql?format=csv
{
"query": "SELECT a, b, c, d, sum(e), sum(f) FROM test GROUP BY a, b, c, d",
"fetch_size": 10
}
Я думаю, что составные агрегаты аналогичны поведению БД, большое спасибо @imotov
хотя я могу запустить составную агрегацию, но похоже, что она выполняет агрегацию только по 10 записям, как выполнить агрегацию по всему индексу и вернуть полный результат? Между тем я обнаружил, что кто-то сказал, что не рекомендуется выполнять агрегацию по большому индексу из-за ограничения кэша, и нам нужно использовать API прокрутки для получения всех данных и выполнения агрегации на локальном сервере. Это меня очень смутило, на самом деле, почему я хочу выполнять агрегацию в ES, так это то, что API прокрутки работает слишком медленно при запросе большого индекса с большим количеством документов. Любые предложения приветствуются@imotov
Ваш вопрос включает в себя несколько сложностей, и даже поверхностное их рассмотрение превысит ограничение в 600 символов для комментариев stackoverflow. Вы можете разбивать составные агрегаты на страницы, как описано в официальной документации. Для более быстрого получения результатов, если у ваших узлов достаточно памяти, рассмотрите возможность одновременной выборки большего количества записей, возможно, около search.max_bucket limit, а не просто 10. Это контролируется параметром size в составных aggs.
@imotov, твой SQL-запрос - очень интересное решение
Введение
Есть еще три способа следить за обновленными документами.
POST test/_bulk?refresh
{"index": {"_id": 1}}
{"a": 1, "b": 2, "c": 3, "d": 4, "e": 50, "f": 50 }
{"index": {"_id": 2}}
{"a": 1, "b": 2, "c": 3, "d": 4, "e": 50, "f": 50 }
{"index": {"_id": 3}}
{"a": 1, "b": 2, "c": 3, "d": 5, "e": 50, "f": 50 }
{"index": {"_id": 4}}
{"a": 1, "b": 2, "c": 3, "d": 5, "e": 50, "f": 50 }
{"index": {"_id": 5}}
{"a": 1, "b": 2, "c": 3, "d": 5, "e": 50, "f": 50 }
{"create": {"_id":6}}
{"a": 4, "b": 3, "c": 2, "d": 1, "e": 50, "f": 50 }
Способ №1. Агрегация multi_terms для того же сопоставления с нумерацией страниц
Запрос
GET /test/_search?filter_path=aggregations.by_a_b_c_d_fields.buckets.key_as_string,aggregations.by_a_b_c_d_fields.buckets.sum_e,aggregations.by_a_b_c_d_fields.buckets.sum_f
{
"aggs": {
"by_a_b_c_d_fields": {
"multi_terms": {
"terms": [
{
"field": "a"
},
{
"field": "b"
},
{
"field": "c"
},
{
"field": "d"
}
]
},
"aggs": {
"sum_e": {
"sum": {
"field": "e"
}
},
"sum_f": {
"sum": {
"field": "f"
}
},
"pagination": {
"bucket_sort": {
"from": 1,
"size": 2
}
}
}
}
}
}
Ответ
{
"aggregations" : {
"by_a_b_c_d_fields" : {
"buckets" : [
{
"key_as_string" : "1|2|3|4",
"sum_f" : {
"value" : 100.0
},
"sum_e" : {
"value" : 100.0
}
},
{
"key_as_string" : "4|3|2|1",
"sum_f" : {
"value" : 50.0
},
"sum_e" : {
"value" : 50.0
}
}
]
}
}
}
Способ №2. Поле времени выполнения для одного и того же сопоставления и исходных документов.
Запрос
GET /test/_search?filter_path=aggregations
{
"runtime_mappings": {
"compound_key": {
"type": "keyword",
"script": """
String[] fieldNames = new String[]{"a", "b", "c", "d"};
List fieldValues = new LinkedList();
int fieldNamesLength = fieldNames.length;
for (int i = 0; i < fieldNamesLength; i++) {
String fieldName = fieldNames[i];
if (params._source.containsKey(fieldName)) {
long fieldValue = params._source[fieldName];
fieldValues.add(Long.toString(fieldValue));
}
}
String compoundKey = String.join(" ", fieldValues);
emit(compoundKey);
"""
}
},
"aggs": {
"by_compound_key": {
"terms": {
"field": "compound_key"
},
"aggs": {
"sum_e": {
"sum": {
"field": "e"
}
},
"sum_f": {
"sum": {
"field": "f"
}
}
}
}
}
}
Ответ
{
"aggregations" : {
"by_compound_key" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "1 2 3 5",
"doc_count" : 3,
"sum_f" : {
"value" : 150.0
},
"sum_e" : {
"value" : 150.0
}
},
{
"key" : "1 2 3 4",
"doc_count" : 2,
"sum_f" : {
"value" : 100.0
},
"sum_e" : {
"value" : 100.0
}
}
]
}
}
}
Способ №3. Составной ключ update_by_query и простая агрегация terms
Новое картографирование
PUT /test
{
"mappings": {
"properties": {
"a": {
"type": "integer"
},
"b": {
"type": "integer"
},
"c": {
"type": "integer"
},
"d": {
"type": "integer"
},
"e": {
"type": "integer"
},
"f": {
"type": "integer"
},
"compound_key": {
"type": "keyword"
}
}
}
}
update_by_query
POST /test/_update_by_query
{
"script": {
"source": """
String[] fieldNames = new String[]{"a", "b", "c", "d"};
List fieldValues = new LinkedList();
for (String fieldName : fieldNames) {
if (ctx._source.containsKey(fieldName)) {
long fieldValue = ctx._source[fieldName];
fieldValues.add(Long.toString(fieldValue));
}
}
String compoundKey = String.join(" ", fieldValues);
ctx._source.compound_key = compoundKey;
""",
"lang": "painless"
}
}
Запрос с нумерацией страниц
GET /test/_search?filter_path=aggregations
{
"aggs": {
"by_compound_key": {
"terms": {
"field": "compound_key"
},
"aggs": {
"sum_e": {
"sum": {
"field": "e"
}
},
"sum_f": {
"sum": {
"field": "f"
}
},
"pagination": {
"bucket_sort": {
"from": 0,
"size": 3
}
}
}
}
}
}
Ответ
{
"aggregations" : {
"by_compound_key" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "1 2 3 5",
"doc_count" : 3,
"sum_f" : {
"value" : 150.0
},
"sum_e" : {
"value" : 150.0
}
},
{
"key" : "1 2 3 4",
"doc_count" : 2,
"sum_f" : {
"value" : 100.0
},
"sum_e" : {
"value" : 100.0
}
},
{
"key" : "4 3 2 1",
"doc_count" : 1,
"sum_f" : {
"value" : 50.0
},
"sum_e" : {
"value" : 50.0
}
}
]
}
}
}
Multi-term — отличное предложение, если вам не нужен пейджинг! Способ № 2 работает, но он обходит некоторые оптимизации, которые elasticsearch мог бы применить в составных и многочленных случаях, поэтому для большого индекса он будет медленнее. Способ №3 не работает — он сгенерирует один и тот же ключ «1 2 3 4» для a=1, b=2, c=3, d=4 и a=4, b=2, c=3. , д=1.
@imotov, спасибо за комментарий. Я исправил способы
Вопрос о том, почему результаты, полученные вами параллельно, не соответствуют вашим ожиданиям, может быть немного более конкретным/простым на ответ, поскольку это место, где вы сможете показать код, фактический результат этого кода и ожидаемый результат этого кода.