Hive运算符和函数

day04 函数和运算符

1、运算符

建表和数据

create external table ext_table(
  id int,
  name string,
  age int,
  s_id int,
  score double
)row format delimited fields terminated by ','
location '/tmp/hive/external/ext_table';
​
1,'tom',18,1,70
1,'tom',18,2,50
1,'tom',18,3,80
2,'jack',20,1,60
2,'jack',20,2,50
2,'jack',20,3,70
3,'tom',21,1,50
3,'tom',21,2,80
3,'tom',21,3,30

1、关系运算

# 不等于
!= , <>
​
# 空值判断
is null
select "adc" is null;
​
# 非空判断
is not null 
​
# like比较
select 'a' like 'a%';

判断的是查的值还是本身的输入？？？？？？

2、数学运算

# + - * /
​
# 按位与&、按位或|、按位异或^
​
# 按位取反~

3、逻辑运算

# 逻辑与 and
​
# 逻辑或 or
​
# 逻辑非 not
select not 1=1;

2、函数

查看函数详情

describe function extended 函数名；

1、条件函数***

1、if(expr1,expr2,expr3)

当表达式expr1正确，返回expr2；expr1错误，返回expr3,相当于三目运算符。

select if(1=1,'true','false');// true
select if(1<>1,'true','false');// false

2、非空查找：coalesce(a1,a2,...)

返回第一个非空参数/非空列

select coalesce(null,'tom',null); //tom
​
select coalesce(id,name) from t_tab;//表格

表格，返回第一个非空列

create table t_tab(
  id int,
  name string
)
row format delimited fields terminated by ',';
​
insert into table t_tab(name) values(null),('tom');
​
select coalesce(id,name) from t_tab;
// 结果:
NULL
tom

3、条件判断：CASE

1、匹配值

当id=100时返回a,id=200时返回b,否则返回c
# 第一种写法
select case id when 100 then a when 200 then b else c end from case_table;

2、条件判断

# 当工资大于5000，返回high，当工资大于3000小于5000时，返回medium，否则返回low
​
# 建表、数据
create table sl_tab(
  id int,
  salary int
)
row format delimited fields terminated by ',';
​
insert into table sl_tab values(1,8000),(2,3000),(3,1000),(4,5000),(5,4000);
​
# 语句
select 
 case
  when salary>5000 then 'high'
  when salary>3000 then 'medium'
  else 'low'
end as salary_level 
from sl_tab s;

2、日期函数

# 1、时间戳转日期 from_unixtime
select from_unixtime(1704439397);
​
# 2、日期转UNIX时间戳 unix_timestamp
select unix_timestamp(current_date);//获取当前时间戳
select unix_timestamp();//获取当前时间戳
select unix_timestamp('2020-02-02 14:20:20');//指定格式日期转UNIX时间戳
​
# 3、日期时间提取日期 to_date
select to_date('2020-02-02 14:20:20');
​
# 4、日期提取年year、月month、日day、小时hour、分钟minute、秒second、周weekofyear
select year('2020-02-02 14:20:20');//提取年
select weekofyear('2020-02-02 14:20:20');//日期提取周
​
# 5、日期比较 datediff(date1,date2)
  --结果为相差几天
select datediff('2024-01-02','2024-01-01');// 结果为1
select datediff('2024-01-01','2024-01-02');// 结果为-1
​
# 6、日期增加date_add(date,int)、日期减少date_sub(date,int)
select date_add('2023-08-15',100);//从8.15日往后加100天的日期是多少
select date_sub('2023-11-23',100);

3、字符串函数

# 1、字符串长度length
select length('tom'); // 3
​
# 2、字符串反转reverse
select reverse('abc'); //cba
​
# 3、字符串转大写 upper,ucase 转小写lower,lcase
​
# 4、去空格函数：trim
左边去空格函数：ltrim
右边去空格函数：rtrim
正则表达式替换函数：regexp_replace
正则表达式解析函数：regexp_extract
URL解析函数：parse_url
空格字符串函数：space
重复字符串函数：repeat
首字符ascii函数：ascii
左补足函数：lpad
右补足函数：rpad
size:求取元素个数
​
# 3、字符串拼接concat****
select concat('abc','cd'); //abccd
​
# 4、字符串concat_ws**
使用指定分隔符连接字符串
concat_ws(separator,string1,string2... | array[string,...])
​
select concat_ws('.','www','baidu','com');//www.baidu.com
​
# 5、字符串截取 substr(str,pos [,len]),substring****
pos=index+1;如果pos为负数，则从后往前数
​
select substr('facebook',3); // cebook
select substr('facebook',-3); // ook
select substr('facebook',3,2); // ce
​
​
# 6、分隔字符串split(str,regex)
​
select split('a,b,c,dd',',');//["a","b","c","dd"]
select size(split('a,b,c,dd',','));//4
​
# 集合查找find_in_set
查找字符串在另一个被逗号隔开的字符串中存在的位置
​
select find_in_set('a','b,d,a,c');//3
1\还可以使用判断语句
select case when (find_in_set('a','b,d,a,c')) > 0 then 'exist' else 'not found' end as result; // exist
2\区分大小写，可以先都转为小写，再查找
select find_in_set(lower('A'),lower('a,b,c,D'));
​
# get_json_object
从json格式的字符串数据解析出所需字段的值，包含一个名为json_col的列，它存储了一些JSON数据。我们想要提取其中的某个字段。
create external table json_tab(
  js string
)
location '/tmp/hive/json';
​
{"name":"lizhang","age":10}
​
select get_json_object(js,'$.name') from json_tab;

