Hive学习笔记-HiveSQL(3)-Hive参数配置 运算符以及函数使用

Hive 客户端与属性配置

CLIs客户端和命令

Hive中的命令行客户端主要分为两种，第一代客户端hive和第二代客户端beeline。

第一代客户端$HIVE_HOME/bin/hive，主要功能是交互式或批处理执行Hive查询，以及启动hive相关的服务，如Metastore服务和HiveServer2服务。

hive # 无参数表示直接进入交互式客户端
-e <quoted-query-string> # 执行指定的sql语句，运行完后退出
-f <filename> # 执行指定的sql文件，运行完后退出
--H,--help # 查看帮助信息
-S,--silent # 静默模式
-v,-verbose # 详细模式，将执行sql回显
-service service_name # 启动hive的相关服务
-i # 进入交互模式之前运行初始化脚本

第二代客户端$HIVE_HOEM/bin/beeline，通过thrift连接到单独的HiveServer2服务上，再连接到Metastore服务。beeline支持的参数可以通过官方文档进行查询：HiveServer2 Clients - Apache Hive - Apache Software Foundation

Configuration Properties属性配置

Hivev除了默认的属性配置之外，还支持用户使用时修改配置。修改配置的时候，我们应该关注有哪些属性支持修改，属性的功能和作用，支持何种方式进行修改，修改后的作用时间和作用范围。

Hive的配置属性再HiveConf.java类中进行管理，详细的配置参数可以参考：Configuration Properties - Apache Hive - Apache Software Foundation

配置属性的方法一共有4种，分别如下：

方式1：hive-site.xml

在$HIVE_HOME/conf路径下可以添加hive-site.xml文件，可以定义配置属性。该配置文件会全局生效

方式2：--hiveconf命令行参数

hiveconf是一个命令行参数，在启动hive或者beeline命令行客户端的时候可以进行指定。该配置在整个会话session过程中有效，会话结束之后即失效

方式3：set命令

在会话中使用set命令进行配置，该配置对该会话中，set命令之后所有SQL语句生效

方式4：服务特定配置文件

服务特定配置文件主要包括hivemetastore-site.xml和hiveserver2-site.xml，分别表示对应服务特定的配置文件。

• Hive Metastore会加载可用的hive-site.xml以及hivemetastore-site.xml配置文件
• HiveServer2会加载可用的hive-site.xml以及hiveserver2-site.xml

如果HiveServer2以嵌入式模式使用元存储，则还将加载hivemetastore-site.xml

总结来说：

1. 配置优先级：set设置 > hiveconf参数 > hive-site.xml配置文件
2. Hive也会读入Hadoop配置，Hive的配置会覆盖Hadoop的配置进行生效

Hive 内置运算符

整体上，Hive支持的运算符可以主要可以分为五个种类，关系运算、算术运算和逻辑运算，还有字符串运算符以及复杂类型操作符，官方参考文档地址为：Language Manual UDF - Apache Hive - Apache Software Foundation

可以通过以下命令查看运算符的使用方式：

--显示所有的函数和运算符
show functions;

--查看运算符或者函数的使用说明
describe function count;

--使用extended查看更加详细的使用说明
describe function extended count;

下面对常见的运算符进行列举，基本都可以见名知义，只有一些特殊地方会注释说明

关系运算符：

= ==
<> !=
< <=
> >=
is null
is not null
like --like匹配: _表示任意单个字符, %表示任意数量字符
rlike --匹配正则表达式，是regexp_like的同义词
regexp --功能同rlike 

select 1 from test where 'haha' like 'ha%'
select 1 from test where 'haha' rlike '^h.*a$'
select 1 from test where 'haha' regexp '^h.*a$'

算术运算符：

+ - * / % 
div --取证
& | ^ ~  --位运算符

逻辑运算符：

and or not !
in 
not in
exists

字符串运算符：

|| --字符串拼接

复杂类型运算符：

--复杂类型构造
map(key1, value1, ...)
struct(val1, val2, ...)
named_struct(name1, val1, ...)
array(val1, val2, ...)
create_union(tag, val1, val2, ...)

--应用举例
select `array`(1, 2, 3) 
from test

--复杂类型取值
A[n] --array取值
M[key] --map取值
S.x --struct取值

