A DBAPI and SQLAlchemy dialect for Elasticsearch
Project description
ElasticSearch DBAPI
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
andnested
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
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for elasticsearch-dbapi-0.1.1.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 1666d44391449b38c5733fcf5c116ae11b3accadf237a428447e6123044e8e8f |
|
MD5 | 26e4023c2e8e41cca5dec9c482393e37 |
|
BLAKE2b-256 | 0da22f15ba72844ab5cd7a7f2a23e4f2cc1e55108e40f9ac46d4742536785bfc |
Hashes for elasticsearch_dbapi-0.1.1-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 33449afaf0850a9509883b59a6639a996f7f83d5113261ec4cea6bce531a8a37 |
|
MD5 | 615be532303d9f161d8f97c4f73de11c |
|
BLAKE2b-256 | 8d9aac886ac91d9855bd6f8b0e226e6fb017adc3df7fecf1f86b36c902aea555 |