Hi Team, when running the following query when usi...
# troubleshooting
g
Hi Team, when running the following query when using unnesting i get not the totalDocs count. Can you confirm that count(*) returns the count of actual ingested items instead of the unnested rows(docs)
x
the one row that got flattened(unnested) will be consider as multiple rows. So 1 MM rows could become 4MM after unnest in Pinot
if you have some original doc id or UUID, then you can do
select count(distinct UUID)
to check the original doc size
g
then what am i getting with this count(*)
x
this is the total rows after the unnest
g
shouldn't it be the same as totalDocs then?
x
I think you use both upsert and hybrid table right
g
i use only upsert in realtime table
x
ok
you can try skipUpsert
g
and i'm sure there are no duplicates because i'm ingesting historical snapshot data currently
x
can you try
Copy code
SET skipUpsert=true;
Select count(*) FROM bets
g
yep got the correct number
x
then maybe filter some primary key with
SET skipUpsert=true;
to check why
g
so this would mean that i have 2827108883 - 947940097 = X duplicates that were upserted
x
yes
I somehow feel when you unnest you keep the Primary key same so only one row got validated
g
what do you mean keep the primary key the same
x
but you ingested all the data
do you use any field in the unnested columns as your primary key
g
i have the following json simplified: { Id: 1231231, Bets [{Selections: [...]...}] } PK is Id it is the same for all unnested rows
so if we have 2 bets with 2 selections each i will have 4 rows with the same Id field or at least this is what i expect
x
say if your data is then pinot will unnest it to multiple rows:
Copy code
{
Id: 1231231,
BetsSelection: xx1
},
{
Id: 1231231,
BetsSelection: xx2
},
...,
{
Id: 1231231,
BetsSelection: xx10
}
so all the 10 rows will be ingested, but because this is an upsert table, the first 9 rows will be consider as old rows
only
Copy code
{
Id: 1231231,
BetsSelection: xx10
}
becomes valid
g
so i need to put PK as some UUID or combination key?
x
yeah, you need a combined key with selectSeqId I guess
g
good thing i understood this now and not when fully in production
x
yeah, the unnest part is a bit confusing. If you want to keep the PK and multiple selections together, you can also try json index for the whole
Bets
field
g
So in theory if i want all rows to be there and updated as a group, then i would need the most inner level to contain a unique primary key that is persisted between updates. Using the example ComboKey will be PK, such that when another update of selection with same key comes it is properly updated: { "Id": 123, "Bets": [ { "Id": 234, "Selections": [ { "Id": 341, "ComboKey": "123_234_341" }, { "Id": 342, "ComboKey": "123_234_342" } ] }, { "Id": 235, "Selections": [ { "Id": 351, "ComboKey": "123_235_351" }, { "Id": 352, "ComboKey": "123_235_352" } ] } ] } ComboKey, Id, Bets_Id, Bets_Selections_Id 123_234_341, 123, 234, 341 123_234_342, 123, 234, 342 123_235_351, 123, 235, 351 123_235_351, 123, 235, 352
my understanding was that if i use the PK of the message inside kafka it is going to work the same way, meaning before it is unnested the PK is compared and updated before unnesting
x
Yes. You need to use the combo key. Pinot unnest is ingestion time processing, all the data are ingested as rows and pinot upsert happens at raw rows level
g
Can i ask one more question in regards to Upsert, i read that if we want to optimize memory usage we should have Metadata TTL setup, but in our case we should base it on a flag that is being ingested, is there a way to say the following or we need to write a Custom Metadata Manager? Upsert records until flag X becomes true, then wait 2 days and discard the key. Also can i enable upsert later at any time or only during initial creation
x
what is this flag X? if the flag is false you want to keep the PK forever?
g
so we have a column called status and if the status is 0 we should keep it until it is different from 0, then once the update comes and makes it different from 0 we should keep it for X amount of time which is TTL, example 2 days
but this status update may take a lot of time to come, while in most cases it happens within a day or at most 7 days, some may be up to 4 years late
x
i see, this condition based thing is not supported. please fire a github issue and the community could prioritize the task.
g
https://github.com/apache/pinot/issues/15802 I created a request/proposal i hope this is fine, tell me if i need to amend it or add something else