Hive 1.1.0 tez hadoop oracle NULL sort

Recently, it was found that there were a lot of bug when hive 0.14 was running on tez, and decided to upgrade hive to hive 1.1.0, because the cluster hadoop version was 2.3.0 and accidentally stepped on the pit:

1. Hive 1.1.0 requires above jdk1.7

2. Set the environment variable HADOOP_USER_CLASSPATH_FIRST= "true"

3. Https://issues.apache.org/jira/browse/HIVE-9957 type this patch, and this patch will be integrated in hive1.2.0. Hive-shims-0.23-1.1.0.jar was replaced after compiling the hive 1.1.0 source code. Finally, remember to replace Hadoop23Shims.class in hive-exec-1.1.0.jar.

In addition, for the sorting of NULL, hive is different from oracle. For the following SQL:

SELECT

T.chanl_id

T.date_id

T.pltfm_id

T.prov_id

T.old_mbr_num

ROW_NUMBER () OVER (partition by t.date_id ORDER BY t.old_mbr_num) ROW_NUMBER

FROM

Data t

The execution result of Oracle is as follows:

56859 2015-02-15 1 1 449 1

56859 2015-02-15 1 1 6869 2

56859 2015-02-15 1 1 7047 3

56859 2015-02-15 1 1 8628 4

56859 2015-02-15 11 11804 5

56859 2015-02-15 1 1 12089 6

56859 2015-02-15 1 1 18778 7

56859 2015-02-15 1 1 25906 8

.

56859 2015-02-15 11 191046115 939

56859 2015-02-15 1 1 NULL 940

56859 2015-02-15 1 1 NULL 941

56859 2015-02-15 1 1 NULL 942

56859 2015-02-15 1 1 NULL 943

The execution result of Hive is as follows:

0 56859 2015-02-15 1 1 NULL 1

1 56859 2015-02-15 1 1 NULL 2

2 56859 2015-02-15 1 1 NULL 3

3 56859 2015-02-15 1 1 NULL 4

4 56859 2015-02-15 1 1 449 5

5 56859 2015-02-15 1 1 6869 6

6 56859 2015-02-15 1 1 7047 7

7 56859 2015-02-15 1 1 8628 8

8 56859 2015-02-15 11 11804 9

It can be seen that the ordering of NULL is not the same.