https://pinot.apache.org/ logo
Join Slack
Powered by
My queries don’t seem to span both OFFLINE and REA...
# troubleshooting
b
My queries don’t seem to span both OFFLINE and REALTIME tables. How do I debug that? Here’s what I did: 1. Added OFFLINE table via 
AddTable
. Loaded data from Oct 1 via 
ImportData
. 2. Query 
select count(1) from tbl where ds = '2021-10-01'
ran successfully. 3. Added REALTIME table via web ui. Kafka ingested a bunch of data for 
ds = '2021-10-03'
. Query shows new data. 4. But the query from #2 now returns no row. I have to query against 
tbl_OFFLINE
to see the offline records. Many thanks!
x
hybrid table compute time boundary and exclude that from offline table: https://docs.pinot.apache.org/basics/components/table#hybrid-table
b
My offline and online data have non-overlapping time ranges.
The timestamp column is 
create_time
, and the min(create_time) of the realtime table is greater than the max(create_time) of the offline table. This looks like a bug, though I’d like someone to confirm:
Copy code
-- 1633219200
select min(create_time)
from tbl_REALTIME

-- 0 (no rows)
select count(1)
from tbl
where create_time < 1633219200

-- 1414034
select count(1)
from tbl_OFFLINE
where create_time < 1633219200
x
timeboundary is max(offline_ts)
pinot will append 
where create_time < time_boundary
to your query
so it will filter out the offline table data when you query with hybrid table name
see https://docs.pinot.apache.org/basics/components/broker on how the time boundary is determined and how the query split works
b
I read that page, but that doesn’t explain what I’m seeing.
pinot will append 
where create_time < time_boundary
  to your query
That should return something if Pinot queries the offline table. Right? My realtime data comes after the offline data with non-overlapping time ranges.
so it will filter out the offline table data when you query with hybrid table name
Could you elaborate? The doc seems to suggest that the hybrid table query should expand into 2 queries with a merge. But that’s not happening. Many thanks again.
Even a simple 
select count(1) from tbl
is excluding the data from the offline table. It almost looks like that Pinot doesn’t recognize the hybrid.
x
your offline table date is 
2021-10-01
, so the time boundary is 
2021-10-01
. In hybrid mode, pinot append time predicate of 
ds < 2021-10-01
, so it will exclude data on date 
2021-10-01
.
Pinot hybrid table counts data up to 
2021-09-30
from offline table
b
The time boundary is the max(offline_ts). Right? So I shouldn’t be getting no rows in the middle query:
Copy code
-- 1633132799
select max(create_time)
from tbl_OFFLINE

-- 0 (no rows)
select count(1)
from tbl
where create_time < 1633132799

-- 1414034
select count(1)
from tbl_OFFLINE
where create_time < 1633132799
x
The reason is that, when you incrementally push data to offline table, the max date on the offline table cannot guarantee the data are fully presented in offline table, so Pinot still rely on realtime table for the data from the time boundary date
you should
that query is split to two:
Copy code
select count(1)
from tbl_OFFLINE
where create_time < 1633132799 AND ds < '2021-10-01'
and
Copy code
select count(1)
from tbl_REALTIME
where create_time < 1633132799 AND ds >= '2021-10-01'
Then merged the results
both of them will give 0
b
The 
ds
field isn’t a time. The ts column is 
create_time
. And if I run the first of your 2 queries manually, I get data back.
Copy code
select count(1)
from tbl_OFFLINE
where create_time < 1633132799

// Returns count = 1414019
x
then the problem here is what’s the timestamp
can you check 
pinot-broker:8099/debug/timeBoundary/tbl
this will give you the table time boundary
b
It’s using the min of the REALTIME ts. But that’s the same result.
Copy code
{
  "timeColumn": "create_time",
  "timeValue": "1633046399"
}
That seems to be offline’s min ts - 1. But I’m puzzled. It means offline will never be used.
x
why?
you only have 1 day data on offline table?
b
Yes. Is the concept of a “day” significant?
x
in real scenario, your offline table and realtime table should have data overlap
it is once comes to the hybrid table and time boundary computation
image.png
b
Right. I understand that part.
x
You can assume that the latest date on offline table is not queried at all
that date should always be covered from realtime table
b
But it sounds like that the offline table is not used at all if there’s no overlap. Is that correct?
x
if it’s no overlap, you will have latest date data miss
data before 10-01 are still therre
you can push one more day data to pinot to validate e.g. 2021-08-30
👍 1
b
Is the “latest offline date” hardcoded? Why a day instead of the latest offline hour, for example?
x
it’s mostly coming from operational easiness
typically people won’t have hourly data pipeline to push data to offline table
even if they do, then there is no harm to it, as long as the data is covered by realtime
b
Cool. Thanks a lot for the explanation @Xiang Fu!
x
most companies have daily ETL pipeline
so offline data is like daily appended
which can have very long retention
then they keep a short realtime table, e.g. 7 days with the hybrid mode
b
Yeah, I can see that. It makes sense. Thanks!
x
Though this is for most of the use cases I’ve dealt with. Would love to discuss more, if your use case has some unique requirements
e.g. some users may want to re-compute data for last X days in offline table
b
It just means that I need to keep at least 1 day of overlap between OFFLINE and REALTIME. According to the scenario you described, I think the boundary should be 
max(offline_ts)
instead of 
max(offline_ts) - 1 day
. Right?
x
it depends on how you add the predicate to both table 😛
offline table is (-inf, time_boundary) and realtime table is [time_boundary, inf)
b
I mean it a PR proposal. If Pinot changes the boundary calculation to use 
max(offline_ts)
instead, wouldn’t that be strictly an improvement?
x
no, in that case, when you have 100 segments to push to pinot offline table
your time_boundary will advance
but you only have partial data for that date in offline table
then query results is wrong
b
Ok, I see what you mean. This current behaviour is to support tables where the time column is of daily grain.
x
right, and also because most people have daily offline table pipeline
👍 1
2 Views
Open in Slack
PreviousNext
Powered by