{"id":2779,"library":"sqlalchemy-drill","title":"SQLAlchemy Apache Drill Dialect","description":"sqlalchemy-drill is an Apache Drill dialect for SQLAlchemy, enabling Python applications to connect and interact with Apache Drill databases using SQLAlchemy's ORM or Core. It supports connections via REST API, JDBC, and ODBC. The library is actively maintained, with version 1.1.10 being the current release, and sees regular bug fix updates. Its primary purpose includes integration with tools like Apache Superset.","status":"active","version":"1.1.10","language":"en","source_language":"en","source_url":"https://github.com/JohnOmernik/sqlalchemy-drill","tags":["SQLAlchemy","Apache Drill","database","dialect","big data","BI"],"install":[{"cmd":"pip install sqlalchemy-drill","lang":"bash","label":"Install core dialect"}],"dependencies":[{"reason":"Required for JDBC connections to Apache Drill. Used in conjunction with jPype.","package":"JayDeBeApi","optional":true},{"reason":"Required for JDBC connections to Apache Drill to manage the JVM and JDBC driver. Specific version 0.6.3 or compatible is noted.","package":"jPype1","optional":true},{"reason":"Required for ODBC connections to Apache Drill.","package":"pyodbc","optional":true}],"imports":[{"symbol":"create_engine","correct":"from sqlalchemy import create_engine"},{"symbol":"text","correct":"from sqlalchemy import text"}],"quickstart":{"code":"from sqlalchemy import create_engine, text\nimport os\n\n# For REST API connection\n# Adjust host and port as per your Drill setup\n# Use environment variables for sensitive data in production\n# Example for local Drill embedded mode (drill+sadrill)\nDR_HOST = os.environ.get('DRILL_HOST', 'localhost')\nDR_PORT = os.environ.get('DRILL_REST_PORT', '8047')\nDR_PLUGIN = os.environ.get('DRILL_STORAGE_PLUGIN', 'dfs')\nDR_USE_SSL = os.environ.get('DRILL_USE_SSL', 'False').lower() == 'true'\n\nconnection_string = f\"drill+sadrill://{DR_HOST}:{DR_PORT}/{DR_PLUGIN}?use_ssl={DR_USE_SSL}\"\n\ntry:\n    engine = create_engine(connection_string)\n    with engine.connect() as connection:\n        result = connection.execute(text(\"SELECT * FROM INFORMATION_SCHEMA.CATALOGS LIMIT 5\"))\n        print(\"Successfully connected to Apache Drill via REST API.\")\n        print(\"First 5 catalogs:\")\n        for row in result:\n            print(row)\nexcept Exception as e:\n    print(f\"Failed to connect to Apache Drill: {e}\")\n    print(\"Ensure Apache Drill is running and accessible at the specified host/port.\")\n    print(\"For JDBC or ODBC, ensure required optional dependencies and drivers are installed and configured.\")\n","lang":"python","description":"This quickstart demonstrates how to establish a connection to Apache Drill using the `drill+sadrill` (REST API) dialect and execute a simple query. The example uses environment variables for configuration, which is recommended for production environments. Ensure Apache Drill is running and accessible from where this code is executed. For JDBC or ODBC connections, additional drivers and optional Python packages are required."},"warnings":[{"fix":"Upgrade Apache Drill to version 1.19 or newer, or explicitly cast string results to their native types in your application code for older Drill versions.","message":"When connecting to Apache Drill versions prior to 1.19 via the REST API (drill+sadrill), all data values are serialized to JSON strings. This means that numerical or temporal data will be returned as strings and require manual typecasting in your application.","severity":"breaking","affected_versions":"<1.19 (Drill version)"},{"fix":"Before creating the SQLAlchemy engine with `drill+jdbc://`, ensure `JayDeBeApi` and `jPype1` are installed, the Drill JDBC driver is downloaded, and the JVM is started and configured correctly in your Python code.","message":"JDBC connections require manual setup of JPype and the Drill JDBC driver. The `sqlalchemy-drill` library does not start the JVM or manage JDBC driver paths for you. Users must explicitly call `jpype.startJVM()` and set environment variables like `DRILL_JDBC_DRIVER_PATH` and `DRILL_JDBC_JAR_NAME`.","severity":"gotcha","affected_versions":"All versions (when using JDBC)"},{"fix":"Install the necessary optional packages manually (e.g., `pip install sqlalchemy-drill[jdbc]` or `pip install JayDeBeApi jPype1`, `pip install pyodbc`) if you intend to use JDBC or ODBC connections.","message":"Optional dependencies (`JayDeBeApi`, `jPype1`, `pyodbc`) for JDBC and ODBC connectivity are NOT installed by default with `pip install sqlalchemy-drill`. Attempting to use JDBC or ODBC dialects without these packages will result in connection errors.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Upgrade to `sqlalchemy-drill` version `1.1.2` or later to ensure correct impersonation functionality with Drill 1.20+.","message":"Versions prior to `1.1.2` had issues with impersonation for Drill 1.20, potentially leading to incorrect user context for queries.","severity":"gotcha","affected_versions":"<1.1.2"},{"fix":"Upgrade to `sqlalchemy-drill` version `1.1.10` or the latest stable version to benefit from various DB-API compliance and data handling bug fixes.","message":"Early versions (e.g., prior to `1.1.7` and `1.18`) contained bugs in DB-API compliance regarding method delegation and return values, which could lead to unexpected behavior during query execution or result fetching.","severity":"gotcha","affected_versions":"<1.1.7, <1.18"}],"env_vars":null,"last_verified":"2026-04-10T00:00:00.000Z","next_check":"2026-07-09T00:00:00.000Z"}