After changing the date format in the oracle source we are getting NULLS inserted into the Hive table

After making the change to source OVERRIDE query we are not getting any values in the final table HIVE for ‘cdr_cal_strt_tmst’ ,‘cdr_cal_end_tmst’, ‘file_date’ and ‘PARTITION_COLUMN’ (Derived based on file_date column). When checked for the same in SQL Editor we are getting mill-seconds value

Override query:

coalesce(TO_CHAR(SRC_ZACT_ROAMING_SMS_CDR_USG.CDR_CAL_STRT_TMST,’mm/dd/yyyy hh24:mi:ss.sssss’),’NULL’) AS CDR_CAL_STRT_TMST,

ORACLE view data:

After changing the date format in the oracle source we are getting NULLS inserted into the Hive table

Target Table Hive:

After changing the date format in the oracle source we are getting NULLS inserted into the Hive table

Beginner Asked on April 23, 2021 in Diyotta Studio.
Add Comment
1 Answer(s)

This is expected when NULLS  are loaded into the hive Target. As hive doesn’t accept the date/timestamp format as mm/dd/yyyy. Hive allows only yyyy-mm-dd format.

You need to change your override SQL date format as shown below for date columns.

TO_CHAR(SRC_ZACT_ROAMING_SMS_CDR_USG.FILE_DATE,’YYYY-MM-DD HH24:MI:SS.SSSSS’).

Expert Answered on April 26, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.