RFC: Pluggable SQL Dialect Framework

Pluggable SQL Dialect Framework for OpenSearch

Goal

Enable OpenSearch to natively accept and execute SQL queries written in third-party SQL dialects
(starting with ClickHouse SQL) through a pluggable dialect translation framework, eliminating the
need for users to rewrite queries when migrating analytics workloads to OpenSearch.

---

Motivation & Background

Organizations increasingly adopt OpenSearch as a unified platform for non-transactional real-time
analytics, log analysis, and observability. Many of these organizations are migrating from
specialized databases such as ClickHouse, and they have significant investments in existing SQL
queries embedded in dashboarding systems (Grafana, Superset, Metabase), alerting pipelines, and
automated reporting tools.

Today, migrating from a database like ClickHouse to OpenSearch requires manually rewriting every
SQL query to conform to OpenSearch SQL syntax. This is a costly, error-prone, and time-consuming
process that slows adoption and creates risk during migration. For organizations with hundreds of
Grafana dashboards built on ClickHouse, the rewriting effort alone can take weeks or months and
block migration projects entirely.

By building a pluggable dialect framework directly into the OpenSearch SQL plugin, we can:

• Drastically reduce migration effort — Users point their dashboards at OpenSearch and set a
  dialect parameter; existing queries work without modification.
• Lower the barrier to OpenSearch adoption — Teams evaluating OpenSearch can run
  proof-of-concept migrations in hours instead of weeks.
• Future-proof the platform — The pluggable architecture allows the community to contribute
  additional dialect support (e.g., MySQL, PostgreSQL, Presto/Trino) over time without modifying
  core query routing logic.

---

Problem Statement

There is no mechanism in OpenSearch SQL to accept queries written in non-OpenSearch SQL dialects.
Users migrating from other databases must manually translate every query — including
dialect-specific functions, type constructors, quoting conventions, and clauses — into
OpenSearch-compatible SQL. This manual translation is the primary blocker for dashboard migration
use cases, particularly for Grafana users moving from ClickHouse to OpenSearch.

---

Target Users & Stakeholders

Persona	Need
Analytics / Platform Engineer	Migrate Grafana/Superset dashboards from ClickHouse to OpenSearch without rewriting queries.
DevOps / SRE	Consolidate observability data into OpenSearch while preserving existing ClickHouse-based monitoring dashboards.
OpenSearch Plugin Developer	Extend the framework with new dialect implementations for additional source databases.
OpenSearch Maintainer	Ensure the dialect framework integrates cleanly with the existing Calcite-based query engine without regressions.

---

Scope

In Scope

• A pluggable dialect registration and routing framework within the OpenSearch SQL plugin.
• A query preprocessing layer that strips or transforms dialect-specific clauses before Calcite
  parsing.
• A per-dialect function translation layer that maps dialect functions to Calcite/OpenSearch
  equivalents.
• A per-dialect Calcite SqlDialect subclass for RelNode-to-SQL unparsing.
• A complete ClickHouse dialect implementation as the first built-in dialect, covering the most
  common functions and syntax used by Grafana dashboards.
• Integration with the existing Calcite query pipeline (parsing, validation, planning, execution).
• Compatibility with standard dashboarding system SQL datasource plugins (Grafana, Superset,
  Metabase).

Out of Scope

• Full ClickHouse SQL language compatibility (e.g., DDL, DML, INSERT, ALTER, CREATE, lambda
  expressions, array joins, or ClickHouse-specific table engines).
• Write-path operations — this framework is read-only (SELECT queries only).
• Cross-cluster or federated query execution against a live ClickHouse instance.
• Automatic schema migration or index mapping from ClickHouse table schemas to OpenSearch index
  mappings.
• Dialect implementations beyond ClickHouse (future work; the framework supports them, but only
  ClickHouse ships in this iteration).
• Changes to the PPL (Piped Processing Language) query path.

---

Dependencies & Assumptions

• Calcite Engine Enabled — The dialect framework requires the Calcite-based query engine
  (plugins.sql.calcite.engine.enabled = true). It does not fall back to the legacy SQL engine.
• Existing Calcite Infrastructure — The framework builds on top of the existing
  CalcitePlanContext, UnifiedQueryCompiler, and UnifiedQueryTranspiler components already
  present in the OpenSearch SQL plugin.
• OpenSearch Index Pre-existence — Target indices must already exist in OpenSearch with
  appropriate mappings. The dialect framework does not create indices or infer schemas from source
  databases.
• Grafana ClickHouse Plugin Query Patterns — The ClickHouse dialect implementation prioritizes
  function and syntax coverage based on query patterns generated by the Grafana ClickHouse
  datasource plugin (time-series panels, table panels, variable queries).

---

Success Metrics

