ODBC driver features and roadmap

[dprophet]

Would like to come up with a list of features and roadmap for the ODBC driver.

[dprophet]

Trino ODBC Driver : Proposed Development Roadmap

Overview

This document outlines a strategic roadmap for the trinodb/trino-odbc driver. The driver's current implementation successfully targets a narrow scope: enabling read-only access for Microsoft Excel and PowerBI on Windows.

The following initiatives are designed to evolve the driver from this specific utility into a more robust, performant, and feature-complete connector. This will broaden its compatibility with other third-party applications and enhance the user experience for existing tools.

---

1. Implement Comprehensive Metadata Caching

Goal: Dramatically improve performance and reduce server load for BI tools and applications that frequently browse database metadata.

Background: BI tools like PowerBI and Excel, as well as database IDEs, constantly send metadata queries (SQLTables, SQLColumns, etc.) to populate navigation panes. Currently, each of these requests results in a new API call to the Trino coordinator. This is slow and inefficient.

Actionable Items:

• Schema-Level Caching: Implement an in-memory, time-to-live (TTL) cache for:
  • Lists of Catalogs
  • Lists of Schemas
  • Lists of Tables and Views
• Table-Level Caching: Cache table-specific metadata, including:
  • Column names and types
  • Column descriptions and other metadata (if available)
• Configuration: Add a DSN (Data Source Name) option to enable/disable metadata caching and configure the cache TTL (e.g., MetadataCacheTTL=3600).

---

2. Introduce Full Support for Prepared Statements

Goal: Enable support for parameterized queries, a "Known Limitation" that is critical for security, performance, and compatibility with a wider range of applications.

Background: The repository's README explicitly states the driver "Does not support prepared execution (SQLPrepare, SQLExecute)" or "Parameterized queries." This is a major gap. Implementing this will prevent SQL injection vulnerabilities, allow Trino to reuse query execution plans, and unlock compatibility with countless tools that rely on this standard ODBC workflow.

Actionable Items:

• Core Function Implementation: Implement the required ODBC functions for this workflow:
  • SQLPrepare: To send the parameterized query to Trino for compilation.
  • SQLBindParameter: To securely bind user-supplied values to the query's parameters.
  • SQLExecute: To execute the prepared and bound statement.
  • SQLNumParams / SQLParamData: To support related parameter metadata discovery.

---

3. Integrate Trino Spooling Protocol (Large Result Set Handling)

Goal: Provide high-performance, reliable fetching of massive result sets by integrating with Trino's large query spooling-to-storage mechanism.

Background: When a query result is too large to efficiently pass through the coordinator, Trino can spool results to an external storage system (like S3) and provide download links to the client. This driver should support this protocol to handle large-scale data analysis without timeouts or coordinator bottlenecks.

Actionable Items:

• Protocol Detection: Enhance the query results client to correctly interpret Trino responses that initiate the spooling protocol.
• Result Set Management: Instead of fetching data from the nextUri on the coordinator, the driver must handle the new set of S3 (or other) URLs provided for the spooled data.
• Direct Download: Integrate logic (likely enhancing the existing libcurl dependency) to download result set fragments directly from object storage, bypassing the coordinator for data transfer and improving throughput.

---

4. Expand and Modernize Authentication

Goal: Move beyond the current limited authentication flows to support the most common and modern methods used in enterprise environments.

Background: The driver's "Known Limitations" note that it "Does not support most forms of Trino authentication including password authentication." Support is currently focused on External Authentication and a specific OIDC Client Credential flow. Expanding this is the highest priority for increasing driver adoption and compatibility.

Actionable Items:

• Username & Password: Implement support for basic username and password authentication, which is a fundamental requirement for many tools and simple deployments.
• OAuth 2.0 Device Flow: Implement the Device Authorization Grant (RFC 8628). This flow is critical for CLI tools, scripts, and other input-constrained devices where a browser-based login is not possible on the same machine.
• Flexible OIDC/OAuth 2.0 Configuration:
  • OIDC Discovery: Implement support for OIDC's discovery (via the .well-known/openid-configuration endpoint) to automatically fetch token, authorization, and other endpoints from a single issuer URI.
  • Manual Endpoint Configuration: For environments without OIDC discovery, provide DSN options to manually specify the token_endpoint, authorization_endpoint, and other necessary URLs.
