How can I use elasticsearch-dbapi with Open Distro for ElasticSearch, ignoring SSL certificates?

Question

How can I use elasticsearch-dbapi with Open Distro for Elasticsearch, ignoring ssl certificates?

I am using Open Distro for Elasticsearch 1.13.2, Python 3.7, elasticsearch client 7.12.1, sqlalchemy: 1.4.17, eland: 7.10.1b1

This works:

from elasticsearch import Elasticsearch
es = Elasticsearch (['https: // admin: admin @ localhost: 9200 /'], use_ssl = True, verify_certs = False, ssl_show_warn = False)
res = es.indices.get_alias ("*")
print (res)

This also works:

import eland as ed
df = ed.DataFrame (es, es_index_pattern = "kibana_sample_data_flights")
df

But the following does not work, because connection errors occur due to self-signed certificates:

# Using sqlalchemy
from sqlalchemy.engine import create_engine
engine = create_engine (
    "odelasticsearch + https: // admin: admin @ localhost: 9200 /"
)
rows = engine.connect (). execute (
    "select Carrier, count (*) from kibana_sample_data_flights GROUP BY Carrier"
)
print ([row for row in rows])

Part of the error message: Error: [('SSL routines', 'tls_process_server_certificate', 'certificate verify failed')]

And the following does not work either:

# Using elasticsearch-dbapi
from es.elastic.api import connect
conn = connect (host = 'localhost')
curs = conn.cursor ()
curs.execute (
    "select * from kibana_sample_data_flights LIMIT 10"
)
print ([row for row in curs])

Part of the error message: ProtocolError: ('Connection aborted.', RemoteDisconnected ('Remote end closed connection without response'))

score 0 · Answer 1 · answered Jun 02 '21 at 15:59

Now this works with sqlalchemy

# sqlalchemy
from sqlalchemy.engine import create_engine
ssl_args = {"use_ssl": True, "verify_certs": False, "ssl_show_warn": False}
engine = create_engine (
     "odelasticsearch + https: // admin: admin @ localhost: 9200 /"
     , connect_args = ssl_args
)
rows = engine.connect (). execute (
     "select Carrier, count (*) from kibana_sample_data_flights GROUP BY Carrier"
)
print ([row for row in rows])

It would only be necessary to solve the issue with elasticsearch-dbapi

How can I use elasticsearch-dbapi with Open Distro for ElasticSearch, ignoring SSL certificates?

1 Answers1