Thanks for the reply folks. I have found the issue ! When we are importing the data from legacy DB servers using Spark, during the Spark execution, Hive staging files are created in target location where data resides. When we export these data to S3 using disctp, these hive staging also moves to that bucket. So when we query these using hive, it seems to be checking all those hive staging files before throwing the o/p and also number of splits matters which are more in number, I have merged these splits together to have less mappers and to get better performance which is achieved now. I get the count of the 3 million records table in fraction of seconds!
... View more
