Support Questions
Find answers, ask questions, and share your expertise

Apache NiFi Execute SQL processor bug

Apache NiFi Execute SQL processor bug

Contributor

Hello! 

I've ran into a problem with use Execute SQL NiFi (version 1.7.1) processor .

The problem is in how this processor extracts a date data ----- from a database table. When we use a setting Use Avro Logical Type with true mode then it takes a data from db table with underlying format (and for future needs a mode = true would be a better solution for me), BUT with this mode we have a problem with date data type - it means that it takes this date and do minus one day from this day. For better understanding I'll show you an example with a screenshots. 

 

1. Select from db table = true result 

1db_c.PNG

2. Execute SQL proc with Avro Logical Type = true -> gives underlying data format from db table - it's ok for our needs

1.2.nifi_c.png

3. Same sql query like in db client

2.1.nifi_c.png

4. But WRONG Date (minus 1 day) in outgoing flow file

1.3.nifi_c.png

 

Then if we change Avro Logical Mode to = false we'll see that date would be right BUT data type would be a STRING -> but this way isn't right and convenient for me.

 

5. Execute SQL proc with Avro Logical Type = false -> converts data from db to STRING data type - it's not ok for our needs

2.2.nifi_c.png

6. But gives for us the right data like from db client (the sql query didn't change)

 

2.3.nifi_c.png

 

But when I did cast the date type data to timestamp type my suggestion about timezone was totally confirmed!

7. SQL query with cast to timestamp

3.1.nifi_c.png

8. Time zone -3 hours (our countries true timezone on the Apache NiFi server)

3.2.nifi_c.png

Here's a question - How can I solve this problem without making some crutches ) Thanks!

4 REPLIES 4

Re: Apache NiFi Execute SQL processor bug

Contributor

I think that this is a bug of 1.7.1 version of Execute Sql proc because I've tried to emulate this situation on 1.11.1 NiFi version and have no any problem with date. But I still have no idea how to solve this issue in 1.7.1 version by normal way.

Re: Apache NiFi Execute SQL processor bug

Contributor

I've found a solution, but I think it's not a better way to deal with this.

I've added an option near date type column in sql query (for pgsql) like:


select deal_date at time zone 'UTC-6' as deal_date 

from schema_name.table_name

 

Why UTC-6 - for equality with 00h00m00s of my country (I've found this by empiric way) then I put this timestamp data to pgtable in column with date data type and that's was fine. 

 

If you have the best way to deal with this issue just let me know

Re: Apache NiFi Execute SQL processor bug

Contributor

A little bit later we've found that we can cast the date type data just to timestamp without any manipulation with timezones and it will ride into a date type column with correct data. Example:

 

select deal_date::timestamp as deal_date 

from schema_name.table_name

 

But if think that in this case you can change column's data type in the table as well and it would be fine - you wrong -> In this case your date data become incorrect again )

 

Re: Apache NiFi Execute SQL processor bug

Contributor

And also we've found that this problem is an official bug of ExecuteSQL in Apache NiFi 1.7.1  version -> it fixed in 1.10 and later versions.