Understanding Apache Calcite: Architecture, SQL Parsing, Validation, and Query Optimization

1. Introduction

Calcite is an open‑source framework that supplies a standard SQL language, multiple query optimizations, and a plug‑in architecture for connecting heterogeneous data sources, allowing big‑data engines to offload parsing, validation, and optimization while keeping storage and execution logic separate.

2. Core Architecture

The central structure consists of a SQL parser, a validator, an optimizer, and a catalog. The parser converts SQL text into an abstract syntax tree (AST). The catalog stores metadata (schemas, tables, types). The optimizer generates a relational expression tree and applies rule‑based transformations. The adaptor layer (not covered here) connects external storage engines.

3. SQL Parser

The parser tokenizes the input and builds an AST where each node is a SqlNode. For example, the statement

INSERT INTO sink_table SELECT s.id, name, age FROM source_table s JOIN dim_table d ON s.id=d.id WHERE s.id>1;

is parsed into a hierarchy of nodes such as SqlInsert, SqlSelect, SqlJoin, SqlIdentifier, and SqlBasicCall. The article details the fields of SqlInsert (targetTable, source, columnList) and the key members of SqlSelect (selectList, from, where), as well as the structure of SqlJoin and the role of SqlIdentifier and SqlBasicCall in representing identifiers and function calls.

4. Catalog

The catalog holds all SQL metadata and namespaces. Its main structures are:

RelDataTypeField – name and type of a single column.

RelDataType – a collection of fields representing a row or scalar result.

Table – metadata for a complete table.

Schema – a container for tables and types.

This hierarchy enables Calcite to resolve names and types during validation.

5. SQL Validator

The validator checks each SqlNode against the catalog, ensuring table existence, column uniqueness, type compatibility for INSERT, etc. Core classes include SqlValidatorNamespace, SqlValidatorScope, and the implementation SqlValidatorImpl, which maintains maps from nodes to scopes and namespaces. A snippet of the implementation shows the internal maps for scopes (where, group‑by, select, order, cursor) and the catalog reader used to access metadata.

6. Query Optimizer

The optimizer first converts the AST to a logical plan of RelNode objects (via SqlToRelConverter) and then applies a set of RelOptRule transformations such as field pruning, projection merging, sub‑query to join conversion, join reordering, and push‑down of projections and filters. Traits like Convention describe the execution engine’s calling convention, and converters adapt plans between different conventions.

7. Application Scenarios

Calcite’s plug‑in design allows many extensions:

Custom SQL syntax (e.g., adding CREATE TABLE or CREATE VIEW for Flink).

Extended metadata handling by implementing custom Schema and Table interfaces.

New type systems via RelDataTypeFactory extensions.

User‑defined optimization rules registered through HepProgramBuilder.

These extensions enable developers to build tailored SQL engines on top of Calcite for various big‑data platforms.