Hi all, hope this is the place to ask, if not then...
# general
r
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
b
Druid will only use the default time column __time for filtering, if you do not use __time in filter, it will result in full table scan. Yes you could have secondary partition but filtering on that might not help as per the above statement. you might want to use both __time and then the secondary partition timestamp column.
r
Thanks Bibek. Yeah I did think that might be the case but I was hoping that there might be some other way round it/different behaviour with the secondary partitions. The issue with filtering on __time is that we aren't able to say when the transaction happened, an event received today could have happened today, yesterday, or 5 years ago. There's not a way to work that out without doing an initial search for the first occurrence of event to get the 'from' __time. I would guess the best bet then would be create a duplicate datasource with the transaction time as the __time, which is something I was hoping to avoid, but might just be the way forward.
b
Yes, Doing the transaction time as primary time column __time seems the best approach, Lets see if we get a better solution.
j
This sounds like the classic "received_date vs event_date" dilemna ... for which there unfortunately is no immediately elegant solution in Druid. If you are looking for "latest" information from the timestamp field, then you might consider creating a separate "latest value" table that is populated through a frequently run batch job. You could also create a series of tables, one for each "received month", that are individually time partitioned based on event_date, use table level UNION ALL in your queries to (performantly) combined the tables together, and through retention rules you can drop off the tables as they age. But if your use case is something different, then please describe your use case in more detail so we can think of other possible solutions to address it.
r
Hi John, thanks for chiming in. Yeah you’re spot on regarding the received date vs event date dilemna. One thing that I left out is that each event has a unique eventId value. I was thinking about the possibility of having the current table 'as is' and then creating a new table which will have the trxTime (transaction time) as __time and eventId. Then you should be able to filter on the trxTime and then pull the fields you want from the ‘full’ table through a join on eventId. You could then keep both tables in-sync by ingesting from the same Kafka stream but change the ingestion spec to just have the fields we're wanting for the new table. I would imagine it would work, and it would prevent the data being duplicated twice between the two datasources, but what the performance of it would be like, I’m not sure. The other thing I wouldn’t be sure on, though it might be a non-issue, is if you’re ingesting data into Druid and the dates aren’t ‘close’ (ie. You have one date today and then the next event was a year ago: although that’s not common, it’s not uncommon either) what impact, if any, that would have as it would need to write to old segments? The reality is unfortunately it’s not just recent data that would be getting searched, it will be historical data too. We might get a request for an event which happened a month ago, but depending certain factors we might have to search for all transactions which are linked which can go back years.
j
"late arrival" data -- you don't write to existing segments (segments are immutable in Druid) ... but you would create a new segment in that old time interval to hold the record that just came in. This creates fragmentation, which then prompts compaction to be run. This is all okay and standard Druid operation.