elasticsearch-dbapi

Warnings

• breaking To connect to Elasticsearch 8.x, the `ELASTIC_CLIENT_APIVERSIONING` environment variable must be set to `1` in your Python application. Failing to do so will result in connection or query errors due to API incompatibilities.
  Fix: Set `os.environ['ELASTIC_CLIENT_APIVERSIONING'] = '1'` in your application or as a system environment variable before initiating connections.
• gotcha This library defaults to complying with SQL v1. If your Elasticsearch/OpenSearch SQL endpoint requires v2, you must pass `v2=true` as a query parameter in the connection string (e.g., `connect(host='localhost', v2=True)`).
  Fix: Add `v2=True` to the `connect` function call or `create_engine` URL if using SQLAlchemy (e.g., `elasticsearch+http://localhost:9200/?v2=true`).
• gotcha There are known limitations including lack of support for array type columns (which are excluded by SQLAlchemy's `get_columns`) and issues with indexes whose names start with a dot ('.'). Specific limitations also exist for AWS ES/OpenDistro, such as only being able to `GROUP BY` keyword fields and problems with indices containing dots (e.g., 'audit_log.2021.01.20').
  Fix: Avoid using array type columns or querying indices with leading dots or specific dot patterns where possible. For AWS ES, review `GROUP BY` usage to ensure it targets keyword fields. Consider using aliases for problematic index names.
• gotcha The maximum number of rows fetched by a single query is limited to 10000 by default. This can lead to truncated results for larger datasets.
  Fix: Adjust the `fetch_size` parameter during connection (e.g., `connect(host='localhost', fetch_size=50000)`) to a higher value if you expect more results. Be mindful of memory consumption with very large fetch sizes.
• gotcha The `elasticsearch-dbapi` library relies on the underlying `elasticsearch-py` and `opensearch-py` clients. Breaking changes in major versions of Elasticsearch or OpenSearch, or in their official Python clients, can indirectly impact the functionality or require configuration adjustments in `elasticsearch-dbapi` even if `elasticsearch-dbapi` itself doesn't have a breaking change.
  Fix: Always review the breaking changes documentation for the specific Elasticsearch/OpenSearch version you are targeting and the corresponding `elasticsearch-py` or `opensearch-py` client versions when upgrading your cluster or clients.

Install

• pip install elasticsearch-dbapi Install stable release

Imports

• connect

  from es.elastic.api import connect

• create_engine

  from sqlalchemy.engine import create_engine

  Used with the 'elasticsearch+http://' or 'odelasticsearch+https://' dialect.

Quickstart

Demonstrates connecting to an Elasticsearch/OpenSearch instance using the DBAPI interface, executing a basic SQL query, and listing available indices (tables). It highlights the critical environment variable for Elasticsearch 8.x compatibility.

import os
from es.elastic.api import connect

# IMPORTANT: For Elasticsearch 8.x, set ELASTIC_CLIENT_APIVERSIONING=1
# in your environment or before connecting to enable compatibility mode.
# os.environ['ELASTIC_CLIENT_APIVERSIONING'] = '1'

# Configure host and port. Adjust as necessary for your setup.
# For OpenSearch, a common port is 19200.
ES_HOST = os.environ.get('ES_HOST', 'localhost')
ES_PORT = int(os.environ.get('ES_PORT', 9200))

try:
    # Connect to Elasticsearch/OpenSearch via DBAPI
    conn = connect(host=ES_HOST, port=ES_PORT)
    curs = conn.cursor()

    # Execute a simple SQL query
    curs.execute("SELECT 1")
    result = curs.fetchall()
    print(f"Connection successful. Query 'SELECT 1' returned: {result}")

    # Example: List available tables (indices)
    curs.execute("SHOW TABLES")
    tables = curs.fetchall()
    print(f"First 5 available tables (indices): {tables[:5]}...")

    curs.close()
    conn.close()
    print("Successfully connected, queried, and closed connection.")
except Exception as e:
    print(f"Failed to connect or query Elasticsearch/OpenSearch: {e}")
    print("Please ensure your Elasticsearch/OpenSearch instance is running and accessible at ")
    print(f"'{ES_HOST}:{ES_PORT}'. For Elasticsearch 8.x, ensure ELASTIC_CLIENT_APIVERSIONING is set.")