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.

On Elastic Elasticsearch: Uses Elastic X-Pack SQL API

On AWS ES, opendistro Elasticsearch: Open Distro SQL

This library supports Elasticsearch 7.X versions.

Installation

$ pip install elasticsearch-dbapi

To install support for AWS Elasticsearch Service / Open Distro:

$ pip install elasticsearch-dbapi[opendistro]

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

Fetch size

By default the maximum number of rows which get fetched by a single query is limited to 10000. This can be adapted through the fetch_size parameter:

from es.elastic.api import connect

conn = connect(host='localhost')
curs = conn.cursor(fetch_size=1000)

If more than 10000 rows should get fetched then max_result_window has to be adapted as well.

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. 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])

Or using DBAPI:

from es.opendistro.api import connect

conn = connect(host='localhost',port=9200,path="", scheme="http")

curs = conn.cursor().execute(
    "select * from flights LIMIT 10"
)

print([row for row in curs])

Opendistro (AWS ES) Basic authentication

Basic authentication is configured as expected on the , fields of the URI

from sqlalchemy.engine import create_engine

engine = create_engine(
    "odelasticsearch+https://my_user:my_password@search-SOME-CLUSTER.us-west-2.es.amazonaws.com:443/"
)

IAM AWS Authentication keys are passed on the URI basic auth location, and by setting aws_keys

Query string keys are:

  • aws_keys
  • aws_region
from sqlalchemy.engine import create_engine

engine = create_engine(
    "odelasticsearch+https://<AWS_ACCESS_KEY>:<AWS_SECRET_KEY>@search-SOME-CLUSTER.us-west-2.es.amazonaws.com:443/?aws_keys=1&&aws_region=<AWS_REGION>"
)

IAM AWS profile is configured has a query parameter name aws_profile on the URI. The value for the key provides the AWS region

from sqlalchemy.engine import create_engine

engine = create_engine(
    "odelasticsearch+https://search-SOME-CLUSTER.us-west-2.es.amazonaws.com:443/?aws_profile=us-west-2"
)

To connect to the provided Opendistro ES on docker-compose use the following URI: odelasticsearch+https://admin:admin@localhost:9400/?verify_certs=False

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

  • AWS ES (opendistro elascticsearch) is supported (still beta), known limitations are:

    • You are only able to GROUP BY keyword fields (new experimental opendistro SQL already supports it)
    • Indices with dots are not supported (indices like 'audit_log.2021.01.20'), on these cases we recommend the use of aliases

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.2.0.tar.gz (57.4 kB view details)

Uploaded Source

Built Distributions

elasticsearch_dbapi-0.2.0-py3.7.egg (77.1 kB view details)

Uploaded Source

elasticsearch_dbapi-0.2.0-py3-none-any.whl (82.9 kB view details)

Uploaded Python 3

File details

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

File metadata

  • Download URL: elasticsearch-dbapi-0.2.0.tar.gz
  • Upload date:
  • Size: 57.4 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/2.0.0 pkginfo/1.6.1 requests/2.25.1 setuptools/41.2.0 requests-toolbelt/0.9.1 tqdm/4.55.1 CPython/3.7.6

File hashes

Hashes for elasticsearch-dbapi-0.2.0.tar.gz
Algorithm Hash digest
SHA256 8f5add94007efabd1772faa1d880dce0c39e53a9a5757e9084bc41b5d7f016fc
MD5 9b7480fc8066c7c08126349436f0731c
BLAKE2b-256 37a201e2cc8b32067afac050acfbbfbbd798b5416b783f9d3cbab43f8d0b0257

See more details on using hashes here.

Provenance

File details

Details for the file elasticsearch_dbapi-0.2.0-py3.7.egg.

File metadata

  • Download URL: elasticsearch_dbapi-0.2.0-py3.7.egg
  • Upload date:
  • Size: 77.1 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/2.0.0 pkginfo/1.6.1 requests/2.25.1 setuptools/41.2.0 requests-toolbelt/0.9.1 tqdm/4.55.1 CPython/3.7.6

File hashes

Hashes for elasticsearch_dbapi-0.2.0-py3.7.egg
Algorithm Hash digest
SHA256 75bd588f2442205c6e5fce6e3fb96d6547ad2dd5ac0a5c02110c65311d7484fb
MD5 6731eca0806d86ba9a0ee16cd6b9518b
BLAKE2b-256 cb97b7585e370971a41cf5e1d99ccb3b345e388597953a918ef4bbe91bfd2755

See more details on using hashes here.

Provenance

File details

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

File metadata

  • Download URL: elasticsearch_dbapi-0.2.0-py3-none-any.whl
  • Upload date:
  • Size: 82.9 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/2.0.0 pkginfo/1.6.1 requests/2.25.1 setuptools/41.2.0 requests-toolbelt/0.9.1 tqdm/4.55.1 CPython/3.7.6

File hashes

Hashes for elasticsearch_dbapi-0.2.0-py3-none-any.whl
Algorithm Hash digest
SHA256 d0b510c18705e020827d6eff6a0225a7e746f05db805007292bf9758bf339227
MD5 11ac1ee5130953f96c6f74747cf3b7f5
BLAKE2b-256 44eb9d666c18651585bca4697d824cc3325703fc0c97620b8e9dd5e45128cfa7

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