ElasticsearchSQLHook fails with AttributeError: __enter__

[Pooort]

Apache Airflow Provider(s)

elasticsearch

Versions of Apache Airflow Providers

apache-airflow-providers-common-io==1.3.2
apache-airflow-providers-common-sql==1.14.0
apache-airflow-providers-elasticsearch==5.4.1
apache-airflow-providers-fab==1.1.1
apache-airflow-providers-ftp==3.9.1
apache-airflow-providers-http==4.11.1
apache-airflow-providers-imap==3.6.1
apache-airflow-providers-smtp==1.7.1
apache-airflow-providers-sqlite==3.8.1

Apache Airflow version

v2.9.2

Operating System

v2.9.2

Deployment

Virtualenv installation

Deployment details

No response

What happened

Documentation's usage example for ElasticsearchSQLHook:

from airflow.providers.elasticsearch.hooks.elasticsearch import ElasticsearchSQLHook

es = ElasticsearchSQLHook(elasticsearch_conn_id='elasticsearch')

# Handle ES conn with context manager
with es.get_conn() as es_conn:
    tables = es_conn.execute("SHOW TABLES")
    for table, *_ in tables:
        print(f"table: {table}")

fails with:

Traceback (most recent call last):
  File "/home/port/projects/Encortex/airflow/new_test.py", line 6, in <module>
    with es.get_conn() as es_conn:
AttributeError: __enter__

What you think should happen instead

No response

How to reproduce

Implement Documentation's usage example.

Anything else

No response

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

[eladkal]

Which version of elastic are you running?

[Pooort]

8.13.1

[eladkal]

cc @Owen-CH-Leung maybe you can take a look?

[Owen-CH-Leung]

Sure. Will take a look soon

[Owen-CH-Leung]

Indeed, I can reproduce the same error as @Pooort pointed out:

The root cause is that the current implementation of ElasticsearchSQLHook is dependent on the unofficial ES library elasticsearch-dbapi which we have already deprecated it since it doesn't support ES8.

Luckily the official es python client has a SQL client available so we can easily refactor our implementation :

https://github.com/elastic/elasticsearch-py/blob/main/elasticsearch/_sync/client/sql.py#L26

I'll file a PR to refactor it soon