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