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

Date column in hive table showing as 31/12/1969 19:00:00 instead of NULL

Date column in hive table showing as 31/12/1969 19:00:00 instead of NULL

Hi Team,


I have been facing an issue in hive table creation of date column.


First step we sqoop the data from the oracle DB and storing it in the HDFS as parquet file. (Some of the date column value will be "null")

Second step after sqoop completion I ran our spark transformation code and store the data as hive tables. In that the date column value stored as like this "31/12/1969 19:00:00" instead of null


After Spark transformation completed we created hive tables for that entity and the date column value showing as 31/12/1969 19:00:00 instead of null

private static String format 				= "dd/MM/yyyy HH:mm:ss";
DataFrame initParquetDf ="initiative/XGL/initiative_v"));

JavaRDD<Initiative> initiative = initParquetDf.javaRDD().map(y ->  Initiative.builder()
				.iniPct1RecorededDate(Optional.ofNullable(y.getLong(19)).map(s -> {
                    try {
                        return new Timestamp(s);
                    catch (Exception e){
                        return null;

JavaRDD<Initiative> initiativeRDD = initiative.filter(x -> x.getDelInd().equals("N"));

#Create dataframe	   
DataFrame initiativeDF  = sqlContext.createDataFrame(initiativeRDD, Initiative.class);

#joining the entities	   
DataFrame initiativeJoin = initiativeDF
                .join("df1"), col("df1.ogrdsEntityCode").equalTo(initiativeDF.col("categoryCode")))
                .join("df2"), initiativeDF.col("brandExtrnCode").equalTo(col("df2.ogrdsEntityCode")));

#write data to hdfs as parquet				
DataFrame nPubinitiative ="initiativeCode").as("ini_code"),
	date_format(initiativeDF.col("iniPct1RecorededDate"), format).as("ini_pct_1_recorded_date"));

Please correct me if am doing any thing wrong in the transformation.


Let me know if need any further informations.




Ganeshbabu R