Hive常用函数和性能调优

最新推荐文章于 2022-11-10 11:09:34 发布
最新推荐文章于 2022-11-10 11:09:34 发布
阅读量940 收藏 2
点赞数
分类专栏： Hive学习 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_32648939/article/details/105179856
版权

    最近有重新回看了《Hadoop权威指南》，又想起来总结一些博文了，所以针对Hive常用函数和性能调优又仔细查了很多资料，写了这篇文章，还是一样，感谢各位在网络上的分享！！！

    博客分享：

        https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

    一.Hive常用函数

    1.时间处理函数

函数名称返回值作用备注
current_date()string返回当天时间返回时间格式‘yyyy-MM-dd’
unix_timestamp()bigint返回当前时区的时间戳 
from_unixtime(bigint unixtime[, string format])string返回Unix时间戳转日期格式可以通过format定义返回结果样式
unix_timestamp(string date,string pattern) bigint返回制定格式转化至时间戳转化失败返回0
to_date(string date)string返回时间字段中的日期部分格式错误返回null
year(string date)int返回时间字段中的‘年’部分month,day,hour,minute,second使用方法相同
datediff(string enddate,string startdate) int返回（结束日期-开始日期）的数值仅返回时间上的天数差距

    2.类型转换函数

        隐式类型转换：所有整数类型都可以隐式转换为一个范围更广的类型。所有整数类型 + float + String都可以转换为Double类型。时间戳和日期可以隐式地转换成文本类型。BOOLEAN不能做任何的类型转换。

        显式类型转换：

            cast( 表达式a as 数据类型b )：可以将表达式a的数据类型显式的转化为数据类型b，并且转化失败会返回null。

    3.条件判断函数

函数名称返回值作用备注
if(boolean condition, T valueTrue, T valueFalse)T

判断condition的正误

正确则返回valueTrue，错误则返回valueFalse

 
coalesce(T value1,T value2,...)T返回参数列表中第一个非空数值如果所有数值均为null，则返回null
case when conditionA then answerB else answerC endT如果conditionA成立，则返回answerB否则返回answerC可以连续 [when a then b] 对多种情况进行判断和结果返回

    4.空值判断函数

        hive表中默认将null存储为\N。可以使用is null和is not null来判断某值是否是空值。可以结合条件判断函数使用（ if( valueA is null ,'true' , 'false' ) ）。

        也可以直接使用nvl()函数对数值列进行处理，同理还可以使用条件判断函数中的coalesce()函数。

            nvl( 表达式a , 表达式b )：判断表达式a是否为null，若是则返回表达b，若不是则返回表达式a。等价于 if( 表达式a is null ,表达式b , 表达式a ) 。

        if函数和nvl函数的区别：两者的区别在于if函数的参数值类型可以不同，但是nvl函数的参数类型必须为同类型。

        在处理空字符串 '' 时，可以使用 a = '' 或者 a <> ''进行判断。

    5.字符串处理函数

函数名称返回值作用备注
length(string a)int返回字符串a的长度 
reverse(string a)string返回字符串a的字符串结果翻转 
concat(string a, string b,…)string返回字符串a和字符串b和...的字符串拼接结果 
concat_ws(string sepString, string a, string b,…)string返回字符串a和字符串b和...的字符串拼接结果，且字符串之间使用sepString连接 
substr(string a,int start[,int end])string返回字符串a从start位置开始到末尾【到end】截取出来的字符串 
upper(string a)/ucase(string a)string返回字符串a的大写格式lower(string a)和lcase(string a)返回字符串a的小写格式
trim(string a)string返回字符串a去除前后两端空格的字符串ltrim(string a)和rtrim(string a)单独去除左端/右端空格
regexp_replace(string a, string pattern, string b)string将字符串a中符合正则表达式字符串pattern的内容替换为字符串b的内容正则表达式pattern字符串需要注意转义
regexp_extract(string a, string pattern, int index)string将字符串a按照正则表达式字符串pattern的内容拆分，并且返回下标为index的内容类似于replace()函数中的小括号和$取值的方式，下标0为原始字符串
split(string a, string b)array将字符串a按照字符串b进行分割，返回字符串数组注意转义
instr(string a, string b) int若字符串b在字符串a中存在则返回具体位置(int)，不存在则返回0 
parse_url(string a, string partUrl[, string key])string解析url字符串a，并且根据具体所需partUrl返回对应位置数据信息。也可通过使用‘QUERY’和具体的key返回指定的参数数值

partUrl可使用的参数包括：

