Does anyone know how to only store only single row per day (or per hour) if all the columns are same for a given row? - I get 30-50M rows per day where unique row combinations are < 1000. I want to store one unique combination for each hour. Yes the same row can repeat but in next hour --------------------------------------- e.g. if there are 3 row in 1 hour col_1, col_2, col_3, hour_1 col_1, col_2, col_3, hour_1 col_1, col_200, col_3, hour_1 Rows in DB should be for each hour col_1, col_2, col_3, hour_1 col_1, col_200, col_3, hour_1 e.g. if there are 3 row in 1 hour col_1, col_2, col_3, hour_1 col_1, col_2, col_3, hour_1 col_1, col_200, col_3, hour_1 col_1, col_2, col_3, hour_2 Rows in DB should be for each hour col_1, col_2, col_3, hour_1 col_1, col_200, col_3, hour_2 col_1, col_2, col_3, hour_2

See if this suits your need: https://docs.pinot.apache.org/basics/data-import/dedup

are you storing the time column at hour granularity as well?

Yes “dateTimeFieldSpecs”: [ { “name”: “eventTime”, “dataType”: “TIMESTAMP”, “format”: “1MILLISECONDSEPOCH”, “granularity”: “1:DAYS” || I can do “1:HOURS” } ],

If you wish to achieve it with dedup (assuming this is realtime table), you would need to add an epoch_hour column to the schema and

primaryKeyColumns

. Then enable dedup in your table config (refer to https://docs.pinot.apache.org/basics/data-import/dedup) With this, pinot will ingest only the first row with a given (event_type, channel, hour). And reject duplicates.

+1, you would want to replace your

eventTime

with an hourly version and use an ingestion transform function to floor it to hourly

What I want is to use filter is superset dashboards. Superset dashboard will need some DB to fetch possible values. Today I pull possible event type from the main table (this query is run by superset filter to populate dropdown) It is taking lot of time. I thought to also put all these filter values in a diff table and the filter will come from this smaller table..