hive-同一份数据多种处理(节省时间的写法)

墨蓝 2022-06-01 09:50 104阅读 0赞

hive 提供了一个独特的语法，可以从一个数据源产生多个数据聚合，无需每次聚合都要重新扫描一次。对于大的数据输入集来说，可优化节约非常可观的时间。

例子：

hive > from table1

> INSERT OVERWRITE TABLE2 select \*  where action='xx1'

> INSERT OVERWRITE TABLE3 select \*  where action='xx2';

这样只需扫描table1表一次。

亲身验证：可以同一时间，同一操作插入数据到同一个表的不同分区

限制：不能在同一时间，同一操作插入数据到同一个表，如：

hive > from table1

> INSERT into table TABLE2 select \*  where action='xx1'

> INSERT  into table TABLE2 select \*  where action='xx2';

这样提示错误

应用场景：

特别适用于笛卡尔积的用户数计算，如：计算15天的每天的月（30天）的活跃用户数

原SQL(mysql语法）:

SELECT b.day\_id, a.col1, a.col2,COUNT(DISTINCT col4) AS num  
    FROM TABLE1 a, TABLE2 b  
   WHERE a.op\_day\_id >=  
         date\_format(date\_add('20140310', INTERVAL - 43 DAY), '%Y%m%d')  
     AND a.op\_day\_id <= b.day\_id  
     AND a.op\_day\_id >= b.month\_day\_id  
   GROUP BY b.day\_id, a.col1, a.col2;

--说明：table2 为时间格式表，day\_id是当天，month\_day\_id是30天前的日期，一共有15条记录

执行时间：10000多秒

优化后的语法：

FROM TABLE1  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table1.txt' SELECT 20140303, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140303 AND col3 >= 20140201 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table2.txt' SELECT 20140302, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140302 AND col3 >= 20140131 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table3.txt' SELECT 20140301, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140301 AND col3 >= 20140130 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table4.txt' SELECT 20140228, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140228 AND col3 >= 20140129 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table5.txt' SELECT 20140227, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140227 AND col3 >= 20140128 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table6.txt' SELECT 20140226, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140226 AND col3 >= 20140127 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table7.txt' SELECT 20140225, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140225 AND col3 >= 20140126 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table8.txt' SELECT 20140224, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140224 AND col3 >= 20140125 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table9.txt' SELECT 20140223, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140223 AND col3 >= 20140124 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table10.txt' SELECT 20140222, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140222 AND col3 >= 20140123 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table11.txt' SELECT 20140221, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140221 AND col3 >= 20140122 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table12.txt' SELECT 20140220, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140220 AND col3 >= 20140121 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table13.txt' SELECT 20140219, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140219 AND col3 >= 20140120 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table14.txt' SELECT 20140218, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140218 AND col3 >= 20140119 GROUP BY col1, col2  
  INSERT OVERWRITE LOCAL DIRECTORY '/data/data\_table/data\_table15.txt' SELECT 20140217, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140217 AND col3 >= 20140118 GROUP BY col1, col2;

Time taken: 685.172 seconds