Table Structure -
hive> desc table1;
OK
col1 string
col2 string
col3 string
col4 bigint
col5 string
Time taken: 0.454 seconds, Fetched: 5 row(s);
NUmber of underlying files -
[user@localhost ~]$ hadoop fs -ls /user/hive/warehouse/database.db/table | wc -l
58822
[user@localhost ~]$
Distinct Query - select distinct concat(col1,'~',col2,'~',col3) from vn_req_tab;
Total records - ~2M Above query runs for 8 hours.
What is causing the issue, How do i debug this query.
You have very large number of small files and this is the main problem. When you are executing the query 1 mapper is executing on each file thus there are lot of mappers are running each mapper on small piece of data(1 file each) which means they are consuming unnecessary resources from cluster and waits for others to finish.
Please note hadoop is ideal for bigger files with large data.
If you would have executed the same query on bigger files it would have given much better performance.
Try setting the below properties
set mapred.min.split.size=100000000; // u can set max.split.size for optimal performance.
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
set mapred.min.split.size=100000000;
Try tweaking the values in property to reach at optimal number of mappers