首先介绍下什么是HIve?
1.基于Hadoop的开源的数据仓库工具,用于处理海量海量结构化数据。
2.Hive把HDFS中的结构化的数据文件映射成数据表。
3.Hive通过HiveSQL进行解析和转换,最终映射成一系列在hadoop上运行的mapreduce任务,通过执行这些任务完成分析和处理。
4.HiveSql和Mysql一样,都遵循着SQL的标准,因此它们很多语句都是一样的。
一、先复习下SQL的语句的结构
SELECT *(必须)FROM 表(数据源)WHERE 条件GROUP BY字段HAVING 条件ORDER BY字段(排序 默认 ASC)LIMIT 限制数据条数
注意:sql对大小写一般不敏感,一般命令用大写,表名,字段用小写
二、 SQL语句的执行顺序
第一步:确定数据源FROMJOINON 第二步:过滤数据WHERE GROUP BY (开始使用SELECT 中的别名,后面的语句中都可以使用)avg,sum.......HAVING第三步:查询数据SELECT第四步:显示数据DISTINCTORDER BY LIMIT
三、优化技巧
技巧一:列裁剪和分区裁剪
1.列裁剪:
列裁剪就是在查询时只读取需要的列。当列很多或者数据量
很大时,如果select 所有的列或者不指定分区,导致的全表
扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的
配置项是hive.optimize.cp,默认是true
2.分区裁剪:
分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪
优化相关的则是hive.optimize.pruner,默认是true。
技巧二:排序技巧 sort by 代替order by
HiveSQL中的order by与其他SQL语言中的功能一样,就是
将结果按某个字段全局排序,这会导致所有map端数据都进
入一个reduce中,在数据量大时可能会长时间计算不完。
如果使用sort by,那么就会视情况启动多个reducer进行排
序,并且保证每个reducer内局部有序。为了控制map端数
据分配到reduce的key,往往还要配合distribute by一同使
用。如果不加distribute by的话,map端数据就会随机分配
给reducer。
-- 未优化写法select a,b,cfrom tablewhere xxxorder by alimit 10;-- 优化写法select a,b,cfrom tablewhere xxxdistribute by asort by alimit 10;
技巧三:去重技巧 --用group by 来代替distinct
-- 取出user_trade表中全部支付用户-- 原有写法SELECT distinct user_nameFROM user_tradeWHERE dt>'0';--测试时长 43 s-- 优化写法SELECT user_nameFROM user_tradeWHERE dt>'0'GROUP BY user_name;--测试时长 29 s
注意:在极大的数据量(且很多重复值)时,可以先group by去重,再count()计数,效率高于直接count(distinct **)
技巧四 :聚合技巧–grouping sets 、cube、rollup
1.grouping sets
想知道用户的性别分布、城市分布、等级分布?
通常写法:
性别分布select sex,count(distinct user_id)from user_infogroup by sex;
城市分布select city,count(distinct user_id)from user_infogroup by city;
等级分布select level,count(distinct user_id)from user_infogroup by level;
通常要写三词sql语句
优化之后
select sex,city,levelcount(distinct user_id)from user_infogroup by sex,city,levelgrouping sets (sex,city,level)
注意:grouping sets 指定分组的维度 聚合结果均在同一列,分类字段用不同列来区分
2.cube :根据group by维度的所有组合进行聚合。
-- 性别、城市、等级的各种组合的用户分布SELECT sex,city,level,count(distinct user_id)FROM user_infoGROUP BY sex,city,levelGROUPING SETS (sex,city,level,(sex,city),(sex,level),(city,level),(sex,city,level));
优化之后:
select sexcity,level,count(distinct user_id)FROM user_infoGROUP BY sex,city,levelwith cube;
3.rollup:以最左侧的维度为主,进行层级聚合,是cube的子集。
计算出,每个月的支付金额,以及每年的总支付金额
一般写法:
SELECT a.dt,sum(a.year_amount),sum(a.month_amount)FROM(SELECT substr(dt,1,4) as dt,sum(pay_amount) year_amount,0 as month_amountFROM user_tradeWHERE dt>'0'GROUP BY substr(dt,1,4)UNION ALLSELECT substr(dt,1,7) as dt,0 as year_amount,sum(pay_amount) as month_amountFROM user_tradeWHERE dt>'0'GROUP BY substr(dt,1,7))aGROUP BY a.dt;
优化写法
SELECT year(dt) as year,month(dt) as month,sum(pay_amount)FROM user_tradeWHERE dt>'0'GROUP BY year(dt),month(dt)with rollup;
技巧五 换个思路解题
条条大路通罗马,写SQL亦是如此,能达到同样效果的SQL有很多种,要学会思路转换,灵活应用。
--在和都购买的用户--SELECT a.user_name FROM(SELECT distinct user_nameFROM user_tradeWHERE year(dt)=)aJOIN(SELECT distinct user_nameFROM user_tradeWHERE year(dt)=)b ona.user_name=b.user_name;
– 方式一
SELECT a.user_nameFROM(SELECT user_name,count(distinct year(dt)) asyear_numFROM user_tradeWHERE year(dt) in (,)GROUP BY user_name)aWHERE a.year_num=2;
– 方式二
SELECT user_name,count(distinct year(dt)) as year_numFROM user_tradeWHERE year(dt) in (,)GROUP BY user_namehaving count(distinct year(dt))=2;
技巧六:union all时可以开启并发执行
Hive中互相没有依赖关系的job间是可以并行执行的,最典型的就是多个子查询union all。在集群资源相对充足的情况下,可以开启并行执行。
参数设置:set hive.exec.parallel=true;
– 每个用户的支付和退款金额汇总
SELECT a.user_name,sum(a.pay_amount),sum(a.refund_amount)FROM( SELECT user_name,sum(pay_amount) as pay_amount,0 as refund_amountFROM user_tradeWHERE dt>'0'GROUP BY user_nameUNION ALLSELECT user_name,0 as pay_amount,sum(refund_amount) asrefund_amountFROM user_refundWHERE dt>'0'GROUP BY user_name)aGROUP BY a.user_name;
时间对比:
未开并发执行 103 s
开启并发执行 64 s
技巧七 表连接优化
1.小表在前,大表在后
Hive假定查询中最后的一个表是大表,它会将其它表缓存起来,然后扫描最后那个表。
2.使用相同的连接键
当对3个或者更多个表进行join连接时,如果每个on子句都使用相同的连接键的话,那么只会产生一个MapReduce job。
3.尽早的过滤数据
减少每个阶段的数据量,对于分区表要加分区,同时只选择需要使用到的字段。
技巧八 遵循严格模式
所谓严格模式,就是强制不允许用户执行3种有风险的HiveSQL语句,一旦执行会直接报错。
1.查询分区表时不限定分区列的语句。
2.两表join产生了笛卡尔积的语句。
3.要order by来排序但没有指定limit的语句。
要开启严格模式,需要将参数hive.mapred.mode设为strict。