Hi all, hope this is the place to ask, if not then could you point me in the correct direction, it would be appreciated.
We’ve have two time fields, __time (which is the time the event is processed in our system) and the transaction time, when the event actually occurred. The transaction time is stored as is laid out in the docs (having it as millisecond timestamps stored as long types):
https://druid.apache.org/docs/latest/ingestion/schema-design/#secondary-timestamps.
The issue is that we are now wanting to filter on the transaction time which can differ to __time by years in the past and future, meaning if we want to filter on this time field we end up performing a full table scan, which ends up taking a long time to run.
Reading previous questions around this and the documentation it looks like we should have a second partition on the transaction time field:
https://druid.apache.org/docs/latest/ingestion/partitioning/#secondary-partitioning. I just wanted to check that is correct and I’m not overlooking something.
Many thanks,
Robert