Hive 优化之 共享中间结果集

转：http://blog.csdn.net/kwu_ganymede/article/details/49927311

1、优化前的SQL

[sql] view plain copy

1. SELECT  
2.     COUNT(*) pv  
3. FROM  
4.     (  
5.         SELECT  
6.             cookieid,  
7.             userid,  
8.             to_date(DATETIME) day1  
9.         FROM  
10.             ods.tracklog_5min  
11.         WHERE  
12.             DAY>='20151001'  
13.         AND DAY<='20151031'  
14.         AND lower(requesturl) IN ('http://chat.hexun.com/',  
15.                                   'http://zhibo.hexun.com/'))t1   
16.   
17. INNER JOIN  
18.     (  
19.         SELECT  
20.             cookieid,  
21.             to_date(DATETIME) day2  
22.         FROM  
23.             ods.tracklog_5min  
24.         WHERE  
25.             DAY>='20151001'  
26.         AND DAY<='20151031'  
27.         AND ((  
28.                     lower(requesturl) LIKE 'http://zhibo.hexun.com/%'  
29.                 OR  lower(requesturl) LIKE 'http://chat.hexun.com/%')  
30.             AND requesturl LIKE '%/default.html%'))t2  
31. ON  
32.     t1.cookieid=t2.cookieid  
33. AND t1.day1=t2.day2  
34. INNER JOIN  
35.     (  
36.         SELECT  
37.             cookieid,  
38.             to_date(DATETIME) day3  
39.         FROM  
40.             ods.tracklog_5min  
41.         WHERE  
42.             DAY>='20151001'  
43.         AND DAY<='20151031'  
44.         AND ( (  
45.                     lower(requesturl) LIKE 'http://px.hexun.com/%'  
46.                 AND lower(requesturl) LIKE '%/default.html%' )  
47.             OR  (  
48.                     lower(requesturl) LIKE 'http://px.hexun.com/pack/%'  
49.                 AND lower(requesturl) LIKE '%.html%' )  
50.             OR  (  
51.                     lower(requesturl) LIKE 'http://px.hexun.com/p/%'  
52.                 AND lower(requesturl) LIKE '%.html%' ) ))t3  
53. ON  
54.     t1.cookieid=t3.cookieid  
55. AND t1.day1=t3.day3  
56. LEFT JOIN  
57.     stage.saleplatform_productvisitdetail_temp t4  
58. ON  
59.     t1.userid=t4.userid  
60. WHERE  
61.     t4.createtime>t1.day1  
62. OR  t4.userid IS NULL;  

可以看，上面的SQL针对同一源表的数据查询了三次，浪费了系统的资源，相同的源完全可以通用。

2、优化后的SQL

抽出公共数据

[sql] view plain copy

1. create table default.tracklog_10month as     
2. select * from  ods.tracklog_5min  
3. WHERE  DAY>='20151001' AND DAY<='20151031';  

利用临时表，替换原SQL的公共部分：

[sql] view plain copy

1. SELECT  
2.     COUNT(*) pv  
3. FROM  
4.     (  
5.         SELECT  
6.             cookieid,  
7.             userid,  
8.             to_date(DATETIME) day1  
9.         FROM  
10.             default.tracklog_10month   
11.         WHERE  
12.              lower(requesturl) IN ('http://chat.hexun.com/',  
13.                                   'http://zhibo.hexun.com/'))t1   
14.   
15. INNER JOIN  
16.     (  
17.        SELECT  
18.             cookieid,  
19.             to_date(DATETIME) day2  
20.         FROM  
21.             default.tracklog_10month   
22.         WHERE  (lower(requesturl) LIKE 'http://zhibo.hexun.com/%'  
23.                 OR  lower(requesturl) LIKE 'http://chat.hexun.com/%')  
24.             AND requesturl LIKE '%/default.html%')t2  
25. ON  
26.     t1.cookieid=t2.cookieid  
27. AND t1.day1=t2.day2  
28. INNER JOIN  
29.     (  
30.         SELECT  
31.             cookieid,  
32.             to_date(DATETIME) day3  
33.         FROM  
34.             default.tracklog_10month  
35.         WHERE          
36.         ( (  
37.                     lower(requesturl) LIKE 'http://px.hexun.com/%'  
38.                 AND lower(requesturl) LIKE '%/default.html%' )  
39.             OR  (  
40.                     lower(requesturl) LIKE 'http://px.hexun.com/pack/%'  
41.                 AND lower(requesturl) LIKE '%.html%' )  
42.             OR  (  
43.                     lower(requesturl) LIKE 'http://px.hexun.com/p/%'  
44.                 AND lower(requesturl) LIKE '%.html%' ) ))t3  
45. ON  
46.     t1.cookieid=t3.cookieid  
47. AND t1.day1=t3.day3  
48. LEFT JOIN  
49.     stage.saleplatform_productvisitdetail_temp t4  
50. ON  
51.     t1.userid=t4.userid  
52. WHERE  
53.     t4.createtime>t1.day1  
54. OR  t4.userid IS NULL;  

3、共享中间结果集

本质就是降IO，减少MR阶段中大量读写磁盘及网络IO的压力。