如何通过Python实现Elasticsearch的数据过滤汇总及数据表可视化

Alright, let's walk through how to accomplish both of your goals with Python and Elasticsearch, building off the code snippet you started with. I'll break this down into clear, actionable steps that mirror what you did manually in Kibana.

First, let's finish setting up your Elasticsearch client with authentication (since you mentioned a user):

from elasticsearch5 import Elasticsearch
import pandas as pd
import matplotlib.pyplot as plt

# Replace these with your actual credentials and ES host
user = 'xxx'
password = 'xxx'
es_host = 'your-es-host:port'  # e.g., 'localhost:9200'

# Initialize the ES client
es = Elasticsearch(
    [es_host],
    http_auth=(user, password),
    # Uncomment below if using HTTPS in a test environment (skip cert verification)
    # verify_certs=False
)

# Verify the connection works
if es.ping():
    print("Connected to Elasticsearch successfully!")
else:
    print("Failed to connect to Elasticsearch. Check your credentials/host.")

This replicates manually pulling data in Kibana, converting it to a tabular format, and even creating basic visualizations (plus exporting to CSV like you did before):

# Define your target index name
target_index = "your-index-name"

# 1. Fetch data from Elasticsearch
# Adjust the query to filter data if needed (here we're fetching all data, up to 1000 rows)
fetch_query = {
    "query": {
        "match_all": {}
    },
    "size": 1000  # For larger datasets, use the Scroll API instead of size
}

# Execute the query
response = es.search(index=target_index, body=fetch_query)

# Extract the source data from hits
raw_data = [hit["_source"] for hit in response["hits"]["hits"]]

# 2. Convert to a tabular format (DataFrame)
data_df = pd.DataFrame(raw_data)

# Display a sample of the table (like Kibana's table visualization)
print("Sample Data Table:")
print(data_df.head(10))

# 3. Export to CSV (same as Kibana's export)
data_df.to_csv("es_table_data.csv", index=False)
print("\nData exported to es_table_data.csv")

# 4. Create a basic visualization (e.g., count of v2 values)
plt.figure(figsize=(10, 6))
data_df["v2"].value_counts().plot(kind="bar", color="#2ecc71")
plt.title("Distribution of v2 Values")
plt.xlabel("v2")
plt.ylabel("Count")
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

To replicate the SQL select v2, count(v2) from index where v1 = "some value" group by v2, we'll use Elasticsearch's aggregation framework:

# Define your filter value for v1
target_v1_value = "some value"

# Build the aggregation query
agg_query = {
    "query": {
        "bool": {
            "filter": [
                # Use .keyword if v1 is a text field (avoids tokenization issues)
                {"term": {"v1.keyword": target_v1_value}}
            ]
        }
    },
    "size": 0,  # We don't need raw data, just aggregation results
    "aggs": {
        "v2_count": {
            "terms": {
                "field": "v2.keyword",  # Again, use .keyword for text fields
                "size": 10  # Return top 10 most frequent v2 values; adjust as needed
            }
        }
    }
}

# Execute the aggregation
agg_response = es.search(index=target_index, body=agg_query)

# Parse the aggregation results
agg_results = agg_response["aggregations"]["v2_count"]["buckets"]
summary_data = [{"v2": bucket["key"], "count": bucket["doc_count"]} for bucket in agg_results]

# Convert to a DataFrame for easy viewing
summary_df = pd.DataFrame(summary_data)
print(f"\nSummary of v2 counts where v1 = '{target_v1_value}':")
print(summary_df)

# Export the summary to CSV
summary_df.to_csv("v2_aggregated_summary.csv", index=False)
print("\nAggregated data exported to v2_aggregated_summary.csv")

• Field Types: If v1 or v2 are text fields (not keyword), you must append .keyword to the field name in your queries/aggregations. This ensures you're matching the full, untokenized value.
• Large Datasets: When fetching more than 1000 rows, use Elasticsearch's Scroll API instead of the size parameter to avoid hitting the default max_result_window limit.
• Visualization Options: For more advanced visualizations (like Kibana's dashboards), you can use libraries like seaborn, plotly, or bokeh instead of matplotlib.

内容的提问来源于stack exchange，提问作者Gaurav Singhal