Metric	Target
ClickHouse function coverage	≥ 90% of functions used in standard Grafana ClickHouse datasource plugin query templates are translated correctly.
Query compatibility rate	≥ 85% of ClickHouse SELECT queries from a representative Grafana dashboard suite execute successfully against OpenSearch without manual modification.
Latency overhead	Dialect preprocessing + function translation adds < 10 ms p99 latency overhead compared to an equivalent native OpenSearch SQL query.
Zero regression	All existing OpenSearch SQL and PPL integration tests continue to pass with the dialect framework enabled.
Extensibility validation	A second dialect (stub/test dialect) can be registered and routed to without modifying any code outside the new dialect's plugin module.

---

Glossary

Term	Definition
Dialect_Handler	The component that accepts SQL queries for a registered dialect via the REST API, orchestrates preprocessing, parsing, function translation, and execution against OpenSearch.
Dialect_Registry	The registry that holds all registered SQL dialect implementations and resolves a dialect by name from the incoming request parameter.
Function_Registry	A per-dialect registry that maps dialect-specific function names to their Calcite/OpenSearch equivalents.
Query_Preprocessor	A per-dialect component that transforms or strips dialect-specific clauses from the raw query string before Calcite parsing.
OpenSearch_Calcite_Engine	The existing Calcite-based query engine that builds RelNode logical plans and executes them against OpenSearch.
Function_Translator	A per-dialect component that translates dialect-specific function calls in the Calcite plan to OpenSearch-compatible equivalents.
Dialect_Plugin	A self-contained dialect implementation that provides a Query_Preprocessor, Function_Registry, Function_Translator, and Calcite SqlDialect subclass for a specific SQL dialect.

---

Requirements

Requirement 1 — Pluggable SQL Dialect Endpoint

User Story: As a user, I want to submit SQL queries in a third-party SQL dialect to
OpenSearch via the existing REST endpoint, so that I can query OpenSearch without rewriting
queries from my source database.

---

Requirement 2 — Dialect Registration and Discovery

User Story: As a developer, I want to register new SQL dialect implementations without
modifying existing query routing code, so that the system is extensible to additional dialects
beyond the initial implementation.

---

Requirement 3 — Query Preprocessing

User Story: As a user, I want dialect-specific clauses that OpenSearch does not support to be
handled gracefully, so that my queries do not fail due to unsupported syntax.

---

Requirement 4 — SQL Parsing with Dialect Configuration

User Story: As a user, I want my dialect-specific SQL queries to be parsed correctly, so that
SELECT statements with dialect-specific syntax are understood by OpenSearch.

---

Requirement 5 — Dialect Function Translation

User Story: As a user, I want dialect-specific functions in my queries to be translated to
OpenSearch equivalents, so that my queries produce correct results.

Acceptance Criteria

---

Requirement 6 — Dialect-Specific SqlDialect for RelNode-to-SQL Unparsing

User Story: As a developer, I want each dialect to provide a Calcite SqlDialect subclass
for unparsing RelNode plans back into dialect-compatible SQL, so that the transpiler pipeline
supports multiple target dialects.

---

Requirement 7 — Query Pipeline Integration

User Story: As a developer, I want dialect queries to integrate with the existing Calcite
query pipeline, so that they flow through parsing, planning, and execution using the established
architecture.

---

Requirement 8 — Error Handling and Diagnostics

User Story: As a user, I want clear error messages when my dialect queries fail, so that I
can diagnose and fix issues.

---

Requirement 9 — ClickHouse Dialect Implementation

User Story: As a Grafana user, I want to submit ClickHouse SQL queries to OpenSearch, so that
my existing Grafana dashboards work without modification after migrating from ClickHouse.

---

Requirement 10 — Dashboarding System Compatibility

User Story: As a dashboarding system user (e.g., Grafana, Superset, Metabase), I want my
time-series and analytics queries to work against OpenSearch via the dialect endpoint, so that I
can visualize data without rewriting queries after migrating to OpenSearch.

---

Architecture

flowchart TD
    A["REST Request: /_plugins/_sql?dialect=clickhouse"] --> B["RestSqlAction"]
    B --> C{"dialect param present?"}
    C -->|No| D["Existing SQL / PPL Handler"]
    C -->|Yes| E["DialectRegistry.resolve()"]
    E --> F["DialectPlugin"]
    F --> G["QueryPreprocessor.preprocess()"]
    G --> H["Calcite SqlParser.parseQuery()"]
    H --> I["SqlNode AST"]
    I --> J["Calcite Validator + DialectOperatorTable"]
    J --> K["Calcite SqlToRelConverter"]
    K --> L["RelNode Logical Plan"]
    L --> M["OpenSearch Calcite Execution Engine"]
    M --> N["JSON Response"]

    subgraph DialectPlugin ["Dialect Plugin"]
        G
        O["FunctionRegistry"]
        P["DialectOperatorTable"]
        Q["SqlDialect subclass"]
    end

Start at RestSqlAction and read the existing CalcitePlanContext, UnifiedQueryCompiler, and UnifiedQueryTranspiler components named in the proposal. Trace how a request reaches parsing, planning, and execution, then determine how dialect registration, preprocessing, function translation, and ClickHouse support would fit without changing the existing SQL and PPL paths.