No content

No content

https://runwayml.com/ai-magic-tools/frame-interpolation/

Interpolating Time Series Data on-demand

Time Series Data ● Track change of value/state mutation over time. ● Discrete (Ex: Orders) vs Continuous (Ex: Status) events. ● Append vs Upsert.

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

Time Series Data ● Sparse data. ● Missing data. ● Higher polling interval => Higher the granularity => More resources => Higher the cost.

Time Series Data

Data Visualization

No content

Climate Spiral: https://climate.nasa.gov

Time Series Charts

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

Use Case: Store uptime

Use case: Food delivery

Use case: Digital Storefronts

Store Status Events Dataset

Store Uptime

Customer facing Uptime Reports

Internal KPI: Online Stores count

Store Status Change Events Dataset timestamp store_id is_open should_be_open

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

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

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

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

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

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

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

Pinot Architecture

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

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

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

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

Under the hood

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

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

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 … …

Challenges: Blind spots

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

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#

Thank you!