Pinot: Schema for readable date time format

Question

I am consuming real time data of kafka into pinot. Date time format of data is given below:

"Mon Mar 14 15:58:24 IST 2022"

I am struggling to write "dateTimeFieldSpecs" in schema file. Can you please help me out.

Thanks,

Sukumar

Answer 1

you can use the transformation functions to do that for you.

First add the column to your regular schema.json. After that, add a transformation function to the table settings, usually, table.json. This function takes in the original column and re-formats it to Pinot's type.

Bare in mind though, it may be necessary to check server vs. local time zones, especially in real-time tables.

Check out these docs

Hope this helps!

Answer 2

at least you have two options below config corresponds to field in format yyyy-MM-ddTHH:mm:ss.SSSZ:

1. you can define this field as dimension one:

    {
        "name": "Date1",
        "dataType": "TIMESTAMP"
    }
]

2. define field as dateTime:

"dateTimeFieldSpec": [
        {
            "name": "DateFeild",
            "dataType": "TIMESTAMP",
            "format": "1:MILLISECONDS:EPOCH",
            "granularity": "1:DAYS"
        }
    ]

with transform function added to table config:

"transformConfig": [
    {
        "columnName": "DateFeild",
        "transformFunction": "FromDateTime(\"Date1\", 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z')"
    }
]