Then use df.sort("<col_name>").write.etc will create exactly the number of files that we mentioned for shuffle.partitions.
Once the spark job is done then trigger hive job insert overwrite by selecting the same table and use sortby,distributedby,clusteredby and set the all hive configurations that you have mentioned in the question.
Insert overwrite table select * from table sort by <col1> distributed by <col2>
If you have ORC table then schedule concatenate job to run periodically
alter table <table_name> concatenate;
If none of the methods seems to be feasible solutions then .repartition(n) will be the way to go as this will take extra overhead but we are going to end up ~evenly sized filesin HDFS and boost up the performance while reading these files from hive/spark.
If the answer is helpful to resolve the issue, Login and Click on Accept button below to close this thread.This will help other community users to find answers quickly 🙂