CalciteQuickStartTest: parse -> validate -semanticAnalyze -> optimization

java

/**
 * calcite-core version: 1.32.9
 */
public class CalciteQuickStartTest {

    private static final String SQL = "select u.id as user_id, u.name as user_name, j.company as user_company, u.age as user_age\n" +
            "from users u join jobs j on u.name = j.name\n" +
            "where u.age > 30 and j.id > 10\n" +
            "order by user_id";

    @Test
    public void test() throws SqlParseException {

        RelDataTypeFactory factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
        final RexBuilder rexBuilder = new RexBuilder(factory);
        RelOptPlanner relOptPlanner = new HepPlanner(new HepProgramBuilder().addRuleInstance(CoreRules.FILTER_INTO_JOIN).build());
        final RelOptCluster cluster = RelOptCluster.create(relOptPlanner, rexBuilder);

        // Phase 1: Parsing SQL, SQL -> SqlNode
        SqlNode sqlNode = parseSql(SQL);

        // Phase 2: validate SQL, SqlNode -> SqlNode
        CalciteCatalogReader calciteCatalogReader = initCatalogReader(factory);
        SqlNode validatedSqlNode = sqlValidate(calciteCatalogReader, factory, sqlNode);
        System.out.println("Validated sqlNode done!");

        // 语义分析： SqlNode --> RelNode
        RelNode relNode = semanticAnalysis(validatedSqlNode, factory, calciteCatalogReader, cluster);

        // 优化
        optimization(relNode, cluster);
    }

    private SqlNode parseSql(String sql) throws SqlParseException {
        SqlParser sqlParser = SqlParser.create(sql, SqlParser.Config.DEFAULT);
        SqlNode sqlNode = sqlParser.parseStmt();
        System.out.printf("Parse sql done, parse from sql %s, \n parsed sqlNode\n%s%n", sql, sqlNode);
        return sqlNode;
    }

    private SqlNode sqlValidate(CalciteCatalogReader calciteCatalogReader, RelDataTypeFactory factory, SqlNode sqlNode) {
        // 校验，包括表名，字段名，函数名，字段类型的校验
        SqlValidator validator = SqlValidatorUtil.newValidator(
                SqlStdOperatorTable.instance(), calciteCatalogReader,
                factory, SqlValidator.Config.DEFAULT);
        return validator.validate(sqlNode);
    }

    private RelNode semanticAnalysis(SqlNode sqlNode, RelDataTypeFactory factory, CalciteCatalogReader calciteCatalogReader, RelOptCluster cluster) {
        final SqlToRelConverter.Config config = SqlToRelConverter.config()
                .withTrimUnusedFields(true)
                .withExpand(false);
        final SqlToRelConverter sqlToRelConverter = new SqlToRelConverter(
                null,
                SqlValidatorUtil.newValidator(
                        SqlStdOperatorTable.instance(),
                        calciteCatalogReader,
                        factory,
                        SqlValidator.Config.DEFAULT),
                calciteCatalogReader,
                cluster,
                StandardConvertletTable.INSTANCE,
                config);

        RelRoot relRoot = sqlToRelConverter.convertQuery(sqlNode, true, true);
        RelNode rel = relRoot.rel;
        System.out.println(RelOptUtil.toString(relRoot.rel));
        return rel;
    }

    private RelNode optimization(RelNode relNode, RelOptCluster cluster) {
        RelOptPlanner planner = cluster.getPlanner();
        planner.setRoot(relNode);
        RelNode bestExp = planner.findBestExp();
        System.out.println("Optimized");
        System.out.println(RelOptUtil.toString(bestExp));
        return bestExp;
    }

    private CalciteCatalogReader initCatalogReader(RelDataTypeFactory factory) {
        // 注册schema
        SchemaPlus rootSchema = Frameworks.createRootSchema(true);
        rootSchema.add("USERS", new AbstractTable() {
            @Override
            public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                RelDataTypeFactory.Builder builder = typeFactory.builder();
                builder.add("ID", SqlTypeName.INTEGER);
                builder.add("NAME", SqlTypeName.VARCHAR);
                builder.add("AGE", SqlTypeName.INTEGER);
                return builder.build();
            }
        });

        rootSchema.add("JOBS", new AbstractTable() {
            @Override
            public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                RelDataTypeFactory.Builder builder = typeFactory.builder();
                builder.add("ID", SqlTypeName.INTEGER);
                builder.add("NAME", SqlTypeName.VARCHAR);
                builder.add("COMPANY", SqlTypeName.VARCHAR);
                return builder.build();
            }
        });

        return new CalciteCatalogReader(
                CalciteSchema.from(rootSchema), CalciteSchema.from(rootSchema).path(null), factory, new CalciteConnectionConfigImpl(new Properties())
        );
    }
}