Dataiku and Dremio date difference

In dataiku you have applied to_date function to convert string to date datatype in SparkSQL and when query same dataset in dremio then you will difference of 1 day.


Example -  In dataiku

    SELECT  offer_date  FROM temp

    Result :      2023-05-18T00:00:00.000Z


Dremio :

    SELECT  offer_date  FROM temp

Result :      2023-05-17 08:00:00.000


Now you will find the difference of  1 day and 8 hours (sometime 16 hours)  between Dataiku and Dremio.


Solutions:

      This difference is due to time difference. In Dataiku we have  correct date to correct it Dremio please use TO_TIMESTAMP function to get correct date.

Example :

select TO_TIMESTAMP(offer_date,'yyyy-mm-dd') as offer_date from temp

Comments

Post a Comment

Popular posts from this blog

Date format issue with spark sql

 If you have date format like  date_col 1/1/1978 0:00 6/6/1975 0:00 1/1/1978 0:00 1/1/1978 0:00 7/23/1988 0:00 1/1/1978 0:00 1/26/1961 0:00 3/3/1980 0:00 6/2/1984 0:00 1/2/1971 0:00 Solution : substring(TO_DATE(date_col ,'MM/dd/yy'),0,10) from table Result : 1978-01-01 1975-06-06 .. 1988-07-23 ..
Read more

Hive Partition sub folders HIVE_UNION_SUBDIR_1,HIVE_UNION_SUBDIR_2,HIVE_UNION_SUBDIR_8

 Hive Partition have sub folders like HIVE_UNION_SUBDIR_1,HIVE_UNION_SUBDIR_2,HIVE_UNION_SUBDIR_3 Problem : When you use UNION ALL in query with Hive version 1.2.0 onward. UNION ALL will not supported and we have TEZ engine setup in hive-site.xml or hive configuration file which is responsible to create  sub folders like HIVE_UNION_SUBDIR_1 etc  on HDFS. When you use spark sql or other sql query to read Partition data then your resultant partition created with blank or 0 value. Solution: Please change the configuration file of hive and set below property. Its work for me in dataiku. hive.execution.engine=mr It will invoke Map Reduce process which will  bit slow to process the job but it will help to stop creating the extra sub folder on HDFS.  
Read more