09-Hive查询操作Distributed by 和sort by

声明：
哈喽，大家好，我是谢老师。
今天来学习的是Distributed by 和sort by语法。

首先还是要来回顾一下上一讲所学的join和mapjoin操作。mapjoin会比join快很多，数据量很小的时候优势不明显，数据量很大的时候就快很多了。mapjoin其实就是join的优化。很多人都说Hive语法很简单，跟写sql语句差不多，但是hive优化就比较难了，这一点我也感觉到了。
那设置mapjoin的方式有哪些呢？有两种。我们重新来做一下实验，当做复习。
1 mapjoin的第一种方式：

set hive.auto.convert.join=true;

这里有两个表：

Time taken: 0.311 seconds
hive> select * from city;                                      
OK
20130829234535	china	henan	nanyang
20130829234536	china	henan	xinyang
20130829234537	china	beijing	beijing
20130829234538	china	jiang	susuzhou
20130829234539	china	hubei	wuhan
20130829234540	china	sandong	weizhi
20130829234541	china	hebei	shijiazhuang
20130829234542	china	neimeng	eeduosi
20130829234543	china	beijing	beijing
20130829234544	china	jilin	jilin
Time taken: 0.169 seconds
hive> select * from province;
OK
20130829234535	china	henan	nanyang
20130829234536	china	henan	xinyang
20130829234537	china	beijing	beijing
20130829234538	china	jiang	susuzhou
20130829234539	china	hubei	wuhan
20130829234540	china	sandong	weizhi
20130829234541	china	hebei	shijiazhuang
20130829234542	china	neimeng	eeduosi
20130829234543	china	beijing	beijing
20130829234544	china	jilin	jilin
Time taken: 0.131 seconds
hive> 

set hive.auto.convert.join=true;
hive> select m.city,n.province
    > from
    > (select province,city from city)m
    > join
    > (select province from province)n
    > on m.province=n.province;
Total MapReduce jobs = 3
Ended Job = 28094960, job is filtered out (removed at runtime).
Ended Job = -243803491, job is filtered out (removed at runtime).
2016-06-06 06:30:11	Starting to launch local task to process map join;	maximum memory = 518979584
2016-06-06 06:30:12	Processing rows:	8	Hashtable size:	8	Memory usage:	5107040	rate:	0.01
2016-06-06 06:30:12	Dump the hashtable into file: file:/tmp/root/hive_2016-06-06_06-30-06_575_9058687622373010883/-local-10002/HashTable-Stage-3/MapJoin-mapfile21--.hashtable
2016-06-06 06:30:12	Upload 1 File to: file:/tmp/root/hive_2016-06-06_06-30-06_575_9058687622373010883/-local-10002/HashTable-Stage-3/MapJoin-mapfile21--.hashtable File size: 752
2016-06-06 06:30:12	End of local task; Time Taken: 1.476 sec.
Execution completed successfully
Mapred Local Task Succeeded . Convert the Join into MapJoin
Mapred Local Task Succeeded . Convert the Join into MapJoin
Launching Job 2 out of 3
Number of reduce ta