Run Elastic ESQL query and read the data as a Pandas DataFrame

Query an Elasticsearch index with the ESQL API and turn the results into a pandas DataFrame, all in one declarative Kestra flow. This blueprint bridges the gap between search and data science: instead of exporting data by hand, copying CSV files, or wiring up a notebook against your cluster, you run an ESQL statement, stream the CSV response straight into a Python script, and start working with the data as a DataFrame. It is a clean pattern for analytics, reporting, data validation, and feeding downstream machine learning or transformation steps from your Elasticsearch documents.

How it works

The flow chains two tasks that run in sequence:

1. The esql_query task (io.kestra.plugin.elasticsearch.Request) connects to your Elastic Cloud cluster over HTTPS using an API key passed through the Authorization header. It sends a POST to the /_query?format=csv endpoint with an ESQL body (FROM test | WHERE title IS NOT NULL), so Elasticsearch returns the matching rows already formatted as CSV.
2. The pandas task (io.kestra.plugin.scripts.python.Script) receives that CSV through inputFiles as data.csv, bound to {{ outputs.esql_query.response }}. It installs the pandas dependency, reads the file with pd.read_csv, and prints data.head() so you can immediately inspect the result.

What you get

• A query-to-DataFrame pipeline with no manual export step.
• CSV output requested directly from Elasticsearch, so no parsing of nested JSON.
• A ready-to-extend Python environment with pandas already declared as a dependency.
• A clear separation between the search layer and the analytics layer.

Who it's for

• Data engineers moving Elasticsearch data into Python workflows.
• Data scientists and analysts who want DataFrames built from search results.
• Platform teams standardizing how teams pull data out of Elastic.

Why orchestrate this with Kestra

Elasticsearch is a search and analytics engine, not a scheduler or workflow tool: it cannot trigger your Python script, retry a failed query, or track which run produced which DataFrame. Kestra fills that gap. You can attach event or schedule triggers to run the query on new data, add retries on transient cluster or network errors, and get full execution lineage from the query response to the Python output. The whole pipeline stays declarative YAML, versioned and reviewable, instead of a script that lives on someone's laptop.

Prerequisites

• A reachable Elasticsearch cluster (the example targets an Elastic Cloud host) and an index to query.
• A worker or Docker environment able to run Python with pandas.

Secrets

• ES_API_TOKEN: the Elasticsearch API key used in the Authorization: ApiKey ... header.

Quick start

1. Add the ES_API_TOKEN secret to your Kestra instance.
2. Update the hosts value to point at your own Elasticsearch cluster.
3. Change the ESQL query and the FROM test index to match your data.
4. Run the flow and check the pandas task logs for the printed DataFrame head.

How to extend

• Replace print(data.head()) with real transformations, aggregations, or feature engineering.
• Write the DataFrame to a file output and pass it to downstream tasks (storage upload, database load, reporting).
• Add a schedule or event trigger to refresh the dataset automatically.
• Parameterize the ESQL query and index name with flow inputs for reuse across teams.

Links

• Elasticsearch plugin documentation
• Python script plugin documentation