How BodoSQL Uses and Extends Apache Calcite: From SQL Text to Optimized Execution Plans — bodo.ai

‍In a previous post, we introduced BodoSQL’s new C++ backend and explained how it brings interactivity to the high performance analytics engine. But before a SQL query can be executed by the backend, it must first be parsed, validated, and converted to an optimized physical plan. In BodoSQL, a majority of this work is done by a customized version of the Apache Calcite framework. In this post, we explore how BodoSQL uses Calcite, explain the role of each major component and how we customized it for our use case, and finally discuss how we keep those customizations up-to-date as the upstream project evolves.
Apache Calcite is an open-source Java framework that provides many of the core components needed to process and optimize SQL queries, including:
For this post, we will be focusing on the SQL parser, validator, relational algebra converter, and query optimizer. These components form the core pipeline that takes BodoSQL from raw SQL text to an executable query plan. Along the way, the query moves through three main representations:
We’ll follow that pipeline from the initial parsing and validation stages through relational plan generation and optimization, highlighting where BodoSQL extends Calcite along the way.
Before query optimization can occur, we need to convert the raw SQL query text to a logical query plan representation. This transformation is done in roughly three stages: SQL Parsing, SQL Validation, and SQL to Relational Algebra Conversion.
SQL Parsing: Calcite’s SQL parser transforms SQL text into an abstract syntax tree (AST). The parser is programmatically generated from a grammar specification using JavaCC. BodoSQL customizes this grammar to support Snowflake-style SQL syntax. When adding new syntax, such as DDL statements, we use Calcite’s built-in parser extension points wherever possible. However, some syntax requires changing how Calcite parses existing SQL constructs, which means modifying the core grammar directly. These changes are less ideal because they must be maintained and reconciled with changes from upstream Calcite. We’ll return to how we manage this later in the post.
SQL Validation: The SQL validator checks that a parsed query is semantically valid given the available schemas and the rules of the target SQL dialect. Because Snowflake semantics sometimes differ from Calcite’s defaults, BodoSQL extends its validator to support Snowflake-compatible semantics for functions like HASH. Where possible, we implement these customizations by extending Calcite’s SqlValidator with our own subclass; for behavior that is not exposed through Calcite’s extension points, we maintain targeted changes to the internal validator implementation. Validation also requires metadata about the tables being queried. To make that metadata available across BodoSQL’s supported data sources, we extend Calcite’s Schema interface. BodoSQL has two basic schema types: LocalSchema, for tables that exist outside a catalog, such as DataFrames and Parquet files, and CatalogSchema, which retrieves table metadata from external catalogs such as Snowflake and Iceberg.
SQL to Relational Algebra Conversion: The final step before query optimization converts the validated SQL AST into a tree of relational operators. BodoSQL extends Calcite’s SqlToRelConverter through a subclass to generate custom relational nodes for constructs such as CREATE TABLE. As in earlier stages, some behavior cannot be customized cleanly through extension points, so we also maintain targeted changes to Calcite’s internal conversion logic.
To make this pipeline concrete, let’s follow a simple query through parsing, validation and relational algebra conversion:
After the parsing stage, the SQL query is represented by an AST of SQLNode:
Next, the validator resolves the column and table references using the provided schema information:
Finally, the AST is translated into a relational tree consisting of RelNodes:
At this point, BodoSQL has converted the query into a logical relational plan, but that plan describes what the query computes rather than the most efficient way to execute it. Next, it’s the optimizer's job to determine how that computation should be performed efficiently and transform the logical plan into a physical plan that BodoSQL’s execution backend can run. The optimizer pipeline consists of several stages, each of which processes a query plan before passing it to the next stage. BodoSQL configures both the stages and the order in which they run, with many stages using one of Calcite’s planners for transforming patterns within the query plan. BodoSQL uses Calcite's two primary planning strategies at different points in this pipeline:
‍HEP (Rule-based Planner): HEP-based stages accept a set of rules that match specific patterns within a plan and define how those patterns should be transformed into more efficient structures. These rules are applied iteratively until the plan converges or a maximum number of iterations is reached.
As an example, let’s look at how a query with multiple joins and a filter transforms during the filter pushdown pass using HEP:
Prior to this pass, the simplified plan looks like:
First, we can use a “push filter below join” rule, which pushes a filter that only depends on one side of the join into that side. Since C_MKTSEGMENT is coming from the Customers table, we can push it below the outermost join:
After we’ve applied this rule, we can apply it again, this time on the innermost join:
Finally, assuming these tables are coming from Snowflake, we can push this filter into the IO node by converting it to a SnowflakeFilter using another rule. The final plan after filter pushdown becomes:
Once we can no longer apply rules or further improve the query plan, the stage ends.
Volcano (Cost-based Planner): Similar to HEP, the Volcano-based stage accepts a set of transformation rules. However, rather than simply applying rules to the current plan, Volcano uses them to explore a space of equivalent p