Calcite - Getting Started (from Sql to Resultset)

Card Puncher Data Processing


A getting started page that shows a query planning process (ie from sql to resultset process)

A running example is available at




Create a schema class

Create a minimal reflective schema with data (The array are the tables)

public class HrSchemaMin {

    public final HrEmployee[] emps = {
            new HrEmployee(1, 10, "Bill", 10000, 1000),
            new HrEmployee(2, 20, "Eric", 8000, 500),
            new HrEmployee(3, 10, "Sebastian", 7000, null),
            new HrEmployee(4, 10, "Theodore", 11500, 250),
            new HrEmployee(5, 10, "Marjorie", 10000, 1000),
            new HrEmployee(6, 20, "Guy", 8000, 500),
            new HrEmployee(7, 10, "Dieudonne", 7000, null),

    public final HrDepartment[] depts = {
            new HrDepartment(10, "Sales", Arrays.asList(emps[0], emps[2]),null),
            new HrDepartment(20, "Marketing", ImmutableList.of(), null),

Build the schema

Build the schema

SchemaPlus rootSchema = Frameworks.createRootSchema(true);
ReflectiveSchema schema = new ReflectiveSchema(new HrSchemaMin());
SchemaPlus hr = rootSchema.add("HR", schema);

Create a parser configuration (insensitive)

Create a configuration for the parser that is not sensitive (ie EMPS is the same than emps)

SqlParser.Config insensitiveParser = SqlParser.configBuilder()

Create a framework configuration (global configuration object)

  • Create a framework configuration (based on the schema and the parser). The framework configuration is a bundle of all configuration.
FrameworkConfig config = Frameworks.newConfigBuilder()

Get the query planning facade object

Get the query planner facade to perform all query planning process (

This is not the optimizer planner but a facade to the query planning process


Planner planner = Frameworks.getPlanner(frameworkConfig)

Parse the query

Parse the query with the query planner

SqlNode sqlNode = planner.parse("select, count(emps.empid) from emps inner join depts on emps.deptno = depts.deptno group by depts.deptno, order by");

Output the SQL in another language


Validate the SQL tree

Validate the SQL parse tree (ie table exists,…)

SqlNode sqlNodeValidated = planner.validate(sqlNode);

Transform the SQL tree in a relation expression

Transform the SQL parse tree in relational algebra

RelRoot relRoot = planner.rel(sqlNodeValidated);

Print it (ie print the logical plan), ie explain it

RelNode relNode = relRoot.project();
final RelWriter relWriter = new RelWriterImpl(new PrintWriter(System.out), SqlExplainLevel.ALL_ATTRIBUTES, false);
LogicalSort(sort0=[$0], dir0=[ASC]): rowcount = 150.0, cumulative cost = {3668.75 rows, 11014.762352915506 cpu, 0.0 io}, id = 18
  LogicalProject(NAME=[$1], EXPR$1=[$2]): rowcount = 150.0, cumulative cost = {3518.75 rows, 5002.0 cpu, 0.0 io}, id = 17
    LogicalAggregate(group=[{0, 1}], EXPR$1=[COUNT()]): rowcount = 150.0, cumulative cost = {3368.75 rows, 4702.0 cpu, 0.0 io}, id = 15
      LogicalProject(deptno0=[$5], NAME=[$6], empid=[$0]): rowcount = 1500.0, cumulative cost = {3200.0 rows, 4702.0 cpu, 0.0 io}, id = 14
        LogicalJoin(condition=[=($1, $5)], joinType=[inner]): rowcount = 1500.0, cumulative cost = {1700.0 rows, 202.0 cpu, 0.0 io}, id = 12
          LogicalTableScan(table=[[HR, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 10
          LogicalTableScan(table=[[HR, depts]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 11

Run the query plan (relational expression)

Run it and shows the results.

The runner will transform the logical plan in a physical plan in order to run it

PreparedStatement run =;
ResultSet resultSet = run.executeQuery();

while ( {
	for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {


A running example is available at

Discover More
Card Puncher Data Processing
Calcite (Farrago, Optiq)

Calcite is a Java SQL Processing engine where the data storage is developed in plugin. Calcite is an open source cost based query optimizer and query execution framework. SQL Parser SQL Validation...
Card Puncher Data Processing
Calcite - Logical Plan (Logical algebra)

in Calcite A logical plan is a relational expression with only logical operator. Logical algebra has no implementation of the relational operator and therefore can't run. The logical plan is the first...
Card Puncher Data Processing
Calcite - Query Cost

in calcite. Calcite applies a Cost based optimizer by default that is called the Volcano planner. The cost is provided by the relational expression (relNode). See Cost is represented by org/apache/calcite/plan/RelOptCostRelOptCost...
Card Puncher Data Processing
Calcite - Query Planning Process (Sql Processing)

in Calcite The query planning process is the entire process that takes a SQL to a result. The process can be resumed as follow: Phase 1: The Sql statement (Query) is parsed to build a parse tree...
Card Puncher Data Processing
Calcite - Relational Expression (RelNode, Algebra)

Relational Algebra in Calcite A relational expression is represented by a tree of RelNode. A RelNode can be considered as the same logic than the Spark dataframe. TableScan Project Filter...
Card Puncher Data Processing
Calcite - Sql Parser

The ''calcite SQL Parser is a LL(k) parser that build a Sql tree (SqlNode) The org/apache/calcite/sql/parser/SqlParserparserConfig parameters control the parse process. For example: identifiers...
Card Puncher Data Processing
Calcite - Sql Validation

Validation of a sql tree (SqlNode) With the query planning utility as shown in the getting started With a org/apache/calcite/sql/validate/SqlValidatorUtilSqlValidatorUtil. In the example below,...

Share this page:
Follow us:
Task Runner