[I] ElasticsearchSQLHook: lack of streaming support for large SQL query results [airflow]

["SameerMesiah97 (via GitHub)" <gi...@apache.org>]

SameerMesiah97 opened a new issue, #66290:
URL: https://github.com/apache/airflow/issues/66290

   ### 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?
   
   - [x] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] ElasticsearchSQLHook: lack of streaming support for large SQL query results [airflow]

["shahar1 (via GitHub)" <gi...@apache.org>]

shahar1 closed issue #66290: ElasticsearchSQLHook: lack of streaming support for large SQL query results
URL: https://github.com/apache/airflow/issues/66290


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] ElasticsearchSQLHook: lack of streaming support for large SQL query results [airflow]

["SameerMesiah97 (via GitHub)" <gi...@apache.org>]

SameerMesiah97 commented on issue #66290:
URL: https://github.com/apache/airflow/issues/66290#issuecomment-4364755832

   @eladkal 
   
   Since you have been reviewing ES provider PRs, I was hoping to hear your thoughts on this?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org