原文:http://calcite.apache.org/docs/algebra.html
每一个查询都是关系操作的组成的一棵树,由SQL到关系代数,或者可以直接构建一个树,Calcite不断的用planner rules来优化查询,构建最优树,同时 planning process是可扩展的,可添加自定义的relational operators, planner rules, cost model, and statistics。
Algebra builder
构建关系表达式的最简单方式:RelBuilder
TableScan
final FrameworkConfig config; final RelBuilder builder = RelBuilder.create(config); final RelNode node = builder .scan("EMP") .build(); System.out.println(RelOptUtil.toString(node)); |
其他例子:RelBuilderExample.java.
以上代码输出:
LogicalTableScan(table=[[scott, EMP]])
相当于:
SELECT *
FROM scott.EMP;
Adding a Project
实现
SELECT ename, deptno
FROM scott.EMP;
调用project方法:
final RelNode node = builder
.scan("EMP")
.project(builder.field("DEPTNO"), builder.field("ENAME"))
.build();
System.out.println(RelOptUtil.toString(node));
输出:
LogicalProject(DEPTNO=[$7], ENAME=[$1])
LogicalTableScan(table=[[scott, EMP]])
调用builder.field构建简单的expressions,从input relational expression中返回fields,Calcite将他们转换成$7 and $1.
Adding a Filter and Aggregate
SELECT deptno, count(*) AS c, sum(sal) AS s FROM emp GROUP BY deptno HAVING count(*) > 10 实现方式: final RelNode node = builder .scan("EMP") .aggregate(builder.groupKey("DEPTNO"), builder.count(false, "C"), builder.sum(false, "S", builder.field("SAL"))) .filter( builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("C"), builder.literal(10))) .build(); System.out.println(RelOptUtil.toString(node)); 输出: LogicalFilter(condition=[>($1, 10)]) LogicalAggregate(group=[{7}], C=[COUNT()], S=[SUM($5)]) LogicalTableScan(table=[[scott, EMP]]) |
Push and pop
builder利用stack存储relational expression,每一个步的expression作为下一步的输入,迭代构建。很多时候我们只能使用build()方法得到最后一个relational expression,也就是tree的根节点
有时当stack有很深的嵌套,为了让事件更简单,我们可以从stack中将expressions remove出来,例如下面我们构建一个复杂join:
. join / \ join join / \ / \ CUSTOMERS ORDERS LINE_ITEMS PRODUCTS |
分3个stage构建,以left和right变量存储中间结果,利用push() 将他们放回stack,构建最终的Join:
Field names and ordinals
可以用name或者序号来标识一个field
其中序号Ordinals是以0为基础的,name全局唯一,
一些relational expression方法提供一些额外的方法管理field names:
(1)project 允许你用alias(expr, fieldName),
(2)values(String[] fieldNames, Object... values) 接收一个field names的数组,如果array中有null,自动生成一个全局唯一的名字
同时field name一旦生成是不可变的,但是一个关系表达式出入一些重写的rules如(RelOptRule),结果的expression可能不像原来输入的了,这时最好还是和输入的原来的名字保持一致
当我们构建一个多输入的表达式时,需要构建field references,例如JOIN
假设我们构建一个join on EMP和DEPT,EMP有8 fields [EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO] ,DEPT有3 fields [DEPTNO, DNAME, LOC],Calcite内部统一标识成11个fields,
左边从#0开始,右边从#8开始
但是通过builder API,我们明确标识输入的field,标识“SAL”,内部的#5, builder.field(2, 0, "SAL"),builder.field(2, "EMP", "SAL"), or builder.field(2, 0, 5),这标识 “the field #5 of input #0 of two inputs”
同理 reference “DNAME”, internal field #9 (8 + 1), write builder.field(2, 1, "DNAME"), builder.field(2, "DEPT", "DNAME"), or builder.field(2, 1, 1).
API summary Permalink
http://calcite.apache.org/docs/algebra.html#api-summary