Issue : Hive on MR and Hive on TEZ query output difference

Issue:

If we see different outputs for the same Hive query when executed in MR mode and TEZ mode, there could be multiple reasons. In this particular case we discuss a scenario where Hive on MR gives correct result while Hive on TEZ returns wrong result.

Analysis:

It was found that the issue is with the location where the actual data files are residing inside the hdfs hive table location. (For this particular query, issue was with one table)

Previously (before transactional feature was introduced in Hive) all files for a partition (or a table if the table is not partitioned) lived in a single directory.  With these changes, any partitions (or tables) written with an ACID aware writer will have a directory for the base files and a directory for each set of delta files. (https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-BaseandDeltaDirectories). From the hive debug logs, it was seen TEZ was reading data multiple times which resulted in wrong results.

 

Following is part of the files and directories inside the table directory.

-rwxr-x--- 1 prodxx prodxx 813 Jun  12 00:04 000000_0_copy_1
-rwxr-x--- 1 prodxx prodxx 832 Jun  12 00:04 000000_0_copy_2
-rwxr-x--- 1 prodxx prodxx 822 Jun  12 00:04 000000_0_copy_3
-rwxr-x--- 1 prodxx prodxx 826 Jun  12 00:05 000000_0_copy_4
-rwxr-x--- 1 prodxx prodxx 836 Jun  12 00:05 000000_0_copy_5
drwxr-x--- 2 prodxx prodxx   5 Jul 16 05:50 delta_0000116_0000116
drwxr-x--- 2 prodxx prodxx   1 Jul 16 05:50 delta_0000117_0000117
drwxr-x--- 2 prodxx prodxx   1 Jul 16 05:50 delta_0000129_0000129
drwxr-x--- 2 prodxx prodxx   5 Jul 16 05:50 delta_0000118_0000118
drwxr-x--- 2 prodxx prodxx   5 Jul 16 05:50 delta_0000130_0000130

Here there are data files (example :- 000000_0_copy_4, 000000_0_copy_5, etc) outside ‘delta_*’ folder. Also, we do not see any ‘base_*’ folder.

Since the files were lying outside of ‘delta_*’ or ‘base_*’ folder, TEZ reader was giving wrong results.

One possible reason why the data files were residing outside designated directory could be using LOAD command to load data.

However, LOAD is not supported for transactional tables. (https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-Limitations)

Another finding in this particular case was, there were no ‘base_*’ folder inside any hive transactional tables.

Compactor is the process responsible for combining multiple delta files into a single delta per bucket during minor compaction and combining delta and base files into single base per bucket during major compaction.

Hive metastore is responsible running the compactor. It is a background process inside hive metastore. So, we need hivemetastore package installed.

In this specific case, there as no hivemetastore package installed.