ElasticSearch 聚合查询

Published 2023年01月31日 18:50 by james

单个条件查询

GET /test-tracing-span-2023-02-01/_search
{
  "query": {
    "term": {
      "region_name": "sg"
    }
  }
}

等价于SQL

SELECT * FROM `test-tracing-span-2023-02-01` WHERE region_name = "sg";

AND 查询

GET /test-tracing-span-2023-02-01/_search
{
  "query": {
    "bool": {
      "must": [
        { "term": { "region_name": "sg"} },
        { "term": { "idc_name": "sg2" } }
      ]
    }
  }
}

等价于SQL

SELECT * FROM `test-tracing-span-2023-02-01` 
WHERE region_name = "sg" AND idc_name = 'sg2';

聚合查询

分组统计

单个分组

SELECT region_name, count(*) 
FROM `test-tracing-span-2023-02-01`
GROUP BY region_name

GET /test-tracing-span-2023-02-01/_search
{
  "size": 0,
  "aggs" : { 
        "region_groups" : {
            "terms" : {
              "field" : "region_name"
            }
        }
    }
}

多个分组

GET /test-tracing-span-2023-02-01/_search
{
  "size": 0,
  "aggs": {
    "agg_project_module": {
      "multi_terms": {
        "terms": [
          { "field": "project_name" },
          { "field": "module_name" }
        ]
      },
      "aggs": {
        "max_latency": {
          "max": { "field": "latency" }
        }
      }
    }
  }
}

multi_terms 在 7.12 后版本才支持，在7.12以下的版本，只能通过嵌套aggs方式实现

GET /test-tracing-span-2023-02-01/_search
{
  "size": 0, 
  "aggs": {
    "project_group": {
      "terms": {
        "field": "project_name"
      },
      "aggs": {
        "module_group": {
          "terms": {
            "field": "module_name"
          }
        }
      }
    }
  }
}

GET /test-tracing_api_in_hour_*/_search
{
  "size": 0, 
  "query": {
    "term": {
      "target_service_name": "user-account"
    }
  },
  "aggs": {
    "group_fan_in": {
      "terms": {
        "field": "source_service_name"
      }
    }
  }
}

SELECT source_service_name, COUNT(source_service_name) FROM `test-tracing_api_in_hour_*`
WHERE target_service_name = 'user-account'
GROUP BY source_service_name

distinct

SELECT DISTINCT(user_id) FROM table WHERE user_id_type = 3;

{
  "query": {
    "term": {
      "user_id_type": 3
    }
  },
  "collapse": {
    "field": "user_id"
  }
}

总结：使用collapse字段后，查询结果中[hits]中会出现[fields]字段，其中包含了去重后的user_id

count + distinct

SELECT COUNT(DISTINCT(user_id)) FROM table WHERE user_id_type = 3;

{
  "query": {
    "term": {
      "user_id_type": 3
    }
  },
  "aggs": {
    "count": {
      "cardinality": {
        "field": "user_id"
      }
    }
  }
}

总结：aggs中cardinality的字段代表需要distinct的字段

count + group by

SELECT COUNT(user_id) FROM table GROUP BY user_id_type;

{
  "aggs": {
    "user_type": {
      "terms": {
        "field": "user_id_type"
      }
    }
  }
}

总结：aggs中terms的字段代表需要group by的字段

count + distinct + group by

SELECT COUNT(DISTINCT(user_id)) FROM table GROUP BY user_id_type;

{
  "aggs": {
    "user_type": {
      "terms": {
        "field": "user_id_type"
      },
      "aggs": {
        "count": {
          "cardinality": {
            "field": "user_id"
          }
        }
      }
    }
  }
}

count + distinct + group by + where

SELECT target_service_name, COUNT(DISTINCT(source_service_name)) 
FROM `test-tracing_api_in_hour_*`
WHERE component in ('http', 'grpc')
GROUP BY target_service_name;

GET /test-tracing_api_in_hour_*/_search
{
  "size": 0, 
  "query": {
    "terms": {
      "component": [
        "http",
        "grpc"
      ]
    }
  },
  "aggs": {
    "group_fan_in": {
      "terms": {
        "size": 100,
        "field": "target_service_name"
      },
      "aggs": {
        "count_uniq_source_service_name": {
          "cardinality": {
            "field": "source_service_name"
          }
        }
      }
    }
  }
}

SELECT source_service_name, COUNT(*), COUNT(DISTINCT target_service_name)  
FROM `test-tracing_api_in_hour_*`
WHERE component in ('http', 'grpc"')  AND source_service_name != ''
GROUP BY source_service_name
ORDER BY count_uniq_service_name DESC
LIMIT 100;