4、自定义函数

1、UDF：(User-Defined-Function)一进一出

1、引入架包hive-exec 3.1.3

2、建类继承GenericUDF

实现三个方法：initialize初始化、evaluate实现功能、帮助文档

initialize方法只处理类型，就是在select 函数的时候，反序列化读取数据时，需要确认数据以什么类型返回的；

evaluate方法只处理真实数据。

package org.example;
​
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorUtils;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;
​
/**
 * @program: hadoop2
 * @description:
 * @author: Brooke
 * @create: 2024-01-06 14:40
 **/
public class ContainFunc extends GenericUDF {
​
    // 定义出读取出的文件类型
    private StringObjectInspector strIO;
​
    /**
     * 初始化只用来定义select使用函数后反序列化二进制文件为数据的数据类型
     *
     * @param objectInspectors
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
        // 限制了输入的值的类型只能是string类型，否则就会报错
        this.strIO = (StringObjectInspector) objectInspectors[0];
​
        // 确定返回的字段的类型为java类型的int，在evaluate方法里的返回值也要是java类型的int
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }
​
    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
        // 传进去的deferrndObjects是String的类型，所以不能直接返回，
        // Object str = deferredObjects[0].get();
        // 需要下面的工具类来拿出String的值
        String value = PrimitiveObjectInspectorUtils.getString(deferredObjects[0].get(), strIO);
        // 再转为int
        return Integer.parseInt(value);
    }
​
    /**
     * 这是帮助文档，在hive中使用desc function extended fuc_name时，会显示
     *
     * @param strings
     * @return
     */
    @Override
    public String getDisplayString(String[] strings) {
        return null;
    }
}

3、生成jar

lifecycle里先clean，在install

日志里有jar模块下的installing后有文件地址

D:\ComputerData\InstallBox\mavenRepository\org\example\customerFunction.0-SNAPSHOT\customerFunction-1.0-SNAPSHOT.jar

4、把jar导入到hive安装目录下的lib下(永久的函数加在lib下，临时的可以随便放)

5、添加jar到类路径

add jar /opt/demo/customerFunction-1.0-SNAPSHOT.jar;

6、创建临时函数

create temporary function ac(函数方法名) as '包名加类名';
​
creaet temporary function ac as 'org.example.ContainFunc';

7、使用方法

# 输入的类型必须为String，返回的是int类型数值
select ac('1');

8、案例

判断集合中是否包含数值的函数

package org.example;
​
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ListObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorUtils;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.BooleanObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.io.BooleanWritable;
​
/**
 * @program: hadoop2
 * @description:
 * @author: Brooke
 * @create: 2024-01-06 16:07
 **/
public class ArrayContain extends GenericUDF {
    // 先设置出两个字段的数据类型
    private ListObjectInspector arr;
    private PrimitiveObjectInspector arg;
    // 设置数组的元素的类型
    private PrimitiveObjectInspector ele;
    //    private BooleanObjectInspector result;//不存数据，
    private BooleanWritable result;
​
​
    /**
     * 两个参数，第一个字段是数组，第二个字段是值，判断数值是否在数组中存在，返回boolean类型
     * 类型详情：
     * 1、第一个字段类型List
     * 2、第一个字段内的元素为基本数据类型Primitive
     * 3、第二个字段为基本数据类型Primitive
     * 4、返回值为布尔类型Boolean
     * ****只初始化一次
     *
     * @param objectInspectors
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
        // 1、显示字段类型
        this.arr = (ListObjectInspector) objectInspectors[0];
        this.ele = (PrimitiveObjectInspector) arr.getListElementObjectInspector();
        this.arg = (PrimitiveObjectInspector) objectInspectors[1];
​
        // 保证第一字段的元素和第二字段类型一致,如果不一致，提出异常
        if (!ObjectInspectorUtils.compareTypes(ele, arg)) {
            throw new UDFArgumentTypeException(000, "元素类型不一致");
        }
​
        // 保证集合里的元素是支持相互比较的，否则抛异常
        // 什么元素不能比较？？？？？
        if (!ObjectInspectorUtils.compareSupported(ele)) {
            throw new UDFArgumentException("集合中的元素不能比较");
        }
​
        // 设定result初始值为false，在evalute方法里，满足条件再改为true
        result = new BooleanWritable(false);
​
        // 返回布尔类型
        return PrimitiveObjectInspectorFactory.writableBooleanObjectInspector;
    }
​
    /**
     * 判断数值是否包含在集合中，方法多次执行
     *
     * @param deferredObjects
     * @return
     * @throws HiveException
     */
    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
        // 多行数据数据会重复执行，所以让每次执行result都先设为false；
        result.set(false);
        // arr是PrimitiveObjectInspector类型，提供了方法getListLength()，能得到集合长度
        // deferredObjects[0].get()表示拿到了集合
        int listLength = this.arr.getListLength(deferredObjects[0].get());
        for (int i = 0; i < listLength; i++) {
            // arr还有getListElement()方法获得集合元素，第一参数是集合，第二参数是下标
            Object ele1 = arr.getListElement(deferredObjects[0].get(), i);
            if (ObjectInspectorUtils.compare(ele1, ele, deferredObjects[1].get(), arg) == 0) {
                result.set(true);
            }
            // 两个方法都可以
//            if (ele1.equals(deferredObjects[1].get())){
//                result.set(true);
//            }
        }
        return result;
    }
