This message was deleted.

Have you tried using joins instead of the LOOKUP functions. If the join is too large for a broadcast join, you can use

sqlJoinAlgorithm:sortMerge

in the query context to reduce the memory footprint required for the joins.

Yes, I have tried to use join with sqlJoinAlgorithm: Broadcast and sortMerge and both have given me errors such as BroadcastTablesTooLarge and TooManyRowsWithSameKey error, that is why the idea of removing the joins and using lookups to solve it and using the lookups executing only the sql statement works well for me , the problem is when I try to use insert

I think sortMerge is your best bet. Is the data skewed on a particular product or location? This is what I think TooManyRowsWithSameKey might be coming from. You may be able to solve it by just adding heap to the Peons so they can deal with the extra skew. Perhaps a single table for product and one for location with all the necessary fields, so there are only two joins would help.

hmm, with

sortMerge

join,

TooManyRowsWithSameKey

should only happen if both sides of the join have a large # of rows with a particular key

Yes @Sergio Ferragut, it may be the case that there is biased data regarding products or places, the issue here is that I was testing, as you mentioned, separating products and places into a single table, these tables previously loaded in druid both with 30k rows and 300k of rows respectively, performing join operations and using sqlJoinAlgorithm: Broadcast and sortMerge, here is the query I was testing before using the lookups:

"

INSERT INTO Sales_plus

SELECT

v."__time",

--Product Dimensions

p.product_id,

p.product_name,

p.product_category,

-- Place Dimensions

l.location_id,

l.location_address,

l.location_locality,

l.location_longitude,

l.location_latitude,

-- Sales data

v.quantity,

v.sum_sales,

v.sum_amount,

v.sum_net_amount

FROM "sales" v

left JOIN product p on v.id_prod = p.product_id

left JOIN location l on v.id_location = l.location_id

PARTITIONED BY DAY

" and this in engine MSQ keeps failing due to the error: Error in the middleManager Task execution process exited unsuccessfully with code[3]. See middleManager logs for more details. The ingestion logs show: "Terminating due to java.lang.OutOfMemoryError: GC overhead limit exceeded, and for more information." the SELECT query with the sql-native engine does work well.

hmm, that's odd. certainly don't want OOM errors. i would love to debug that if you could get a heap dump of the task that terminated due to "GC overhead limit exceeded". you can get that by adding

-XX:+HeapDumpOnOutOfMemoryError

to

druid.indexer.runner.javaOpts

or

druid.indexer.runner.javaOptsArray

in the

middleManager/runtime.properties

. the task log would be helpful too

@Gian Merlino Here I was reviewing the sales data with products and places and there is not a large number of rows with a particular key, this TooManyRowsWithSameKey error was obtained due to an error in the creation of the SQL that I made where I placed the Product key in the join in the Location table example: ".. FROM "sales" v left JOIN product p on v.id_prod = p.product_id left JOIN location l on v.id_prod = l.location_id" resulting in that error, then I realized and adjusted it. After that I have not been able to avoid this error in every operation I perform with the MSQ engine. "Error in the middleManager Task execution process exited unsuccessfully with code[3]. See middleManager logs for more details. The ingestion logs show: "Terminating due to java.lang.OutOfMemoryError: GC overhead limit exceeded, and for more information."

> left JOIN location l on v.id_prod = l.location_id" I would have thought this would result in NO matches here. Is your l.location_id unique within the location table? You can confirm with

select count(*), count(distinct location_id) from location

and

select count(*), count(distinct product_id from product

... (and remember to uncheck the "use approx count(distinct)" box so you get exact numbers here.) Also, have you tried pulling things out of the query to get to a simpler version of the query that does work? I.e. pull out one of the joins ... does just sales LEFT JOIN product work? Or sales LEFT JOIN location? And for your original query with Lookups, start removing lookups one at a time.

Which version of druid are you using. There was some improvements in lookup memory management in the recent druid versions

For the solution of this problem, I increase the memory size on the heap, remove all the druid lookups and run again using sqljoinalgorithm Softmerge to use the joins and won by running the ingestion fine, thank you very much.