GET /test-tracing_api_in_hour_*/_search
{
  "size": 0, 
  "query": {
    "bool": {
     "must": [
        {"terms": { "component": [ "http", "grpc" ] }}
      ],
      "must_not": [
        {"term": { "source_service_name": "" }}
      ]
    }
  },
  "aggs": {
    "group_fan_out": {
      "terms": {
        "size": 100,
        "order": {"count_uniq_service_name": "desc"},
        "field": "source_service_name"
      },
      "aggs": {
        "count_uniq_service_name": {
          "cardinality": {
            "field": "target_service_name"
          }
        }
      }
    }
  }
}

总结：对于既有group by又有distinct的查询要求，需要在aggs中嵌套子aggs

附录

更多例子

最大API扇出数量

GET /test-tracing_api_in_hour_*/_search
{
  "size": 0, 
  "query": {
    "bool": {
     "must": [
        {"terms": { "component": [ "http", "grpc" ] }}
      ],
      "must_not": [
        {"term": { "source_api_name": "" }}
      ]
    }
  },
  "aggs": {
    "group_fan_out": {
      "terms": {
        "size": 100,
        "order": {"count_uniq_api_name": "desc"},
        "field": "source_api_name"
      },
      "aggs": {
        "count_uniq_api_name": {
          "cardinality": {
            "field": "api_name"
          }
        }
      }
    }
  }
}

最大API扇入数量

GET /test-tracing_api_in_hour_*/_search
{
  "size": 0, 
  "query": {
    "bool": {
     "must": [
        {"terms": { "component": [ "http", "grpc" ] }}
      ]
    }
  },
  "aggs": {
    "group_fan_out": {
      "terms": {
        "size": 100,
        "order": {"count_uniq_api_name": "desc"},
        "field": "api_name"
      },
      "aggs": {
        "count_uniq_api_name": {
          "cardinality": {
            "field": "source_api_name"
          }
        }
      }
    }
  }
}

min_doc_count

GET /test-tracing-span-*/_search
{
  "size": 0,
  "aggs" : { 
      "region_groups" : {
          "terms" : {
            "size": 100, 
            "field" : "trace_id",
            "min_doc_count": 100
          }
      }
    }
}

Having

GET /test-tracing-span-*/_search
{
  "aggregations": {
    "trace_ids": {
      "aggregations": {
        "filter_count": {
          "bucket_selector": {
            "buckets_path": { "doc_count": "_count" },
            "script": { "source": "params.doc_count <= 100" }
          }
        }
      },
      "terms": { "field": "trace_id", "size": 200 }
    }
  },
  "size": 0
}

GET /test-tracing-span-*/_search
{
  "aggregations": {
    "trace_ids": {
      "aggregations": {
        "filter_count": {
          "bucket_selector": {
            "buckets_path": { "doc_count": "_count" },
            "script": { "source": "params.doc_count <= 100" }
          }
        }
      },
      "terms": { "field": "trace_id", "size": 10000 }
    }
  },
  "size": 0
}

GET /live-tracing_api_in_hour_*/_search
{
  "aggregations": {
    "timestamp_start": { 
      "min": { "field": "timestamp_end" }
    },
    "timestamp_end": { 
      "max": { "field": "timestamp_end" } 
    },
    "project_count": {
      "cardinality": {
        "field": "target_project_name"
      }
    },
    "module_count": {
      "cardinality": {
        "field": "target_module_name"
      }
    },
    "api_count": {
      "cardinality": {
        "field": "api_name"
      }
    }
  },
  "size": 0
}

GET /live-tracing-span-*/_search
{
  "query": {
    "terms": {
      "component": [
        "http",
        "grpc",
        "gateway"
      ]
    }
  },
  "aggs": {
    "api-count": {
      "terms": {
        "field": "service_name",
        "size": 100
      },
      "aggs": {
        "count": {
          "cardinality": {
            "field": "api_name"
          }
        }
      }
    }
  },
  "size": 0
}

GET /test-tracing-span-*/_search
{
  "query": {
    "terms": {
      "component": [
        "http",
        "grpc",
        "gateway"
      ]
    }
  },
  "aggs": {
    "group-by-service": {
      "terms": {
        "field": "service_name",
        "order": {"api-count": "desc"},
        "size": 100
      },
      "aggs": {
        "api-count": {
          "cardinality": {
            "field": "api_name"
          }
        }
      }
    }
  },
  "size": 0
}

实战

GROUP BY + COUNT + SORT + 分页