• Implement Refresh Token Handling: For OAuth flows that provide a refresh token, securely store it (e.g., in the Windows Credential Manager) and automatically use it to request a new access token when the current one expires. This is critical for long-running, non-interactive sessions in BI tools.
• Solidify OIDC Client Credentials: Review and expand the existing client credential flow to ensure broad compatibility with various identity providers.
• Support for Custom Secure Token Service (STS): Implement a generic "credential broker" flow for maximum flexibility. This allows the driver to integrate with non-standard, proprietary, or cross-cloud authentication systems.
  • The driver would gather a "source" credential (like a Windows Kerberos ticket or an API key from an env variable).
  • It would POST this credential to a user-configured, external STS_Endpoint.
  • This external service would perform the non-standard logic (e.g., token exchange, legacy auth) and return a final, Trino-compatible JWT.
  • Requires new DSN options like AuthType=CustomSTS, STS_Endpoint, and STS_Credential_Source.
• Modernize with RFC 9728: Implement OAuth 2.0 Rich Authorization Requests (RAR). This ensures the driver remains compatible with advanced enterprise security postures that require fine-grained authorization details during the auth handshake.

---

5. Intelligent Rate Limiting & Throttling Support

Goal: Ensure the driver behaves as a "good citizen" within a complex multi-gateway architecture, gracefully handling both network-layer throttling (Custom Gateway) and compute-layer queuing (Trino Resource Groups).

Background: The environment involves a Custom Gateway (enforcing DDoS API limits) sitting in front of a Trino Gateway and multiple Trino clusters (enforcing Resource Groups). The driver must handle rejection signals from both layers without crashing the user's application.

Actionable Items:

• Phase 1: Naive Backoff (Immediate Priority): Prior to implementing full header parsing, immediate stability can be achieved by implementing a client-side backoff strategy for any HTTP 429 or 503 error.
  • Algorithm: Utilize a standard exponential backoff or Fibonacci sequence (e.g., 1s, 1s, 2s, 3s, 5s...) to determine retry intervals.
  • Goal: This serves as the "V1" implementation to prevent retry storms and reduce server load while the more complex, header-aware logic (Phase 2) is being developed.
• Phase 2: IETF RateLimit Header Parsing: Implement support for the IETF RateLimit Header Fields.
  • The driver must inspect HTTP 429 (Too Many Requests) responses from the Trino Gateway and Custom DDoS limiter.
  • It must parse headers such as RateLimit-Remaining, RateLimit-Reset, and Retry-After.
  • Logic: If RateLimit-Remaining is low, the driver can proactively throttle internal polling. If Retry-After is received, the driver must effectively "sleep" the connection for the specified duration before retrying, rather than bubbling an error immediately to the BI tool.
• Phase 3: Trino Resource Group Awareness: Integrate awareness of Trino's Resource Group states.
  • While standard queuing (state: QUEUED) is already handled, the driver must gracefully handle Hard Concurrency Limit rejections.
  • If Trino rejects a query because a Resource Group is full (e.g., QUERY_QUEUE_FULL), the driver should interpret this similarly to a Gateway 429: wait and retry with exponential backoff.
• Phase 4: Adaptive Polling Strategy: Implement an interleaving strategy where the driver dynamically adjusts its polling frequency based on both signals.
  • Scenario: If the Trino Gateway indicates high load (RateLimit-Remaining is low) AND the Trino query is in a QUEUED state for an extended period, the driver should automatically decrease its polling frequency (e.g., from 100ms to 2s) to reduce pressure on the Gateway while waiting for the Trino Resource Group to free up slots.

---

6. Prioritize Development via Application-Driven Testing

Goal: Use real-world BI tools as the primary drivers for prioritizing which ODBC features to implement next.

Background: The ODBC specification is vast, and this driver is "partially implemented." We cannot, nor should we, attempt to implement every ODBC function at once. Crucially, every BI tool and data application uses a different subset of the ODBC API. A feature critical for Tableau (e.g., specific SQLGetInfo calls) might be completely unused by PowerBI.

Therefore, a generic "pass/fail" test suite is insufficient. Our development must be prioritized by the specific ODBC functions that our target applications require. When a tool fails, it's not a "bug" but a "feature gap" that needs to be triaged.

Actionable Items:

