Calcite Paper

I have been building a SQL engine and took the opportunity to reread the Calcite paper (I have to complain: documentation on Calcite really is sparse). Original paper. I previously wrote a somewhat superficial code walkthrough, SQL Parsing Framework - Calcite.

Introduction

As NoSQL databases grow in popularity, many OLAP developers face these problems:

• Supporting queries against different NoSQL databases requires implementing several similar sets of optimization logic.
• Cross-database queries lack a suitable abstraction and often require custom development.

Apache Calcite aims to solve these problems. In short, it is a customizable framework that uses SQL as its interface and integrates SQL parsing, optimization, and execution. The rough workflow is:

1. The user submits a SQL request through a JDBC client.
2. Calcite receives the SQL and uses SQLParser and Validator to parse and validate it, producing a SQL tree.
3. Expression rules supplied by the system traverse the SQL tree and create corresponding expressions at its nodes.
4. The optimizer optimizes the expression tree using costs and rules.
5. The expression tree becomes an execution plan, and the corresponding code runs to produce results.

Query Algebra

This section explains several key concepts in Calcite.

• Operators: Elements of relational algebra, represented in code by RelNode, such as Project, Filter, and Join.
• Traits: Describe an operator’s properties. For example, if a database’s scan already produces ordered data, TableScan can carry an ordering trait. The final execution plan can exploit this property: if a Sort follows TableScan, it can be skipped.
• Convention: A type of trait commonly used to identify different data-processing systems (you could also define your own trait). Apache Flink, for example, uses conventions to distinguish streaming and batch processing. The same SQL can then produce different executions according to the convention.

Adapters

Adapters are Calcite’s extension point for developers. Based on Calcite’s adapter interfaces, developers implement query logic for their own data sources. Calcite’s source includes a CSV example.

Query Processing And Optimization

Several extensible components are discussed:

• Planner Rule: Rules optimize both logical plans and execution plans.
• Metadata Provider: Supplies metadata about source tables for cost estimation.
• Planner Engines: Two planners, VolcanoPlanner and HepPlanner, are currently available as a basis for extensions.
• Materialized Views: Can multiple stored results become a view?

Extending Calcite

1. Support for semi-structured data.
2. Calcite also supports SQL parsing for streaming data, using the STREAM keyword to distinguish it, for example:

   1
   SELECT STREAM rowtime , productId , units FROM Orders
   2
   WHERE units > 25;

3. Support for geographic queries, such as latitude and longitude.