【Hive

guduadmin09小时前

导航：

【Hive_01】hive关于数据库和表的语法
【Hive_02】查询、分组以及四个by
【Hive_03】单行函数、聚合函数、窗口函数、自定义函数、炸裂函数
【Hive_04】分区分桶表以及文件格式
【Hive_05】企业调优1（资源配置、explain、join优化）

1、函数简介
2、单行函数
- 2.1 算术运算函数
- 2.2 数值函数
- 2.3 字符串函数
- - （1）substring 截取字符串
  - （2）replace 替换
  - （3）regexp_replace 正则替换
  - （4）regexp 正则匹配
  - （5）repeat 重复字符串
  - （6）split 字符串切割
  - （7）nvl 替换null值
  - （8）concat 拼接字符串
  - （9）concat_ws 以指定分隔符拼接字符串或者字符串数组
  - （10）get_json_object 解析json字符串
  - 2.4 日期函数
  - - （1）unix_timestamp 返回当前或指定时间的时间戳
    - （2）from_unixtime
    - （3）current_date 当前日期
    - （4）current_timestamp 当前的日期加时间，并且精确的毫秒
    - （5）month：获取日期中的月
    - （6）day 获取日期中的日
    - （7）hour 获取日期中的小时
    - （8）datediff 两个日期相差的天数（结束日期减去开始日期的天数）
    - （9）date_add：日期加天数
    - （10）date_sub：日期减天数
    - （11）date_format:将标准日期解析成指定格式字符串
    - 2.5 流程控制函数
    - - （1）case when：条件判断函数
      - （2）if: 条件判断，类似于Java中三元运算符
      - 2.6 集合函数
      - （1）size 集合中元素的个数
        （2）map 创建map集合
        （3）map_keys 返回map中的key
        （4）map_values 返回map中的value
        （5）array 声明array集合
        （6）array_contains 判断array中是否包含某个元素
        （7）sort_array 将array中的元素排序
        （8）struct 声明struct中的各属性
        （9）named_struct 声明struct的属性和值
        3、高级聚合函数
        3.1 collect_list 函数
        3.2 collect_set 函数
        4、炸裂函数
        4.1 expload 函数
        4.2 posexplode 函数
        4.3 inline 函数
        4.4 Lateral View
        5、窗口函数（开窗函数）
        5.1 概述
        5.2 常用窗口函数
        （1）聚合函数
        （2）跨行取值函数
        （3）排名函数
        6、自定义函数
        7、自定义UDF函数
        8、其他
        8.1 hive当中去重的手段？面试题
        （1）distinct 去重
        （2）group by
        （3）利用窗口函数去重
        1、函数简介
        
        Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。
        
        好处：避免用户反复写逻辑，可以直接拿来使用。
        
        重点：用户需要知道函数叫什么，能做什么。
        
        Hive提供了大量的内置函数，按照其特点可大致分为如下几类：单行函数、聚合函数、炸裂函数、窗口函数。
        
        以下命令可用于查询所有内置函数的相关信息。
        
        1）查看系统内置函数
        
        hive> show functions;
        
        查看和string有关的函数：
        
        show functions like 'string%';
        
        2）查看内置函数用法
        
        hive> desc function upper; --查看upper函数的用法
        
        3）查看内置函数详细信息
        
        hive> desc function extended substring;
        
        2、单行函数
        
        单行函数的特点是一进一出，即输入一行，输出一行。进：传的参数，出：返回值。
        
        单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等。
        
        2.1 算术运算函数
        
        运算符描述
        A+B A和B相加
        A-B A减去B
        A*B A和B 相乘
        A/B A除以B
        A%B A对B取余
        A&B A和B按位取与
        A|B A和B按位取或
        A^B A和B按位取异或
        ~A A按位取反
        
        --算术运算函数 select 1&0; --结果为0 select 3&2; --结果为2。先把3和2转换为2进制，再把处理结果转换为十进制。
        
        查询出所有员工的薪水后加1显示:
        
        hive (default)> select sal + 1 from emp;
        
        2.2 数值函数
        
        1）round：四舍五入
        
        hive> select round(3.3); 3
        
        2）ceil：向上取整
        
        hive> select ceil(3.1) ; 4
        
        3）floor：向下取整
        
        hive> select floor(4.8); 4
        
        2.3 字符串函数
        
        （1）substring 截取字符串
        
        语法一：substring(string A, int start)
        返回值：string
        说明：返回字符串A从start位置到结尾的字符串
        语法二：substring(string A, int start, int len)
        返回值：string
        说明：返回字符串A从start位置开始，长度为len的字符串
        案例实操：
        
        （1）获取第二个字符以后的所有字符
        
        hive> select substring("wenxin",2);
        
        输出：
        
        enxin
        
        （2）获取倒数第三个字符以后的所有字符
        
        hive> select substring("wenxin",-3);
        
        输出：
        
        xin
        
        （3）从第3个字符开始，向后获取2个字符
        
        hive> select substring("wenxin",3,2);
        
        输出：
        
        nx
        
        （2）replace 替换
        
        语法：replace(string A, string B, string C)
        
        返回值：string
        
        说明：将字符串A中的子字符串B替换为C。
        
        hive> select replace('wenxinwenxin', 'w', 'W')
        
        输出：
        
        hive> WenxinWenxin
        
        （3）regexp_replace 正则替换
        
        语法：regexp_replace(string A, string B, string C)
        
        返回值：string
        
        说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符。
        
        案例实操：
        
        hive> select regexp_replace('100-200', '(\\d+)', 'num')
        
        上面这个涉及反斜杠转义字符的问题，所有有两个反斜杠
        
        输出：
        
        hive> num-num
        
        正则表达式可视化网址
        
        （4）regexp 正则匹配
        
        语法：字符串 regexp 正则表达式
        
        返回值：boolean
        
        说明：若字符串符合正则表达式，则返回true，否则返回false。
        
        （1）正则匹配成功，输出true
        
        hive> select 'dfsaaaa' regexp 'dfsa+'
        
        输出：
        
        hive> true
        
        （2）正则匹配失败，输出false
        
        hive> select 'dfsaaaa' regexp 'dfsb+';
        
        输出：
        
        hive> false
        
        （5）repeat 重复字符串
        
        语法：repeat(string A, int n)
        
        返回值：string
        
        说明：将字符串A重复n遍。
        
        hive> select repeat('123', 3);
        
        输出：
        
        hive> 123123123
        
        （6）split 字符串切割
        
        语法：split(string str, string pat)
        
        返回值：array
        
        说明：按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。
        
        hive> select split('a-b-c-d','-');
        
        输出：
        
        hive> ["a","b","c","d"]
        
        （7）nvl 替换null值
        
        语法：nvl(A,B)
        
        说明：若A的值不为null，则返回A，否则返回B。可以把其理解为给默认值的操作。
        
        hive> select nvl(null,0);
        
        输出：
        
        hive> 0
        
        （8）concat 拼接字符串
        
        语法：concat(string A, string B, string C, ……)
        
        返回：string
        
        说明：将A,B,C……等字符拼接为一个字符串
        
        hive> select concat('beijing','-','shanghai','-','shenzhen');
        
        输出：
        
        hive> beijing-shanghai-shenzhen
        
        （9）concat_ws 以指定分隔符拼接字符串或者字符串数组
        
        语法：concat_ws(string A, string…| array(string))
        返回值：string
        说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。
        
        hive>select concat_ws('-','beijing','shanghai','shenzhen');
        
        输出：
        
        hive> beijing-shanghai-shenzhen
        
        （10）get_json_object 解析json字符串
        
        语法：get_json_object(string json_string, string path)
        返回值：string
        
        说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
        
        案例实操：
        
        （1）获取json数组里面的json具体数据
        
        hive> select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0].name');
        
        输出：
        
        hive> 大海海
        
        （2）获取json数组里面的数据
        
        hive> select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0]');
        
        输出：
        
        hive> {"name":"大海海","sex":"男","age":"25"}
        
        2.4 日期函数
        
        （1）unix_timestamp 返回当前或指定时间的时间戳
        
        语法：unix_timestamp()
        返回值：bigint
        案例实操：
        
        hive> select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');
        
        输出：
        
        1659946088
        
        说明：-前面是日期后面是指，日期传进来的具体格式
        
        时间戳有10位的和13位的：
        
        10位的时间戳单位是秒
        13位的单位是毫秒
        无论是把时间戳转换为时间字符串，还是将时间字符串转换为时间戳，都需要考虑时区的问题。
        
        （2）from_unixtime
        
        用法：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式
        
        语法：from_unixtime(bigint unixtime[, string format])
        
        返回值：string
        
        案例实操：
        
        hive> select from_unixtime(1659946088);
        
        输出：
        
        2022-08-08 08:08:08
        
        （3）current_date 当前日期
        
        hive> select current_date;
        
        输出：
        
        2022-07-11
        
        （4）current_timestamp 当前的日期加时间，并且精确的毫秒
        
        hive> select current_timestamp;
        
        输出：
        
        2022-07-11 15:32:22.402
        
        （5）month：获取日期中的月
        
        语法：month (string date)
        返回值：int
        案例实操：
        
        hive> select month('2022-08-08 08:08:08');
        
        输出：
        
        8
        
        （6）day 获取日期中的日
        
        语法：day (string date)
        返回值：int
        案例实操：
        
        hive> select day('2022-08-08 08:08:08')
        
        输出：
        
        8
        
        （7）hour 获取日期中的小时
        
        语法：hour (string date)
        返回值：int
        案例实操：
        
        hive> select hour('2022-08-08 08:08:08');
        
        输出：
        
        8
        
        （8）datediff 两个日期相差的天数（结束日期减去开始日期的天数）
        
        语法：datediff(string enddate, string startdate)
        返回值：int
        案例实操：
        
        hive> select datediff('2021-08-08','2022-10-09');
        
        输出：
        
        -427
        
        （9）date_add：日期加天数
        
        语法：date_add(string startdate, int days)
        
        返回值：string
        
        说明：返回开始日期 startdate 增加 days 天后的日期
        
        案例实操：
        
        hive> select date_add('2022-08-08',2);
        
        输出：
        
        2022-08-10
        
        （10）date_sub：日期减天数
        
        语法：date_sub (string startdate, int days)
        
        返回值：string
        
        说明：返回开始日期startdate减少days天后的日期。
        
        案例实操：
        
        hive> select date_sub('2022-08-08',2);
        
        输出：
        
        2022-08-06
        
        （11）date_format:将标准日期解析成指定格式字符串
        
        hive> select date_format('2022-08-08','yyyy年-MM月-dd日')
        
        输出：
        
        2022年-08月-08日
        
        2.5 流程控制函数
        
        （1）case when：条件判断函数
        
        语法一：case when a then b [when c then d]* [else e] end
        返回值：T
        说明：如果a为true，则返回b；如果c为true，则返回d；否则返回 e
        
        hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tabl eName; mary
        
        语法二： case a when b then c [when d then e]* [else f] end
        返回值: T
        说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
        
        hive> select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from t ableName; mary
        
        case在执行的时候，是逐个条件去判断的，先判断第一个when，不满足才去判断第二个when。
        
        （2）if: 条件判断，类似于Java中三元运算符
        
        语法：if（boolean testCondition, T valueTrue, T valueFalseOrNull）
        
        返回值：T
        
        说明：当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull
        
        （1）条件满足，输出正确
        
        hive> select if(10 > 5,'正确','错误');
        
        输出：正确
        
        （2）条件满足，输出错误
        
        hive> select if(10 < 5,'正确','错误');
        
        输出：错误
        
        2.6 集合函数
        
        （1）size 集合中元素的个数
        
        hive> select size(friends) from test; --2/2 每一行数据中的friends集合里的个数
        
        （2）map 创建map集合
        
        语法：map (key1, value1, key2, value2, …)
        
        说明：根据输入的key和value对构建map类型
        
        案例实操：
        
        hive> select map('xiaohai',1,'dahai',2);
        
        输出：
        
        hive> {"xiaohai":1,"dahai":2}
        
        （3）map_keys 返回map中的key
        
        hive> select map_keys(map('xiaohai',1,'dahai',2));
        
        输出：
        
        hive>["xiaohai","dahai"]
        
        （4）map_values 返回map中的value
        
        hive> select map_values(map('xiaohai',1,'dahai',2));
        
        输出：
        
        hive>[1,2]
        
        （5）array 声明array集合
        
        语法：array(val1, val2, …)
        
        说明：根据输入的参数构建数组array类
        
        案例实操：
        
        hive> select array('1','2','3','4');
        
        输出：
        
        hive>["1","2","3","4"]
        
        （6）array_contains 判断array中是否包含某个元素
        
        hive> select array_contains(array('a','b','c','d'),'a');
        
        输出：
        
        hive> true
        
        （7）sort_array 将array中的元素排序
        
        hive> select sort_array(array('a','d','c'));
        
        输出：
        
        hive> ["a","c","d"]
        
        （8）struct 声明struct中的各属性
        
        语法：struct(val1, val2, val3, …)
        
        说明：根据输入的参数构建结构体struct类
        
        案例实操：
        
        hive> select struct('name','age','weight');
        
        输出：得到带有字段名的结构体
        
        hive> {"col1":"name","col2":"age","col3":"weight"}
        
        （9）named_struct 声明struct的属性和值
        
        hive> select named_struct('name','xiaosong','age',18,'weight',80);
        
        输出：
        
        hive> {"name":"xiaosong","age":18,"weight":80}
        
        3、高级聚合函数
        
        聚合效果：多进一出（多行传入，一个行输出）。
        
        1）普通聚合 count/sum
        
        3.1 collect_list 函数
        
        2）collect_list 收集并形成list集合，结果不去重
        
        hive> select sex, collect_list(job) from employee group by sex
        
        结果：
        
        女 ["行政","研发","行政","前台"] 男 ["销售","研发","销售","前台"]
        
        3.2 collect_set 函数
        
        3）collect_set 收集并形成set集合，结果去重
        
        hive> select sex, collect_set(job) from employee group by sex
        
        结果：
        
        女 ["行政","研发","前台"] 男 ["销售","研发","前台"]
        
        4、炸裂函数
        
        4.1 expload 函数
        
        4.2 posexplode 函数
        
        posexplode相比于explode还会返回索引。
        
        4.3 inline 函数
        
        inline里面接受的是一个数组，数组里面的元素是结构体
        
        4.4 Lateral View
        
        5、窗口函数（开窗函数）
        
        5.1 概述
        
        这里窗口范围描述的上一行到当前行指的是：计算当中实际参与的数据的上一行到当前行，而指的不是原表的上一行到当前行。
        
        并且对于基于值的窗口函数，只有当between和and当中有包含数值的时候，必须保证order by后面的column是数值型或者字符串的。
        
        total_amount：相当于截止到每一个下单日期的总额。
        
        由于进行了分区，因此当到达4的时候，和前面的3不在同一个分区，因此只包含其自身。
        有分区之后的第一行到当前行指的是：该分区当中的第一行到当前行。
        
        对于order by：在基于行和基于值两方面是不同的。在基于行上，如果不写order by，则是随机的，不确定，这样后面写range…between也没有意义【从负无穷到正无穷】。
        5.2 常用窗口函数
        
        按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。
        
        （1）聚合函数
        
        max：最大值。
        min：最小值。
        sum：求和。
        avg：平均值。
        count：计数。
        
        （2）跨行取值函数
        
        （1）lead和lag
        
        注：lag和lead函数不支持自定义窗口。
        
        （2）first_value和last_value
        
        （3）排名函数
        
        注：rank 、dense_rank、row_number不支持自定义窗口。
        6、自定义函数
        
        在hive当中有多种函数：比如单行函数、聚合函数、UDTF函数、窗口函数。而这四类函数都可以进行自定义。
        
        在具体使用时候，一般较少自定义，如果有也是自定义单行函数。
        1）Hive自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
        
        2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
        
        3）根据用户自定义函数类别分为以下三种：
        
        UDF（User-Defined-Function）：一进一出。
        UDAF（User-Defined Aggregation Function）：用户自定义聚合函数，多进一出。
        类似于：count/max/min
        UDTF（User-Defined Table-Generating Functions）：用户自定义表生成函数，一进多出。
        如lateral view explode()
        4）官方文档地址
        https://cwiki.apache.org/confluence/display/Hive/HivePlugins
        
        5）编程步骤
        
        （1）继承Hive提供的类
        
        org.apache.hadoop.hive.ql.udf.generic.GenericUDF org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
        
        （2）实现类中的抽象方法
        
        （3）在hive的命令行窗口创建函数
        
        添加jar。
        
        add jar linux_jar_path
        
        创建function。
        
        create [temporary] function [dbname.]function_name AS class_name;
        
        （4）在hive的命令行窗口删除函数
        
        drop [temporary] function [if exists] [dbname.]function_name;
        
        7、自定义UDF函数
        
        需求：
        
        自定义一个UDF实现计算给定基本数据类型的长度，例如：
        
        hive(default)> select my_len("abcd"); 4
        
        1）创建一个Maven工程Hive
        
        2）导入依赖
        
        org.apache.hive hive-exec 3.1.3
        
        3）创建一个类
        
        public class MyUDF extends GenericUDF { /** * 判断传进来的参数的类型和长度 * 约定返回的数据类型 */ @Override public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException { if (arguments.length !=1) { throw new UDFArgumentLengthException("please give me only one arg"); } if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){ throw new UDFArgumentTypeException(1, "i need primitive type arg"); } return PrimitiveObjectInspectorFactory.javaIntObjectInspector; } /** * 解决具体逻辑的 */ @Override public Object evaluate(DeferredObject[] arguments) throws HiveException { Object o = arguments[0].get(); if(o==null){ return 0; } return o.toString().length(); } @Override // 用于获取解释的字符串 public String getDisplayString(String[] children) { return ""; } }
        
        4）创建临时函数
        
        （1）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
        
        （2）将jar包添加到hive的classpath，临时生效
        
        hive (default)> add jar /opt/module/hive/datas/myudf.jar;
        
        （3）创建临时函数与开发好的java class关联
        
        hive (default)> create temporary function my_len as "com.wenxin.hive.udf.MyUDF";
        
        （4）即可在hql中使用自定义的临时函数
        
        hive (default)> select ename, my_len(ename) ename_len from emp;
        
        （5）删除临时函数
        
        hive (default)> drop temporary function my_len;
        
        注意：临时函数只跟会话有关系，跟库没有关系。只要创建临时函数的会话不断，在当前会话下，任意一个库都可以使用，其他会话全都不能使用。
        
        5）创建永久函数
        
        （1）创建永久函数
        
        注意：因为add jar本身也是临时生效，所以在创建永久函数的时候，需要制定路径（并且因为元数据的原因，这个路径还得是HDFS上的路径）。
        
        hive (default)> create function my_len2 as "com.atguigu.hive.udf.MyUDF" using jar "hdfs://hadoop102:8020/udf/myudf.jar";
        
        （2）即可在hql中使用自定义的永久函数
        
        hive (default)> select ename, my_len2(ename) ename_len from emp;
        
        （3）删除永久函数
        
        hive (default)> drop function my_len2;
        
        注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。
        
        永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。
        
        永久函数使用的时候，需要在指定的库里面操作，或者在其他库里面使用的话加上，库名.函数名。
        
        8、其他
        
        8.1 hive当中去重的手段？面试题
        
        （1）distinct 去重
        
        select distinct user_id, create_date from order_info
        
        distinct后面直接跟多个字段，则会根据多个字段进行去重。
        对多个字段进行去重的时候，不需要加括号！
        
        （2）group by
        
        select user_id, create_date from order_info group byuser_id,create_date
        
        按照两个字段去重，则会按照两个字段进行分组，因为group by会把两个字段相同的分到一组。并且一组只会返回一条数据。
        （3）利用窗口函数去重
        
        select user_id, create_date, row_number() over (partition by user_id,create_date) rn from order_info
        
        将每个分区里面排名为1的取出来即可。
        select user_id, create_date, from ( select user_id, create_date, row_number() over (partition by user_id,create_date) rn from order_info )t1 where rn=1;