how to build sql from RelBuild without schema info?

Question

i want to generate sql use calcite. like this

org.apache.calcite.rel.rel2sql.RelToSqlConverterTest#testAntiJoin

final FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
       .parserConfig(SqlParser.Config.DEFAULT)
//       .defaultSchema(schema)
       .build();

final RelBuilder builder = RelBuilder.create(frameworkConfig);

final RelBuilder builder = relBuilder();
final RelNode root = builder
       .scan("DEPT")
        .scan("EMP")
        .join(
            JoinRelType.ANTI, builder.equals(
              builder.field(2, 1, "DEPTNO"),
              builder.field(2, 0, "DEPTNO")))
        .project(builder.field("DEPTNO"))
        .build();

but if i don't set the schema, the exception table not found will be throw. is there any way to generate sql without schema info.

the aim is generate sql. just generate sql.

---

---

reply for first answer. because comment character length limit.

My scenario is Business Intelligence. DataSource can be many, such as Hive, ClickHouse, and so on. And there are many tables. I also need to dynamically delete or add datasource. So I don't think it's appropriate for Calcite to be aware of all the data sources. I have two more questions:

1. How to create 'free-standing' table objects as you said
2. Check whether SqlNode can be used to do this. for example:

        SqlIdentifier from = new SqlIdentifier("testTable", SqlParserPos.QUOTED_ZERO);
        SqlNode[] nodes = new SqlNode[2];
        nodes[0] = new SqlIdentifier("a", SqlParserPos.QUOTED_ZERO);
        nodes[1] = SqlLiteral.createExactNumeric("1", SqlParserPos.QUOTED_ZERO);
        SqlNode where = new SqlBasicCall(SqlStdOperatorTable.EQUALS, nodes, SqlParserPos.QUOTED_ZERO);
        SqlIdentifier selectNode = new SqlIdentifier("a", SqlParserPos.QUOTED_ZERO);
        SqlSelect select = new SqlSelect(SqlParserPos.QUOTED_ZERO, SqlNodeList.EMPTY,
                new SqlNodeList(Arrays.asList(selectNode), SqlParserPos.QUOTED_ZERO),
                from,
                where,
                null,
                null,
                null,
                null,
                null,
                null,
                null);
        SqlString sqlString = select.toSqlString(CalciteSqlDialect.DEFAULT);
        System.out.println(sqlString.getSql());

Answer 1

Only one method in RelBuilder uses a RelOptSchema: scan(String...) (and its variant Scan(Iterable<String>)). Which makes sense when you consider that the purpose of RelOptSchema is as a directory service, converting a table name (or table path, consisting of a table name qualified with catalog and/or schema names) into a RelOptTable object.

If you have 'free-standing' table objects that are not accessed via a namespace then you can create TableScan relational expressions directly and then call RelBuilder.push(RelNode) to add them to the stack. Since you never call RelBuilder.scan you can create RelBuilder with a null RelOptSchema.

But in your case, it looks as if you don't have free-standing table objects. That's a problem for Calcite, because it needs to know that your "EMP" table has a field called "DEPTNO" and it has type INTEGER.

So I suggest that you create a 'virtual' schema that contains type information but is not necessarily backed by real tables. The MockCatalogReader class, used in several of Calcite's tests, is a good example to follow.