I tried all the possible scenarios to debug it and got some clue about the issue mentioned above.
1)Did a Select * from table1 where 1=2; (fetches 0 records without error)
Select column1 from table1 ;(Tried each column individually to fetch single row ,it fails showing ODBC read failed)
Here figured out issue is not with table structure and it fails while it tries to read any data from table.
2)To check it was issue with ODBC connector tried using OLEDB to hive and still same issue.
Here figured out issue is not with connector.
3)Downloaded data into CSV and tried to load and it worked perfectly.
Here figured out issue is not with type of data in table.
4)Created a similar table using
Create table table1_test(select * from table1); it worked perfectly when i load data from table_test.
Here figured out issue is in not with table structure and data in it but with Constraints or any other approach followed while creating table.
5)compared create table scrtipt for table1 and table1_test by using.
show create table table1_test;
Show create table table1;
The only difference i see was table1 has partition and table1_test doesn't have partition.
6)So i tried to load table2 which also has partition and it worked perfectly.
So compared table1 and table2 create script and difference i see
table2 has partion along with cluster by(Column) and table1 has partition alone.
Now i'm trying to figure out how is cluster by responsible for the read failed error.
Please do share your thoughts if you know how partition with cluster by and partition with out cluster by is making this difference?
Hope this helps some of you facing similar issues.