Hi Team, We have a hybrid table for our analytics use case and were using UPSERT for REALTIME table. It was working perfectly fine in 0.8. When minion was moving data to OFFLINE, we were using mergeType: "dedup" and duplicates were getting eliminated in OFFLINE flow also. When we upgraded to 0.9, the UPSERT is no more supported for hybrid table. This validator is blocking our table deployment. We understand UPSERT cannot work for OFFLINE table but why is it blocked for hybrid tables? Can someone clarify if we are missing something here?

If a row is being upserted (via RT ingestion today), but the previous row for the pk has been moved to offline part, then the upsert won't work.

relaxing this check for now: https://github.com/apache/pinot/pull/7733

according to me, we should not support the hybrid table + upsert. this particular case is an exception ( a combination of the dedup functionality and realtime retention) and there’s still cases where it won’t work (as pointed by mayank). It will not work properly for majority of the usecases

what would be the error message for the failure use case Mayank mentioned? I guess as long as the task itself will error out with proper error message indicating the issue we should be ok to remove this constrain from the validation phase.

no error message. upsert just won’t work

@Vibhor Jain can you describe exactly what behavior you want to achieve with this realtime to offline transfer together with upsert? I am not 100% sure we sorted out all the scenarios

Hi @Neha Pawar Do that means if @Vibhor Jain can ensure his table’s data without duplicate rows, we can relax this check by configuration?

Hi @Mayank, @Neha Pawar, @Rong R Thanks for looking into this. 1. Ours is a hybrid table where realtime is holding data for say 7 days and minion is moving it to offline (to support backfill for future needs). 2. Now, duplicates occur in small batches for us since our Flink flows are at least once. There is no scenario where a duplicate is coming after say 4 days. 3. So we only need UPSERT for realtime. Blocking this for hybrid table is not working out for us. 4. Till now, we were handling deduplication flow for our hybrid table in 2 steps: • realtime handles it via UPSERT. • offline handles it via mergeType: "dedup" in "RealtimeToOfflineSegmentsTask" configs. This was working perfectly fine and no duplicates were seen. 5. Pls note, we understand UPSERT cannot work across realtime and offline tables together. We were leveraging this functionality only for REALTIME. UPSERT documentation clearly states it's for REALTIME. So if I'm using it for the hybrid table, it should be allowed (given the limitation to real time only that's already documented). Let me know if you need further info on this one.

sorry for the delay, will respond shortly

I feel that this is a specific case that works, however, in general we know that upsert does not currently work in hybrid setup (regardless of managed offline or otherwise). I am leaning towards keeping the check and overriding for special casing. wdyt @Jackie @Yupeng Fu

@Neha Pawar, @Mayank, For our flow, duplicates won't come with a delay of hours/days. They would be coming in a much smaller window, if at all. So UPSERT + dedup mergeType is making perfect sense for our use case. We totally understand the limitations of UPSERT + dedup. If we could enable it again (may be via config), that would help. Documentation is already stating UPSERT works only for realtime, we are good with that.

Yes, that's what I am also suggesting, overriding to enable with config.

Upsert won’t work for hybrid table, there’ll be duplicates. So I suggest we keep the check for correctness

felt like it is not one single use case, see: https://medium.com/@msoni6226/handling-of-duplicate-data-with-apache-pinot-78d35e7c8465.

Or is it the same one from @Vibhor Jain ? I still recommend keeping the check and allowing a override to disable

i suggest we just remove the check. Anyway the check right now is catching realtimeToOffline + upsert. this will only catch cases where realtimeToOffline is being used to setup hybrid table. We cannot really catch all hybrid table cases here anyway

@Neha Pawar could you explain a bit on this? catching sth (but not all) is still better than catching nothing? I'm fine with @Mayank’s proposal to disable the check with a config, if the users are ok with the duplicates and inaccurate result. but by default a guard will prevent surprises to most users

I think @Neha Pawar is saying the check is incomplete to begin with and was recently added. Perhaps we should add the check in the correct way, and then add the override.

this is not true? only realtime table is allowed by the checker https://github.com/apache/pinot/blob/2107b2cd1880ec74934ec5412dbee8c874f05610/pino[…]java/org/apache/pinot/segment/local/utils/TableConfigUtils.java

there can be hybrid tables, that don’t have

realtiemToOffline

configured, but have

upsert

configured in the realtime table. This check does nothign to block that. This is what i mean by the check is incomplete @Yupeng Fu

hmm then this hybrid table will fail at the

OFFLINE

table type?

it will not fail any validation. it will let people setup.

Why? Shouldn’t the code above guard it?

which code? nothing stops someone from first creating a realtie table with upsert, and then adding an offline table

This one: https://github.com/apache/pinot/blob/2107b2cd1880ec74934ec5412dbee8c874f05610/pinot-segment-local/src/main/java/org/apache/pinot/segment/local/utils/TableConfigUtils.java#L374

but this is on realtime table only. you can still create an offline table later on and make your table a hybrid table. there’s no check for that

Offline table creation does not go through the table validation code path?

I'm not following.. if there is no flag set in offline table for upsert, it should return from here and pass right?

if (tableConfig.getUpsertMode() == UpsertConfig.Mode.NONE) {
      return;
    }

oh, i see your point finally

exactly the point. so you’re also saying that if users have a hybrid table, and have set upsert only in the realtime conterpart, and have not set upsert in offline, it should be allowed?

well, the user guide makes it clear that not supporting offline, as the query result is hard to interpret. for lambda architecture, pinot gives the federated view, and users cannot tell which part is from offline vs realtime. but I get your point on either failing hybrid table altogether, or relaxing the check on realtimeToOffline flow to be consistent