Hive use Skill (iv.)--using Mapjoin to solve the problem of data skew _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

Hive's Mapjoin, which is completed during the join operation in the map phase, will no longer be required if the required data is accessible in the map process.
When a small table is associated with a large table, it is easy to skew the data, you can use Mapjoin to load the small table into memory on the map side to join, to avoid reducer processing.

Actually analysis

Select C.channel_name,count (T.requesturl) PV from
 ods.cms_channel C
 Join
 (select Host,requesturl from  dms.tracklog_5min where day= ' 20151111 ') t on
 c.channel_name=t.host
 GROUP by C.channel_name
 ORDER BY C.channel_name;

For small table join large table operation, you can use Mapjoin to put the small table in memory processing, the syntax is very simple only need to increase/*+ Mapjoin (PT) * *, the need to distribute the table into memory

Select/*+ Mapjoin (c) */
C.channel_name,count (T.requesturl) PV from
 ods.cms_channel C
 join
 (SELECT Host,requesturl from  dms.tracklog_5min where day= ' 20151111 ') t on
 c.channel_name=t.host
 GROUP BY C.channel_name ORDER by
 C.channel_name;

This is used frequently when data skew occurs

Parameter description:

1 if it is a small table, automatically select Mapjoin:

Set hive.auto.convert.join = true; # Default to False

True, Hive automatically counts the table on the left and, if it is a small table, adds memory, which uses a map join for the small table

2) The valve value of the large table small table:

Set hive.mapjoin.smalltable.filesize;

hive.mapjoin.smalltable.filesize=25000000
The default value is 25MB

3 when the map join does a group by operation, how much memory can be used to store the data, if the data is too large, it will not be saved in memory

Set hive.mapjoin.followby.gby.localtask.max.memory.usage;

Default value: 0.55

4 The percentage of memory that local tasks can use

Set hive.mapjoin.localtask.max.memory.usage;

Default value: 0.90