Use unix time and write the unix time to oracle as number column type ,create virtual column in oracle database for the unix time like “oracle_time generated always as (to_date('1970010108','YYYYMMDDHH24')+(1/24/60/60)*unixtime )
> On Mar 20, 2018, at 11:08 PM, Gurusamy Thirupathy <thirug...@gmail.com> wrote: > > HI Jorn, > > Thanks for your sharing different options, yes we are trying to build a > generic tool for Hive to Spark export. > FYI, currently we are using sqoop, we are trying to migrate from sqoop to > spark. > > Thanks > -G > > On Tue, Mar 20, 2018 at 2:17 AM, Jörn Franke <jornfra...@gmail.com > <mailto:jornfra...@gmail.com>> wrote: > Write your own Spark UDF. Apply it to all varchar columns. > > Within this udf you can use the SimpleDateFormat parse method. If this method > returns null you return the content as varchar if not you return a date. If > the content is null you return null. > > Alternatively you can define an insert function as pl/sql on Oracle side. > > Another alternative is to read the Oracle metadata for the table at runtime > and then adapt your conversion based on this. > > However, this may not be perfect depending on your use case. Can you please > provide more details/examples? Do you aim at a generic hive to Oracle import > tool using Spark? Sqoop would not be an alternative? > > On 20. Mar 2018, at 03:45, Gurusamy Thirupathy <thirug...@gmail.com > <mailto:thirug...@gmail.com>> wrote: > >> Hi guha, >> >> Thanks for your quick response, option a and b are in our table already. For >> option b, again the same problem, we don't know which column is date. >> >> >> Thanks, >> -G >> >> On Sun, Mar 18, 2018 at 9:36 PM, Deepak Sharma <deepakmc...@gmail.com >> <mailto:deepakmc...@gmail.com>> wrote: >> The other approach would to write to temp table and then merge the data. >> But this may be expensive solution. >> >> Thanks >> Deepak >> >> On Mon, Mar 19, 2018, 08:04 Gurusamy Thirupathy <thirug...@gmail.com >> <mailto:thirug...@gmail.com>> wrote: >> Hi, >> >> I am trying to read data from Hive as DataFrame, then trying to write the DF >> into the Oracle data base. In this case, the date field/column in hive is >> with Type Varchar(20) >> but the corresponding column type in Oracle is Date. While reading from hive >> , the hive table names are dynamically decided(read from another table) >> based on some job condition(ex. Job1). There are multiple tables like this, >> so column and the table names are decided only run time. So I can't do type >> conversion explicitly when read from Hive. >> >> So is there any utility/api available in Spark to achieve this conversion >> issue? >> >> >> Thanks, >> Guru >> >> >> >> -- >> Thanks, >> Guru > > > > -- > Thanks, > Guru