[HOST(域名),PATH(路径),QUERY(参数),REF(定位),

PROTOCOL(协议)FILE(路径+参数),AUTHORITY(域名),USERINFO]

parse_url_tuple(string a, string partUrl[, string key])string解析url字符串a，用法与parse_url类似，可以同时返回多个key的value值 
get_json_object(string a string path)string解析json字符串a，并且根据path获取json字符串中对应key的value值

字符串a必须符合json格式，若字符串无效则返回null

json_tuple(string a, string keyValue,...)string解析json字符串a，并且根据keyValue取得对应key的value值get_json_object可以处理更加复杂的json，json_tuple可以处理一次想取出多个key的场景
lpad(string a, int len, string b)string将字符串a左侧用字符串b补全至长度为lenrpad(string a, int len, string b)作用于右侧
find_in_set(string a, string strList) int返回字符串a在strList中出现的位置（strList必须是用逗号分隔的字符串）如果没有找到对应字符，则返回0

    6.其他类型处理函数

函数名称返回值作用备注
explode() 

用于array类型数据时，array中每个元素生成一行

用于map类型数据时，map中每个键值对一行，key/value各自一列

不能关联原有的表中的其他字段。不能与group by、cluster by、distribute by、sort by联用。不能进行UDTF嵌套。不允许选择其他表达式。

array_contains(array, T b)T/F用于判断array类型数据中是否存在T类型的b可用于where条件查询语句
lateral view explode(array) 

用于将单行array类型数据拆分为分开的多行数据结果集

array位置为想拆分的数组字段，单独拆分一列，相当于是该列产生的结果集与原表进行笛卡尔积，也可以同时拆分多列

 
collect_list(arrayName) 将分组中的某列转为一个数组返回，不去重 
collect_set(arrayName) 将分组中的某列转为一个数组返回，去重 

    7.分析窗口函数

        用于分区排序（ over( partition by ) ），动态group by，计算topN（ row_number() ）等场景。

        定义window子句（eg. : rows between current row and 2 preceding），如果不指定ROWS BETWEEN，默认是从起点到当前行：

        2 PRECEDING：前两行

        2 FOLLOWING：后两行

        CURRENT ROW：当前行

        UNBOUNDED：起点

        UNBOUNDED PRECEDING：从前面的起点

        UNBOUNDED FOLLOWING：从后边的终点

函数名称返回值作用备注
rank()序列函数

并列跳跃排名：在返回排名结果字段中并列名次的下一名将为并列数量+并列名次（如并列第一两名，则排行为1，1，3）

不支持window子句

 
dense_rank()序列函数

并列不跳跃排名：在返回排名结果字段中并列名次的下一名将为正常名次（如并列第一两名，则排行为1，1，2）

不支持window子句

 
row_number()序列函数

顺序排序：在返回排名结果字段中无视并列名次（如并列第一两名，则排行为1，2，3）

不支持window子句

 
first_value()取值函数返回当前排序分组内第一个数据的值（也可通过改变排序次序获取最大值或最小值） 
last_value()取值函数返回当前排序分组内最后一个数据的值（也可通过改变排序次序获取最大值或最小值） 
ntile(int a)序列函数

将返回结果分为a份，可用于取百分比数量数据

 
lag(string lineName[, int a])上提函数用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）		 
lead(string lineName[, int a])下沉函数

作用同上，往下第n行值，其余相同

 
cume_dist()序列函数用于统计指定值占总数的百分比 

    二.Hive性能调优

    1.Hive配置调优（https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties）

    所有黑体+下划线一般都不会选择默认值，用于调优，而所有正常字体一般可选择默认值。

配置语句默认值配置作用备注
hive.auto.convert.joinfalse

是否启用Hive基于输入文件大小的，将普通join转换为mapjoin的优化

MapJoin是常用的优化操作，适用于小表JOIN大表的场景，由于表的JOIN操作是在Map端且在内存进行的，所以其并不需要启动Reduce任务也就不需要经过shuffle阶段，从而能在一定程度上节省资源提高JOIN效率

