Cannot use the SQLExecuteQueryOperator with elastic connection type (e.g. ElasticsearchSQLHook )

[dabla]

Apache Airflow Provider(s)

elasticsearch

Versions of Apache Airflow Providers

apache-airflow-providers-elasticsearch==6.0.0

Apache Airflow version

2.10.4

Operating System

Linux

Deployment

Other 3rd-party Helm chart

Deployment details

No response

What happened

Today we tried to use the Elasticsearch connection type (e.g. ElasticsearchSQLHook ) through the SQLExecuteQueryOperator.
You would expect it to work as the ElasticsearchSQLHook extends the DbApiHook, unfortunately it doesn't as it is missing some implementations.

What you think should happen instead

I would expect the Elasticsearch connection type to work with the SQLExecuteQueryOperator, as it supports executing SQL statements.

How to reproduce

Just use the SQLExecuteQueryOperator with the Elasticsearch connection type. Also in the TestElasticsearchSQLHook unit test you can see the cursor is mocked but the ESConnection wrapper class doesn't actually return a cursor.

Anything else

We've patched the ElasticsearchSQLHook in our Airflow installation to make it work, so I created a PR for this to make it work in elasticsearch provider

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct