Hi folks A question about `dateTimeFieldSpecs` Is the `TIMES Apache Pinot #general

Hi folks! A question about `dateTimeFieldSpecs`: I...

Diogo Baeder

11/12/2021, 6:31 PM

Hi folks! A question about `dateTimeFieldSpecs`: Is the

TIMESTAMP

type something that will stay in Pinot? Because @User gave me a nice hint about that but we don't see that in the docs, so I'm unsure whether I can safely use it and know that it won't get removed on future Pinot versions

Neha Pawar

11/12/2021, 6:34 PM

it is here to stay. It’s part of the datatypes in this page https://docs.pinot.apache.org/basics/components/schema#data-types

Diogo Baeder

11/12/2021, 6:34 PM

Ah, nice! Thanks!

Diogo Baeder

11/12/2021, 6:35 PM

@User strange that it's not defined here though: https://docs.pinot.apache.org/configuration-reference/schema#datetimefieldspec

Neha Pawar

11/12/2021, 6:35 PM

good catch 🙂 will fix it, and maybe also add some examples

Diogo Baeder

11/12/2021, 6:37 PM

Awesome, thanks a lot! ❤️

Diogo Baeder

11/12/2021, 6:50 PM

@User what format should be used though when publishing values through Kafka? I'm using, for example,

2020-04-04 00:00:00 UTC

, as a string, and it's not saving on Pinot

Neha Pawar

11/12/2021, 6:56 PM

should be

Copy code

"dateTimeFieldSpecs": [
    {
      "name": "time_col_name",
      "dataType": "STRING",
      "format": "1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyyMMdd HH:mm:ss z",
      "granularity": "1:MILLISECONDS"
    }
  ]

https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Diogo Baeder

11/12/2021, 8:21 PM

Oh... I thought the dataType would be

TIMESTAMP

, am I wrong?

Neha Pawar

11/12/2021, 9:39 PM

@User does this

2020-04-04 00:00:00 UTC

string also work for

TIMESTAMP

Jackie

11/12/2021, 9:40 PM

No, you need to remove

UTC

Neha Pawar

11/12/2021, 9:40 PM

so it’s

yyyyMMdd HH:mm:ss

and

epoch millis

that can use

TIMESTAMP

Jackie

11/12/2021, 9:40 PM

Yes

Diogo Baeder

11/12/2021, 9:40 PM

Oh, just

2020-04-04 00:00:00

then? And in terms of performance, how does that compare to using

SIMPLE_DATE_FORMAT

with a date-time format?

Jackie

11/12/2021, 9:40 PM

Standard

TIMESTAMP

format would be

yyyy-MM-dd HH:mm:ss.SSS

Diogo Baeder

11/12/2021, 9:41 PM

I mean, comparing the dataTypes, it would be

TIMESTAMP

STRING

, would there be a significant performance difference?

Jackie

11/12/2021, 9:41 PM

TIMESTAMP

is stored as long (millis since epoch), so it has better performance than string

Diogo Baeder

11/12/2021, 9:42 PM

Ah, that's very relevant! 🙂

Diogo Baeder

11/12/2021, 9:42 PM

You guys rock so hard! Thank you very very much! ❤️

❤️ 1

Jackie

11/12/2021, 9:42 PM

The performance improvement is depending on the queries, but long should always be faster than string

Jackie

11/12/2021, 9:42 PM

Pleasure to help

Diogo Baeder

11/12/2021, 9:42 PM

Indeed, cause it's numeric and all that... awesome!

Diogo Baeder

11/13/2021, 2:11 AM

It worked fine here for me, thanks! The only odd thing I found about it is that, when getting the value from Pinot - not sure if because I'm using it through SQLAlchemy -, I'm getting a quoted string, like

"2020-01-01 00:00:00.000"

, instead of just the string with the datetime format. No big deal really, just a strange difference I found compared to when I was using

SIMPLE_DATE_FORMAT

earlier today. Thanks a lot, have a great weekend, folks!

Open in Slack

Previous Next