该参数为true时，Hive自动对左边的表统计量，如果是小表就加入内存，即对小表使用mapjoin
hive.mapjoin.smalltable.filesize25000000小表的输入文件大小的阈值（单位：字节）；如果文件大小小于此阈值，它将尝试将普通join转换为mapjoin该参数作为大表，小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行
hive.auto.convert.join.noconditionaltasktrue是否启用Hive基于输入文件大小的，将普通join转换为mapjoin的优化将普通join转换为mapjoin时，是否将多个mapjoin转换为一个mapjoin
hive.auto.convert.join.noconditionaltask.size10000000将普通join转换为mapjoin时，最多可以将最大值为多少的小表转换为一个mapjoin多个mapjoin转换为1个时，所有小表的文件大小总和的最大值
hive.exec.parallelfalse是否并行执行jobs，适用于可以并行执行的MapReduce job 
hive.exec.parallel.thread.number8设定最多可以并行执行多少个job代表一次 SQL 计算中允许并行执行的 job 个数的最大值
hive.execution.enginemr（2.0.0后弃用）选择执行引擎。可选项有MapReduce，Tez和Spark当执行引擎发生改变时，可以通过对对应引擎进行调优达到对hive语句执行的优化
mapred.reduce.tasks-1

默认每一个job对应的reducer的数量，通常设定为可用主机的近似值

 
hive.exec.reducers.bytes.per.reducer256000000每一个reducer处理的数据量大小 
hive.exec.reducers.max1009每一个job最大可以使用的reducer数量 
hive.map.aggrtrue在使用group by查询语句时，是否使用map端聚合 
hive.groupby.mapaggr.checkinterval100000在map端group by聚合时处理的数据行数 
hive.map.aggr.hash.min.reduction=0.5;0.5进行聚合的最小比例，如果聚合后数据量和处理的数据行数的比值大于该数字，则将关闭聚合。设置为1可以确保从未关闭聚合 
hive.map.aggr.hash.percentmemory0.3当使用map端group by聚合时，map端聚合可使用的内存上限 
hive.groupby.skewindatafalse在使用group by时是否进行数据倾斜优化 

    2.Hive语句调优

       (1).在表连接时将小表放在前边，大表放在后边。Hive会采取将小表都放在内存中，而后扫描大表的方式。

       (2).慎重使用COUNT(DISTINCT col)，distinct会将数据保存在内存中，加快速度，但是可能会发生OOM，可以通过使用ROW_NUMBER() OVER(PARTITION BY col)等方式替代。

       (3).尽量不使用select *而是使用具体字段进行查询操作。

       (4).尽量采取谓词下推（将数据过滤表达式尽可能的移动至靠近数据源的位置，以便尽可能早的处理表达式语句和尽可能少的处理无关数据）的方式书写语句。

       (5).尽可能不使用order by语句或在使用order by语句时同limit语句一齐使用，否则可能会导致只有一个reducer运行的结果。

