{"id":9329,"library":"sqlalchemy-dremio","title":"SQLAlchemy Dremio","description":"SQLAlchemy Dremio is a SQLAlchemy dialect that enables connecting to Dremio via its Apache Arrow Flight interface. It's actively maintained, with the current stable version being 3.0.5, and generally follows a release cadence tied to Dremio Flight client updates and SQLAlchemy compatibility.","status":"active","version":"3.0.5","language":"en","source_language":"en","source_url":"https://github.com/narendrans/sqlalchemy_dremio","tags":["sqlalchemy","dremio","database","dialect","flight","apache-arrow"],"install":[{"cmd":"pip install sqlalchemy-dremio","lang":"bash","label":"Install stable version"},{"cmd":"pip install sqlalchemy-dremio[pandas]","lang":"bash","label":"Install with Pandas support"}],"dependencies":[{"reason":"Core SQLAlchemy library for database abstraction.","package":"SQLAlchemy","optional":false},{"reason":"Underlying data format for Apache Arrow Flight communication.","package":"pyarrow","optional":false},{"reason":"Official Dremio Flight client for establishing connections and executing queries.","package":"dremio-flight-client","optional":false},{"reason":"Optional dependency for easily converting query results into Pandas DataFrames.","package":"pandas","optional":true}],"imports":[],"quickstart":{"code":"import sqlalchemy\nimport os\n\n# Configure Dremio connection details using environment variables for security\nDREMIO_HOST = os.environ.get('DREMIO_HOST', 'localhost')\nDREMIO_PORT = os.environ.get('DREMIO_PORT', '32010')\nDREMIO_USER = os.environ.get('DREMIO_USER', 'dremio_user')\nDREMIO_PASS = os.environ.get('DREMIO_PASS', 'dremio_password')\nDREMIO_CATALOG = os.environ.get('DREMIO_CATALOG', 'DREMIO') # e.g., 'DREMIO' or your space name\nUSE_SSL = os.environ.get('DREMIO_USE_SSL', 'true').lower() == 'true'\n\n# Construct the connection string\nconnection_string = (\n    f\"dremio+flight://{DREMIO_USER}:{DREMIO_PASS}@{DREMIO_HOST}:{DREMIO_PORT}/\"\n    f\"{DREMIO_CATALOG}?USE_SSL={str(USE_SSL).lower()}\"\n)\n\nprint(f\"Connecting to: {connection_string.split(DREMIO_PASS)[0]}*****@{DREMIO_HOST}:{DREMIO_PORT}/{DREMIO_CATALOG}...\")\n\ntry:\n    # Create the SQLAlchemy engine\n    engine = sqlalchemy.create_engine(connection_string)\n\n    # Establish a connection and execute a simple query\n    with engine.connect() as connection:\n        # Ensure you have a table accessible, e.g., 'sys.version'\n        result = connection.execute(sqlalchemy.text(\"SELECT * FROM sys.version\"))\n        \n        # Fetch and print results\n        print(\"\\nQuery Results:\")\n        for row in result:\n            print(row)\n\n    print(\"\\nSuccessfully connected to Dremio and executed a query.\")\n\nexcept Exception as e:\n    print(f\"\\nError connecting to Dremio or executing query: {e}\")\n    print(\"Please ensure Dremio is running, credentials are correct, \")\n    print(\"and the Flight endpoint is accessible (often port 32010, sometimes with SSL).\")\n","lang":"python","description":"This quickstart demonstrates how to establish a connection to Dremio using `sqlalchemy-dremio` via the Flight interface and execute a basic query. It uses environment variables for secure credential handling and highlights common connection string parameters like host, port, user, password, catalog, and SSL settings."},"warnings":[{"fix":"Update your connection string from `dremio://...` to `dremio+flight://...` and ensure `dremio-flight-client` is installed.","message":"Version 3.x introduced a breaking change by switching from the deprecated `PyDremio` library to the official `dremio-flight-client` and updating the connection string scheme. Older versions used `dremio://`, while 3.x+ requires `dremio+flight://`.","severity":"breaking","affected_versions":">=3.0.0"},{"fix":"Add `?USE_SSL=true` to your connection string if connecting to a Dremio instance that uses SSL (which is common). Example: `dremio+flight://user:pass@host:32010/DREMIO?USE_SSL=true`.","message":"Dremio's Flight endpoint often requires SSL/TLS by default. Failing to include `USE_SSL=true` in your connection string (or explicitly setting it to false if the server doesn't use SSL) can lead to connection failures.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure your connection string includes a valid Dremio catalog/space name, e.g., `dremio+flight://user:pass@host:32010/DREMIO` or `dremio+flight://user:pass@host:32010/MySpace`.","message":"The connection string must include a catalog (e.g., `/DREMIO`, `/your_space`) after the host and port. Omitting it or providing an incorrect one can lead to 'Table not found' or 'Schema not found' errors, as queries might not resolve to the correct Dremio context.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Verify the correct Dremio Flight port for your installation, which is typically 32010. Update your connection string accordingly: `...:32010/...`.","message":"Dremio Flight often runs on a different port than the UI or ODBC/JDBC connections (commonly 32010). Using the wrong port (e.g., 9047 for UI) will result in connection refused errors.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Run `pip install sqlalchemy-dremio` to install the dialect.","cause":"`sqlalchemy-dremio` library is not installed or accessible in the current Python environment.","error":"sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:dremio"},{"fix":"Run `pip install dremio-flight-client` or simply `pip install sqlalchemy-dremio` (which should pull it as a dependency).","cause":"The core dependency `dremio-flight-client` is missing. This is required for establishing Flight connections.","error":"ModuleNotFoundError: No module named 'dremio_flight_client'"},{"fix":"Verify that Dremio is running, the Flight endpoint is enabled, the host and port (commonly 32010) are correct, and network firewalls allow the connection. Also, check if `USE_SSL=true` is needed in the connection string.","cause":"The Dremio Flight server is not reachable at the specified host and port, or a firewall is blocking the connection. This can also indicate incorrect port or a missing SSL flag if the server expects SSL.","error":"pyarrow.lib.ArrowIOError: Failed to connect to ... Connection refused"},{"fix":"Double-check the username and password in your connection string or environment variables. Ensure the user has permissions to access Dremio via Flight.","cause":"The username or password provided in the connection string is incorrect for the Dremio instance.","error":"pyarrow.lib.ArrowIOError: UNAUTHENTICATED: Invalid credentials"},{"fix":"Ensure the catalog specified in the connection string (e.g., `/DREMIO`) is correct. Also, verify the table/schema name in your query and ensure it's fully qualified if necessary (e.g., `\"Dremio\".\"myspace\".\"mytable\"`).","cause":"The SQL query references a table or schema that does not exist or is not accessible, often due to an incorrect default catalog in the connection string or an incorrect path in the query.","error":"sqlalchemy.exc.ProgrammingError: (dremio_flight_client.client.DremioFlightClientException) RESOURCE_EXHAUSTED: Schema/Table '...' not found"}]}