Hive use Skill (c)--to use group by to realize the statistics __hive

Related articles recommended:

Hive Usage Tips (i) Automating dynamic allocation table partitioning and modifying Hive table field names
Hive use Tips (ii)--sharing intermediate result sets
Hive use Skill (iii.)--using group by to realize statistics

Hive use Skill (iv.)--using mapjoin to solve data skew problem

Hive Use Tips (v)--one row to multiple lines, one line to one row

Site statistics commonly used indicators, PV, UV, independent IP, login users, etc., are involved in the heavy operation. The annual statistics, PV more than 10 billion. Even simple statistics are very difficult.

1, statistics to go heavy, the original SQL is as follows

Select substr (day,1,4) year,count (*) Pv,count (distinct Cookieid) uv,count (distinct IP) ip,count (distinct userid) login< C0/>from dms.tracklog_5min a  
where substr (day,1,4) = ' 2015 '
Group by substr (day,1,4)
;

The statistics of four instructions, three are involved in the weight, the task ran for several hours did not result.

2, the use of group by implementation to weight

Select "2015", "PV", COUNT (*) from dms.tracklog_5min
where day>= ' 2015 ' and day< ' 2016 '
Union 
All Select "201505", "UV", COUNT (*) from (
select  cookieid to Dms.tracklog_5min
where day>= ' 2015 ' and Day < ' 2016 '  GROUP by Cookieid-a 
UNION ALL 
Select ' 2015 ', ' IP ', COUNT (*) from (
select  IP from Dms.tracklog_5min
where day>= ' 2015 ' and day< ' 2016 '  GROUP by IP-a 
UNION ALL 
Select "2015", " LOGIN ", COUNT (*) from (
select  userid from Dms.tracklog_5min
where day>= ' 2015 ' and day< ' 2016 ') GROUP by UserID) b;

Separate statistics Pv,uv,ip,login and other indicators, and union together, the task ran less than 1 hours to come to the results

3, the parameter optimization

SET mapred.reduce.tasks=50;
SET mapreduce.reduce.memory.mb=6000;
SET mapreduce.reduce.shuffle.memory.limit.percent=0.06;

When it comes to data skew, it is primarily the problem of data skew in reduce, which may be resolved by setting the parallel number of reduce in hive, and by the ratio of the memory size of reduce to the shuffle brush disk in M,reduce.