hive group by | distinct区别以及性能比较

Hive去重统计

去重方式：

• Distinct
• Group by

实际论证

order_snap为订单的快照表 总记录条数763191489，即将近8亿条记录,总大小:108.877GB,存储的是公司所有的订单信息，表的字段大概有20个,其中订单号是没有重复的,所以在统计总共有多少订单号的时候去重不去重结果都一样，我们来看看:
统计所有的订单有多少条条数，一个count函数就可以搞定的sql性能如何。

Distinct

select count(distinct order_no) from order_snap;

Stage-Stage-1: Map: 396 Reduce: 1 Cumulative CPU: 7915.67 sec HDFS Read: 119072894175 HDFS Write: 10 SUCCESS

Total MapReduce CPU Time Spent: 0 days 2 hours 11 minutes 55 seconds 670 msec

OK

_c0

763191489

Time taken: 1818.864 seconds, Fetched: 1 row(s)

Group by

select count(t.order_no) from (select order_no from order_snap group by order_no) t;

Stage-Stage-1: Map: 396 Reduce: 457 Cumulative CPU: 10056.7 sec HDFS Read: 119074266583 HDFS Write: 53469 SUCCESS

Stage-Stag