Created UPSERT table with composit primary key havin

Question

< here> Created UPSERT table with composit primary key having 8 columns Many times few of the column value can be null But Together all 8 columns ensure the uniqueness Do you see concern with this Will upsert work properly

Xiang Fu · Answer

< Jackie>

Jackie · Answer

< Varun Srivastava> `null` value will be replaced to the default value so it should work

Jackie · Answer

Do you want to use UPSERT for deduplication purpose FYI it might not be a good idea to use UPSERT with mostly unique primary keys for a large table

Varun Srivastava · Answer

1 So what can be the use case Suiting for UPSERT

Varun Srivastava · Answer

2 Our USE CASE We need to update previous inserted rows So we are planning as below

Varun Srivastava · Answer

```1 fetch previous row 2 Updated add more values 3 Push this message back to topic 4 As this updated message has all same primarykey values so it will upsert the previous record that we fetched at step 1```

Varun Srivastava · Answer

Please let me know if you see any concern

Varun Srivastava · Answer

might not be a good idea to use UPSERT with mostly unique primary keys for a large table i am not getting this Primary key shuould always be unique right

Jackie · Answer

Different from UPSERT in traditional DB because segments in Pinot are immutable we don t directly modify the record in place but maintain extra metadata in memory to track the latest record for each primary key If the cardinality of primary key is unbounded say several billion server might run into memory issue If the table is not too large then it should be fine

Jackie · Answer

I think your use case should be a good candidate for UPSERT as long as the table is not too large

Varun Srivastava · Answer

Thanks Jackie

Varun Srivastava · Answer

One more info I need Does Join is supported now in PINOT

Varun Srivastava · Answer

If yes can you please share any sample query and any doc