大数据框架复习-hive
hive的架构
- meta store:元数据库
- 客户端client:
- CLI,JDBC 到
- 4个器:SQL parser解析器;编译器;优化器;执行器; 到
- MR 到 hdfs
Hive和数据库比较
- Hive 和数据库除了拥有类似的查询语言,再无类似之处
- 数据存储位置:Hive 存储在 HDFS 。数据库将数据保存在块设备或者本地文件系统中
- 数据更新:Hive中不建议对数据的改写。而数据库中的数据通常是需要经常进行修改的
- 执行延迟:Hive 执行延迟较高。数据库的执行延迟较低。当然,这个是有条件的,即数据规模较小,当数据规模大到超过数据库的处理能力的时候,Hive的并行计算显然能体现出优势
- 数据规模:Hive支持很大规模的数据计算;数据库可以支持的数据规模较小
内部表和外部表
- 内部表:当我们删除一个内部表时,Hive也会删除这个表中数据。内部表不适合和其他工具共享数据。
- 外部表:删除该表并不会删除掉原始数据,删除的是表的元数据
4个by
- sort by:分区内有序
- order by:全局排序,只有一个reducer
- distribute by:类似MR中Partition,进行分区,结合sort by使用
- cluster by:当Distribute by和Sorts by字段相同时,可以使用Cluster by方式。Cluster by除了具有Distribute by的功能外还兼具Sort by的功能。但是排序只能是升序排序,不能指定排序规则为ASC或者DESC
窗口函数
- rank() dense_rank() row_rank() : 分别效果 113 112 123
- over()、current row、n preceding、n following、unbounded preceding、lag(col,n)往下n行、lead、ntile
自定义UDF、UDTF函数
- 用UDF函数解析公共字段;用UDTF函数解析事件字段。
- 自定义UDF:继承UDF,重写evaluate方法
- 自定义UDTF:继承自GenericUDTF,重写3个方法:
- initialize(自定义输出的列名和类型)
- process(将结果返回forward(result))
- close
- 为什么要自定义UDF/UDTF,因为自定义函数,可以自己埋点Log打印日志,出错或者数据异常,方便调试。
hive优化
- MapJoin
- 如果不指定MapJoin或者不符合MapJoin的条件,那么Hive解析器会将Join操作转换成Common Join,即:在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join,避免reducer处理。
- 行列过滤
- 列处理:在SELECT中,只拿需要的列,如果有,尽量使用分区过滤,少用SELECT *
- 行处理:在分区剪裁中,当使用外关联时,如果将副表的过滤条件写在Where后面,那么就会先全表关联,之后再过滤
- 分桶技术、分区技术
- 合理设置Map数
- 是不是map数越多越好?
- 答案是否定的。如果一个任务有很多小文件(远远小于块大小128m),则每个小文件也会被当做一个块,用一个map任务来完成,而一个map任务启动和初始化的时间远远大于逻辑处理的时间,就会造成很大的资源浪费。而且,同时可执行的map数是受限的。
- 是不是保证每个map处理接近128m的文件块,就高枕无忧?
- 答案也是不一定。比如有一个127m的文件,正常会用一个map去完成,但这个文件只有一个或者两个小字段,却有几千万的记录,如果map处理的逻辑比较复杂,用一个map任务去做,肯定也比较耗时。
- 小文件进行合并
- 在Map执行前合并小文件,减少Map数:
CombineHiveInputFormat
具有对小文件进行合并的功能(系统默认的格式)
- 在Map执行前合并小文件,减少Map数:
- 合理设置Reduce数
- Reduce个数并不是越多越好
- 过多的启动和初始化Reduce也会消耗时间和资源;
- 另外,有多少个Reduce,就会有多少个输出文件,如果生成了很多个小文件,那么如果这些小文件作为下一个任务的输入,则也会出现小文件过多的问题;
- 常用参数
- 输出合并小文件
SET hive.merge.mapfiles = true; -- 默认true
,在map-only任务结束时合并小文件SET hive.merge.mapredfiles = true; -- 默认false
,在map-reduce任务结束时合并小文件SET hive.merge.size.per.task = 268435456;
– 默认256MSET hive.merge.smallfiles.avgsize = 16777216;
– 当输出文件的平均大小小于该值时,启动一个独立的map-reduce任务进行文件merge
- sql优化
- 大表对大表:尽量减少数据集,可以通过分区表,避免扫描全表或者全字段
- 设置自动识别小表,将小表放入内存中去执行
hive小文件过多
- 使用 hive 自带的 concatenate 命令,自动合并小文件
- 调整参数减少 Map 数量
- 减少 Reduce 的数量
- 使用 hadoop 的 archive 将小文件归档
- Hadoop Archive 简称 HAR,是一个高效地将小文件放入 HDFS 块中的文件存档工 具,它能够将多个小文件打包成一个 HAR 文件,这样在减少 namenode 内存使用 的同时,仍然允许对文件进行透明的访问
hive sql翻译成MR的过程
- 6个阶段
- 词法、语法解析: Antlr 定义 SQL 的语法规则,完成 SQL 词法,语法解 析,将 SQL 转化为抽象语法树 AST T
- 语义解析: 遍历 AST Tree,抽象出查询的基本组成单元 QueryBlock;
- 生成逻辑执行计划:遍历 QueryBlock,翻译为执行操作树 OperatorTree;
- 优化逻辑执行计划:逻辑层优化器进行 OperatorTree 变换,合并 Operator,达到减少 MapReduce Job,减少数据传输及 shuffle 数据量;
- 生成物理执行计划: 遍历 OperatorTree,翻译为 MapReduce任务;
- 优化物理执行计划: 物理层优化器进行 MapReduce 任务的变换,生成最终的执行计划;
hive的数据倾斜
- 在 map 和 reduce 两个阶段中,最容易出现数据倾斜的就是 reduce 阶段,因为 map 到 reduce 会经过 shuffle 阶段,在 shuffle 中默认会按照 key 进行 hash,如果相同的 key 过多,那么 hash 的结果就是大量相同的 key 进入到同 一个 reduce 中,导致数据倾斜
- 发生数据倾斜的原因有两种:一是任务中需要处理大量相同 的 key 的数据。二是任务读取不可分割的大文件。
hive的数据倾斜解决方案
- MapReduce 和 Spark 中的数据倾斜解决方案原理都是类似的,以下讨论 Hive 使 用 MapReduce 引擎引发的数据倾斜,Spark 数据倾斜也可以此为参照。
- 空值引发的数据倾斜
- 实际业务中有些大量的 null 值或者一些无意义的数据参与到计算作业中,表中 有大量的 null 值,如果表之间进行 join 操作,就会有 shuffle 产生,这样所有 的 null 值都会被分配到一个 reduce 中,必然产生数据倾斜
- 解决方案
- 不让null值参与运算
where a.user is not null
- 给null值随机赋值,hash结果就不一样;
when a.user is nulll then concat("hive_",rand()) else a.user
- 不让null值参与运算
- 不同数据类型引发的数据倾斜
- 对于两个表join,表a中需要 join的字段key为 int,表b中key 字段既有string 类型也有 int 类型。当按照 key 进行两个表的 join 操作时,默认的 Hash 操作会 按 int 型的 id 来进行分配,这样所有的 string 类型都被分配成同一个 id,结 果就是所有的 string 类型的字段进入到一个 reduce 中,引发数据倾斜
- 解决方案
- 如果 key 字段既有 string 类型也有 int 类型,默认的 hash 就都会按 int 类型来 分配,那我们直接把 int 类型都转为 string 就好了,这样 key 字段都为 string, hash 时就按照 string 类型
user a left join logs b on a.id=cast(b.id as string);
(string与int 比较 string会自动转为int去比较)
- 如果 key 字段既有 string 类型也有 int 类型,默认的 hash 就都会按 int 类型来 分配,那我们直接把 int 类型都转为 string 就好了,这样 key 字段都为 string, hash 时就按照 string 类型
- 不可拆分大文件引发的数据倾斜
- 当对文件使用 GZIP 压缩等不支持文件分割操作的压缩方式,在日 后有作业涉及读取压缩后的文件时,该压缩文件只会被一个任务所读取
- 解决方案
- 所以,我们在对文件进行压缩时,为避免因不可拆分大文件而引发数据读取的倾斜, 在数据压缩的时候可以采用 bzip2 和 Zip 等支持文件分割
- 数据膨胀引发的数据倾斜
- 在多维聚合计算时,如果进行分组聚合的字段过多,如下:
select a,b,c,count(1)from log group by a,b,c with rollup
; - 注:对于最后的 with rollup 关键字不知道大家用过没,with rollup 是用来在分组统计数据的基础上再进行统计汇总,即用来得到 group by 的汇总信息。 如果上面的 log 表的数据量很大,并且 Map 端的聚合不能很好地起到数据压缩的 情况下,会导致 Map 端产出的数据急速膨胀,这种情况容易导致作业内存溢出的异常。如果 log 表含有数据倾斜 key,会加剧 Shuffle 过程的数据倾斜;
- 解决方案
- 将sql拆分为几条,但字段多了就不好拆分了,在 Hive 中可以通过参数
hive.new.job.grouping.set.cardinality
配置的方式 自动控制作业的拆解,该参数默认值是30
- 将sql拆分为几条,但字段多了就不好拆分了,在 Hive 中可以通过参数
- 在多维聚合计算时,如果进行分组聚合的字段过多,如下:
- 表连接时引发的数据倾斜
- 两表进行普通的 repartition join 时,如果表连接的键存在倾斜,那么在 Shuffle 阶段必然会引起数据倾斜。
- 解决方案
- 是将倾斜的数据存到分布式缓存中,分发到各个 Map 任务所在节点。 在 Map 阶段完成 join 操作,即 MapJoin,这避免了 Shuffle,从而避免了数据 倾斜
- 确实无法减少数据量引发的数据倾斜
- collect_list:将分组的某一列转换为数组输出,导致内存溢出;
- 解决方案
- 这类问题最直接的方式就是调整 reduce 所执行的内存大小。 调整 reduce 的内存大小使用
mapreduce.reduce.memory.mb
这个配置。
- 这类问题最直接的方式就是调整 reduce 所执行的内存大小。 调整 reduce 的内存大小使用
hive sql重点介绍
- 日期相差的天数:date_add(‘2020-10-28’,4)
- 格式化日期 date_format(date/timestamp/string) date_format(‘2020-10-28’,‘yyyy-MM-dd HH:mm:ss’)
- 连续问题:蚂蚁森林用户领取的减少碳排放量
-
蚂蚁森林中用户领取的减少碳排放量
-
找出来连续三天及以上碳排放量在100以上的用户 每天会领取多次
-
id dt lowcarbon 1001 2021-12-12 123 1002 2021-12-12 45 1001 2021-12-13 43 1001 2021-12-13 45 1001 2021-12-13 23 1002 2021-12-14 45 1001 2021-12-14 230 1002 2021-12-15 45 1001 2021-12-15 23 … …
-
思路 等差数列法
-
按照用户id及dt时间字段 分组 计算 每个用户单日 减少的碳排放,并having过滤
select id, dt, sum(lowcarbon) lowcarbon from test1 group by id,dt;t1 having lowcarbon > 100;
-
-
等差数列法 两个等差数列如果等差相同 则相同位置的数据相减得到的结果相同;按照用户分组 同时按照时间排序 计算每条数据的rank值,相当于插入123…
rank() over(partition by id order by dt) rk
-
将每行数据中的日期减去rank值 ;
date_sub(dt,rk) flag
-
按照用户及flag分组,求每一组有多少条数据,找出大于等于3条的数据
conut(*) ct ... group by id,flag having ct>=3
-
分组问题:
- 电商公司用户访问时间数据,将访问时间间隔小于60s的分为同一个组;
-
思路: 就是下面一行减去上面一行,直接减不了
-
直接减减不了 所以把上一条时间数据往下移动 开窗 lead往上 lag往下
lag(ts,1,0) over(partiton by id,order by ts) lagts
没有就默认是0
-
-
当前行可以减去上一行;
ts-lagts tsdiff
-
碰到>=60组数就加1 (另一个角度 遇到>=60的数据的总个数) 计算每个用户范围内从第一行到当前行tsdiff求大于等于60的条数(分组号)
sum(if(tsdiff>=60,1,0)) over(partiton by id order by ts) groupid
-
注意!!! 必须要加开窗 不然 只有一条数据 因为sum只有一个结果 从开始到当前行 默认就是这个 因此不用指定rows
-
间隔连续登陆问题,不要等差了,不然间隔几天就需要等差几次
-
思路
- 不要等差,要做 分组 ;
- 将上一行时间下移动;
- 将当前行时间减去上一行时间数据 datediff(dt1,dt2)(日期相减)
lag(ts,1,1970-01-01) over(partiton by id order by dt) dt2
datediff(dt1,dt2,)
- 按照用户分组同时按照时间排序 计算从第一行到当前行大于2的数据的总条数
sum(if(flag>2),1,0)
-
按照用户和flag分组,求最大时间减去最小时间并加上1 得到登陆天数
-
取连续登陆天数的最大值
7.
-
打折日期交叉问题 : 计算每个品牌总的打折销售天数 注意交叉日期 只算一次
- 将当前行以前的数据中最大的edt放置当前行 开窗到上一行
max(edt) over(partition by id order by stt rows between unbounded preceding and 1 preceding) maxedt
- 比较开始时间与移动下来的数据时间,如果开始时间大,不操作,否则,用移动下来的时间数据加1替换当前行的开始时间;如果是第一行数据,maxedt为null 不操作
id(maxedt is null,stt,if(stt>maxedt,stt,date_add(maxedt,1))) stt
- 日期可以直接比大小
- 将每行数据中的结束日期减去开始日期
diff(edt,stt) days
- 按照品牌分组 计算每条数据+1的总和 小于0的不记录
sum(if(days>=0,days+1,0)) days...group by id
- 同时在线问题
-
思路:流式数据的思想,在开始时间数据后 加1,代表有主播上线,关播后数据-1,有主播下线
-
select id,stt dt,1 p from test5 union select id,edt dt,-1 from test5;t1
-
按照时间排序,计算累加人数
- -
select id,dt,sum(p) over(order by dt) sum_p from t1
-
找出最大值
max(sum_p)