Interpolating Time Series Data On Demand (Lakshmanan Velusamy, Stealth Startup) | RTA Summit 2023

Many real-world datasets are time series in nature, continuously recording measurements and status of entities over time. In time series datasets, missing data and gaps can occur frequently due to system failures or data processing errors. Time series interpolation (aka Gap filling) is a recently introduced feature in Pinot 0.11 that allows you to fill in missing data in your time series dataset.

When using gap filling in Pinot, you can configure the size of the gap to be filled, the type of interpolation algorithm to use (such as linear or spline), and the maximum number of missing data points that can be filled. Pinot also provides options to customize the behavior of gap filling for different use cases, such as supporting multi-dimensional gap filling and handling noisy data.

Gap filling is a powerful feature in Pinot that helps you to work with incomplete data sets more effectively and make more accurate predictions. It can also help you reduce the polling interval to store fewer data to improve storage cost and performance.

Transcript

3. https://runwayml.com/ai-magic-tools/frame-interpolation/
4. Interpolating Time Series Data on-demand
5. Time Series Data • Track change of value/state mutation over time. • Discrete (Ex: Orders) vs Continuous (Ex: Status) events. • Append vs Upsert.
6. Time Series Data: Examples • Real-time vehicle data: speed, braking, acceleration, etc • Server performance metrics: CPU, memory, network usage, etc • Industrial equipments: Telemetry from IOT sensors
7. Time Series Data • Sparse data. • Missing data. • Higher polling interval => Higher the granularity => More resources => Higher the cost.
8. Time Series Data
9. Data Visualization
11. Climate Spiral: https://climate.nasa.gov
12. Time Series Charts
13. Past Present Future What happened? Why did it happen? What should I have done? What is happening? Why is it happening? What should I do now? What will happen? Why will it happen? What should I do next? What Why Should Time Series Charts
14. Use Case: Store uptime
15. Use case: Food delivery
16. Use case: Digital Storefronts
17. Store Status Events Dataset
18. Store Uptime
19. Customer facing Uptime Reports
20. Internal KPI: Online Stores count
21. Store Status Change Events Dataset timestamp store_id is_open should_be_open
22. Computing Store Uptime at Scale • High fidelity pre-aggregation of events ◦ Less flexible ◦ High cost • Interpolating raw state transitions on-demand ◦ More flexibility ◦ Low cost
23. Total Expected Store Uptime store_id1 store_id2 store_id3 store_id4 2022/07/01 11:00 2022/07/01 10:00 2022/07/01 16:00 2022/07/01 21:00 10 hours 6 hours 10 hours 6 hours
24. Actual Store Uptime store_1 store_2 store_3 store_4 2022/07/01 11:00 2022/07/01 10:00 2022/07/01 16:00 2022/07/01 21:00 10 hours 6 hours 9.5 hours 6 hours 2022/07/01 13:00 2022/07/01 13:30
25. Sample Dataset store_id event_ts is_open should_be_open store_1 2023-04-01 11:00 true true store_1 2023-04-01 21:00 false false store_2 2023-04-01 10:00 true true store_2 2023-04-01 16:00 false false store_3 2023-04-01 11:00 true true store_3 2023-04-01 13:00 false true store_3 2023-04-01 13:30 true true store_3 2023-04-01 21:00 false false store_4 2023-04-01 10:00 true true store_4 2023-04-01 16:00 false false
26. Total Store Uptime With Gapfill store_s1 store_s2 store_s3 store_s4 2023/04/01 10:00 2023/04/01 21:00 10 hours 6 hours 10 hours 6 hours
27. Total Expected Store Uptime With Gapfill store_s1 store_s2 store_s3 store_s4 2022/07/01 10:00 2022/07/01 21:00 10 hours 6 hours 10 hours 6 hours
28. Query: Uptime of a specific store SELECT time_col, SUM(*) as total_store_uptime_minutes FROM ( SELECT GapFill(time_col, '1:MILLISECONDS:EPOCH',1656633600,1656720000,'1:MINUTES', '1:DAYS', Fill(status, 'FILL_PREVIOUS_VALUE'), TimeSeriesOn(store_id)), status FROM ( SELECT DATETIMECONVERT(event_ts, '1:SECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MINUTES') as time_col, LastWithTime(is_open, created_at, 'BOOLEAN') as status FROM store_status_events_tbl WHERE (event_ts BETWEEN 1656633600 AND 1656720000) AND (store_id = 'store_id1') GROUP BY time_col ORDER BY time_col ASC LIMIT 100000 ) LIMIT 2000000 ) WHERE status = 'true' GROUP BY time_col LIMIT 2000
29. Pinot Architecture
30. Step 1: Select raw events SELECT time_col, SUM(*) as total_store_uptime_minutes FROM ( SELECT GapFill(time_col, '1:MILLISECONDS:EPOCH',1656633600,1656720000,'1:MINUTES', '1:DAYS', FILL(status, 'FILL_PREVIOUS_VALUE'), TIMESERIESON(store_id), status FROM ( SELECT DATETIMECONVERT(event_ts, '1:SECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MINUTES') as time_col, LastWithTime(is_open, created_at, 'BOOLEAN') as status FROM store_status_events_tbl WHERE (event_ts BETWEEN 1656633600 AND 1656720000) AND (store_id = 'store_id1') GROUP BY time_col ORDER BY time_col ASC LIMIT 100000 ) LIMIT 2000000 ) WHERE status = 'true' GROUP BY time_col LIMIT 2000
31. Step 2: Fill the gaps SELECT time_col, SUM(*) as total_store_uptime_minutes FROM ( SELECT GapFill(time_col, '1:MILLISECONDS:EPOCH',1656633600,1656720000,'1:MINUTES', '1:DAYS', Fill(status, 'FILL_PREVIOUS_VALUE'), TimeSeriesOn(store_id)), status FROM ( SELECT DATETIMECONVERT(event_ts, '1:SECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MINUTES') as time_col, LastWithTime(is_open, created_at, 'BOOLEAN') as status FROM store_status_events_tbl WHERE (event_ts BETWEEN 1656633600 AND 1656720000) AND (store_id = 'store_id1') GROUP BY time_col ORDER BY time_col ASC LIMIT 100000 ) LIMIT 2000000 ) WHERE status = 'true' GROUP BY time_col LIMIT 2000
32. Step 3: Aggregate SELECT time_col, SUM(*) as total_store_uptime_minutes FROM ( SELECT GapFill(time_col, '1:MILLISECONDS:EPOCH',1656633600,1656720000,'1:MINUTES', '1:DAYS', Fill(status, 'FILL_PREVIOUS_VALUE'), TimeSeriesOn(store_id)), status FROM ( SELECT DATETIMECONVERT(event_ts, '1:SECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MINUTES') as time_col, LastWithTime(is_open, created_at, 'BOOLEAN') as status FROM store_status_events_tbl WHERE (event_ts BETWEEN 1656633600 AND 1656720000) AND (store_id IN ('store_id1') GROUP BY time_col ORDER BY time_col ASC LIMIT 100000 ) LIMIT 2000000 ) WHERE status = 'true' GROUP BY time_col LIMIT 2000
33. Query: Total online stores SELECT time_col, COUNT(*) as total_store_uptime_minutes FROM ( SELECT GapFill(time_col, '1:MILLISECONDS:EPOCH',1656633600,1656720000,'1:MINUTES', '1:DAYS', FILL(status, 'FILL_PREVIOUS_VALUE'), TIMESERIESON(store_id)), status, store_id FROM ( SELECT DATETIMECONVERT(event_ts, '1:SECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MINUTES') as time_col, LastWithTime(is_open, event_time, 'BOOLEAN') as status, store_id FROM enriched_store_staus_events WHERE (event_ts BETWEEN 1656633600 AND 1656720000) GROUP BY time_col, store_id ORDER BY time_col ASC LIMIT 100000 ) LIMIT 2000000) WHERE status = 'true' GROUP BY time_col LIMIT 2000
34. Under the hood
35. Request Flow Pinot Broker Server BrokerReducerService QueryRouter Pinot Server n …….. 2.Stripped Sql Query Result Set n Result set 0 Result set n …….. 1. Gapfill Sql Query 5.GapFilledResult Result Set 0 2. Stripped Sql Query GapfillProcessor 4.1 Gapfilling 4.2 Filtering 5.1 Aggregation 5.2 Filtering Reduced Result 3 Pinot Server 0
36. Execution Plan: Server Side Pinot Server 0 Filter Operator Aggregate Operator Combine Operator Pinot Server 1 Filter Operator Aggregate Operator Projector Operator 2023-04-01 11:00, store_id_0, true 2023-04-01 11:00, store_id_1, true 2023-04-01 13:00, store_id_1, false Transform Operator Transform Operator 2023-04-01 13:30, store_id_1, true 2023-04-01 21:00, store_id_0, false 2023-04-01 21:00, store_id_1, false Same as above Same as above Same as above Same as above Same as above Same as above Broker
37. Execution Plan: Broker Side Pinot Broker Reducer Gapfill 2023-04-01 11:00, store_id_0, true 2023-04-01 11:00, store_id_1, true 2023-04-01 13:00, store_id_1, false 2023-04-01 13:30, store_id_1, true 2023-04-01 21:00, store_id_0, false 2023-04-01 21:00, store_id_1, false Filter Aggregate 2023-04-01 11:00, store_id_0, true 2023-04-01 11:01, store_id_0, true 2023-04-01 11:02, store_id_0, true … 2023-04-01 11:00, store_id_1, true 2023-04-01 11:01, store_id_1, true 2023-04-01 11:02, store_id_1, true … 2023-04-01 13:00, store_id_1, false 2023-04-01 13:01, store_id_1, false 2023-04-01 13:01, store_id_1, false … 2023-04-01 11:00, store_id_0, true 2023-04-01 11:01, store_id_0, true 2023-04-01 11:02, store_id_0, true … … 2023-04-01 11:00, store_id_1, true 2023-04-01 11:01, store_id_1, true 2023-04-01 11:02, store_id_1, true … …
38. Challenges: Blind spots
39. Future Work • Push down the gapfill logic from pinot broker to pinot server. ◦ Leverage data partitioning if events are co-located. ◦ Running the gapfill logic on the pinot servers where the data live. ◦ Reduce the data transmission. ◦ Increase the parallelism and performance of gapfill. • Leverage V2 engine. • Add support for advanced interpolations: Linear, Polynomial, Spline etc • Time series extrapolation
40. To learn more… • Documentation: https://docs.pinot.apache.org/users/user-guide-query/gap-fill-functions • Blog: https://startree.ai/blog/gapfill-function-for-time-series-datasets-in-pinot • RFC: https://docs.google.com/document/d/1FQbsTVywWLlmFaeUcYqDkb-XRWC pVhZe10BZyD2VzcU/edit#
41. Thank you!