Created 03-06-2017 06:14 AM
Hello,
We are trying to read data from Oracle tables, "Date" based data types are converted into "Timestamp" Data types.
e.g: Table is Oracle.
desc hr.employees;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SSN VARCHAR2(55)
and schema read in the DataFrame in Scala
|-- EMPLOYEE_ID: decimal(6,0) (nullable = false)
|-- FIRST_NAME: string (nullable = true)
|-- LAST_NAME: string (nullable = false)
|-- EMAIL: string (nullable = false)
|-- PHONE_NUMBER: string (nullable = true)
|-- HIRE_DATE: timestamp (nullable = false) (Incorrect data type read here)
|-- JOB_ID: string (nullable = false)
|-- SALARY: decimal(8,2) (nullable = true)
|-- COMMISSION_PCT: decimal(2,2) (nullable = true)
|-- MANAGER_ID: decimal(6,0) (nullable = true)
|-- DEPARTMENT_ID: decimal(4,0) (nullable = true)
|-- SSN: string (nullable = true)
Hire_Date is read incorrectly as TimeStamp, is there a way to correct.
Data is being read from Oracle on the fly and the application does not have an upfront knowledge of datatypes and can't convert it after being read.
Thanks in advance.
Nagesh
Created 03-21-2017 06:00 PM
Analysis: As per oracle -
Oracle Database 8i and earlier versions did not support TIMESTAMP data, but Oracle DATE data used to have a time component as an extension to the SQL standard. So, Oracle Database 8i and earlier versions of JDBC drivers mapped oracle.sql.DATE to java.sql.Timestamp to preserve the time component. Starting with Oracle Database 9.0.1, TIMESTAMP support was included and 9i JDBC drivers started mapping oracle.sql.DATE to java.sql.Date. This mapping was incorrect as it truncated the time component of Oracle DATE data. To overcome this problem, Oracle Database 11.1 introduced a new flag mapDateToTimestamp. The default value of this flag is true, which means that by default the drivers will correctly map oracle.sql.DATE to java.sql.Timestamp, retaining the time information. If you still want the incorrect but 10g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false.
Ref link is here.
Solution:
Class.forName("oracle.jdbc.driver.OracleDriver")var info : java.util.Properties=new java.util.Properties()
info.put("user", user)
info.put("password", password)
info.put("oracle.jdbc.mapDateToTimestamp","false")val jdbcDF = spark.read.jdbc(jdbcURL, tableFullName, info)
Hope it helps!
Created 03-21-2017 06:00 PM
Analysis: As per oracle -
Oracle Database 8i and earlier versions did not support TIMESTAMP data, but Oracle DATE data used to have a time component as an extension to the SQL standard. So, Oracle Database 8i and earlier versions of JDBC drivers mapped oracle.sql.DATE to java.sql.Timestamp to preserve the time component. Starting with Oracle Database 9.0.1, TIMESTAMP support was included and 9i JDBC drivers started mapping oracle.sql.DATE to java.sql.Date. This mapping was incorrect as it truncated the time component of Oracle DATE data. To overcome this problem, Oracle Database 11.1 introduced a new flag mapDateToTimestamp. The default value of this flag is true, which means that by default the drivers will correctly map oracle.sql.DATE to java.sql.Timestamp, retaining the time information. If you still want the incorrect but 10g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false.
Ref link is here.
Solution:
Class.forName("oracle.jdbc.driver.OracleDriver")var info : java.util.Properties=new java.util.Properties()
info.put("user", user)
info.put("password", password)
info.put("oracle.jdbc.mapDateToTimestamp","false")val jdbcDF = spark.read.jdbc(jdbcURL, tableFullName, info)
Hope it helps!