Hive 函数基础

Hive中内置了很多函数，用于满足用户的不同需求。我们可以通过show functions来查看当前可用的所有函数，通过describe function extended func_name来查看函数的使用方式

Hive中的函数分为两大类：内置函数（Built-in Functions）和用户定义函数UDF（User-Defined Functions）

Hive 内置函数

内置函数根据应用整体可以分为：字符串函数、日期函数、数学函数、集合函数、条件函数、类型转换函数、数据脱敏函数、其他杂项函数。这里只分别介绍常用的函数

字符串函数：

--字符串拼接
select concat("a","b");

--带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('a', 'cn'));

--字符串截取函数：substr(str, pos[, len]) 或者  substring(str, pos[, len])
select substr("hahaha",-2); --pos是从1开始的索引，可以为负数
select substr("hahaha",2,2);

--正则表达式替换函数：regexp_replace(str, regexp, rep)
select regexp_replace('100-200', '(\\d+)', 'num');

--正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容
select regexp_extract('100-200', '(\\d+)-(\\d+)', 2);

--URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
select parse_url('http://www.hahaha.cn/path/p1.php?query=1', 'HOST');

--分割字符串函数: split(str, regex)
select split('apache hive', '\\s+');

--json解析函数：get_json_object(json_txt, path)
--path用于指定获取该json对象中的哪一部分，$表示json对象
select get_json_object('[{"key1":"value1","key2":"value2"},{"key1":"value3","key2":"value4"}]', '$.[1].key1');

--字符串长度函数：length(str | binary)
select length("hahaha");

--字符串反转函数：reverse
select reverse("hahaha");

--字符串转大写函数：upper,ucase
select upper("hahaha");
select ucase("hahaha");

--字符串转小写函数：lower,lcase
select lower("HAHAHA");
select lcase("HAHAHA");

--去空格函数：trim 去除左右两边的空格
select trim(" hahaha ");
--左边去空格函数：ltrim
select ltrim(" hahaha ");
--右边去空格函数：rtrim
select rtrim(" hahaha ");

--空格字符串函数：space(n) 返回指定个数空格
select space(4);

--重复字符串函数：repeat(str, n) 重复str字符串n次
select repeat("hahaha",2);

--首字符ascii函数：ascii
select ascii("hahaha");  --返回首字符h啊ascii码

--左补足函数：lpad
select lpad('hi', 5, '??');  --???hi
select lpad('hi', 1, '??');  --h

--右补足函数：rpad
select rpad('hi', 5, '??');

--集合查找函数: find_in_set(str,str_array)
select find_in_set('a','abc,b,ab,c,def');

日期函数：

--获取当前日期: current_date
select current_date();

--获取当前时间戳: current_timestamp
--同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();

--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();

--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");

--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');

--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');

--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');

--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);

--提取日期函数: to_date
select to_date('2009-07-30 04:17:52');
--提取年份函数: year
select year('2009-07-30 04:17:52');
--提取月份函数: month
select month('2009-07-30 04:17:52');
--提起天数函数: day
select day('2009-07-30 04:17:52');
--提取小时函数: hour
select hour('2009-07-30 04:17:52');
--提取分钟函数: minute
select minute('2009-07-30 04:17:52');
--提取秒函数: second
select second('2009-07-30 04:17:52');
--提取周函数: weekofyear 返回指定日期所示年份第几周
select weekofyear('2009-07-30 04:17:52');

数学函数：

--取整函数: round  返回double类型的整数值部分 （四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);

--向下取整函数: floor
select floor(3.1415926);

--向上取整函数: ceil
select ceil(3.1415926);

--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);

--二进制函数:  bin(BIGINT a)
select bin(18);
--进制转换函数: conv(BIGINT num, int from_base, int to_base)
select conv(17,10,16);
--绝对值函数: abs
select abs(-3.9);

集合函数：

--集合元素size函数: size(Map<K.V>) size(Array<T>)
select size(`array`(11,22,33));
select size(`map`("id",10086,"name","zhangsan","age",18));

--取map集合keys函数: map_keys(Map<K.V>)
select map_keys(`map`("id",10086,"name","zhangsan","age",18));

--取map集合values函数: map_values(Map<K.V>)
select map_values(`map`("id",10086,"name","zhangsan","age",18));

