python 通过 SQL 分页查询大量Elasticsearch数据的方法

MarvelHonor

已于 2024-05-17 10:57:31 修改

阅读量228

点赞数 2

文章标签： python sql elasticsearch

于 2024-05-17 10:56:31 首次发布

本文链接：https://blog.csdn.net/chscomfaner/article/details/138999464

版权

import time
from elasticsearch import Elasticsearch
from base.config import config
import logging
logging.getLogger("requests").setLevel(logging.WARNING)
logging.getLogger("elasticsearch").setLevel(logging.WARNING)
def inites():
    host = config["es"]["host"]
    port = config["es"]["port"]
    # es = Elasticsearch(hosts=host, port=port, timeout=200)
    es = Elasticsearch(hosts=host, port=port, timeout=200 , http_auth=(config["es"]["user"],config["es"]["pwd"]))
    return es

def sql(data=[],**sq):
    es=inites()
    if not sq:
        return False
    rows=es.sql.query(sq)
    if not rows:
        return False
    else:
        for row in rows["rows"]:
            data.append(row)
    if "fetch_size" in sq.keys():
        if len(rows["rows"])==sq["fetch_size"]:
            return sql(data,cursor=rows["cursor"],fetch_size=sq["fetch_size"])
    return data



#应用

def fetch_1day_ldap_auth():
    data=[]
    now = time.strftime('log_topic_micr_%Y.%m.%d', time.localtime(time.time()-24 * 60 * 60))
    query=f"SELECT COUNT(*) AS count,IpAddress FROM \"{now}\" WHERE PackageName = 'PACKAGE_V1_0' group by IpAddress"
    rows = es.sql(query=query,fetch_size=10000)
    if rows:
        for row in rows:
            if row[0] and row[1]:
                data.append({"count":row[0],"ipaddress":row[1]})
        data.sort(key= lambda item: item["count"],reverse=True)
    return data