Date import issue from Oracle to hive using sqoop

0
votes

I'm trying to import data from Oracle to hive. There is column in Oracle 'LOAD_DATE' and its values are in the format '8/13/2015 2:55:17 AM'. After importing I'm getting the value like '2015-08-13 02:55:17.0'. 'AM' is missing in the imported data. Please give a solution to import the correct data.

This is the command I'm triggering,

sqoop import --connect jdbc:oracle:thin:@ipaddr/dbname --username uname --password pword --table tname --hive-import --hive-table dbname.tname -m 1 --null-string '\N' --null-non-string '\N' --warehouse-dir /user/hive/warehouse/dbname.db/tname

2
oraclehadoophivesqoopimpala
What is the data type of LOAD_DATE column? – Lalit Kumar B
In Oracle it is 'Date' and after import the datatype is 'string' in Hive – user3844662

2 Answers

1
votes

Once you copy to hive date as a string type, you can use below query to get a result in original format. select unix_timestamp(date_s, 'MM/dd/yyyy hh:mm:ss a') as tm, date_s from DUAL;

0
votes

Is it possible that hive uses the 24h format and oracle the am/pm? If hive imports the date as a varchar then use a TO_CHAR(date, format) for this column like:

SELECT to_char(SYSDATE,'MM/DD/YYYY HH12:MI:SS PM') FROM DUAL

The PM will be translated correctly between AM and PM. And here a reference for formats: http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#CDEHIFJA