GET /test-tracing-span-*/_search
{
  "query": {
    "terms": {
      "component": [
        "http",
        "grpc",
        "gateway"
      ]
    }
  },
  "aggs": {
    "group-by-service": {
      "terms": {
        "field": "service_name",
        "order": {"api-count": "desc"},
        "size": 100
      },
      "aggs": {
        "api-count": {
          "cardinality": {
            "field": "api_name"
          }
        }
      }
    }
  },
  "size": 0
}

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "term": { "target_project_name": "shopeepaybase" } },
        { "term": { "target_module_name": "gateway" } }
      ] 
    }
  },
  "aggs": {
    "group-by-service": {
      "terms": {
        "field": "api_name",
        "size": 10000
      },
      "aggs": {
        "max_latency": { 
          "max": { "field": "max_latency" } 
        },
        "min_latency": { 
          "min": { "field": "min_latency" } 
        },
        "request_count": {
          "sum": { "field": "request_count" }
        }
      }
    }
  },
  "size": 0
}

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "term": { "target_project_name": "shopeepaybase" } },
        { "term": { "target_module_name": "gateway" } }
      ] 
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "api_name": { "terms": { "field": "api_name" } } },
          { "region_name": { "terms": { "field": "region_name" } } },
          { "idc_name": { "terms": { "field": "idc_name" } } }
        ]
      },
      "aggs": {
        "max_latency": { "max": { "field": "max_latency" } },
        "min_latency": { "min": { "field": "min_latency" } },
        "request_count": { "sum": { "field": "request_count" } }
      }
    }
  },
  "size": 0
}

附录

Basic Info

Summary

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": { "terms": { "component": [ "http", "grpc",  "gateway" ] } }
    }
  },
  "aggs": {
    "groupby": {
      "terms": { "field": "env_name" },
      "aggs": {
        "max_timestamp_end": { "max": { "field": "timestamp_end" } },
        "min_timestamp_end": { "min": { "field": "timestamp_end" } },
        "regions": { "terms": { "field": "region_name" } },
        "project_count": {"cardinality": { "field": "target_project_name" }},
        "module_count": {"cardinality": { "field": "target_module_name" }},
        "api_count": {"cardinality": { "field": "api_name" }}
      }
    }
  },
  "size": 0
}

TOP API

GET /test-tracing-span-*/_search
{
  "query": {
    "bool": {
      "filter": { "terms": { "component": [ "http", "grpc",  "gateway" ] } }
    }
  },
  "aggs": {
    "groupby": {
      "terms": { "field": "service_name", "size": 10, "order": {"api_count": "desc"} },
      "aggs": {
        "api_count": {"cardinality": { "field": "api_name" }}
      }
    }
  },
  "size": 0
}

Analyze Info

Module High Fan-In Sort

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [ {"terms": { "component": [ "http", "grpc",  "gateway" ] }} ],
      "must_not": [ { "term": { "source_project_name": "" } } ]
    }
  },
  "aggs": {
    "groupby": {
      "terms": { "field": "target_service_name", "size": 10, "order": {"source_service_count": "desc"} },
      "aggs": {
        "source_service_count": {"cardinality": { "field": "source_service_name" }}
      }
    }
  },
  "size": 0
}

Module High Fan-Out Sort

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [ {"terms": { "component": [ "http", "grpc",  "gateway" ] }} ],
      "must_not": [
        { "term": { "source_project_name": "" } }
      ]
    }
  },
  "aggs": {
    "groupby": {
      "terms": { "field": "source_service_name", "size": 10, "order": {"target_service_count": "desc"} },
      "aggs": {
        "target_service_count": {"cardinality": { "field": "target_service_name" }}
      }
    }
  },
  "size": 0
}

API High Fan-In Sort

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [ {"terms": { "component": [ "http", "grpc",  "gateway" ] }} ],
      "must_not": [ { "term": { "source_api_name": "" } } ]
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "target_project_name": { "terms": { "field": "target_project_name" } } },
          { "target_module_name": { "terms": { "field": "target_module_name" } } },
          { "target_api_name": { "terms": { "field": "api_name" } } }
        ]
      },
      "aggs": {
        "source_api_count": {"cardinality": { "field": "source_api_name" }},
        "filter_count": {
          "bucket_selector": {
            "buckets_path": { "source_api_count": "source_api_count" },
            "script": { "source": "params.source_api_count > 2" }
          }
        }
      }
    }
  },
  "size": 0
}

API High Fan-Out Sort

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [{"terms": { "component": [ "http", "grpc",  "gateway" ] }}],
      "must_not": [ { "term": { "source_api_name": "" } } ]
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "source_project_name": { "terms": { "field": "source_project_name" } } },
          { "source_module_name": { "terms": { "field": "source_module_name" } } },
          { "source_api_name": { "terms": { "field": "source_api_name" } } }
        ]
      },
      "aggs": {
        "target_api_count": {"cardinality": { "field": "api_name" }},
        "filter_count": {
          "bucket_selector": {
            "buckets_path": { "target_api_count": "target_api_count" },
            "script": { "source": "params.target_api_count > 2" }
          }
        }
      }
    }
  },
  "size": 0
}

