Amazing answer! ... View more

I was able to import all tables in the following format: sqoop import -connect jdbc:oracle:thin:@<fdqn>/<server> -username <username> -P -table CUST_NAV -columns "<column names separated by commas" -hive-import -hive-table databasenameinhive.New_CUST_NAV -target-dir 'location in hdfs' @bpreachuk I understood the workaround in my problem using your suggestion. I'll import all tables as is from the oracle db and create different views which i can then use in my select statements. Thanks guys. ... View more

Hi, I found another way of doing this: 1. I first loaded my data set in HDFS. The data set contained the following columns: rwid, ctrname, clndrdate and clndrmonth. Note that column rwid had no values. 2. Then i created an external table that maps to this data set in hdfs CREATE EXTERNAL TABLE IF NOT EXISTS calendar(rwid int, ctrname string, clndrdate DATE, clndrmonth string ) COMMENT 'Calendar for Non Business Days' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE location '<location of my file in hdfs>'; 3. I created an ORC CREATE TABLE IF NOT EXISTS calendar_nbd(rwid int, ctrname string, clndrdate DATE, clndrmonth string ) COMMENT 'Calendar for Non Business Days' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS ORC; 4. The last step is most important. I used row_number() over() in the insert overwrite query. This automatically updated the rwid column with the row number. insert overwrite table calendar_nbd select row_number() over () as rwid, ctrname,clndrdate, clndrmonth from calendarnonbusdays; ... View more