使用 Calcite Algebra 构建查询
Algebra
Algebra：（名字）代数、代数学，官方介绍 Relational Algebra 是 Calcite 的核心，每个 SQL 查询都可以作为一个关系运算符树。可以直接将 SQL 转换为 Algebra 或者直接构建一个查询树。
意思就是 Calcite 提供一系列对象 如：RelNode，来转换或者构建查询 SQL。
JDBC 使用
目标：使用对象构建查询 SQL，打印查询语句，并执行输出结果。
准备工作
mysql 数据库一张如下表：
CREATE TABLE `human` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`likes` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;准备一个 maven 工程，添加下面的依赖：
<dependencies>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.verdictdb/verdictdb-core 格式化输出查询结果-->
<dependency>
<groupId>org.verdictdb</groupId>
<artifactId>verdictdb-core</artifactId>
<version>0.5.8</version>
<exclusions>
<exclusion>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</exclusion>
<exclusion>
<groupId>org.codehaus.janino</groupId>
<artifactId>commons-compiler</artifactId>
</exclusion>
<exclusion>
<groupId>org.codehaus.janino</groupId>
<artifactId>janino</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.calcite/calcite-core -->
<dependency>
<groupId>org.apache.calcite</groupId>
<artifactId>calcite-core</artifactId>
<version>1.31.0</version>
</dependency>
</dependencies>Java 代码
定义数据源
// Build our connection
Connection connection = DriverManager.getConnection("jdbc:calcite:");
// Unwrap our connection using the CalciteConnection
CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
// Get a pointer to our root schema for our Calcite Connection
SchemaPlus rootSchema = calciteConnection.getRootSchema();
// Instantiate a data source, this can be autowired in using Spring as well
DataSource mysqlDataSource = JdbcSchema.dataSource(
"jdbc:mysql://localhost:3306/local_test",
"com.mysql.cj.jdbc.Driver", // Change this if you want to use something like MySQL, Oracle, etc.
"root", // username
"123456" // password
);
// Attach our MySQL Jdbc Datasource to our Root Schema
rootSchema.add(MYSQL_SCHEMA, JdbcSchema.create(rootSchema, MYSQL_SCHEMA, mysqlDataSource, null, null));定义 builder
// Build a framework config to attach to our Calcite Planners and Optimizers
FrameworkConfig config = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema)
.build();
RelBuilder rb = RelBuilder.create(config);构建查询
RelNode node = rb
// First parameter is the Schema, the second is the table name
// .scan("PUBLIC", "TABLE_NAME_IN_POSTGRES")
.scan("mysql", "human")
// If you want to select from more than one table, you can do so by adding a second scan parameter
.filter(
rb.equals(rb.field("age"), rb.literal("18"))
)
// These are the fields you want to return from your query
.project(
rb.field("id"),
rb.field("name"),
rb.field("age")
)
.build();
用下面的方法将 node 对象转换为 SQL 语句
/**
* Converts a relational expression to SQL in a given dialect.
*/
private static String toSql(RelNode root, SqlDialect dialect) {
return toSql(root, dialect, c ->
c.withAlwaysUseParentheses(false)
.withSelectListItemsOnSeparateLines(false)
.withUpdateSetListNewline(false)
.withIndentation(0));
}
/**
* Converts a relational expression to SQL in a given dialect
* and with a particular writer configuration.
*/
private static String toSql(RelNode root, SqlDialect dialect,
UnaryOperator<SqlWriterConfig> transform) {
final RelToSqlConverter converter = new RelToSqlConverter(dialect);
final SqlNode sqlNode = converter.visitRoot(root).asStatement();
return sqlNode.toSqlString(c -> transform.apply(c.withDialect(dialect)))
.getSql();
}
protected static String writeSql(SqlPrettyWriter sqlWriter, RelToSqlConverter relToSql, RelNode query) {
sqlWriter.reset();
SqlSelect select = relToSql.visit(query).asSelect();
return sqlWriter.format(select);
}String sql = toSql(node, SqlDialect.DatabaseProduct.MYSQL.getDialect());
System.out.println(sql);输出如下：
SELECT `id`, `name`, `age`
FROM `human`
WHERE `age` = '18'使用 planner 执行查询并输出：
HepProgram program = HepProgram.builder().build();
HepPlanner planner = new HepPlanner(program);
planner.setRoot(node);
// System.out.println(planner);
RelNode optimizedNode = planner.findBestExp();
final RelShuttle shuttle = new RelHomogeneousShuttle() {
@Override
public RelNode visit(TableScan scan) {
final RelOptTable table = scan.getTable();
if (scan instanceof LogicalTableScan && Bindables.BindableTableScan.canHandle(table)) {
return Bindables.BindableTableScan.create(scan.getCluster(), table);
}
return super.visit(scan);
}
};
optimizedNode = optimizedNode.accept(shuttle);
final RelRunner runner = connection.unwrap(RelRunner.class);
try (PreparedStatement ps = runner.prepareStatement(optimizedNode)) {
// System.out.println(ps);
ps.execute();
try (ResultSet resultSet = ps.getResultSet()) {
DBTablePrinter.printResultSet(resultSet);
}
}结果：
+------+----------+-----+
| id | name | age |
+------+----------+-----+
| 208 | chandler | 18 |
+------+----------+-----+
| 209 | jack | 18 |
+------+----------+-----+
| 210 | moon | 18 |
+------+----------+-----+
| 211 | luna | 18 |
+------+----------+-----+另一个计数的例子：
final RelNode node2 = rb
.scan("mysql", "human")
.aggregate(rb.groupKey("likes"),
rb.count(false, "C"))
.build();生成的 SQL
SELECT `likes`, COUNT(*) AS `C`
FROM `human`
GROUP BY `likes`用法非常多，用这个功能可以弄出一个非常灵活的 OLAP 系统了。
参考：
https://codetinkering.com/apache-calcite-tutorial/
https://calcite.apache.org/docs/algebra.html