--判断数组是否包含指定元素: array_contains(Array<T>, value)
select array_contains(`array`(11,22,33),11);
select array_contains(`array`(11,22,33),66);

--数组排序函数:sort_array(Array<T>)
select sort_array(`array`(12,2,32));

条件函数：

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
--相当于三元运算符
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

--空判断函数: isnull( a )
select isnull("allen");
select isnull(null);
--非空判断函数: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);

--空值转换函数: nvl(T value, T default_value)
--如果value为空就将其转换成default_value
select nvl("allen","hahaha");
select nvl(null,"hahaha");

--非空查找函数: COALESCE(T v1, T v2, ...)
--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select COALESCE(null,11,22,33); --11
select COALESCE(null,null,null,33); --33
select COALESCE(null,null,null); --null

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
--case when then表达式
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

--nullif( a, b ):
-- 如果a = b，则返回NULL，否则返回第一个表达式的值
select nullif(11,11);
select nullif(11,12);

--assert_true(condition)
--如果'condition'不为真，则引发异常，否则返回null
SELECT assert_true(11 >= 0);
SELECT assert_true(-1 >= 0);

类型转换函数：主要用于显式的数据类型转换

--任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);

数据脱敏函数：主要完成数据脱敏，屏蔽原始数据

--mask
--将查询回的数据，默认大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); --自定义替换的字母

--mask_first_n(string str[, int n])
--对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);
--mask_last_n(string str[, int n])
--对后n个进行脱敏替换
select mask_last_n("abc123DEF",4);

--mask_show_first_n(string str[, int n])
--除了前n个字符，其余进行掩码处理
select mask_show_first_n("abc123DEF",4);
--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);

--mask_hash(string|char|varchar str)
--返回字符串的hash编码。
select mask_hash("abc123DEF");

其他杂项函数：

--如果你要调用的java方法所在的jar包不是hive自带的 可以通过使用add jar命令进行添加
--hive调用java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22);
--反射函数: reflect(class, method[, arg1[, arg2..]])
select reflect("java.lang.Math","max",11,22);

--取哈希值函数:hash
select hash("allen");

--current_user()、logged_in_user()、current_database()、version()

--SHA-1加密: sha1(string/binary)
select sha1("allen");
--SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);
--crc32加密:
select crc32("allen");
--MD5加密: md5(string/binary)
select md5("allen");

Hive 自定义函数

用户定义函数则可以根据输入输出的行数来分为3类：UDF、UDAF、UDTF

• UDF（User-Defined Function）：普通函数，一进一出
• UDAF（User-Defined Aggregation Function）：聚合函数，多进一出
• UDTF（User-Defined Table-Generating Function）：表生成函数，一进多出

UDF分类标准扩大化：

UDF分类标准本来针对的是用户自己编写开发实现的函数，但是现在可以扩大到Hive的所有函数中，包括内置函数和用户自定义函数。因为内置函数也可以满足根据输入输出划分的要求

自定义UDF函数实现步骤：

1. 自己写一个Java类，继承UDF，并重载evaluate方法，方法中实现函数的业务逻辑（注意重载）
2. 将程序打包成jar包，上传到服务器环境中（本地或者HDFS）
3. 在客户端命令行中添加jar包到Hive的classpath中：add jar /.../xxx.jar
4. 注册称为临时函数，即给自定义函数命名：create temporary function 函数名 as 类的全路径
5. 之后就可以在HQL中使用该函数了

在maven工程中应该引入下面的依赖：

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>3.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>3.1.3</version>
</dependency>

import org.apache.hadoop.hive.ql.exec.UDF;


public class MyUDF extends UDF {
    public String evaluate(...){
        ...
    }
}

自定义其他函数如UDTF，规则不尽相同。

Hive 函数高级用法

explode函数和侧视图

explode函数可以接受map、array类型的数据作为输入，然后把输入数据中的每个元素拆开成为一行数据。array类型则一行对应一列，map类型则一行对应两列（Key-Value）

explode函数是一个UDTF函数，它的执行结果可以理解成为一张虚拟的表，数据来源于源表。我们可以在select中查询源表数据，也可以只查询explode生成的虚拟表数据，但是不能直接查询源表和虚拟表的数据。（不能不做任何处理就查询分别属于两张表的字段）