详情页

TOP API 分页展开

GET /test-tracing-span-*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "terms": { "component": [ "http", "grpc",  "gateway" ] } },
        { "term": { "project_name": "shopeepaybase" } },
        { "term": { "module_name": "gateway" } }
      ] 
    }
  },
  "aggs": {
    "group-by-service": {
      "terms": { "field": "service_name", "order": {"api_count": "desc"}, "size": 10000 },
      "aggs": {
        "api_count": { "cardinality": { "field": "api_name" } }
      }
    }
  },
  "size": 0
}

查看指定模块的API明细

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "term": { "target_project_name": "shopeepaybase" } },
        { "term": { "target_module_name": "gateway" } }
      ] 
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "api_name": { "terms": { "field": "api_name" } } },
          { "region_name": { "terms": { "field": "region_name" } } },
          { "idc_name": { "terms": { "field": "idc_name" } } }
        ]
      },
      "aggs": {
        "max_latency": { "max": { "field": "max_latency" } },
        "min_latency": { "min": { "field": "min_latency" } },
        "sum_request_count": { "sum": { "field": "request_count" } }
      }
    }
  },
  "size": 0
}

查询指定模块的扇入模块明细

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "term": { "target_project_name": "shopeepayuser" } },
        { "term": { "target_module_name": "account" } }
      ] 
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "source_region_name": { "terms": { "field": "source_region_name" } } },
          { "source_idc_name": { "terms": { "field": "source_idc_name" } } },
          { "source_project_name": { "terms": { "field": "source_project_name" } } },
          { "source_module_name": { "terms": { "field": "source_module_name" } } }
        ]
      },
      "aggs": {
        "max_latency": { "max": { "field": "max_latency" } },
        "min_latency": { "min": { "field": "min_latency" } },
        "sum_request_count": { "sum": { "field": "request_count" } }
      }
    }
  },
  "size": 0
}

查询指定模块的扇出模块明细

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "terms": { "component": [ "http", "grpc",  "gateway" ] } },
        { "term": { "source_project_name": "shopeepayuser" } },
        { "term": { "source_module_name": "account" } }
      ] 
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "target_region_name": { "terms": { "field": "region_name" } } },
          { "target_idc_name": { "terms": { "field": "idc_name" } } },
          { "target_project_name": { "terms": { "field": "target_project_name" } } },
          { "target_module_name": { "terms": { "field": "target_module_name" } } }
        ]
      },
      "aggs": {
        "max_latency": { "max": { "field": "max_latency" } },
        "min_latency": { "min": { "field": "min_latency" } },
        "sum_request_count": { "sum": { "field": "request_count" } }
      }
    }
  },
  "size": 0
}

查询指定API的扇入API明细

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "term": { "target_project_name": "shopeepayuser" } },
        { "term": { "target_module_name": "info" } },
        { "term": { "api_name": "/user.InfoSApi/GetUserInfo" } }
      ] 
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "source_region_name": { "terms": { "field": "source_region_name" } } },
          { "source_idc_name": { "terms": { "field": "source_idc_name" } } },
          { "source_project_name": { "terms": { "field": "source_project_name" } } },
          { "source_module_name": { "terms": { "field": "source_module_name" } } },
          { "source_api_name": { "terms": { "field": "source_api_name" } } }
        ]
      },
      "aggs": {
        "max_latency": { "max": { "field": "max_latency" } },
        "min_latency": { "min": { "field": "min_latency" } },
        "sum_request_count": { "sum": { "field": "request_count" } }
      }
    }
  },
  "size": 0
}

查询指定API的扇出API明细

GET /test-tracing_api_in_hour_*/_search
{
  "query": {
    "bool": {
      "filter": [
        { "terms": { "component": [ "http", "grpc", "gateway" ] } },
        { "term": { "source_project_name": "shopeepayuser" } },
        { "term": { "source_module_name": "account" } },
        { "term": { "source_api_name": "/user.AccountSApi/GetUserStatus" } }
      ] 
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10000,
        "sources": [
          { "target_region_name": { "terms": { "field": "region_name" } } },
          { "target_idc_name": { "terms": { "field": "idc_name" } } },
          { "target_project_name": { "terms": { "field": "target_project_name" } } },
          { "target_module_name": { "terms": { "field": "target_module_name" } } },
          { "target_api_name": { "terms": { "field": "api_name" } } }
        ]
      },
      "aggs": {
        "max_latency": { "max": { "field": "max_latency" } },
        "min_latency": { "min": { "field": "min_latency" } },
        "sum_request_count": { "sum": { "field": "request_count" } }
      }
    }
  },
  "size": 0
}

0 comments

There are no comments yet.

Add a new comment

Similar posts

Elasticsearch Advanced Practice

Elasticsearch 相关概念