Apache Calcite | HSQLDB - Table Not Found Exception

Question

I am trying to learn Apache Calcite by following the RelBuilderExample with the storage layer being HSQLDB. Unfortunately, I keep getting "Table Not Found exception" when i call builder.scan(tableName) API of Apache Calcite. When I query the data in HSQL directly using ResultSet rs = statement.executeQuery("SELECT * from file"); then i am able to retrieve the data. Here is the relevant code:

//I create an instance of RelBuilder using the Config defined below
RelBuilder builder = RelBuilder.create(config().build());
//This line throws me exception: org.apache.calcite.runtime.CalciteException: Table 'file' not found

            builder = builder.scan("file");

/**
  Building the configuration backed by HSQLDB
*/
public static Frameworks.ConfigBuilder config() throws Exception{

            //Getting the ConnectionSpec for the in memory HSQLDB
            //FileHSQLDB.URI = "jdbc:hsqldb:mem:intel"
            //FileHSQLDB.USER = "user"
            //FileHSQLDB.PASSWORD = "password"
            final ConnectionSpec cs = new ConnectionSpec(FileHSQLDB.URI, FileHSQLDB.USER, FileHSQLDB.PASSWORD, "org.hsqldb.jdbcDriver", "intel");

            //cs.url = "jdbc:hsqldb:mem:intel"
            //cs.driver = "org.hsqldb.jdbcDriver"
            //cs.username = "user"
            //cs.password = "password"
            DataSource dataSource = JdbcSchema.dataSource(cs.url, cs.driver, cs.username, cs.password);
            Connection connection = dataSource.getConnection();
            Statement statement = connection.createStatement();
            //This returns me 3 results
            ResultSet rs = statement.executeQuery("SELECT * from file");
            while(rs.next()) {
                String id = rs.getString("file_id");
                System.out.println(id);
            }
            // Next I create the rootSchema
            SchemaPlus rootSchema = Frameworks.createRootSchema(true);

            //I suspect that there is some issue in the below line. I think I 
            //am not using Apache Calcite APIs properly, but not sure what I 
            //am doing wrong.
            rootSchema.add("intel", JdbcSchema.create(rootSchema, "intel", dataSource, cs.catalog, cs.schema));
return Frameworks.newConfigBuilder().defaultSchema(rootSchema);

Can someone please help me what I may be doing wrong.

score 0 · Answer 1 · answered Jun 05 '18 at 12:11

0

If your table is file (lowercase) then make sure you quote the table name in the query, i.e. "SELECT * from \"file\"".

answered Jun 05 '18 at 12:11

Michael Mior

28,107
9
89
113

So I do not have issues retrieving the data directly from HSQL using `statement.executeQuery` but I cant fetch it using Apache Calcite APIs. Updated my question to reflect this clearly. – Anuj Jun 05 '18 at 13:27

Apache Calcite | HSQLDB - Table Not Found Exception

1 Answers1