Apache NiFi working with nulls/empty in date/datetime/timezone columns
hi there,
So I need your help with something. So I am extracting 4 columns out of a database. Out of these 4 columns, two columns are DATE in Oracle and I want to convert them into DATETIME (as they are going to be inserted into BigQuery). In addition to this, I am using an UpdateRecord to generate a new column and for that, I am using the following AVRO Schema:
{
"namespace": "example.avro",
"type": "record",
"name": "my_relevant_table",
"fields": [
{
"name": "ID_REQUEST",
"type": [
"string",
"null"
]
},
{
"name": "ID_PROCESS",
"type": [
"string",
"null"
]
},
{
"name": "ARCHIVE_DATE",
"type": [
"null",
{
"type": "long",
"logicalType": "local-timestamp-millis"
}
]
},
{
"name": "EXEC_DATE",
"type": [
"null",
{
"type": "long",
"logicalType": "local-timestamp-millis"
}
]
},{
"name": "LOAD_DATE",
"type": [
"null",
{
"type": "int",
"logicalType": "date"
}
]
},
]
}Now, as you can imagine, I will encounter some null value within the ARCHIVE_DATE column, because this is a normal behavior. If I let the flow execute as it is, the AVRO File get's generated even with the NULL value.
However, I would like to change something. If within that column I have a value, I would like to modify the value and assign it a Timezone ... as the actual value gets converted into UTC and I do not want that.
How can I achieve this using the UpdateRecord Processor? I am pretty sure that I will have to use an ifElse statement but in terms of values, I have tried several things and all of the ended with an error:
${field.value:isNull():ifElse('0', ${field.value:toNumber():toDate('yyyy-MM-dd HH:mm:ss.SSS','Europe/Bucharest'):format('yyyy-MM-dd HH:mm:ss.SSS','Europe/Bucharest')})}The thing is that if I use '0', it works and assigns the value 0 if the field value is empty or null (the column contains no value, no space, not even the string null). However, if I replace '0' with '' I receive an error message for NumberConversion.
In this case, how can I make sure that the values from within that column remain null/empty if they are like that and otherwise, apply the Europe/Bucharest timezone for that specific field value.
