Druid Data Export Druid Experts - We have a requirement to update all the existing data in Druid. This is mainly because the reference data changes periodically. The Druid tables are enriched before ingestion as Druid does not have good join support for large tables. Ref data size: ~3.5 MM rows Druid table size: ~1.5-2 MM rows (daily aggregated). Has about 4 years of history. Totally ~500MM rows We thought of the following approaches to update the existing data in Druid. Approach 1: Load the reference data as a Druid table and reingest using the MSQ engine and a join. I expect that this would fail as the number of records produced by the Druid table join exceeds the limit. Approach 2: Read the reference data as an external table in MSQ engine and join it with the Druid table for update. Would the join record count limit be applicable in this case as well? Approach 3: Export all the data from Druid table to an external source like S3 and run the update using a distributed processor such as Spark. Then ingest the data back to Druid. Is there a way to export the data from Druid? Or is there a tool/utility to read the segment files from deep storage through an external process? Approach 4: Directly access the Druid table from Spark using JDBC, perform the update, save the updated data back to S3. Then ingest it back to Druid. Would this work? If we connect to Druid from multiple Spark executors, would there be a significant impact on the broker/historical processes? ldeally, we would prefer to follow Approach 2 or 3. Any thoughts? We have multiple ref data and Druid tables to update. The stats that I shared above would be the largest one. Druid version: 24.0.2 Regards, AR.

1. Since you are using MSQ, I strongly urge you to update Druid to the latest version, and try using

sortMerge

join. It doesn’t have the memory limit that the

broadcast

join suffers from (though under special circumstances it can still OOM, if the join keys are skewed, for a well distributed dataset it shouldn’t happen). This would be the best solution, if it works for your use case. 2. Yes, the limit applies to amount of data that can be materialized, which is same in both the cases. In the latest versions, MSQ doesn’t rebuild the broadcast tables, and therefore is more optimal in disk space usage. 3. You can try using the iceberg extensions, if you want to ingest the data using Spark. To export the data from the segment files to a location, you can use the newly introduced (experimentai) EXPORT functionality, however - it is experimental, and it is in 29.0.0, (hence requiring an update). In 24.0.2, I am not sure if there’s an easy way to export, you can run the SELECT query and export the data into a CSV. 4. No suggestions regarding this option IMO, 1 would be the most ideal solution. It requires upgrading Druid version, though all the other answers also require upgrading.

Thanks Laksh. I checked our internal repo and 27.0.0 is the latest version available. I checked the release notes and it looks like

sortMerge

join is available in this version. Can I upgrade directly from 24.0.2 - 27.0.0? I looked at the release notes but didn't find any obvious blockers. We are still on Java 8. Will v27.0.0 run on Java 8 or is a higher version needed for it? Regards, AR.

Will v27.0.0 run on Java 8 or is a higher version needed for it?

Afaict all versions till now are supported on Java 8. Regarding compatibility, it’s difficult to make a blanket statement.

Experimental will have to do since we don't have a higher version. 😞 Will try to do a rolling upgrade of the cluster as explained in the docs and see if that works. Thanks again. Regards, AR.

we don’t have a higher version

It should be on Druid’s website. In either case, LMK how it goes!

One of the goals of MSQ was to do large shuffle joins. So yeah sort merge should work. I highly recommend using the latest versions of druid since they would be available on the druid's website.