- Hive join查询的时候on和where有什么区别
左右关联时:
• 条件不为主表条件时,放在on和where后面效果一样。
• 条件为主表条件时,放在on后面,结果为主表全量,放在where后面为主表条件筛选过后的全量。
select * from a left join b on a.id=b.id where a.dt=20181115;
推荐这么写,先过滤数据然后再join
-
left join执行顺序
不考虑where条件下,left join会把左表所有数据查询出来,on及其后面的条件仅仅会影响右表的数据(符合就显示,不符合全部为null)。
在join阶段。where字句的条件都不会被使用,仅在join阶段完成以后,where子句条件才会被使用,它将从匹配阶段产生的数据中检索过滤。
所以左连接关注的是左边的主表数据,不应该把on后面的从表中的条件加到where后,这样会影响原有主表的数据。
where后面:是先连接生成临时查询结果,然后再筛选on后面:先根据条件过滤筛选,再连接生成临时查询结果。
对于条件在on加个and还是用子查询。查询结果是一模一样的,至于如何使用这个需要分情况,用子查询的话会多一个maptask,但是如果利用这个子查询能过滤很多数据的话,用子查询还是比较建议的,因为不会加载太多的数据到内存中,如果过滤数据不多的情况下,建议用on后面加and条件 -
hive和mysql的区别
• 查询语言不同:Hive是hql语言,mysql是sql语句;
• 数据存储位置不同:Hive是把数据存储在HDFS上,而mysql数据是存储在自己的系统中;
• 数据格式:Hive数据格式可以用户自定义,mysql有自己的系统定义格式;
• 延迟性:Hive延迟性高,而mysql延迟性低
• 数据规模:Hive存储的数据量超级大,而mysql只是存储一些少量的业务数据;
• 底层执行原理:Hive底层是用的MapReduce,而mysql是excutor执行器。 -
数据倾斜的处理
key不均匀,打散key
开启map端聚合
hive.map.aggr=true
小表作为驱动表加载进入内存
- Hive中Mapper和Reduce的个数
map数量是根据数据文件的大小进行切割而成的,而reduce的数量可以手动设置reduce个数(修改相应参数)
如果文件过小,也可以通过合并小文件
set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
控制hive的reduce个数
mapred.reduce.tasks
设置reduce的数量,可以由此参数进行指定
hive.exec.reducers.bytes.per.reducer
设定每个reducer能够处理的数据量,默认值是256m
如果配置此参数是默认256,如果reduce输入是1g,那么就会有4个reducer
hive.exec.reducer.max
设置一个作业运行的最大reducer个数,默认值是999
hive.multigroupby.singereducer
表示如果一个sql语句,有多个分组聚合操作,且分组是使用相同的字段,那么多组聚合操作可以用一个作业的reducer来完成,而不是拆分成多个作业,多个reduce完成,减少重复读取,和shuffle操作
hive.mapred.reduce.tasks.speculative.execution
开启推测reduce任务推测在执行,系统在一个reduce任务中执行进度远低于其他任务的执行进度,会尝试正在另外的机器上启动一个相同的reduce任务
hive.optimize.reducededuplication
表示当数据需要按照相同的键再次聚合时,开启这个配置,可以减少重复的聚合操作
hive.vectorized.execution.reduce.enabled
表示是否启用reduce任务的向量化执行模式,默认值是true,但是mapreduce引擎并不支持,需要更换引擎才可以使用这个参数
- Hive中Sort By,Order By, Cluster By,Distrbute By的作用
- order by
Hive中的order by跟传统的sql语言中的order by作用是一样的,会对查询的结果做一次全局排序,所以说,只有Hive的sql中制定了order by所有的数据都会到同一个reduce进行处理(不管有多少map,也不管文件有多少的block只会启动一个reduce)。但是对于大量数据这将会消耗很长的时间去执行。这里跟传统的sql还有一点区别:如果指定了hive.mapred.mode=strict(默认值是nonstrict),这时就必须指定limit来限制输出条数,原因是:所有的数据都会在同一个reduce端进行,数据量大的情况下可能不能出结果,那么在这样的严格模式下,必须指定输出的条数。
- sort by
Hive中指定了sort by,那么在每个reducer端都会做排序,也就是说保证了局部有序(每个reducer出来的数据是有序的,但是不能保证所有的数据是有序的,除非只有一个reducer),好处是:执行了局部排序之后可以为接下来的全局排序提高不少的效率(其实就是做一次归并排序就可以做到全局排序了)
- distribute by 和 sort by 一起使用
distribute by是控制map的输出在reducer是如何划分的,举个例子,我们有一张表,mid是指这个store所属的商户,money是这个商户的盈利,name是这个store的名字,执行Hive语句:
select mid,money,name from store distribute by mid sort by mid asc,money asc
。
我们所有的mid相同的数据会被送到同一个reducer去处理,这就是因为指定了distribute by mid,这样的话就可以统计出每个商户中各个商店盈利的排序了(这个肯定是全局有序的,因为相同的商户会放到同一个reducer去处理)。这里需要注意的是distribute by必须要写在sort by之前。
- cluster by
cluster by的功能就是distribute by 和sort by相结合,如下2个语句是等价的
select mid,money,name from store cluster by mid;
select mid,money,name from store distribute by mid sort by mid;
如果需要获得与3中语句一样得效果:
select mid,money,name from store cluster by mid sort by money
被cluster by指定得列只能是降序,不能指定asc和desc。
- 生产环境中为什么建议使用外部表
1)因为外部表不会加载数据到Hive,减少数据传输,数据还能共享。
2)Hive不会修改数据,所以无需担心数据的损坏。
3)删除表时,只删除表结构,不删除数据。