explode函数将复杂类型展开之后，展开的每一行和源表的行会有一个对应关系，即源表中一行对应到展开表中的多行，根据这种关系我们可以使用join连接两张表，不过在Hive中给我们提供了侧视图lateral view语法来实现这种效果。explode函数一般会结合侧视图lateral view一起使用

语法如下：

--单独使用explode函数
select explode(col_map) from test_table;

--UDTF+侧视图
select …… from tabelA 别名1 lateral view UDTF(xxx) 别名2 as col1,col2,col3……;

这里的UDTF可以使用explode函数，当然侧视图的效果可以配合其他的表生成函数一起使用，不仅仅局限于explode函数。

• 别名1表示源表的别名
• 别名2表示UDTF函数生成的表别名
• as col1 col2 col3...表示给生成表中的列取名
• 侧视图的底层相当于就是自带条件的inner join

聚合函数

聚合函数的功能是对一组值执行计算并返回单一的值，是典型的UDAF函数，多进一出。通常搭配group by语法一起使用，在分组之后进行聚合操作

常见的聚合函数有：

• count：统计检索到的总行数
• sum：求和
• avg：求平均
• min：最小值
• max：最大值
• collect_set(col)：数据收集函数，去重
• collect_list(col)：数据收集函数，不qu'chong

同时Hive来提供增强聚合的方式，包括grouping sets、cube、rollup等函数。增强聚合多用于多维数据分析，它达到的效果是可以综合多个分组后的结果，然后利用字段grouping__id来表示该行是通过哪个分组group得到的。

如果利用已有的方式来达到这样的操作，应该使用union连接多个经过group by的表，并且需要手动填充null列来保证列数的对应，而使用增强聚合就可以方便地达到这样的效果

注意grouping__id是双下划线

grouping sets：

可以将多个group by逻辑写在一个sql语句中，等价于将不同维度group by结果集进行union all，grouping__id表示结果数据哪一个分组集合

select
    month,
    day,
    count(distinct cookieid) as nums,
    grouping__id
from cookie_info
group by month,day
grouping sets (month,day) --这里是关键
order by grouping__id;

--等价于
--注意这里补充的null，以及grouping__id位置上的1和2
select month,null,count(distinct cookieid) as nums,1 as grouping__id from cookie_info group by month
union all
select null as month,day,count(distinct cookieid) as nums,2 as grouping__id from cookie_info group by day;

cube：

cube表示根据group by维度的所有组合进行聚合

例如group by有a b c三个维度，则cube取所有的子集，共8个，注意包括空集，即表示不进行group by

select
    month,
    day,
    count(distinct cookieid) as nums,
    grouping__id
from cookie_info
group by month,day
with cube --cube语法，上面group by有两个元素，则最终结果一共有4个grouping__id
order by grouping__id;

--等价于

select null,null,count(distinct cookieid) as nums,0 as grouping__id from cookie_info
union all
select month,null,count(distinct cookieid) as nums,1 as grouping__id from cookie_info group by month
union all
select null,day,count(distinct cookieid) as nums,2 as grouping__id from cookie_info group by day
union all
select month,day,count(distinct cookieid) as nums,3 as grouping__id from cookie_info group by month,day;

rollup：

rollup是cube的子集，以最左侧的维度为主。只有当左侧维度出现，右侧维度才可能出现。相当于是一个递进的关系

例如group by有a b c三个维度，则rollup取的组合情况有()、(a)、(a, b)、(a, b, c)

select
    month,
    day,
    count(distinct cookieid) as nums,
    grouping__id
from cookie_info
group by month,day
with rollup --rollup语法，聚合维度应该为空、month、month+day三种
order by grouping__id;

--等价于

select null,null,count(distinct cookieid) as nums,0 as grouping__id from cookie_info
union all
select month,null,count(distinct cookieid) as nums,1 as grouping__id from cookie_info group by month
union all
select month,day,count(distinct cookieid) as nums,2 as grouping__id from cookie_info group by month,day;

窗口函数

基本语法

Hive中的窗口函数也和MySQL中的类似，语法如下：

Function(arg1, ...) over ([partition by <...>] [order by <...>][<window_expression>])

