Calcite - Getting Started (from Sql to Resultset)

About

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

A running example is available at CalciteFrameworksTest.java

Steps

Dependency

compile("org.apache.calcite:calcite-core:1.22.0")

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()
                .setCaseSensitive(false)
                .build();

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()
    .parserConfig(insensitiveParser)
    .defaultSchema(hr)
    .build();

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 depts.name, count(emps.empid) from emps inner join depts on emps.deptno = depts.deptno group by depts.deptno, depts.name order by depts.name");

Output the SQL in another language

System.out.println(sqlNode.toSqlString(OracleSqlDialect.DEFAULT));
SELECT "DEPTS"."NAME", COUNT("EMPS"."EMPID")
FROM "EMPS"
INNER JOIN "DEPTS" ON "EMPS"."DEPTNO" = "DEPTS"."DEPTNO"
GROUP BY "DEPTS"."DEPTNO", "DEPTS"."NAME"
ORDER BY "DEPTS"."NAME"

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);
relNode.explain(relWriter);
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 = RelRunners.run(relNode);
ResultSet resultSet = run.executeQuery();

System.out.println("Result:");
while (resultSet.next()) {
	for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
		System.out.print(resultSet.getObject(i)+",");
	}
	System.out.println();
}
Marketing,2,
Sales,5,

Github

A running example is available at CalciteFrameworksTest.java


Powered by ComboStrap