I’m trying to setup a dateTimeFieldSpec with a dateTrunc and I’m getting an error:

{
      "name": "created_at_1_week_seconds",
      "dataType": "LONG",
      "defaultNullValue": 0,
      "transformFunction": "dateTrunc('week', created_at, 'MILLISECONDS')",
      "format": "1:SECONDS:EPOCH",
      "granularity": "1:SECONDS"
    },

Error:

Exception in getting arguments for transform function 'dateTrunc('week', created_at, MILLISECONDS)' for column 'created_at_1_week_seconds'"
}

This works:

{
      "name": "created_at_1_day_seconds",
      "dataType": "LONG",
      "defaultNullValue": 0,
      "transformFunction": "toEpochSecondsRounded(fromEpochDaysBucket(toEpochDaysBucket(created_at, 1), 1), 1)",
      "format": "1:SECONDS:EPOCH",
      "granularity": "1:SECONDS"
    },

Something about the dateTrunc function doesn’t seem to be compatible. Looking through the docs and the code it looks like dateTrunc may not be listed as a valid transform function in all use cases. The main issue is I want a toEpochDaysBucket(7) that lines up with the calendar week (Sunday or Monday) instead of the epoch week (Thursday). Any ideas?

I think the issue is that dateTrunc implements internal

BaseTransformFunction

function not

ScalarFunction

function which can be used in the

transformFunction

field.

I see. Is that an alternate query anyone can think of? Would there be a groovy syntax that would work?

hmmm

I’ll try that. How do I retrieve the stack trace?

check pinot server log

@Jai Patel won't this just truncate to the day? Looks like you wanted it by week.

@Elon we are creating time buckets to help with segmenting our data for different graphical views. we are able to use the time bucket functions for 15 minutes, hour, and day. But the time bucket function has an undesirable offset for week. I think datetrunc will work and we already use a presto datetrunc fucntion to get the right behavior when using reconcile with offline tables. I want the same behavior with realtime.

Sounds good, we can work on this.

@Xiang Fu I tried the syntax with the syntax without the single quotes and got the same error. I checked the controller and server containers and didn’t see any logs/exceptions connected with this.

hmm, it should be in server log, so it’s just silently has no msg consumed?

I didn’t see anything. I tried a few times with the log running in -f

hmm, have you changed the log level to info

we can't:) logging team said we generate too many logs

oic 😛

but it should generate a warn or error msg, right? 😃

like quickstart and create a table

yep, @Jai Patel you can use quickstart to do that

@Neha Pawar not sure if you have more insight

the error is happening on schema import, so can’t even get to the point of creating a table.

yep

where do the logs dump to?

if you run a local container you can just tail the logs from the docker container - if you run via intellij should be stdout/stderr

Well… that was a bit of a surprise. Schema addition worked when run locally on Pinot 0.7.1. I’ll try again on Pinot 0.6.0.