• Function表示需要应用的函数，可以是聚合函数（例如sum、max、avg等），可以是排序函数（例如rank、row_number等），可以是分析函数（例如lead、lag、first_value等）
• partition by：分组依据。
• order by：分组内部的排序规则
• window_expression：用于指定窗口的范围

over中的参数是可选的，但是出现与否的效果有所差异：

1. 没有partition by，则将整张表看作一个分组
2. 没有order by没有窗口范围，则计算分组中的所有行
3. 有order by没有窗口范围，则计算分组中从开始到当前行的范围

窗口表达式

窗口表达式提供给我们一种控制行范围的能力，语法如下：

--关键字是rows between,可选项有
preceding: 往前
following: 往后
current row: 当前行
unbounded preceding: 表示从前面的起点
unbounded following: 表示到后面的终点

举例如下：

--向前3行到当前行，共4行
rows between 3 preceding and current row

--向前3行到向后1行，共5行
rows between 3 preceding and 1 following

--当前行到最后一行
rows between current row and unbounded following

--第一行到最后一行，即分组内的所有行
rows between unbounded preceding  and unbounded following

窗口排序函数

主要用于给每个分组内的数据打上排序的标号，主要用于TopN的业务分析，注意窗口排序函数不支持窗口表达式

• row_number()：递增，不考虑重复，序号连续
• rank()：考虑重复，但是会挤占后续位置，序号可能不连续
• dense_rank()：考虑重复，但是不挤占后续位置，序号连续

窗口排序函数中还有一个ntile()，用于将每个分组内的数据分到指定的若干个桶内，即分为若干个部分，并且为每个桶分配一个桶编号。划分的依据主要是按照顺序平均分配，如果不能平均分配，则优先分配到较小编号的桶中，并且各个桶之间的行数相差最多为1。（对应需求可能是将数据排序后分成几个部分，业务人员只关心其中的一部分）

窗口分析函数

• lag(col, n, default)：返回当前行往上的第n行的对应值，相当于一个数据偏移的操作
  • 第一个参数为列名
  • 第二个参数为n，表示偏移多少行
  • 第三个参数为默认值，表示如果往上第n行为null的时候，取默认值default。如果不指定，则为null
• lead(col, n, default)：返回当前行往下的第n行的对应值
• first_value()：取分组内排序后的第一个值
• last_value()：取分组内排序后的最后一个值

其中first_value()和last_value()取的是分组范围内的第一个和最后一个，因此窗口范围很重要，要终点关注窗口范围。

抽样函数

抽样是一种用于识别和分析数据中子集的技术，用以发现整个数据集中的模式和趋势。在HQL中，可以通过三种方式来进行数据采样：随机采样，存储桶表采样和块采样

Random随机采样：

• 使用rand()函数确保随机获得数据，使用limit限制抽取的数据个数
• 优点是随机，缺点是速度慢
  • 推荐使用distribute+sort，底层执行效率更高

--需求：随机抽取2个学生的情况进行查看
select * from student
distribute by rand() sort by rand() limit 2;

--使用order by+rand也可以实现同样的效果 但是效率不高
select * from student
    order by rand() limit 2;

Block基于块随机抽样：

• 块采样允许随机获取n行数据、百分比数据或者指定大小的数据
• 采样粒度是HDFS文件块大小
• 优点是速度快，缺点是不随机

select * from student tablesample(1 rows);

--根据数据大小百分比抽样
select * from student tablesample(50 percent);

--根据数据大小抽样
--支持数据单位 b/b, k/k, m/m, g/g
select * from student tablesample(1k);

Bucket table基于分桶表抽样：

• 一种特殊的采样方法，针对分桶表进行优化
• 既随机，速度也较快

语法如下：

tablesample (bucket x out of y [on colname])

1. y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例
   • 例如，table总共分了4份（4个bucket），当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
2. x表示从哪个bucket开始抽取
   • 例如，table总bucket数为4，tablesample(bucket 4 out of 4)表示总共抽取（4/4=）1个bucket的数据，抽取第4个bucket的数据
   • 注意：x的值必须小于等于y的值，否则会报错failed:numerator should not be bigger than denominator in sample clause for table xxx
3. on colname表示基于什么抽
   • on rand()表示随机抽
   • on 分桶字段 表示基于分桶字段抽样 效率更高 推荐