Description
Elasticsearch SQL supports cursor-based pagination, but the current ElasticsearchSQLHook only exposes APIs that materialize the full result set in memory (e.g. via get_df, which ultimately delegates to pandas.read_sql).
This approach does not align well with Elasticsearch’s execution model. While Elasticsearch is designed for incremental retrieval using cursors, the current implementation loads all results into memory before returning them, which can lead to memory pressure or out-of-memory issues for large datasets.
Additionally, while Airflow provides get_df_by_chunks, this is not compatible with Elasticsearch. The chunked implementations rely on pandas (read_sql) or polars (read_database), which expect DB-API-style batching semantics that the ElasticsearchSQLCursor does not implement.
As part of this gap, I recently opened PR #66220, which introduces Polars DataFrame support via a custom SQL reader using Elasticsearch cursor-based pagination. That work highlights the limitation more broadly: Elasticsearch requires a custom execution model rather than relying on DB-API abstractions.
Use case/motivation
Rationale
Users may need to process large Elasticsearch datasets within Airflow tasks (e.g. exporting data, transformations, or loading into downstream systems).
In such cases, loading the entire result set into memory is not always feasible. Elasticsearch already provides cursor-based pagination for incremental access, but this capability is not currently exposed through the hook in a way that allows memory-efficient processing.
Proposal
Would it be acceptable to introduce a streaming-style interface (e.g. yielding results per page using Elasticsearch cursor pagination) in the hook? I am happy to build on the approach from PR #66220 if this direction makes sense.
Related issues
No response
Are you willing to submit a PR?
Code of Conduct
Description
Elasticsearch SQL supports cursor-based pagination, but the current
ElasticsearchSQLHookonly exposes APIs that materialize the full result set in memory (e.g.via get_df, which ultimately delegates topandas.read_sql).This approach does not align well with Elasticsearch’s execution model. While Elasticsearch is designed for incremental retrieval using cursors, the current implementation loads all results into memory before returning them, which can lead to memory pressure or out-of-memory issues for large datasets.
Additionally, while Airflow provides
get_df_by_chunks, this is not compatible with Elasticsearch. The chunked implementations rely on pandas (read_sql) or polars (read_database), which expect DB-API-style batching semantics that theElasticsearchSQLCursordoes not implement.As part of this gap, I recently opened PR #66220, which introduces Polars DataFrame support via a custom SQL reader using Elasticsearch cursor-based pagination. That work highlights the limitation more broadly: Elasticsearch requires a custom execution model rather than relying on DB-API abstractions.
Use case/motivation
Rationale
Users may need to process large Elasticsearch datasets within Airflow tasks (e.g. exporting data, transformations, or loading into downstream systems).
In such cases, loading the entire result set into memory is not always feasible. Elasticsearch already provides cursor-based pagination for incremental access, but this capability is not currently exposed through the hook in a way that allows memory-efficient processing.
Proposal
Would it be acceptable to introduce a streaming-style interface (e.g. yielding results per page using Elasticsearch cursor pagination) in the hook? I am happy to build on the approach from PR #66220 if this direction makes sense.
Related issues
No response
Are you willing to submit a PR?
Code of Conduct