[druid-user] Druid no response for SQL with multiple left joins

Hi,

I am using Druid 0.19.0 in cluster mode.
There are 14 nodes with 128 GB RAM and 16 Core.

2 Master nodes (Coordinator + Overload process)
2 Query nodes (Broker + Router process)
10 Data nodes (Historical + Middle manager process)

As of now the size of Druid data source is 2.2 TB (Replication) with 7.0 Billion data.
Now Product want to get some data for their analytics for every fortnight or month or could be any number of days.

No I am executing following query to get data from same data source with multiple left joins. Query runs for 1-2 hours and completed without writing any results to output file on disk.

{“query” : “select b.gid, b.frequency, c.Recency,d.volume,syft,plan_selected,Success FROM (select gid, count() as frequency FROM (select SUBSTRING(TIME_FORMAT(__time), 1, 10) as c_date, gid FROM Events where __time >= ‘2021-03-17’ AND __time < ‘2021-03-31’ GROUP BY SUBSTRING(TIME_FORMAT(__time), 1, 10), gid)f GROUP BY gid)b LEFT JOIN (select gid, TIMESTAMPDIFF(DAY,m_date,cast(‘2021-03-30’ as Date)) as Recency FROM (select max(cast(SUBSTRING(TIME_FORMAT(__time), 1, 10) as DATE)) as m_date, gid FROM Events where __time >= ‘2021-03-17’ AND __time < ‘2021-03-31’ GROUP BY gid)r)c on b.gid=c.gid LEFT JOIN (select gid, count() as volume FROM (select gid, msid FROM Events where __time >= ‘2021-03-17’ AND __time < ‘2021-03-31’ GROUP BY gid, msid)b GROUP BY gid)d on b.gid=d.gid LEFT JOIN (select gid, SUM(CASE WHEN event_action = ‘SYFT’ THEN 1 ELSE 0 END) as syft, SUM(CASE WHEN event_action = ‘Plan Selected’ THEN 1 ELSE 0 END) as plan_selected, SUM(CASE WHEN event_action = ‘Success’ THEN 1 ELSE 0 END) as Success FROM Events where __time >= ‘2021-03-17’ AND __time < ‘2021-03-31’ AND event_category = ‘Subscription Flow’ AND event_action in (‘SYFT’,‘Plan Selected’, ‘Success’) GROUP BY gid)aq on aq.gid=b.gid”,
“resultFormat” : “csv”,
“header”: true,
“context” : {
“timeout” : 7200000
}
}

But when I executed without left join it executed successfully -

{“query” : “select b.gid, b.frequency FROM (select gid, count(*) as frequency FROM (select SUBSTRING(TIME_FORMAT(__time), 1, 10) as c_date, gid FROM Events where __time >= ‘2021-03-17’ AND __time < ‘2021-03-31’ GROUP BY SUBSTRING(TIME_FORMAT(__time), 1, 10), gid)f GROUP BY gid)b”,
“resultFormat” : “csv”,
“header”: true,
“context” : {
“timeout” : 7200000
}
}

I have also tried to execute with scalar queries but I think Druid is not supporting this -

select m.gid,
(select TIMESTAMPDIFF(DAY, max(cast(SUBSTRING(TIME_FORMAT(__time), 1, 10) as DATE)), cast(‘2021-03-30’ as Date)) as Recency FROM ETClientEvents r
where __time >= ‘2021-03-17’ AND __time < ‘2021-03-31’ AND m.gid=r.gid )
From ETClientEvents m
where __time >= ‘2021-03-17’ AND __time < ‘2021-03-31’
GROUP BY gid

This query throws an exception like -
Unknown exception / index (76) must be less than size (2) / java.lang.IndexOutOfBoundsException

What should I do now to get the above query with multiple left joins get executed or please suggest a better query that may return the same data that I am looking for.

Thanks & Regards
Amit Srivastava

Can you run the explain plan for the JOIN query that will not return? Sometimes rewriting them into subqueries will get better performance. Also, you may want to reconsider your data model if this is a query you will be running a lot.

Hi Rachel,

Thanks for your prompt response. I think the issue is not related to the data model because the same query is working for one week data. In the case of fortnight and months query the data becomes very huge due to multiple left joins.

