This message was deleted.

Hi Shantha, Shuffle join is just join ... you have to set the query context though in order for it to be used. You can run select statements directly in the Query tab of the web console, or you can submit them through the MSQ task API ... although for the latter you have to retrieve the results in a separate API call. Here's an example join on the kttm demo database ... it's self-joining but if you try to run this with the native engine it will give you the familiar

subquery max[100000]

error:

select a.session, 
       count(b.session) numMatches, 
       count(distinct b.client_ip) numIPs
  from "kttm-nested-v2-2019-08-25" a 
  join "kttm-nested-v2-2019-08-25" b on a.session = b.session
 group by a.session

To elaborate on that, shuffle join only exists within MSQ. Using MSQ for ingestion (including shuffle joins) is a fully supported good to go feature. Using MSQ for querying (i.e. running queries in MSQ that return results to you rather than write them to a datasource) is experimental and the "interactive" (result getting) API is definitely going to change in the future (see: https://github.com/apache/druid/pull/14416) when that change goes in (hopefully in Druid 27) it will also be a fully supported non-experimental feature. For today (Druid 26) you are welcome to use shuffle joins for ingestion use-cases, you can have a play around with the querying but do not build workflows around it just yet - I would advise to stick to running interactive queries from the console which will update in tandem with the APIs.

Thank you for the detailed explanation! For the ingestion MSQ usecase, do the query_worker nodes talk to each other for shuffling? While using MSQ for querying, does it still use the brokers and historicals for the query or are query_controller and query_worker tasks used for that as well?

Yes the query_worker tasks talk to each other for shuffling. When using MSQ (with the current APIs) you do not engage with the broker or historical at all (this will likely change in the future)

this is really cool work! Excited for the future 🙂