apache / apache/pinot

'null' value support udf

Open
#7,606 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

I have a date of birth (dob) column in my source postgres table. I'm creating a realtime table in Pinot and using datatype:timestamp in my schema but this ends up defaulting the 'null' dob values to '1970-01-01' which is incorrect. So instead I'm using datatype:string and using the transformConfigs in table config to convert it.

Below is what I've tried out.

Error on the 'null' values when trying to use the 'todatetime' function.

`,
"ingestionConfig": {
"transformConfigs": [
{
"columnName": "dob",
"transformFunction": "toDateTime(date_of_birth,'yyyy-MM-dd')"
}
]
},`

Throws this error:

`java.lang.IllegalStateException: Caught exception while invoking method: public static java.lang.String org.apache.pinot.common.function.scalar.DateTimeFunctions.toDateTime(long,java.lang.String) with arguments: [null, yyyy-MM-dd]`

Attempted using Groovy but it does not work with Pinot builtin functions.

` "ingestionConfig": {
"transformConfigs": [
{
"columnName": "dob",
"transformFunction": "Groovy({date_of_birth != 'null' ? toDateTime(date_of_birth,'yyyy-MM-dd'): '1900-01-01'},date_of_birth)"
}
]
},`

Give this error:
`groovy.lang.MissingMethodException: No signature of method: Script1.toDateTime() is applicable for argument types: (java.lang.Long, java.lang.String) values: [304992000000, yyyy-MM-dd]`

Request is to add null support udf.

Pinot version: 0.8.0

Contributor guide

Open the contributing guide

Research direction

Start with org.apache.pinot.common.function.scalar.DateTimeFunctions.toDateTime and the transformConfigs ingestion configuration shown in the issue. Reproduce the conversion with a null date_of_birth and compare it with the Groovy example; done means the requested null-support UDF behavior works for the realtime table without the reported exception.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, postgresql
Domain
databases
Issue type
Feature
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.