I have created a hybrid table in Apache Pinot that...
# troubleshooting
a
I have created a hybrid table in Apache Pinot that ingests data from a Kafka topic. One of the columns,
valid_from
, contains timestamp values in the format:
2025-04-30T09:06:10.597000Z
. To derive additional time-based columns (
day
,
week
,
month
, and
date
) from this timestamp, I configured a set of transformation functions in the table config file. Here's what I’ve done: "transformConfigs": [ { "columnName": "valid_from_millis", "transformFunction": "FromDateTime(valid_from, 'yyyy-MM-dd''T''HHmmss')" }, { "columnName": "valid_until_millis", "transformFunction": "FromDateTime(valid_until, 'yyyy-MM-dd''T''HHmmss')" }, { "columnName": "valid_from_day", "transformFunction": "dateTrunc('day', valid_from_millis)" }, { "columnName": "valid_from_week", "transformFunction": "dateTrunc('week', valid_from_millis)" }, { "columnName": "valid_from_month", "transformFunction": "dateTrunc('month', valid_from_millis)" }, { "columnName": "valid_from_date", "transformFunction": "dateTimeConvert(valid_from_millis, '1MILLISECONDSEPOCH', '1DAYSSIMPLE_DATE_FORMAT:yyyy-MM-dd', '1:DAYS')" } ] My Approach: 1. Convert the ISO 8601 timestamp (
valid_from
) into milliseconds using
FromDateTime
. 2. Apply
dateTrunc
functions on the millisecond value to extract the day, week, and month. 3. Use
dateTimeConvert
to derive a
yyyy-MM-dd
formatted date string. Problem: Despite this setup, I’m seeing incorrect values in the table for these derived columns. Below is screenshot of sample values that i am getting in table
g
Got the same issue, still trying to figure it out, @Xiang Fu suggested me to try fromEpochDays(toEpochDays(valid_from_millis)), but it didn't work out for me
x
for ISO8601 timestamp string, you can use
convertTimestampToMillis(valid_from)
to convert the String format
valid_from
to millis epoch long value
valid_from_millis
then use
fromEpochDays(toEpochDays(valid_from_millis))
to round the value
a
I am not finding convertTimestampToMillis and toEpochDays method in pinot documentation. Also, how to fetch date from valid_from timestamp? I am using this method to fetch date : columnName": "valid_from_date", "transformFunction": "dateTimeConvert(valid_from_millis, '1MILLISECONDSEPOCH', '1DAYSSIMPLE_DATE_FORMAT:yyyy-MM-dd', '1:DAYS')" but it is generating wrong output as shown in the screenshot.
Tried using convertTimeStampToMillis and getting this error: {"code":400,"error":"Invalid transform function 'convertTimestampToMillis(valid_from)' for column 'valid_from_millis'"
g
fromEpochSeconds(toEpochSeconds(DATETIMECONVERT(CreationDate,'1MILLISECONDSEPOCH', '1SECONDSEPOCH', '1:SECONDS'))) so something like this?
given that CreationDate is ISO 8601 string timestamp
x
You can do this:
Copy code
public static void main(String[] args) {
    String tsStr = "2022-09-27 18:05:32.222";
    String pattern = "yyyy-MM-dd HH:mm:ss.SSS";
    long ts = fromDateTime(tsStr, pattern);
    System.out.println("ts: " + ts);
    System.out.println("fromDateTime: " + fromDateTime(tsStr, pattern));
    System.out.println("fromEpochSeconds(toEpochSeconds(fromDateTime(tsStr, pattern))) = " + fromEpochSeconds(toEpochSeconds(fromDateTime(tsStr, pattern))));
    System.out.println("fromEpochDays(toEpochDays(fromDateTime(tsStr, pattern))) = " + fromEpochDays(toEpochDays(fromDateTime(tsStr, pattern))));
  }
The output is
Copy code
ts: 1664301932222
fromDateTime: 1664301932222
fromEpochDays(toEpochDays(fromDateTime(tsStr, pattern))) = 1664236800000
so
fromDateTime(valid_from, 'yyyy-MM-dd HH:mm:ss.SSS')
I think I will add a few more functions just for ISO8601 timestamp to make the conversion simpler
g
so i got it working with fromDateTime(CreationDate, 'YYYY-MM-dd''T''HHmmss.SSSSSS'), but it is strange that it is partially working
2024-02-01T000556.4954502 -> this fails 2024-02-01T002704.927811 - this is ok 2024-02-01T005219.948 - this is ok
x
you need 7 S in the pattern?
g
@Aman Satya check the format of the date, as mine was not serialized according to ISO8601 and trailing 0s were removed, meaning 2024-02-01T005219.0000000 is serialized by .NET as 2024-02-01T005219, which fails the conversions
after i fixed all dates to come as 2024-02-01T005219.0000000 i no longer have issues with the above functions
a
Can same method be used to fetch week, month and date?
g
i used the following pattern
fromEpochMinutes(toEpochMinutes(fromDateTime(Date, 'YYYY-MM-dd''T''HHmmss.SSSSSSS))) fromEpochHours(toEpochHours(fromDateTime(Date, 'YYYY-MM-dd''T''HHmmss.SSSSSSS)))
a
okay while following this what should be the data type of valid_from_millies ? I have set it as LONG.
g
i have it as Timestamp
{ "name": "creation_date", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:MILLISECONDS", "notNull": true }, { "name": "creation_date_hour", "dataType": "TIMESTAMP", "format": "1HOURSEPOCH", "granularity": "1:HOURS", "notNull": true }, { "columnName": "creation_date", "transformFunction": "CreationDate" }, { "columnName": "creation_date_hour", "transformFunction": "fromEpochHours(toEpochHours(fromDateTime(CreationDate, 'YYYY-MM-dd''T''HHmmss.SSSSSSS)))" },
x
Timestamp
type is stored as
LONG
millis epoch value internally
g
as far as indexing goes if we have a milliseconds column that is used for range queries and not exact match by date, is it a good idea to add it to rangeIndexColumns and also to noDictionaryColumns in order to avoid unnecessary memory/indexes, because i see by default Pinot created a dictionary index as well