​
    /**
     * 帮助文档
     *
     * @param strings
     * @return
     */
    @Override
    public String getDisplayString(String[] strings) {
        return null;
    }
}

安装scala，idea里面下载插件

2、UDAF：(User-Defined Aggregation Function)

多进一出，比如count，sum，多行转一行

package com.hylk;
​
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator;
import org.apache.hadoop.hive.serde2.objectinspector.*;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorUtils;
import org.apache.hadoop.io.Text;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.*;
​
/**
 * eg:
 *  select customer_avg(course_id,completed_status,completed_date)
 *  from record
 *  group by user_id
 * ++++++++++++++++++++++++++++++++++++++++++++++
 * result:
 *  map
 **/
public class GeneratorUDAFAvg extends GenericUDAFEvaluator {
​
    private static final Logger LOG = LoggerFactory.getLogger(GeneratorUDAFAvg.class.getName());
​
    private PrimitiveObjectInspector courseIdOI;
    private PrimitiveObjectInspector completeStatusOI;
    private PrimitiveObjectInspector completeDateOI;
​
    private MapObjectInspector aggOI;
​
    @Override
    public ObjectInspector init(Mode m, ObjectInspector[] parameters) throws HiveException {
        super.init(m,parameters);
        if(m == Mode.PARTIAL1 || m == Mode.COMPLETE){
            courseIdOI = (PrimitiveObjectInspector)parameters[0];
            completeStatusOI = (PrimitiveObjectInspector) parameters[1];
            completeDateOI = (PrimitiveObjectInspector) parameters[2];
        }else{
            aggOI = (MapObjectInspector) parameters[0];
        }
        ObjectInspector mapKeyOI = ObjectInspectorFactory.getReflectionObjectInspector(String.class, ObjectInspectorFactory.ObjectInspectorOptions.JAVA);
        List structFieldName = new ArrayList<>();
        structFieldName.add("completed_status");
        structFieldName.add("completed_date");
​
        List structFieldObjectInspectors = new ArrayList<>();
        structFieldObjectInspectors.add(ObjectInspectorFactory.getReflectionObjectInspector(Integer.class , ObjectInspectorFactory.ObjectInspectorOptions.JAVA));
        structFieldObjectInspectors.add(ObjectInspectorFactory.getReflectionObjectInspector(String.class , ObjectInspectorFactory.ObjectInspectorOptions.JAVA));
​
        StandardStructObjectInspector mapValueOI = ObjectInspectorFactory.getStandardStructObjectInspector(structFieldName, structFieldObjectInspectors);
        return ObjectInspectorFactory.getStandardMapObjectInspector(mapKeyOI, mapValueOI);
    }
​
    @Override
    public AggregationBuffer getNewAggregationBuffer() throws HiveException {
        return new AvgBuffer();
    }
​
    @Override
    public void reset(AggregationBuffer aggregationBuffer) throws HiveException {
        AvgBuffer agg = (AvgBuffer) aggregationBuffer;
        agg.cache.clear();
    }
​
    @Override
    public void iterate(AggregationBuffer aggregationBuffer, Object[] objects) throws HiveException {
​
        AvgBuffer agg = (AvgBuffer) aggregationBuffer;
        agg.put(
                PrimitiveObjectInspectorUtils.getString(objects[0] , courseIdOI),
                PrimitiveObjectInspectorUtils.getInt(objects[1] , completeStatusOI),
                PrimitiveObjectInspectorUtils.getString(objects[2] , completeDateOI)
        );
    }
​
    @Override
    public Object terminatePartial(AggregationBuffer aggregationBuffer) throws HiveException {
        AvgBuffer agg = (AvgBuffer) aggregationBuffer;
        return agg.cache;
    }
​
    @Override
    public void merge(AggregationBuffer aggregationBuffer, Object par) throws HiveException {
        if(par != null){
            AvgBuffer agg = (AvgBuffer) aggregationBuffer;
​
            Map> map = (Map>)ObjectInspectorUtils.copyToStandardJavaObject(par, aggOI);
​
            for(Map.Entry> entry: map.entrySet()){
                String courseId         = entry.getKey().toString();
                List