This message was deleted.

Hi Swapnil, here are my thoughts -- Approach 1: There is a concept of "Dimension Tables" being discussed/developed that will provide multiple attributes per key ... not ETA yet that I know of though. Approach 2: This would be my choice for query simplicity and management if you don't want to create four Lookup tables (Approach 3) and you are always updating all of the data at the same time. This one as you mention requires a __time column to be added even though it is essentially not used. Assuming you use a static __time value then any segment granularity should work ... for simplicity I would just use DAY. Approach 3: I'm not sure what your 1:M argument is ... you would create four separate Lookups, each with 1:1 Key:Value relationship ... should be easily usable, but it is four different lookups. The advantage here is that individual attribute sets could be updated independently, if that were an advantage. The disadvantage is four times the maintenance if all four attributes sets always are updated together. Thanks. John

@John Kowtko thanks for sharing your feedback. • Approach 2: Create a new datasoruce (table) to store a key and 4 value fields and execute a Druid query with left join between TableA (existing table) and TableB (new table). Could you please check the following question and share your feedback? ◦ Currently I am able to execute the Druid query with an existing druid datasource (TableA) and this new datasource (TableB) with the left join by adding a limit to fetch around 86,000 records form TableA, however when I increase the number of records form TableA then the Druid query execution gets failed with

Error: Resource limit exceeded

exception. Currently TableA contains > 92 billion records and TableB contains > 1.5 million records and this will keep getting increased in future. So, considering the data volume of these two tables this approach does not look feasible to me. • Could you please share your thoughts on this and suggest what can be done to fix this resource limit error without adding more performance overhead on the Druid cluster? • Approach 3: Create 4 Druid Lookups to store 4 value fields and use these 4 Druid Looups in Druid query to fetch the data form TableA. ◦ For one Lookup, it will have 1:M relationship between key and the value field. With the following sample records, I am able to create the Druid Lookup but the generated Lookup is not storing all the records including key, values with 1:M relationship and just stores the last record with key, value combination. Could you please let me know whether is it possible to create a Druid Lookup with 1:M relation between key, value fields?

{
"1", "101",
"1", "102",
"2", "103",
"2", "104", 
"3", "105"
}

Thanks.

For the

resource limit exceeded

error -- check

maxSubQueryRows

... I think the default is 100k, but I've heard of it being bumped up to 1-2m and not running out of memory. Since this table is relatively slim that might be okay. However if Table B's size is going to increase in the future, then consider using MSQ SQL instead as it should not have this limit if you use sortMerge as the join method and the limit will not apply anymore: https://druid.apache.org/docs/latest/multi-stage-query/reference#sort-merge For 1:M -- I do not think lookup tables support non-unique keys -- either it won't be allowed at ingest time, or at query time can produce erratic results. Someone else should clarify this. Thanks. John

If you are going to raise

maxSubQueryRows

this means that the 1.5 million rows will be broadcast to each historical and each peon task involved in the query and be placed in their heap to then resolve the join. Depending on how many historicals/peons are involved this can be a significant data movement. This is why the default for this limit is smaller. With Druid 27.0 there is the alternative of

maxSubQueryBytes

instead of rows such that you are controlling the memory footprint of these join operations. Have you considered approach X - pre-joining that data? If the lookup data is static or its value at ingestion time is good for the analytics you want...(i.e. in retail analytics

marital_status

is useful to keep as it was at the time of the event). While pre-joining uses more space, it can be a great tool for accelerating performance. If you want to learn a lot more about how joins are processed in Druid, I recommend https://github.com/implydata/learn-druid. Have a look at the joins notebook <- this link will work once you have started the environment locally.

@John Kowtko As suggested in https://druid.apache.org/docs/latest/multi-stage-query/reference#sort-merge, I have tried to execute the query by setting

"sqlJoinAlgorithm": "sortMerge"

in query context with left join between TableA (with 88,000 records) and TableB (13,973 records) and the query was failed with the same

Error: Resource limit exceeded Subquery generated results beyond maximum[100000]

exception. Could you please let me know if I am doing something wrong here?

@Swapnil Jokhakar sounds like you are submitting the request through the native engine, not the MSQ engine. How are you submitting the request?

@Sergio Ferragut @John Kowtko I am submitting the query through Druid Console (web UI), I am not sure internally whether it uses native or MSQ engine. Could either of you please guide me or share any help documentation regarding how to submit the query using MSQ engine?

In the web UI, you can select the engine or leave it as auto. 'auto' will select the MSQ engine only if you use REPLACE, INSERT or EXTERN clauses which are only supported through MSQ. If you want to force a particular engine, select it from the dropdown:

I have checked in Druid Console, I am able to see the "Live query" option for which in the drop down it shows the parameters as Auto, On, Off and not able to see the sql-msq-task option. We are currently using 0.21.1 Druid version.

Hi Swapnil, I don't know if Druid 21 has MSQ in it ... one way to find out is to add extension

"druid-multi-stage-query"

to the Druid loadlist in the common.properties file and see if it shows up, as in the screenshot that Sergio posted. Thanks. John

It does not, the extension was introduces in Druid 24.0.0.

I would create 4 lookup tables, lookup values upon ingestion and store them in target data source. Less joins = faster query response (especially if you will want to filter using table that you are joining to)