Why do ExecuteSQLRecord and CSVRecordSetWriter updated the time zone of datetime values?

[#1]

Hello! I am new to NiFi. I hope someone here can advise me about my problem with time zone. I have these processors:

ListDatabaseTables -> GenerateTableFetch -> ExecuteSQLRecord (writing to csv file by CSVRecordWriter) -> ... ... PutSQL (loading csv file to MySQL using Load Data command)

The source DB is Oracle. CSVRecordWriter has the following properties:

Schema Write Strategy -> Do Not Write Schema
Schema Access Strategy -> Inherit Record Schema
Schema Name -> ${schema.name}
Schema Text -> ${avro.schema}
Date Format -> yyyy-MM-dd
Time Format -> HH:mm:ss
Timestamp Format -> yyyy-MM-dd HH:mm:ss

My source DB and the target DB are both in US east time zone. However, I noticed that the output of ExecuteSQLRecord having time values converted to UTC (added to 5 hours). That results in the wrong time values in the target DB. There may be some ways to convert each date/time column individually, but that will require a huge amount of development effort. 

Is there a way to handle this issue properly at global level, or at least at table level? Please note that Time Format needs to be acceptable to MySQL Load Data. 

Thank you in advance!

Gary

[#2 · Accepted solution]

I figured out a workaround myself and hope it's useful for others. I use the following query to generate another query to be executed by the next step. This query converts Oracle date values to the preferred strings at the global level so it save the development effort at column level or table level.

SELECT LISTAGG( CASE WHEN COLUMN_ID =1 THEN 'SELECT ' || CASE WHEN DATA_TYPE IN ('DATE','TIMESTAMP') THEN 'TO_CHAR(' || COLUMN_NAME || ',''YYYY-MM-DD HH24:MI:SS'') AS ' || COLUMN_NAME ELSE COLUMN_NAME END ELSE CASE WHEN DATA_TYPE IN ('DATE','TIMESTAMP') THEN 'TO_CHAR(' || COLUMN_NAME || ',''YYYY-MM-DD HH24:MI:SS'') AS ' || COLUMN_NAME ELSE COLUMN_NAME END END ,',') WITHIN GROUP (ORDER BY COLUMN_ID) || ' FROM ' || '${db.table.name}' AS MY_RECORD
from user_tab_columns where table_name = '${db.table.name}' ;

[#3 · Accepted solution]

I figured out a workaround myself and hope it's useful for others. I use the following query to generate another query to be executed by the next step. This query converts Oracle date values to the preferred strings at the global level so it save the development effort at column level or table level.

SELECT LISTAGG( CASE WHEN COLUMN_ID =1 THEN 'SELECT ' || CASE WHEN DATA_TYPE IN ('DATE','TIMESTAMP') THEN 'TO_CHAR(' || COLUMN_NAME || ',''YYYY-MM-DD HH24:MI:SS'') AS ' || COLUMN_NAME ELSE COLUMN_NAME END ELSE CASE WHEN DATA_TYPE IN ('DATE','TIMESTAMP') THEN 'TO_CHAR(' || COLUMN_NAME || ',''YYYY-MM-DD HH24:MI:SS'') AS ' || COLUMN_NAME ELSE COLUMN_NAME END END ,',') WITHIN GROUP (ORDER BY COLUMN_ID) || ' FROM ' || '${db.table.name}' AS MY_RECORD
from user_tab_columns where table_name = '${db.table.name}' ;

[#4]

Hello I have to ask how would you be able to convert CSV data into Mysql-Load-statement ?
because I can see that you are using a PutSql processor which directly needs SQL-query/Cmd.

[#5 · Accepted solution]

@rohit2811 When you have to figure out your own problem, like I did here, it could be painful. Fortunately, I received an email notification for the post that I totally forgot.

What I did was using PutFile to write the CSV flowfiles to the local system, then call PutSQL to execute your Load Data command to load the local csv files to your target DB.

I think there should be a processor to load data from flowfiles to the DB directly, but I failed to do so, not sure if NiFi has such as functionality. If anyone figures out how, please let me know.

[#6]

@rohit2811 as this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.