Hi Team, Can anyone suggest me how to create materialized view in druid. like joining on 2 data sources and use that materialized view in any BI tool for visualization

Hi Tejas, Druid does not support arbitrary materialized views that involve joins, so for this specifically you would have to create and maintain a separate table to hold the MV equivalent, and refresh it periodically using your "MV query" via a reindex batch job. it is relatively easy do this in SQL using the MSQ engine. Let us know if you think this will work for you or if you need something different. Thanks. John

Thanks @John Kowtko for the response, Do you mean we have to do joins before loading into druid?

Hi Tejas, I am new to Druid but based on my recent studies I would say unless it's a simple lookup table for the join it's better to use a stream processor to do the join in real time using Kafka before loading into druid. That's the architecture we're using where I work. I would suggest plain Kafka Streams or Flink for that. Do the join of the two topics and sink to another topic in Kafka and then load into Druid.

But If we are using that druid data sources in Superset then in superset SQl Lab if we do joins and based on output we create dataset so that also works fine right same like materialized view

Doing joins at query time is fine ... I don't know Superset but if it has a "view" capability then that sound like it should work functionally. Many analytic tools allow you to define datacubes using arbitrary SQL statements, which also allow you to do joins, etc, at that layer. However that is more-or-less in the application layer (business data layer to be more specific), and is not in the database itself. If query performance is an issue then you moving the join upstream should theoretically improve query performance. For this you could implement a batch update job (as I mentioned), or lookups, or upstreaming joining (as Marcos mentioned). Fortunately you have multiple options here so you can choose the most appropriate option for your use case.

@Tejas Galande - John has a good insight. Joins might work but is usually not performant which if you find that's the case (if you have huge amounts of data) it would be best to shift left and do the join with streaming, specially if they're complex joins. Druid documentation also mentions this -> https://druid.apache.org/docs/latest/querying/joins/

Yeah Thanks @John Kowtko @Marcos Maia for the inputs. Just one doubt currently I am running query in superset on top of druid and based on that query I am creating dataset in superset that is fine , but what if I want to store that newly created data set, where it will get stored? As I saw you both don't have much experience about Superset but you can suggest someone to answer my doubt.

At the Druid level you can ingest into a new datasource using a query as the source. I don't know that Superset has an "insert into Druid" capability, so the only thing I can think of is to export the output from Superset to a (CSV?) file and then ingest that file into Druid separately.

Okay thank @John Kowtko