Here is the explain plan -

DruidJoinQueryRel(condition=[=($0, $6)], joinType=[left], query=[{“queryType”:“scan”,“dataSource”:{“type”:“table”,“name”:“join”},“intervals”:{“type”:“intervals”,“intervals”:["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},“virtualColumns”:[],“resultFormat”:“compactedList”,“batchSize”:20480,“limit”:100,“order”:“none”,“filter”:null,“columns”:[“Recency”,“Success”,“frequency”,“gid”,“plan_selected”,“syft”,“volume”],“legacy”:false,“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false,“granularity”:{“type”:“all”}}], signature=[{gid:STRING, frequency:LONG, Recency:LONG, volume:LONG, syft:LONG, plan_selected:LONG, Success:LONG}])
DruidJoinQueryRel(condition=[=($0, $4)], joinType=[left], query=[{“queryType”:“scan”,“dataSource”:{“type”:“table”,“name”:“join”},“intervals”:{“type”:“intervals”,“intervals”:["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},“virtualColumns”:[],“resultFormat”:“compactedList”,“batchSize”:20480,“limit”:9223372036854775807,“order”:“none”,“filter”:null,“columns”:[“Recency”,“frequency”,“gid”,“gid0”,“gid1”,“volume”],“legacy”:false,“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false,“granularity”:{“type”:“all”}}], signature=[{gid:STRING, frequency:LONG, gid0:STRING, Recency:LONG, gid1:STRING, volume:LONG}])
DruidJoinQueryRel(condition=[=($0, $2)], joinType=[left], query=[{“queryType”:“scan”,“dataSource”:{“type”:“table”,“name”:“join”},“intervals”:{“type”:“intervals”,“intervals”:["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},“virtualColumns”:[],“resultFormat”:“compactedList”,“batchSize”:20480,“limit”:9223372036854775807,“order”:“none”,“filter”:null,“columns”:[“Recency”,“frequency”,“gid”,“gid0”],“legacy”:false,“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false,“granularity”:{“type”:“all”}}], signature=[{gid:STRING, frequency:LONG, gid0:STRING, Recency:LONG}])
DruidOuterQueryRel(query=[{“queryType”:“groupBy”,“dataSource”:{“type”:“table”,“name”:“subquery”},“intervals”:{“type”:“intervals”,“intervals”:["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},“virtualColumns”:[],“filter”:null,“granularity”:{“type”:“all”},“dimensions”:[{“type”:“default”,“dimension”:“gid”,“outputName”:“d0”,“outputType”:“STRING”}],“aggregations”:[{“type”:“count”,“name”:“a0”}],“postAggregations”:[],“having”:null,“limitSpec”:{“type”:“NoopLimitSpec”},“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false}], signature=[{d0:STRING, a0:LONG}])
DruidQueryRel(query=[{“queryType”:“groupBy”,“dataSource”:{“type”:“table”,“name”:“Events”},“intervals”:{“type”:“intervals”,“intervals”:[“2021-03-16T18:30:00.000Z/2021-03-30T18:30:00.000Z”]},“virtualColumns”:[{“type”:“expression”,“name”:“v0”,“expression”:“substring(timestamp_format(”__time",‘yyyy-MM-dd\u0027T\u0027HH:mm:ss.SSSZZ’,‘Asia/Kolkata’), 0, 10)",“outputType”:“STRING”}],“filter”:null,“granularity”:{“type”:“all”},“dimensions”:[{“type”:“default”,“dimension”:“v0”,“outputName”:“d0”,“outputType”:“STRING”},{“type”:“default”,“dimension”:“gid”,“outputName”:“d1”,“outputType”:“STRING”}],“aggregations”:[],“postAggregations”:[],“having”:null,“limitSpec”:{“type”:“NoopLimitSpec”},“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false}], signature=[{d0:STRING, d1:STRING}])
DruidQueryRel(query=[{“queryType”:“groupBy”,“dataSource”:{“type”:“table”,“name”:“Events”},“intervals”:{“type”:“intervals”,“intervals”:[“2021-03-16T18:30:00.000Z/2021-03-30T18:30:00.000Z”]},“virtualColumns”:[],“filter”:null,“granularity”:{“type”:“all”},“dimensions”:[{“type”:“default”,“dimension”:“gid”,“outputName”:“d0”,“outputType”:“STRING”}],“aggregations”:[{“type”:“longMax”,“name”:“a0”,“fieldName”:null,“expression”:“timestamp_floor(timestamp_parse(substring(timestamp_format(”__time",‘yyyy-MM-dd\u0027T\u0027HH:mm:ss.SSSZZ’,‘Asia/Kolkata’), 0, 10),null,‘Asia/Kolkata’),‘P1D’,null,‘Asia/Kolkata’)"}],“postAggregations”:[{“type”:“expression”,“name”:“p0”,“expression”:“div((1617042600000 - “a0”),86400000)”,“ordering”:null}],“having”:null,“limitSpec”:{“type”:“NoopLimitSpec”},“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false}], signature=[{d0:STRING, p0:LONG}])
DruidOuterQueryRel(query=[{“queryType”:“groupBy”,“dataSource”:{“type”:“table”,“name”:“subquery”},“intervals”:{“type”:“intervals”,“intervals”:["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},“virtualColumns”:[],“filter”:null,“granularity”:{“type”:“all”},“dimensions”:[{“type”:“default”,“dimension”:“gid”,“outputName”:“d0”,“outputType”:“STRING”}],“aggregations”:[{“type”:“count”,“name”:“a0”}],“postAggregations”:[],“having”:null,“limitSpec”:{“type”:“NoopLimitSpec”},“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false}], signature=[{d0:STRING, a0:LONG}])
DruidQueryRel(query=[{“queryType”:“groupBy”,“dataSource”:{“type”:“table”,“name”:“Events”},“intervals”:{“type”:“intervals”,“intervals”:[“2021-03-16T18:30:00.000Z/2021-03-30T18:30:00.000Z”]},“virtualColumns”:[],“filter”:null,“granularity”:{“type”:“all”},“dimensions”:[{“type”:“default”,“dimension”:“gid”,“outputName”:“d0”,“outputType”:“STRING”},{“type”:“default”,“dimension”:“msid”,“outputName”:“d1”,“outputType”:“STRING”}],“aggregations”:[],“postAggregations”:[],“having”:null,“limitSpec”:{“type”:“NoopLimitSpec”},“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false}], signature=[{d0:STRING, d1:STRING}])
DruidQueryRel(query=[{“queryType”:“groupBy”,“dataSource”:{“type”:“table”,“name”:“Events”},“intervals”:{“type”:“intervals”,“intervals”:[“2021-03-16T18:30:00.000Z/2021-03-30T18:30:00.000Z”]},“virtualColumns”:[],“filter”:{“type”:“and”,“fields”:[{“type”:“selector”,“dimension”:“event_category”,“value”:“Subscription Flow”,“extractionFn”:null},{“type”:“in”,“dimension”:“event_action”,“values”:[“Plan Selected”,“Success”,“SYFT”]}]},“granularity”:{“type”:“all”},“dimensions”:[{“type”:“default”,“dimension”:“gid”,“outputName”:“d0”,“outputType”:“STRING”}],“aggregations”:[{“type”:“filtered”,“aggregator”:{“type”:“count”,“name”:“a0”},“filter”:{“type”:“selector”,“dimension”:“event_action”,“value”:“SYFT”,“extractionFn”:null},“name”:“a0”},{“type”:“filtered”,“aggregator”:{“type”:“count”,“name”:“a1”},“filter”:{“type”:“selector”,“dimension”:“event_action”,“value”:“Plan Selected”,“extractionFn”:null},“name”:“a1”},{“type”:“filtered”,“aggregator”:{“type”:“count”,“name”:“a2”},“filter”:{“type”:“selector”,“dimension”:“event_action”,“value”:“Success”,“extractionFn”:null},“name”:“a2”}],“postAggregations”:[],“having”:null,“limitSpec”:{“type”:“NoopLimitSpec”},“context”:{“sqlOuterLimit”:100,“sqlQueryId”:“b0e0d932-ce45-42a9-b85b-284571185c60”},“descending”:false}], signature=[{d0:STRING, a0:LONG, a1:LONG, a2:LONG}])

Please suggest.

Regards
Amit Srivastava