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

Question

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

Garyy · Accepted Answer

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}' ;

rohit2811 · Answer

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.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.