08/14/2023, 9:08 PM
Hi All, I am trying to create a databricks sql to read from delta table using jdbc table (sql format) and insert the records into Oracle database. The connection is established, and records have been inserted but one of the date columns has a valid date (‘yyyy-mm-dd’) in delta table but gets inserted as Timestamp . Though the oracle table is defined as DATE for the particular column, databricks process somehow converts it to timestamp and loads it. I did try workarounds – date_format, to_date etc it does not work out, though I can convert it to STRING and load them .I want to understand why date column is converted into timestamp.
Serde Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.SequenceFileInputFormat
Partition Provider: Catalog
Schema: root
-- code: string (nullable = true)
-- curr_rec_ind: integer (nullable = true)
-- strt_dt: string (nullable = true)
-- end_dt: string (nullable = true)
), false
(4) SubqueryAlias
Arguments: spark_catalog.db_edw.sesn_map
(5) Project
Arguments: [code#2132,CURR_REC_IND#2136, to_date(STRT_DT#2137, Some(yyyy-MM-dd)) AS STRT_DT#2123, to_date(end_dt#2138, Some(yyyy-MM-dd)) AS end_dt#2124]
(6) Project
Arguments: [ansi_cast(code#2132 as string) AS code#2140, ansi_cast(CURR_REC_IND#2136 as decimal(1,0)) AS CURR_REC_IND#2144, ansi_cast(STRT_DT#2123 as timestamp) AS STRT_DT#2145, ansi_cast(end_dt#2124 as timestamp) AS END_DT#2146]
Thanks, Mohan


08/15/2023, 3:12 PM
What’s exactly the query and connector?
will return a DATE in a dataframe, but it’s depending on the connector whether to keep it as DATE or do something magic conversions.


08/15/2023, 6:39 PM
oracle jdbc connector and please find the sql below . DROP TABLE IF EXISTS db.test_sesn; CREATE TABLE IF NOT EXISTS db.test_sesn USING org.apache.spark.sql.jdbc OPTIONS ( driver ‘oracle.jdbc.driver.OracleDriver’, url ‘jdbcoraclethin:@//’, dbtable ‘STAGING.test_sesn_orcl’, user ‘test’, password ‘test’ ) ; %sql INSERT OVERWRITE db.test_sesn SELECT SESN_YR, CURR_REC_IND, TO_DATE(STRT_DT, ‘yyyy-MM-dd’) AS STRT_DT, TO_DATE(end_dt, ‘yyyy-MM-dd’) AS end_dt FROM db.sesn_map_stg ; ;

jonathan rowe

08/16/2023, 1:39 PM
you do know oracle DATE type also stores the time?
👍 1


08/18/2023, 4:34 PM
Thanks @jonathan rowe. yes it does, got it