Implementing inner joins for mongo

[Dennis Knochenwefel <de...@reportix.com>]

Hello Drill Dev Pros,

I have found the drill mongo store and would like to extend it to push 
down INNER JOINs. Therefore I would like to rewrite INNER JOINs into the 
mongo aggregation pipeline. Here is a SQL example:

SELECT *
FROM `mymongo.db`.`facts` `facts`
   INNER JOIN `mymongo.db`.`set` `set` ON (`facts`.`group` = `set`.`group`)
WHERE ((`set`.`date` = '09.05.2017') AND (`set`.`id` = '1'))

Could you give me a hint how to do that? I am familiar with the 
aggregation pipeline of mongo, but am not sure how to implement the 
rewrite. I have found the push down of WHERE clauses for mongo [1]

But I am still struggling to do the same for inner joins. If I implement 
"public class MongoPushDownInnerJoinScan extends 
StoragePluginOptimizerRule" then how would the constructor look like. 
Which equivalent of MongoGroupScan (AbstractGroupScan) [2] would I have 
to implement? Any help would be very much appreciated.

Thank you and kind regards,

Dennis


[1] 
https://github.com/apache/drill/blob/master/contrib/storage-mongo/src/main/java/org/apache/drill/exec/store/mongo/MongoPushDownFilterForScan.java

[2] 
https://github.com/apache/drill/blob/master/contrib/storage-mongo/src/main/java/org/apache/drill/exec/store/mongo/MongoGroupScan.java

-- 
Dennis Knochenwefel
Founder
Reportix
Germany

Re: Implementing inner joins for mongo

[Chunhui Shi <cs...@mapr.com>]

Hi Dennis,


I would suggest you to look at the logical plan of this query will be generating by running this sql statement 'explain plan without implementation for <Your mongo Join Query'', meanwhile you also define what is the target query you want to get for this join query against mongoDB, then you may create a rule to match the two-way Join pattern by following the example of Calcite's rule JoinPushThroughJoinRule:


https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/JoinPushThroughJoinRule.java


Hope this helps,


Chunhui

________________________________
From: Dennis Knochenwefel <de...@reportix.com>
Sent: Thursday, December 28, 2017 4:48:29 AM
To: dev@drill.apache.org
Subject: Implementing inner joins for mongo

Hello Drill Dev Pros,

I have found the drill mongo store and would like to extend it to push
down INNER JOINs. Therefore I would like to rewrite INNER JOINs into the
mongo aggregation pipeline. Here is a SQL example:

SELECT *
FROM `mymongo.db`.`facts` `facts`
   INNER JOIN `mymongo.db`.`set` `set` ON (`facts`.`group` = `set`.`group`)
WHERE ((`set`.`date` = '09.05.2017') AND (`set`.`id` = '1'))

Could you give me a hint how to do that? I am familiar with the
aggregation pipeline of mongo, but am not sure how to implement the
rewrite. I have found the push down of WHERE clauses for mongo [1]

But I am still struggling to do the same for inner joins. If I implement
"public class MongoPushDownInnerJoinScan extends
StoragePluginOptimizerRule" then how would the constructor look like.
Which equivalent of MongoGroupScan (AbstractGroupScan) [2] would I have
to implement? Any help would be very much appreciated.

Thank you and kind regards,

Dennis


[1]
https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_drill_blob_master_contrib_storage-2Dmongo_src_main_java_org_apache_drill_exec_store_mongo_MongoPushDownFilterForScan.java&d=DwIDaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=FCGQb-L4gJ1XbsL1WU2sugDtPvzIxWFzAi5u4TTtxaI&m=AgtqT-QgqWrLpsLSl6rmMe7wlrIS_ZxLsgbN53Dw9UE&s=deXYtgUqGSZXm13dZpenqfHCMpb_H6fL6DVcSHXTMQs&e=

[2]
https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_drill_blob_master_contrib_storage-2Dmongo_src_main_java_org_apache_drill_exec_store_mongo_MongoGroupScan.java&d=DwIDaQ&c=cskdkSMqhcnjZxdQVpwTXg&r=FCGQb-L4gJ1XbsL1WU2sugDtPvzIxWFzAi5u4TTtxaI&m=AgtqT-QgqWrLpsLSl6rmMe7wlrIS_ZxLsgbN53Dw9UE&s=zuccYRJCUAMkxZwbmaDUWZoUBdvXf8jvkQGQcS3qfZE&e=

--
Dennis Knochenwefel
Founder
Reportix
Germany