• Establish a Target Application Test Matrix: Define a priority list of applications to test against for each release.
• Triage Failures as Feature Gaps: When a tool fails, the primary goal is to identify the missing ODBC function call (e..g., SQLPrepare, SQLGetFunctions, SQLFetchScroll). These gaps should be logged as new issues and linked to this roadmap.
• Prioritize Roadmap Based on Gaps: Failures in high-priority applications should directly influence the priority of the development items (Sections 1-4). If Tableau is a P0 target and it fails due to missing SQLPrepare, the priority of Section 2 (Prepared Statements) increases significantly.
• Target Application List (Verified for ODBC):
  • P0 (Core Targets):
    • Microsoft Power BI Desktop: (Natively uses ODBC for non-native connectors).
    • Microsoft Excel (Power Query): (Natively uses ODBC).
    • Tableau Desktop: (Provides a generic "Other Databases (ODBC)" connector).
  • P1 (High-Value Targets):
    • Qlik Sense: (Natively uses ODBC; requires an ODBC-JDBC bridge for JDBC).
    • MicroStrategy: (Natively supports ODBC).
    • SQL Server Integration Services (SSIS): (Provides native "ODBC Source" and "ODBC Destination").
  • P2 (Compatibility Targets):
    • Microsoft Access: (A classic, heavy user of ODBC for linked tables).
    • QGIS: (Popular open-source GIS tool that uses ODBC for database connections).
    • DBeaver (Pro): (Note: DBeaver is primarily a JDBC tool. However, the Pro version includes a native ODBC driver, making it a valid advanced test case).

---

7. Stretch Goal: Client-Side Query Acceleration via Embedded DuckDB

Goal: Provide a massive performance boost for iterative analysis and dashboarding by transparently answering queries using a local, on-disk cache powered by an embedded query engine.

Background: Many analytical workflows, especially in BI tools, are iterative. A user imports a large dataset and then runs multiple "local" queries (e.g., changing a filter, adding a GROUP BY) that all operate on that same base data. Today, every one of these changes triggers a new query to Trino.

This initiative proposes embedding an in-process analytical engine like DuckDB directly into the ODBC driver. When combined with the Trino Spooling Protocol (Item #3), the driver could intelligently persist spooled data (e.g., as Parquet files or a DuckDB file) on the client's machine.

When the application sends a new SQL query, the driver would first:

1. Intercept & Analyze: Parse the SQL to determine if it can be answered entirely by the locally cached data.
2. Check Freshness: Validate that the local data is not stale.
3. Delegate or Passthrough:
   • If (Query is local) AND (Data is fresh): Send the SQL query to the local, embedded DuckDB instance for a near-instantaneous response.
   • Else: Send the query to the Trino coordinator as normal.

Protecting Trino from AI Workloads: This local cache also acts as a critical "shock absorber" for generative AI and LLM-based queries. These new workloads (e.g., an AI agent translating natural language to SQL) can create unpredictable, high-frequency query bursts that can easily overwhelm a backend cluster. By caching the initial dataset locally, the DuckDB layer can absorb the entire "burst" of iterative follow-up questions from the AI, answering them on-device in milliseconds without ever touching the Trino cluster again. This protects the central Trino infrastructure, reduces operational costs, and prevents AI workloads from creating a "noisy neighbor" problem that impacts other users.

Iceberg-Specific Optimization: This feature is especially powerful for modern table formats. For Apache Iceberg, the "Freshness Check" could be an extremely lightweight metadata query to Trino to fetch the table's current manifest snapshot ID. The driver can compare this to the snapshot ID it stored when it cached the data.

• If IDs match: The local data is guaranteed to be 100% fresh.
• If IDs differ: The cache is invalidated, and the query is passed through to Trino.

Conceptual Steps:

• Add DuckDB as an embedded library dependency.
• Design a local cache persistence strategy (e.g., managing DuckDB files or Parquet files).
• Implement a query interception engine to analyze incoming SQL.
• Build the data freshness/validation logic, starting with the Iceberg snapshot ID check as a "golden path."
• Add DSN configuration to enable/disable this feature and manage the local cache size and location.

[rileymcdowell]

Nice looking list! I have an implementation of prepared statements in a local branch. All that's missing is a way to test it. Put my name down to contribute that piece.

[lozbrown]

This is awesome.

If like to suggest a minor enhancement to the meta cacheing. Some odbc implementations also frequently query server version "select version()"

Our query logs are cluttered with these, when it almost never changes, I'd like to see that cached too