Calcite - Getting Started (from Sql to Resultset)

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 (

)

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");

Print the SQL in a SQL dialect

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 the relational expression (ie logical plan)

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.

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,