A getting started page that shows a query planning process (ie from sql to resultset process)
A running example is available at CalciteFrameworksTest.java
compile("org.apache.calcite:calcite-core:1.22.0")
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
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
ReflectiveSchema schema = new ReflectiveSchema(new HrSchemaMin());
SchemaPlus hr = rootSchema.add("HR", schema);
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();
FrameworkConfig config = Frameworks.newConfigBuilder()
.parserConfig(insensitiveParser)
.defaultSchema(hr)
.build();
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 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 parse tree (ie table exists,…)
SqlNode sqlNodeValidated = planner.validate(sqlNode);
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 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,
A running example is available at CalciteFrameworksTest.java