sqlalchemy-drill

sqlalchemy-drill is a database dialect that plugs Apache Drill into the SQLAlchemy engine registry, so any tool or codebase that speaks SQLAlchemy can query Drill’s schema-free datasets (files, HDFS, MongoDB, Splunk, and more) as if they were ordinary relational tables. It registers three drivers under the drill+sadrill, drill+jdbc, and drill+odbc connection schemes, each translating SQLAlchemy’s compiler output into Drill-compatible SQL (backtick-quoted identifiers, FROM (values(1)) for from-less selects, Drill’s own reserved-word list) and mapping Drill’s result-set metadata back into SQLAlchemy column types.

The primary REST driver ships its own Python DB-API 2.0 implementation (drilldbapi) built on requests and the ijson streaming parser, so large result sets are parsed incrementally instead of buffered fully in memory before being handed back through the cursor interface. Because it was built to make Drill visible to Apache Superset’s SQLAlchemy-based connection layer, the project’s evolution has tracked what Superset and other BI/ORM consumers need from a dialect: schema/table/view introspection, foreign-key and index no-ops for a system that has neither, and JSON column support for nested data.

With roughly a dozen active contributors and continuous small fixes across data-type coercion, connection-string parsing, and reserved-word handling, it remains the primary way Python and SQL-oriented tooling talk to Drill without hand-writing REST or JDBC calls.

What You Get

• Three connection drivers - drill+sadrill (REST, the primary and most actively maintained path), drill+jdbc, and drill+odbc, selectable purely through the SQLAlchemy connection URL scheme.
• A streaming DB-API 2.0 cursor - the REST driver’s drilldbapi module parses Drill’s JSON responses incrementally with ijson rather than loading entire result sets into memory first.
• Schema introspection for schema-free sources - get_table_names, get_columns, and get_schema_names query Drill’s INFORMATION_SCHEMA and SHOW FILES/SHOW DATABASES to expose files, views, and workspaces as if they were normal tables.
• Drill-aware SQL compilation - a custom SQLCompiler and IdentifierPreparer handle Drill’s backtick quoting, its extensive SQL reserved-word list, and its FROM (values(1)) idiom for FROM-less SELECTs.
• Type mapping between Drill and SQLAlchemy - a _type_map table converts Drill’s REST/JDBC column type names (including map, list, struct, and JSON) into SQLAlchemy column types for use in ORM and Core queries.

Common Use Cases

• Connecting Apache Superset to Drill - the project exists primarily so Superset’s SQLAlchemy-based connection layer can query Drill-backed data sources through its BI dashboards.
• Querying files and HDFS through SQL tooling - point any SQLAlchemy-aware client at Drill’s dfs storage plugin to run SQL over CSV/Parquet/JSON files without a separate data warehouse.
• Federating MongoDB or Splunk data via SQL - Drill’s storage plugins expose MongoDB and Splunk collections as query-able schemas, and this dialect surfaces their dynamic (**) schemas through standard column introspection.
• Scripting ad-hoc analytics in Python - create a SQLAlchemy engine with a drill+sadrill:// URL and run engine.execute()/Core queries against Drill from notebooks or ETL scripts without hand-building REST calls.
• JDBC/ODBC integration for BI tools that require it - the drill+jdbc and drill+odbc drivers cover clients that need a JDBC or ODBC connection string rather than the REST API.

Under The Hood

Architecture The dialect is organized around SQLAlchemy’s plugin contract: sqlalchemy_drill/base.py defines the shared DrillDialect (type map, compiler, identifier preparer, introspection methods), and sadrill.py/jdbc.py/odbc.py each subclass it to register a distinct entry point (drill.sadrill, drill.jdbc, drill.odbc) in setup.py, so SQLAlchemy’s create_engine() dispatches to the right driver purely from the URL scheme. The REST path additionally ships a self-contained DB-API 2.0 layer under sqlalchemy_drill/drilldbapi/ (_drilldbapi.py’s Cursor/Connection classes, api_exceptions.py, api_globals.py) that Drill’s REST responses are parsed into via ijson’s incremental object builder, keeping the dialect’s job cleanly split between “speak DB-API to SQLAlchemy” and “speak REST to Drill.” Introspection methods (get_table_names, get_columns, get_plugin_type) branch on Drill’s storage-plugin type (file vs. mongo/splunk vs. relational-style) because Drill’s schema-discovery SQL differs per plugin, which is the core complexity a change to Drill’s INFORMATION_SCHEMA behavior would touch first.

Tech Stack Pure Python targeting the SQLAlchemy dialect API (declared via entry_points in setup.py), with requests for the REST transport, ijson (pinned to ~=3.3.0) for streaming JSON parsing, and optional extras for JPype1/JayDeBeApi (JDBC) and pyodbc (ODBC) kept out of the default install so consumers only pull in what their chosen driver needs. There is no build step beyond standard setuptools packaging; CI runs CodeQL security scanning against the Python source.

Code Quality The test/ directory includes a DB-API 2.0 compliance suite (test_dbapi_compliance.py against the third-party dbapi-compliance package), dialect-specific tests using pytest and testcontainers to spin up a real local Drill instance, and separate JDBC/ODBC test modules, giving the REST path meaningful integration coverage beyond unit tests. Error handling favors explicit try/except blocks around Drill-specific operations (schema queries, connection parsing) with logging via a dedicated drilldbapi/sadrill logger rather than silent failures, and the CHANGELOG shows a steady cadence of targeted bug fixes (null-value typecasting, leaked StopIteration, reserved-word additions) rather than large rewrites.

What Makes It Unique Unlike most SQLAlchemy dialects, which wrap an existing lower-level driver, this project had to build its own DB-API implementation from scratch for the REST path because no standard Python driver for Drill’s HTTP query API existed, and it does so with a streaming JSON parser so large Drill result sets don’t have to be fully buffered before SQLAlchemy can iterate them. It also has to reconcile Drill’s schema-free, storage-plugin-based data model (files, MongoDB, Splunk, HDFS) with SQLAlchemy’s assumption of a conventional relational catalog, branching its introspection logic per plugin type rather than assuming one uniform INFORMATION_SCHEMA shape.