无语梦醒
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
最强HiveSQL开发指南.pdf
11-30
《最强HiveSQL开发指南》是一本专注于Hive性能调优和实战操作的教程，旨在帮助读者深入理解和熟练运用Hive进行大数据处理。Hive作为Apache Hadoop生态系统中的一个组件，主要用于处理和分析大规模分布式存储的数据。...
Hive函数09_条件判断函数（IF,NVL,COALESCE,CASE WHEN）
清平乐的技术专栏
05-07 1514
1.if() 语法格式：if（条件表达式，结果1，结果2） 解释： 当条件为TRUE时，返回结果1；否则返回结果2，相当于java中的三目运算符,只是if后面的表达式类型可以不一样。 hive> select if(a=a,’bbbb’,111) fromlxw_dual; bbbb hive> select if(1<2,100,200) from...
Hive性能调优总结
follweme888的专栏
06-25 774
一、Fetch抓取 1、理论分析 Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。 在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走map
MySQL case when与if的性能探索
Fan_1504251998的博客
11-10 4829
MySQL，case when与if的性能探索
Hive-case when和if
m0_46538284的博客
12-01 1138
case when 表信息 select dname , sum(case gender when '男' then 1 else 0 end) M , sum(case gender when '女' then 1 else 0 end) F from tb_emp group by dname ; 结果 if select dname , sum(if(gender='男',1,0)) M , sum(if(gender='女',1,0)) F from tb_emp gr
hive中的空值处理函数 nvl、 nvl2、if、 coalesce
yuanyuan___的博客
06-27 3457
hive中的空值处理函数
Hive编程指南
04-25
**Hive编程指南** Hive是Apache软件基金会开发的一个数据仓库工具，主要用于处理和分析大量分布式存储的数据。...这份指南通常会涵盖上述所有知识点，并可能包含更多关于性能调优、故障排查和最佳实践的内容。
sparkSQL底层实现原理-sparkSQL调优资料包附课件、代码、资料
10-08
SparkSQL是Apache Spark项目的一部分，它提供了一个用于处理结构化和半结构化数据的...调优主要涉及数据组织、内存管理、执行计划优化等多个方面，通过对这些策略的深入理解和应用，可以显著提升SparkSQL的性能和效率。
大数据场景化解决方案.pdf
12-24
【大数据概念】 ...数据倾斜是Hive常见的性能瓶颈，可以通过调整参数如`hive.map.aggr`和`hive.groupby.skewindata`来优化。Hive将查询转化为多个阶段，当阶段间关联性不大时，可以并行执行以提高效率。
基于spark sql引擎的即席查询服务.zip
最新发布
04-25
8. **监控与调优**：利用Spark的监控工具，如Web UI、Event Log和Metrics System，监控任务执行情况，进行性能调优。 综上所述，基于Spark SQL引擎的即席查询服务旨在提供一个高性能、灵活且易于使用的平台，满足...
大数据开发技术之Hive开窗函数的使用
m0_58371965的博客
09-06 412
与聚合函数类似，开窗函数也是对行集组进行聚合计算。但是它不像普通聚合函数那样，每组通常只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。 常见的格式如下： FUNCTION_NAME([argument_list]) OVER ( [PARTITION BY window_partition,…] [ORDER BY window_ordering, … [ASC|DESC]]) [ { ROWS | RANGE } BETWEEN frame_start A
数仓工具—Hive语法之窗口函数first_value和last_value(12)
热门推荐
01-06 6万+
1. first_value和last_value 见名知意,就是计算当前窗口截止当前行的第一个值和最后一个值(或者是排名的第一名个最后一名) 2. 其实我们可以理解为取当前窗口截止当前行的最值(最大值和最小值) 3. 这两个窗口函数其实用的不多，也没什么常见的场景
大数据Hive之group by、count、over
luo981695830的博客
01-05 2315
group by和count在Hive与mysql用法基本一致， 今天说一下group by、count组合用法在hive中的需要注意的地方，以及实现一个功能的sql写法 目标：统计表中id和总条数 模型:tab(id string) 输出：id、count(*) 需求1：输出表中总记录数 select count(*) from tab; 需求2：输出表中id，总条数 select id,count(*) from tab; 这样计算就会报错 解决方式两...
hive 中的max, min,first,sum,avg,count,比较,运算对null的处理
weixin_43381459的博客
10-17 1万+
在用hive处理数据过程中发现有些数值不可避免的出现null的情况，以下是hive中null参与运算的处理属性，以便更好的处理数据： max() min() first() sum() avg() count() 总结： 内置函数在处理null时自动忽略 比较运算 总结：hive在处理比较运算时，对包含null的返回结果也为null，且均为否 计算运算 总结：计算运算时包含nu...
Hive函数及性能优化
qq_40333693的博客
09-29 392
Hive函数分类 从输入输出角度分类 标准函数：一行数据中的一列或多列为输入，结果为单一值 聚合函数：多行的零列到多列为输入，结果为单一值 表生成函数：零个或多个输入，结果为多列或多行 从实现方式分类 内置函数 自定义函数 UDF：自定义标准函数 UDAF：自定义聚合函数 UDTF：自定义表生成函数 内置函数 Hive提供大量内置函数供开发者使用 标准函数 字符函数 类型转换函数 数学函数 日期函数 集合函数 条件函数 聚合函数 表生成函数 字符函数： 返回值 函数 描述 string concat(str
Hive窗口函数
weixin_44516261的博客
10-03 1073
Hive窗口函数 1）定义 窗口函数属于sql中比较高级的函数 mysql从8.0版本才支持窗口函数，5.6,5.7都没有窗口函数 oracle 里面一直支持窗口函数 hive也支持窗口函数 以下函数才是窗口函数 窗口函数（13个）： LEAD LEAD(col,n, default_val)：往后第n行数据 col 列名 n 往后第几行 默认为1 默认值 默认null LAG LAG(col,n,default_val)：往前第n行数据 col 列名 n 往前第几行 默认为1
hive 开窗函数之lag,lead,first_value,last_value
鸭梨的博客
11-28 3838
概念 测试数据 -- 数据准备： 姓名,购买日期,购买数量 saml 2018-01-01 10 saml 2018-01-08 55 tony 2018-01-07 50 saml 2018-01-05 46 tony 2018-01-04 29 tony 2018-01-02 15 saml 2018-02-03 23 mart 2018-04-13 94 saml 2018-04-06 42 mart 2018-04-11 75 mart 2018-04-0
Hive中常用的函数
qq_37162437的博客
04-21 676
Hive中常用的函数 常用函数的用法 1、first_value:取分组内排序后，截止到当前行，第一个值 2、last_value:取分组内排序后，截止到当前行，最后一个值 3、lead(col,n,default) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL） 4、ro...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

无语梦醒

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值