Another question somewhat related to the previous one is it Apache Pinot #troubleshooting

Another question, somewhat related to the previous...

Diogo Baeder

04/01/2022, 9:42 PM

Another question, somewhat related to the previous one: is it possible to have a date column? And to set it up as the

timeColumn

? I was looking at using

DAYS

as the type for this, but it doesn't seem correct - I want to use real dates and not "days since Epoch".

Mayank

04/01/2022, 9:43 PM

You can use DateTimeFieldSpec

Mayank

04/01/2022, 9:44 PM

https://docs.pinot.apache.org/configuration-reference/schema#datetimefieldspec

Diogo Baeder

04/01/2022, 9:45 PM

Is this for display purposes only? Or does it also affect the storing of values?

Neha Pawar

04/01/2022, 9:53 PM

if you use the sql standard date (yyyy-MM-dd HHmmss), then you ca use dataType

TIMESTAMP

, and internally it’ll be stored as long, but displayed as your date string. If you want to use any other format (like ISO), then you would use dataType

STRING

, it will be stored as string.

Neha Pawar

04/01/2022, 9:54 PM

for performance reasons, usually it’s better to have millisSinceEpoch (rounded to a nearest hour/day granularity as needed), so we can store as long

Diogo Baeder

04/01/2022, 9:57 PM

Ah, thanks @User, that's what I wanted to know! Like, what's the best way to store dates for performance, partitioning, rollups etc. But what if I define the schema as data type

LONG

, and format

1:DAYS:SIMPLE_DATE_FORMAT:yyyyMMdd

? Does this work, too?

Neha Pawar

04/01/2022, 9:59 PM

yup this is fine. this uses LONG and also has coarse granularity 👍

Diogo Baeder

04/01/2022, 10:00 PM

Would it work with

INT

, too, for less memory/space usage?

Neha Pawar

04/01/2022, 10:05 PM

it should work for yyyyMMdd with INT

Neha Pawar

04/01/2022, 10:09 PM

for performance, partitioning, rollups, what you have will work fine. For extensibility, millisSinceEpoch works best. You have day granularity right now, so you would have values rolled up to the nearest day. In future if you want to make the granularity finer, helps to have millisSinceEpoch . You could go from day granularity to finer granularity anytime, without having to change anything else

Diogo Baeder

04/01/2022, 10:33 PM

Got it. But there's absolutely zero chance we'll need finer granularity, that data we have has always been and will always be daily - it's a large ecosystem that depends on this data being daily. So I guess that would be the best approach for this case, right?

Neha Pawar

04/01/2022, 11:12 PM

yes it would in that case

Diogo Baeder

04/02/2022, 12:21 AM

Awesome, thanks! (And sorry for the long delay)

Open in Slack

Previous Next