Dear all, thank you very much for building Apache ...
# generalo
Oliver
09/14/2020, 9:59 AMDear all, thank you very much for building Apache Pinot - it is indeed a great tool! One question that came up during our evaluation of Pinot is how to handle year over year (or period over period) comparisons in Pinot (or in the Viz Tooling). How would you typically do this? In SQL, one normally would either use WINDOW functions (like LAG with PARTITION OVER) oder self-JOINS. Any advice is highly appreciated 🙂 THANKS!
🙏 2
👍 1
k
Kenny Bastani
09/14/2020, 1:35 PMGood question. Yes, there's an easy solution here. Time granularity queries will aggregate your data period to period for comparison. Let me get an example.
Kenny Bastani
09/14/2020, 1:37 PM Copy code
SELECT EVENT_TYPE,
DATETIMECONVERT(EPOCH_TIMESTAMP, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS'),
COUNT(*)
FROM "stormEvents"
WHERE EVENT_TYPE IN ('Hail',
'Thunderstorm Wind')
GROUP BY EVENT_TYPE,
DATETIMECONVERT(EPOCH_TIMESTAMP, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS')
ORDER BY COUNT(*) DESC
LIMIT 50000;Kenny Bastani
09/14/2020, 1:37 PMI used this query for the following chart:
Kenny Bastani
09/14/2020, 1:40 PMFor the period granularity, check out our documentation.
For ingestion and query transformations: https://docs.pinot.apache.org/users/user-guide-query/supported-transformations#datetime-functions
o
Oliver
09/14/2020, 1:43 PMHi Kenny, thanks, this looks great! one follow-up question: to get out the time series, this feels pretty straight forward 🙂 What about period over period comparison kpis - e.g., I want to report the 2 bold KPIs in a table:
Sales 2018: 100m
Sales 2019: 150m
Sales uplift: +50%
Similar for Week over previous year's week. Do you solve this in the VIz tool or directly in SQL?
k
Kenny Bastani
09/14/2020, 1:44 PMAh I see.
Kenny Bastani
09/14/2020, 1:46 PMI use the visualization tool directly. Our SQL support has feature parity for anything that is immutable (read-only) queries.
Kenny Bastani
09/14/2020, 1:48 PMThere are a few different ways to do this with transforms. You can do a granularity ingest transformation that creates a dynamic column for datetime in your rows.
Kenny Bastani
09/14/2020, 1:52 PMI meant to paste this screenshot from table configuration.
Kenny Bastani
09/14/2020, 1:52 PMLet me know if you're still struggling, we can jump into a private DM and figure it out.
o
Oliver
09/14/2020, 1:55 PMThanks for the offer - maybe one last question here: Where would you do the transformation to divide (150/100-1) ?
k
Kenny Bastani
09/14/2020, 2:06 PM*DIV(col1, col2)*
o
Oliver
09/14/2020, 2:08 PMah, ok got it. With the configuration ^^ both figures would appear in the same row?
Oliver
09/14/2020, 2:09 PMOtherwise to get the figures into 1 row, in traditional sql it is a bit more complicated (with self-joins or using window function like lag)
k
Kishore G
09/14/2020, 3:05 PM
Oliver, you need to use post aggregation function to perform what you are looking for. You might want to look at ThirdEye tool as well, it does all the things you are looking for