Can I use uspert and RealtimeToOfflineSegmentsTask at the same time?

No.. upsert can only be used with real-time only tables.

I mean Can I configure upsert and RealtimeToOfflineSegmentsTask in realtime table config?

No.

Something like the following doesn’t work. We got such error.

@Alice Upsert feature works on realtime-only tables. RealtimeToOfflineTask implies there is an offline component, and hence upsert functionality won’t work. If you are looking for dedup, you got the pointer to it in the other thread.

Actually, RealtimeToOfflineSegmentsTask is introduced to reduce segment number and at the same time mergeType is set dedup to achieve deduplication in offline table. I depend on upsert/dedup feature to achieve deduplication for realtime table. I will try to figure out other way to achieve deduplication in both realtime and offline tables.

in case of dedup in realtime table, the duplicate rows are not stored in Pinot anyway. so you don’t need to again set dedup in the R2O task.

@Alice Enabling dedup on realtime table (assuming the stream is partitioned by the PK and all other dedup requirements are met) prohibits ingestion of > 1 rows with the same primary key. Hence all the segments being processed by the r2o job will already effectively have unique rows. This configuration should work. However, if the offline table isn't exclusively being populated by the r2o job (you're using r2o ,as well as pushing data to the offline table via some other route), the offline table may end up with duplicate rows since dedup as a feature, isn't applicable for offline tables.

Excellent. Thanks.👍🌷

Yes it is 👍

@Neha Pawar I see Startree has a recipe for this, assume this is not a recommended approach https://dev.startree.ai/docs/pinot/recipes/upserts-real-time-offline-job. Think there is some config to turn off the table validation for upsert and RealtimeToOffline task, then some custom job to achieve the upsert in offline.

hmm, the recipe also calls out that doing this will make you lose the upsert logic, and that you have to backfill your segments

The main thing to keep in mind when combining these features is that upsert functionality only applies to real-time tables.

As soon as those segments are moved to an offline table, the upsert logic is no longer applied at query time. We will need to backfill the offline segments created by the real-time to offline job to achieve upsert-like queries.

@Mark Needham assuming you added this, do you think we should even have this recipe published? we block this at validations itself now

@Neha Pawar so are you saying that what's described in that recipe wouldn't work now? It seems I used a 0.10 snapshot version to get it to work