python做es的聚合统计和分页读取

通过python实现对es数据的聚合统计最后将数值写入excel文件

#!/usr/bin/python3
#-*- coding: utf-8 -*-

from elasticsearch import Elasticsearch
from openpyxl import load_workbook

es = Elasticsearch(hosts=http://localhost, port=9200, http_auth=(admin, admin), timeout=60)
#es聚合查询数据
para = {
   "aggs": {
      "date": {
        "date_histogram": {
          "field" : "timestamp",
          "interval" : "month",
          "format" : "yyyy-MM-dd"
      },
      "aggs": {
        "ip":{
          "terms": {
            "field": "ip",
            "size":0
          },
          "aggs": {
          "aborad":{
            "terms": {
              "field": "geoip.country_code",
              "size":0
          }
      }
    }
}
}
}
}
}
###分页读取数据
queryData = es.search(index="risk_threaten*", doc_type="doc",scroll =2m, body=para,size =100)
data = queryData[aggregations][date][buckets]
print(data)
sid = queryData[_scroll_id]
scroll_size = queryData[hits][total]
###时间列表
date_list = []
ip_list = []
queryData = es.scroll(scroll_id=sid, scroll=2m)
#Update the scroll ID
sid = queryData[_scroll_id]
scroll_size = queryData[hits][total]
wt = load_workbook("D:\python\es_risk\threaten_month.xlsx")
sheet = wt.active
#sheet.cell(row=1, column=1, value="datetime")
#sheet.cell(row=1, column=2, value="")##读取数据
sheet.cell(row=1, column=5, value="ip_abroad_total")
row = 1
for ip_data in data:
    date = ip_data[key_as_string]
    print(date)
    total = ip_data[doc_count]
    row += 1
    ip_list=[]
    # sheet.cell(row=row, column=1, value=date); ###ip后面跟着城市
    for list_ip in ip_data[ip][buckets]:
        dst_ip =list_ip[key]
        abroad = list_ip[aborad]
        for region in abroad[buckets]:
            if region[key] != CN:
                ip_list.append(dst_ip)

    s = 0
    for ip in ip_list:
        s+=1
    print(s)
    sheet.cell(row=row, column=5, value=s);
    print(ip_list)
##保存文件
wt.save("D:\python\es_risk\threaten_month.xlsx")