表的优化

小表、大表Join

---

将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用map join让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce
实际测试发现：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别

测试大表JOIN小表和小表JOIN大表的效率

1.建大表、小表和JOIN后表的语句

// 创建大表
create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
// 创建小表
create table smalltable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
// 创建join后的表
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

2. 分别向大表和小表中导入数据

hive (default)> load data local inpath '/opt/moudles/cdh/input/bigtable' into table bigtable;

hive (default)> load data local inpath '/opt/moudles/cdh/input/smalltable' into table smalltable;

3. 关闭mapjoin功能（默认是打开的）

hive (default)> set hive.auto.convert.join = false;  

4. 执行小表JOIN大表语句

insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from smalltable s
left join bigtable  b
on b.id = s.id;

5. 执行大表JOIN小表语句

insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable  b
left join smalltable  s
on s.id = b.id;

大表Join大表

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下：

一 空KEY过滤

1. 配置历史服务器

	    <property>
                <name>mapreduce.jobhistory.adress</name>
                <value>hadoop-senior01.xcu.com:10020</value>
        </property>

        <property>
                <name>mapreduce.jobhistory.webapp.adress</name>
                <value>hadoop-senior01.xcu.com:19888</value>
		</property>

注意分发给各个节点

2. 启动历史服务器

sbin/mr-jobhistory-daemon.sh start historyserver

3.查看jobhistory

http://hadoop-senior01.xcu.com:19888/jobhistory

4. 创建原始数据表、空id表、合并后数据表

// 创建原始表
create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
// 创建空id表
create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
// 创建join后表的语句
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

若表存在，清空数据即可

hive (default)> truncate table jointable;

5. 分别加载原始数据和空id数据到对应表中

hive (default)> load data local inpath '/opt/moudles/cdh/input/SogouQ1.txt' into table ori;

hive (default)> load data local inpath '/opt/moudles/cdh/input/nullid' into table nullidtable;

6. 测试不过滤空id

hive (default)> insert overwrite table jointable 
              > select n.* from nullidtable n left join ori o on n.id = o.id;

7. 测试过滤空id

hive (default)> insert overwrite table jointable 
select n.* from (select * from nullidtable where id is not null ) n  left join ori o on n.id = o.id;

二 空key转换

1. 设置5个reduce个数

set mapreduce.job.reduces = 5;

6. 随机分布空null值

insert overwrite table jointable
select n.* from nullidtable n full join ori o on 
case when n.id is null then concat('hive', rand()) else n.id end = o.id;