Converting Tableau Report that uses flattened MongoDB virtual tables to use Apache Drill

[Daniel Clark <cl...@gmail.com>]

Hi,

I'm in the process of converting a tableau report that previously used
the MongoDB BI Connector to use Apache Drill instead. One gotcha that
I'm working to overcome is that the report utilizes virtual tables
flattened from embedded array columns in the Mongo collection. I did
some googling, to my dismay I didn't find anywhere that Apache Drill
had this functionality.

I had the idea of using views
(https://drill.apache.org/docs/create-view/) to mimic this behavior in
drill using the flatten (https://drill.apache.org/docs/flatten/)
function where appropriate. When I tried to create the view I get the
following error message:

org.apache.drill.common.exceptions.UserRemoteException: VALIDATION
ERROR: Unable to create or drop objects. Schema [mongo.grounds] is
immutable.

I read online at;
https://drill.apache.org/docs/mongodb-storage-plugin/, that the
MongoDB storage plugin is read-only. Does that also include DDL? Is
there a better approach that I should be taking with this?

Re: Converting Tableau Report that uses flattened MongoDB virtual tables to use Apache Drill

[Charles Givre <cg...@gmail.com>]

HI Daniel. 
Welcome to Drill! 

I think you might have misunderstood how Drill handles views.  Views in Drill are stored as a JSON file on a writable file system.  Now, that is just where the actual view info is stored.  Views can contain data from any storage plugin.  So, let’s say that I have a Hadoop connection called hdfs and a mongo connection, the following query should work.

CREATE VIEW hdfs.my_view AS SELECT foo, bar FROM mongo WHERE bar=true.

Then the query:

SELECT * FROM hdfs.my_view

Would actually execute the query against your mongoDB instance.  One thing about views is that it is STRONGLY recommended to specify the column names and data types in the CREATE VIEW statement and also not to create views from SELECT * queries.

Regarding your question about flattening nested data, you can certainly do that.  If you’re still stuck, if you could please share a sanitized version of your data (or at least the structure) and what you’re trying to get to look like, we can probably figure something out. 
Best,
— C



> On Dec 15, 2021, at 1:32 PM, Daniel Clark <cl...@gmail.com> wrote:
> 
> Hi,
> 
> I'm in the process of converting a tableau report that previously used
> the MongoDB BI Connector to use Apache Drill instead. One gotcha that
> I'm working to overcome is that the report utilizes virtual tables
> flattened from embedded array columns in the Mongo collection. I did
> some googling, to my dismay I didn't find anywhere that Apache Drill
> had this functionality.
> 
> I had the idea of using views
> (https://drill.apache.org/docs/create-view/) to mimic this behavior in
> drill using the flatten (https://drill.apache.org/docs/flatten/)
> function where appropriate. When I tried to create the view I get the
> following error message:
> 
> org.apache.drill.common.exceptions.UserRemoteException: VALIDATION
> ERROR: Unable to create or drop objects. Schema [mongo.grounds] is
> immutable.
> 
> I read online at;
> https://drill.apache.org/docs/mongodb-storage-plugin/, that the
> MongoDB storage plugin is read-only. Does that also include DDL? Is
> there a better approach that I should be taking with this?