Apache Pinot

Hi team, one of the requirements for supporting stream ingestion w/ upsert is to <https://docs.pinot.apache.org/basics/data-import/upsert|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)?

<@U02U9SVBLR0> can you elaborate more on your "record primary key"? Are you referring to the column used as the sorted index in your Pinot table?

No. I am referring to the key defined in <https://docs.pinot.apache.org/basics/data-import/upsert#define-the-primary-key-in-the-schema|primaryKeyColumns> in table schema.

Hi Nizar, you will have to repartition the input stream according to the primary key (employee id in this case)

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)?

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?

Rather, partitioned on (company-id + employee-id)

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 &lt;company ID, employee ID % fixed_number&gt;.
In both cases, every row and all of its future versions belong to the same server and the same parititon.

<@UDQU92KBK> 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 <https://docs.pinot.apache.org/basics/data-import/upsert|Pinot doc page> w/ this info (and add an example of the data I am working on: company/employee)?

Conceptually it should work. I am cc’ing <@UUZ3KPZ8T> <@UDT7GFEG6> to confirm that the implementation holds up too.

That's fine. If you partition by A and B, then you are also partitioning by A. In this case, A is the PK

Upsert works as long as all the records for the same primary key are in the same partition 