Hive HQL使用技巧和函数

Hive HQL使用技巧

HQL与SQL基本类似。

注意点：

• HQL查询中，使用表达式计算出的值，表达式中若有一个变量为NULL，那么整个表达式都为NULL。如：sal*12+comm，若comm或sal为NULL，那么表达式结果也为NULL。可以使用函数nvl(var, if_null_value)判断空值和赋予默认值。

Fetch Task(Since ver0.10.0)：

该功能可以使简单查询语句不转换为MapReduce任务，直接操作HDFS。

• set hive.fetch.task.conversion=more;
• hive –hiveconf hive.fetch.task.conversion=more;
• 修改hive-site.xml。

HQL排序：

• 使用order by：后可跟：列，表达式，别名，列序号。
• 使用列序号要开启：set hive.groupby.orderby.position.alias=true;
• NULL默认排在最前。

HQL子查询：

• hive只支持：from和where子句中的子查询。
• 如果子查询返回的结果集当中含有NULL，不能使用NOT IN，可以使用IN。

Hive函数：

• 内置函数
• 自定义函数

字符函数：

• lower
• upper
• length：一个汉子长度为1。
• concat
• substr
• trim
• lpad
• rpad

收集函数：

• size

转换函数：

• cast：如：cast(1 as float)，cast(‘2017-06-12’ as date)。

日期函数：

• to_date
• year
• month
• day
• weekofyear
• datediff
• date_add
• date_sub

条件函数：

• coalesce：coalesce(var1, var2, var3, …)，从左到右返回第一个不为NULL的值。
• case…when…：条件表达式。

聚合函数：

• count
• sum
• min
• max
• avg

表生成函数：

• explode：

  select explode(map(1, ‘Tom’, 2, ‘Mary’, 3, ‘Mike’));
  结果:
  1 Tom
  2 Mary
  3 Mike

自定义函数（UDF：User Defined Function）

• 自定义类继承自org.apache.hadoop.hive.ql.UDF。
• 实现evaluate函数。
• 把程序打包放到目标机器。
• 进入hive客户端：hive>add jar < jar path >;
• 创建临时函数：hive>create temporary function < func_name > as < java class name >;
• 销毁自定义函数：hive>drop temporary function < func_name >;

UDF例子：
自定义字符串连接函数：

package demo.udf;

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

public class ConcatString extends UDF {
    public Text evaluate(Text a, Text b) {
        return new Text(a.toString() + "****" + b.toString();
    }    
}

• 打包成jar包上传至目标机器
• hive>add jar < your_jar >;
• hive>create temporary function myconcat as ‘demo.udf.ConcatString’;
• select myconcat(‘Hello’, ‘World’)
• 结果：Hello****World