https://pinot.apache.org/ logo
v

Varun Srivastava

02/09/2021, 7:07 AM
<!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 ?
x

Xiang Fu

02/09/2021, 8:16 AM
@Jackie
j

Jackie

02/09/2021, 6:33 PM
@Varun Srivastava
null
value will be replaced to the default value, so it should work
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
v

Varun Srivastava

02/10/2021, 6:03 AM
1. So what can be the use case Suiting for UPSERT ?
2. Our USE CASE - We need to update previous inserted rows. So we are planning as below :
Copy code
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
Please let me know .... if you see any concern
# 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 ?
j

Jackie

02/10/2021, 6:50 PM
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
I think your use case should be a good candidate for UPSERT as long as the table is not too large
v

Varun Srivastava

02/11/2021, 7:32 AM
Thanks Jackie
One more info I need - Does Join is supported now in PINOT
If yes...... can you please share any sample query and any doc