one more blocker that I am having a retention of 3...
# troubleshooting
r
one more blocker that I am having a retention of 3 days on my Realtime table out of Hybrid Table. Now that suppose today is 30th and after 3 days segments that came today will be deleted, but what I want is I dont want any data that has a created_at of 30th april on 4th may
x
any reason for this instead of filter during query time? Pinot uses segment endtime to expire segments, and Pinot retention manager is a scheduled task. So the guarantees is for a 3 days retention, the table ensures all the data within 3 days are available.
r
because we are using Hybrid table and in that the Offline part we are since upsert is not available in Offline table we are going with the approach of ---- realtime and offline data must not club so as to get the upsert data. we will be reuploading the offline segments on 1-2 hour daily to handle upserts
and so that no data is lost/overlapped we were doing this
x
if you use
created_at
as the timestamp, then your upsert realtime table and offline table won’t have duplicates right>
pinot apply timestamp to offline and realtime table to ensure even there are time overlap between two tables, but results will be unique
r
I was using , { "name" : "s_created_at_day", "dataType" : "TIMESTAMP", "fieldType" : "DATE_TIME", "format": "1DAYSEPOCH", "granularity": "1:DAYS" } as the timeColumn in the tableConfig of both realtime and offline table
but I want the logic for not getting records from more than 3 days in the table
basically retention(delete) policy. I also thought of one way as to use: "ingestionConfig": { "transformConfigs": [ { "columnName": "is_deleted", "transformFunction": "compareFlieds(op, 'd') OR (toEpochDays(now()) - toEpochDays(s_created_at) > 3)" }, /* …other transforms… _/ ] }, "upsertConfig": { "mode": "FULL", "deleteRecordColumn": "is_deleted", /_ … */ } but this is also not working on older segments that already has older data
this created at is coming from the data and I want to handle that
x
can you give an example? If batch table side is applying
s_created_at_day < today-3d
and realtime table is applying
s_created_at_day >= today-3d
, what would be the issue?
you just need to ensure you batch side is update to date for today-3d’s data
r
this s_created_at_day is not a field coming from kafka I am creating it on my own so maybe it is the default ts_ms that pinot will create if I want to use s_created_at coming from kafka for all of this boundary I have to use s_created_at as the timeColumnName, right?
"
s_created_at_day < today-3d
and realtime table is applying
s_created_at_day >= today-3d
, " how does Pinot manages that?
why is it showing me older data than 3 days? when timeColumnName is s_created_at and having the Retention of 3 days
retention of 3 days meaning we will guarantee the data ingested within 3 days will be available
it won’t guarantee the data out of 3 days will be deleted immediately
r
yes how can we achieve that?
x
what’s the time column you configured for this table
r
i want to delete the data in the current query that has
s_created_at
older than 3 days
x
can you try to use
s_created_at
r
yes I did that only
s_created_at
x
then you query the table with
s_created_at > now() - 3d
or
Copy code
SELECT * 
FROM tableName
WHERE s_created_at > ago('P3D')
r
okay so you're saying that in this query I only have s_created_at of last 3 days only?
x
why not
do you have new events coming with old timestamp
or even older than 3 days?
r
yeah but won't it increase the computation if for each query I will have to run this query as window function?
yeah my current kafka streams have data with s_created_at older than 3 days as new records are coming on s_updated_at for "op" : "u" using debezium
x
well you pay the cost for filtering, which is less than paying the keep deleting records every second
ok
but technically you may have up to 6 days data in your realtime table
that’s due to the out of order events from kafka
try out the filter first
r
okay lets hope we won't drop this project😅
x
well usually the filter is not that heavy weight comparing to window function
r
only blocker after all the POCs
x
I don’t see filter to be the big blocker here
how much data you have for 3 days
r
how are you suggesting to use filtering? like it will not filter the data that came today with s_created_at of yesterday but a few days later suppose on 4th may, my data will be reading from 2nd may with 30th april data but I dont want that
3 day data is almost 15GB
x
how many rows
r
totalDocs: 116127206
x
on may 4th you just add the predicate
WHERE s_created_at > ago('P3D')
of it will filter data from May2/3/4
you may have ingested data on May 1 or Apr 28, but the filter at runtime will not select those
r
oh filtering in query level. but my queries on the application will be reading from hybrid table i dont want to add that filter of
WHERE s_created_at > ago('P3D')
I want this filter just so that my data doesn't club between offline and online while adding data
x
you can run a query like
select count(*) from myTable where s_created_at > ago('P3D')
and comparing it with ``select count(*) from myTable` so the time difference is the extra latency for your predicate
how often you push data to offline table ?
and what’s the time column used for both realtime and offline table
try to configure
s_created_at
in both table configs for
timeColumnName
r
into offline table we will be pushing data at different frequency for different segments earliest is each hour
x
then when you query the table, pinot will query offline table as soon as possible the latest data are ready
usually it’s the latest timestamp - 1day
r
timeColumn earlier I used s_created_at_day(which was wrong as there was no field in kafka with that name but just pinot creating a new field) now I will try with s_created_at
x
you can create this field in realtime table with transform function
where this field
s_created_at
comes from?
r
this is coming from kafka
we can use that instead of
s_created_at_day
will this solve the issue?
because we cant do query level filtering as we will be querying on Hybrid Table and we dont want to reduce the time window for that
x
did you configure the time column for hybrid table?
r
there is nothing called hybrid table config right?
by configuring you meant to run the timeBoundary?
x
yes
r
yeah I have done that
x
you can create a REALTIME table and OFFLINE table with same table name, then they form a hybrid table automatically
then you can call broker timeboundary api to see that
and that’s the time boundary used to filter data from both OFFLINE and REALTIME table
think of it’s internally like:
Copy code
SELECT * FROM myTable_OFFLINE WHERE s_created_at_day < $TIME_BOUNDARY
UNION
SELECT * FROM myTable_REALTIME WHERE s_created_at_day >= $TIME_BOUNDARY
and this is your hybrid table definition
when you do
select count(*) from myTable
, the underlying logic to compute is similar to this:
Copy code
select count(*) from 
(
  SELECT * FROM myTable_OFFLINE WHERE s_created_at_day < $TIME_BOUNDARY
  UNION
  SELECT * FROM myTable_REALTIME WHERE s_created_at_day >= $TIME_BOUNDARY
)
r
Oh it is working like that.
Then the timeboundary is based on the time column that we have specified?
Of both offline table and realtime table?
Suppose I use
s_created_at
the timeColumn that is coming from kafka. It will work the same right?
Instead of using
s_created_at_day
x
yes, you need to ensure this column on both side
so
s_created_at_day
is only in REALTIME table not offline table?
r
Yeah I mean I just added a schema with granuarality of day, with a different name than the kafka topic
So maybe it is not reading the one from kafka topic
x
yeah, whatever works for you
I think the time handling should be clear to you
r
Yeah thanks for that. Will update you if we resolved the issue
One more doubt @Xiang Fu I had was how the time boundary is being calculated in hybrid table?
x
max value from the time column
r
okay