pydruid

pydruid is the reference Python connector for Apache Druid, the real-time analytics database. It exposes a query-builder DSL for Druid’s native JSON queries (timeseries, topN, groupBy, select, scan, segment metadata, time boundary) where filters and post-aggregations are written as Python expressions — Dimension(‘lang’) == ‘en’ — rather than hand-assembled JSON, and it ships both a synchronous client (built on stdlib urllib) and a Tornado-based asynchronous client for use inside async applications.

Beyond the native query DSL, pydruid also implements the Python DB API 2.0 and a SQLAlchemy dialect, so Druid can be queried with ordinary SQL through create_engine(‘druid://…’) or a portable pydruid.db.connect() cursor — useful for BI tools and ORMs that expect a standard database driver rather than a bespoke query builder. Query results can be exported directly to TSV or pandas DataFrames for downstream analysis.

What You Get

• Native query builder - Construct timeseries, topN, groupBy, select, and scan queries as Python objects instead of hand-written Druid JSON.
• Operator-overloaded filters - Build nested filter and post-aggregation trees using ==, &, and ~ on Dimension/Field objects.
• Sync and async clients - PyDruid for blocking calls and AsyncPyDruid (Tornado-based) for non-blocking use inside async frameworks.
• DB API 2.0 + SQLAlchemy dialect - Query Druid as a standard SQL database via pydruid.db.connect() or create_engine('druid://...').
• Pandas/TSV export - Turn any query result into a pandas.DataFrame or a TSV file for downstream analysis.
• CLI console - Interactive pydruid command-line client for running Druid SQL against a broker.

Common Use Cases

• Ad-hoc analytics dashboards - Data teams query Druid timeseries/topN/groupBy results and pull them straight into pandas for exploration.
• BI tool integration - Connecting SQLAlchemy-aware BI tools (e.g. Apache Superset) to Druid through the bundled SQLAlchemy dialect.
• Async web services - Backend services built on Tornado query Druid without blocking the event loop via AsyncPyDruid.
• Batch export pipelines - Scheduled jobs run scan/select queries and export results to TSV for downstream ETL.
• Ops CLI queries - Engineers use the bundled console to run one-off Druid SQL checks from the terminal.

Under The Hood

Architecture The library has a layered structure with two parallel entry surfaces atop Druid’s REST query API: a JSON-over-HTTP query builder (pydruid/client.py, pydruid/query.py) and a DB-API/SQLAlchemy adapter (pydruid/db/api.py, pydruid/db/sqlalchemy.py). BaseDruidClient centralizes header/auth/URL assembly (_prepare_url_headers_and_body), while PyDruid and AsyncPyDruid subclass it to implement _post differently — blocking urllib versus Tornado’s async HTTP client — a clean template-method split. QueryBuilder.build_query is the core translation layer, walking a plain kwargs dict and dispatching keys like aggregations, post_aggregations, filter, having, and dimension(s) to dedicated builder modules under pydruid/utils/ (aggregators.py, filters.py, having.py, postaggregator.py, dimensions.py), each of which serializes its own DSL objects into Druid’s wire JSON. Query wraps the parsed response as a MutableSequence and exposes export_tsv/export_pandas with per-query-type branches. Because the DSL/query-builder path and the DB-API/SQLAlchemy path each implement their own HTTP handling and error parsing rather than sharing BaseDruidClient, a change to Druid’s auth or error-response format needs to be applied in two places — the main coupling risk if Druid’s wire protocol shifts.

Tech Stack A pure Python 3.6+ package with only requests as an unconditional install dependency; everything else is opt-in through extras (pandas for DataFrame export, tornado for the async client, sqlalchemy for the dialect, and pygments/prompt_toolkit/tabulate for the CLI console). Notably, the primary PyDruid client talks to Druid using stdlib urllib.request rather than requests — requests is actually consumed only inside the DB-API layer and CLI — so the two client surfaces run on two different HTTP stacks. Build tooling is minimal (setuptools, pinned via pip-compile), and CI (.travis.yml, tox.ini) runs pytest plus separate black/flake8/isort tox environments across Python 3.6-3.8. It registers a pydruid console-script entry point and sqlalchemy.dialects entry points (druid, druid.http, druid.https) so SQLAlchemy can discover the dialect without an explicit import.

Code Quality Test coverage is comprehensive — dedicated suites for the sync client, async client, query builder, each utils module, and a tests/db/ subtree covering connection, cursor, dialect, and bearer-auth paths, all on pytest. Error handling has a genuinely useful pattern in PyDruid._post, which catches urllib.error.HTTPError and re-raises as IOError with both the Druid error body and the offending query attached for debugging; the same method’s error-body parsing also has a broad except (ValueError, AttributeError, KeyError): pass that can silently swallow secondary parsing failures. Naming is consistent snake_case matching PEP-249/Druid-JSON conventions. There are no type hints anywhere in the library, and although flake8-mypy is a listed dev dependency, no mypy environment is actually wired into tox.ini — a minor docs/CI mismatch. Overall, solid conventional coverage with a couple of rough edges rather than exemplary rigor.

API Design The standout ergonomic idea is pydruid.utils.filters.Dimension overloading Python’s comparison and bitwise operators so filter trees read as boolean expressions instead of nested dicts — (Dimension('user_lang') == 'en') & (Dimension('first_hashtag') == 'oscars') — meaningfully cutting the boilerplate of hand-writing Druid’s native filter/aggregation JSON. The same pattern extends to post-aggregations (Field('length') / Field('count')) and theta-sketch set operations. Getting started needs almost no ceremony: instantiate PyDruid(url, endpoint) and call one of timeseries/topn/groupby/select/scan, each thoroughly docstringed with a runnable example and expected output shape directly in the source. The DB-API/SQLAlchemy surface additionally lets users treat Druid as an ordinary SQL-speaking engine via create_engine('druid://...'), a distinct and valuable ergonomic path for users who would rather use plain SQL than learn the native query DSL — effectively two audiences served from one package.