https://pinot.apache.org/ logo
#general
Title
# general
n

Nizar Hejazi

03/25/2022, 1:01 AM
Hi team, one of the requirements for supporting stream ingestion w/ upsert is to partition the input stream by the primary key. What if the input stream partition key (e.g. company id) is different from the record primary key (e.g. employee id)?
t

Tim Santos

03/25/2022, 1:25 AM
@User can you elaborate more on your "record primary key"? Are you referring to the column used as the sorted index in your Pinot table?
n

Nizar Hejazi

03/25/2022, 1:52 AM
No. I am referring to the key defined in primaryKeyColumns in table schema.
k

Kishore G

03/25/2022, 1:59 AM
Hi Nizar, you will have to repartition the input stream according to the primary key (employee id in this case)
n

Nizar Hejazi

03/25/2022, 2:01 AM
The problem of partitioning the stream by employee id is that each query will need to hit now all partitions. One the other side, partitioning by company ID allows queries (which are always company-specific) to hit fewer partitions (in most cases a single partition).
How difficult is it to update Upsert functionality in Pinot to remove this coupling (between stream partition key, and record primary key)?
m

Mayank

03/25/2022, 2:21 AM
Upsert relies on data to be partitioned on the primary key for upsert. This way, all versions of a row being upserted are on the same partition/server. Removing that coupling means we are now talking about transactional updates.
BTW, for your case, if a company-id is in one partition, doesn’t it imply that all employe-id’s for that company are also in the same partition? If so, your data is already partitioned by employee-id as well?
👍 2
Rather, partitioned on (company-id + employee-id)
n

Nizar Hejazi

03/25/2022, 2:39 AM
yes, you are right Mayank. All employees belonging to the same company will land on the same partition.
We plan to use two different partitioning schema: 1. For medium-sized tables, partition by company ID only. 2. For large tables, partition by <company ID, employee ID % fixed_number>. In both cases, every row and all of its future versions belong to the same server and the same parititon.
@User I understand from your response that as long as the partitioning key is a prefix/superset of the primary key, upsert will work. Should I update Pinot doc page w/ this info (and add an example of the data I am working on: company/employee)?
m

Mayank

03/25/2022, 3:04 AM
Conceptually it should work. I am cc’ing @User @User to confirm that the implementation holds up too.
y

Yupeng Fu

03/25/2022, 3:21 AM
That's fine. If you partition by A and B, then you are also partitioning by A. In this case, A is the PK
j

Jackie

03/25/2022, 3:49 AM
Upsert works as long as all the records for the same primary key are in the same partition
👍 1
n

Nizar Hejazi

03/25/2022, 6:11 PM
thanks everyone
👍 1