Apache Calcite | Querying data from MongoDB by using Relational algebra

Question

I am able to get a MongoDB connection and able to get a node

(LogicalTableScan(table=[[enlivenDev, collection1]]))

But when I execute the node, I am getting null pointer exception.

Complete code:

private void executeMongoDB(){
        final FrameworkConfig config = mongoConfig().build();
        final RelBuilder builder = RelBuilder.create(config);
        final RelNode node =  builder.scan("collection1").build();
        System.out.println(RelOptUtil.toString(node));  
        PreparedStatement ps = RelRunners.run(node);
        ResultSet resultSet = ps.executeQuery();
}
    public static Frameworks.ConfigBuilder mongoConfig() {
            final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
            org.apache.calcite.tools.Frameworks.ConfigBuilder configBuilder =Frameworks.newConfigBuilder()
                    .parserConfig(SqlParser.Config.DEFAULT)
                    .defaultSchema(
                        MongoDBConnection.addMongoSchema(rootSchema, CalciteAssert.SchemaSpec.MONGO_DB))
                    .traitDefs((List<RelTraitDef>) null)
                    .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2));
            return configBuilder;
        }
public static SchemaPlus addMongoSchema(SchemaPlus rootSchema, SchemaSpec schema) {
        switch (schema) {
        case MONGO_DB:
            return rootSchema.add("enlivenDev",
                    MongoSchemaFactory.create(rootSchema, "192.168.1.01", "enlivenDev", 27017, "mgp", "mg1"));
        default:
            throw new AssertionError("unknown schema " + schema);
        }
    }

Getting following exception from an above code,there is schema getting null values,while execute relnode of Mongo DB collection

SEVERE: exception while executing query: null
java.sql.SQLException: exception while executing query: null
    at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
    at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
    at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:540)
    at org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:133)
    at org.ramyam.eis.core.ApachecalcitePOC.processMongoDB(ApachecalcitePOC.java:106)
    at org.ramyam.eis.core.ApachecalcitePOC.main(ApachecalcitePOC.java:42)
Caused by: java.lang.NullPointerException
    at org.apache.calcite.schema.Schemas.queryable(Schemas.java:232)
    at Baz.bind(Unknown Source)
    at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:335)
    at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:294)
    at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:559)
    at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:550)
    at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:204)
    at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:67)
    at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44)
    at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:536)

Can you show the full exception? – Michael Mior Jul 30 '17 at 20:52 — Michael Mior, Jul 30 '17 at 20:52
added full exception – Kamatchi Gunasekaran Jul 31 '17 at 04:45 — Kamatchi Gunasekaran, Jul 31 '17 at 04:45
Any one help me on this..Thanks is advance – Kamatchi Gunasekaran Aug 02 '17 at 10:19 — Kamatchi Gunasekaran, Aug 02 '17 at 10:19

1r3k · Answer 1 · 2017-11-21T15:29:38.470

This should help:

public void useCalcite(String modelPath) {
    Connection connection = DriverManager.getConnection("jdbc:calcite:model=" + modelPath);
    CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
    String query = "select count(*) from zips";
    Statement statement = calciteConnection.createStatement();
    ResultSet result = statement.executeQuery(query);
    ...
}

You need a model of your collection. Like this: model-example The model file contains all data needed to establish a connection to mongodb.

score 0 · Answer 2 · answered Jun 17 '21 at 18:12

The above answer posted by @1r3k should work, for me it worked with a minor tweak on the Apache Calcite side for a different reason.

Although its a different story and is not directly related to this issue but still Let me elaborate could be helpful for someone with a similar issue.

My use case is to run standard SQL on Cosmos DB on Azure which provides MongoDB drivers to connect.

The issue with latest version of Apache Calcite is that it is using older MongoDB Java drivers and has not been updated for quite a while now. Also a major flaw is that it does not accept any options which can be passed and there by used by Apache Calcite while creating the MongoClient internally, refer to the below code snippet from Apache Calcite https://github.com/apache/calcite/blob/master/mongodb/src/main/java/org/apache/calcite/adapter/mongodb/MongoSchema.java

MongoSchema(String host, String database,
  MongoCredential credential, MongoClientOptions options) {
super();
try {
  final MongoClient mongo = credential == null
      ? new MongoClient(new ServerAddress(host), options)
      : new MongoClient(new ServerAddress(host), credential, options);
  this.mongoDb = mongo.getDatabase(database);
} catch (Exception e) {
  throw new RuntimeException(e);
}}

This needs to be updated and has already raised a PR for the same but not sure when will this be merged, details are here

https://github.com/apache/calcite/pull/2345

Using the above changes and a minor change in the Model file was able to connect just fine. The change in the model file is to just use the connection URL instead of passing host and other details. Connection string will have all the required connection options encoded within it which includes enabling SSL.

Apache Calcite | Querying data from MongoDB by using Relational algebra

2 Answers2

Linked