Search code examples
mongodbapache-drill

Execute Mongo geospatial queries through Apache Drill

We have a question regarding the MongoDB and Drill integration through the Mongo storage plugin; we need to query and execute geospatial queries through Drill using the MongoDB geospatial functions. That means we have data in Mongo and using Drill we need to call those Mongo functions. For example, consider we have a zones collection in Mongo with zones information including a geojson. Directly in Mongo we can execute the following query using the $geoIntersects Mongo function (find the zones that intersects a specific Point) with successful results:

db.getCollection('zones').find({ geom: { $geoIntersects: { $geometry: { type: "Point", coordinates: [ -73.93414657, 40.82302903 ] } } } })

The main question:

Is it possible to use Mongo geospatial query operators in Drill and how can we do it?

Also, if we want to execute the same query described above but using Drill, is that possible? And if it is, how can we do it?

Thanks for your help!

Solution

  • Apache Drill's Mongo storage plugin acts as a facade over a Mongo client, this facade does not support all the features of the underlying Mongo client. The $geoIntersects and $geometry operators are not supported or to put it another way; Apache Drill's Mongo storage plugin is not aware of those operators.

    The original documentation for Apache Drill's Mongo storage plugin stated:

    As of now, predicate pushdown is implemented for the following filters: >, >=, <, <=, ==, !=, isNull and isNotNull.

    Looking at the latest version of the code this remains the case.

    So, the answer to this question ...

    Is it possible to use Mongo geospatial query operators in Drill ?

    ... is: no. Apache Drill's Mongo storage plugin only supports the following query operators: >, >=, <, <=, ==, !=, isNull and isNotNull.

    And the answer to this question ...

    Also, if we want to execute the same query described above but using Drill, is that possible?

    ... is no. Bear in mind that Apache Drill's Mongo storage plugin is trying to provide SQL-esque query features over a document store and geospatial query operators such as $geoIntersects and $geometry do not have common, well understood equivalents in SQL.

    Unfortunately, there's no good news for you in the above response :( I think your options are

    • Implement these yourself. There's a patch attached to this issue which might provide some with guidance on how to implement a new query operator in Apache Drill's Mongo storage plugin
    • Raise an issue against the Drill project specifying Component=Storage - MongoDB