Skip to main content

A DBAPI and SQLAlchemy dialect for Elasticsearch

Project description

ElasticSearch DBAPI

Build Status PyPI version Coverage Status

elasticsearch-dbapi Implements a DBAPI (PEP-249) and SQLAlchemy dialect, that enables SQL access on elasticsearch clusters for query only access. Uses Elastic X-Pack SQL API

We are currently building support for opendistro/_sql API for AWS ES

This library supports Elasticsearch 7.X versions.

Installation

$ pip install elasticsearch-dbapi

To install support for AWS ES:

$ pip install elasticsearch-dbapi[aws]

Usage:

Using DBAPI:

from es.elastic.api import connect

conn = connect(host='localhost')
curs = conn.cursor()
curs.execute(
    "select * from flights LIMIT 10"
)
print([row for row in curs])

Using SQLAlchemy execute:

from sqlalchemy.engine import create_engine

engine = create_engine("elasticsearch+http://localhost:9200/")
rows = engine.connect().execute(
    "select * from flights LIMIT 10"
)
print([row for row in rows])

Using SQLAlchemy:

from sqlalchemy import func, select
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import MetaData, Table


engine = create_engine("elasticsearch+http://localhost:9200/")
logs = Table("flights", MetaData(bind=engine), autoload=True)
count = select([func.count("*")], from_obj=logs).scalar()
print(f"COUNT: {count}")

Using SQLAlchemy reflection:

from sqlalchemy.engine import create_engine
from sqlalchemy.schema import Table, MetaData

engine = create_engine("elasticsearch+http://localhost:9200/")
logs = Table("flights", MetaData(bind=engine), autoload=True)
print(engine.table_names())

metadata = MetaData()
metadata.reflect(bind=engine)
print([table for table in metadata.sorted_tables])
print(logs.columns)

Connection Parameters:

elasticsearch-py is used to establish connections and transport, this is the official elastic python library. Elasticsearch constructor accepts multiple optional parameters that can be used to properly configure your connection on aspects like security, performance and high availability. These optional parameters can be set at the connection string, for example:

   elasticsearch+http://localhost:9200/?http_compress=True&timeout=100

will set transport to use gzip (http_compress) and timeout to 10 seconds.

For more information on configuration options, look at elasticsearch-py’s documentation:

The connection string follows RFC-1738, to support multiple nodes you should use sniff_* parameters

Tests

To run unittest launch elasticsearch and kibana (kibana is really not required but is a nice to have)

$ docker-compose up -d
$ nosetests -v

Special case for sql opendistro endpoint (AWS ES)

AWS ES exposes the opendistro SQL plugin, and it follows a different SQL dialect. Because of dialect and API response differences, we provide limited support for opendistro SQL on this package using the odelasticsearch driver:

from sqlalchemy.engine import create_engine

engine = create_engine(
    "odelasticsearch+https://search-SOME-CLUSTER.us-west-2.es.amazonaws.com:443/"
)
rows = engine.connect().execute(
    "select count(*), Carrier from flights GROUP BY Carrier"
)
print([row for row in rows])

Known limitations

This library does not yet support the following features:

  • Array type columns are not supported. Elaticsearch SQL does not support them either. SQLAlchemy get_columns will exclude them.
  • object and nested column types are not well supported and are converted to strings
  • Indexes that whose name start with .
  • GEO points are not currently well-supported and are converted to strings
  • Very limited support for AWS ES, no AWS Auth yet for example

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

elasticsearch-dbapi-0.1.1.tar.gz (32.6 kB view details)

Uploaded Source

Built Distribution

elasticsearch_dbapi-0.1.1-py3-none-any.whl (83.6 kB view details)

Uploaded Python 3

File details

Details for the file elasticsearch-dbapi-0.1.1.tar.gz.

File metadata

  • Download URL: elasticsearch-dbapi-0.1.1.tar.gz
  • Upload date:
  • Size: 32.6 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/2.0.0 pkginfo/1.5.0.1 requests/2.23.0 setuptools/39.0.1 requests-toolbelt/0.9.1 tqdm/4.43.0 CPython/3.6.8

File hashes

Hashes for elasticsearch-dbapi-0.1.1.tar.gz
Algorithm Hash digest
SHA256 1666d44391449b38c5733fcf5c116ae11b3accadf237a428447e6123044e8e8f
MD5 26e4023c2e8e41cca5dec9c482393e37
BLAKE2b-256 0da22f15ba72844ab5cd7a7f2a23e4f2cc1e55108e40f9ac46d4742536785bfc

See more details on using hashes here.

Provenance

File details

Details for the file elasticsearch_dbapi-0.1.1-py3-none-any.whl.

File metadata

  • Download URL: elasticsearch_dbapi-0.1.1-py3-none-any.whl
  • Upload date:
  • Size: 83.6 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/2.0.0 pkginfo/1.5.0.1 requests/2.23.0 setuptools/39.0.1 requests-toolbelt/0.9.1 tqdm/4.43.0 CPython/3.6.8

File hashes

Hashes for elasticsearch_dbapi-0.1.1-py3-none-any.whl
Algorithm Hash digest
SHA256 33449afaf0850a9509883b59a6639a996f7f83d5113261ec4cea6bce531a8a37
MD5 615be532303d9f161d8f97c4f73de11c
BLAKE2b-256 8d9aac886ac91d9855bd6f8b0e226e6fb017adc3df7fecf1f86b36c902aea555

See more details on using hashes here.

Provenance

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page