Hive内置函数总结

1、相关帮助操作函数

查看内置函数：show functions;
显示函数的详细信息：desc function abs;
显示函数的扩展信息：desc function extended concat;

2、学习内置函数的终极心法

第一步：把 show functions 命令的所有函数都仔细阅读一遍，建立整体认识和印象
第二步：使用 desc extended function function_name 方式来查询该 函数 的详细使用方式
第三布：通过以上的方式找到了函数，但是不清楚使用方式，请按照："hive函数 function_name详解"
这种格式的关键词进行搜索
第四步：通过以上的方式，没有找到合适的函数可以使用，那么请组合函数使用，或者自定义函数

3、测试内置函数的快捷方式

第一种方式：直接使用，不用from语法分支，例如：

select concat('a','a')

第二种方式：创建dual表，帮助我们写完整SQL

1、创建一个dual表create table dual(id string);
2、load一个文件（一行，一个空格）到dual表
3、select substr('huangbo',2,3) from dual;

4、内置函数列表

一、关系运算

1. 等值比较: =
2. 等值比较:<=>
3. 不等值比较: <>和!=
4. 小于比较: <
5. 小于等于比较: <=
6. 大于比较: >
7. 大于等于比较: >=
8. 区间比较
9. 空值判断: IS NULL
10. 非空判断: IS NOT NULL
11. LIKE比较: LIKE
12. JAVA的LIKE操作: RLIKE
13. REGEXP操作: REGEXP

二、数学运算

1. 加法操作: +
2. 减法操作: –
3. 乘法操作: *
4. 除法操作: /
5. 取余操作: %
6. 位与操作: &
7. 位或操作: |
8. 位异或操作: ^
9．位取反操作: ~

三、逻辑运算

1. 逻辑与操作: AND、&&
2. 逻辑或操作: OR、||
3. 逻辑非操作: NOT、!

四、复合类型构造函数

1. map结构
2. struct结构
3. named_struct结构
4. array结构
5. create_union

五、复合类型操作符

1. 获取array中的元素
2. 获取map中的元素
3. 获取struct中的元素

六、数值计算函数

1. 取整函数: round
2. 指定精度取整函数: round
3. 向下取整函数: floor
4. 向上取整函数: ceil
5. 向上取整函数: ceiling
6. 取随机数函数: rand
7. 自然指数函数: exp
8. 以10为底对数函数: log10
9. 以2为底对数函数: log2
10. 对数函数: log
11. 幂运算函数: pow
12. 幂运算函数: power
13. 开平方函数: sqrt
14. 二进制函数: bin
15. 十六进制函数: hex
16. 反转十六进制函数: unhex
17. 进制转换函数: conv
18. 绝对值函数: abs
19. 正取余函数: pmod
20. 正弦函数: sin
21. 反正弦函数: asin
22. 余弦函数: cos
23. 反余弦函数: acos
24. positive函数: positive
25. negative函数: negative

七、集合操作函数

1. map类型大小：size
2. array类型大小：size
3. 判断元素数组是否包含元素：array_contains
4. 获取map中所有value集合
5. 获取map中所有key集合
6. 数组排序

八、类型转换函数

1. 二进制转换：binary
2. 基础类型之间强制转换：cast

九、日期函数

1. UNIX时间戳转日期函数: from_unixtime
2. 获取当前UNIX时间戳函数: unix_timestamp
3. 日期转UNIX时间戳函数: unix_timestamp
4. 指定格式日期转UNIX时间戳函数: unix_timestamp
5. 日期时间转日期函数: to_date
6. 日期转年函数: year
7. 日期转月函数: month
8. 日期转天函数: day
9. 日期转小时函数: hour
10. 日期转分钟函数: minute
11. 日期转秒函数: second
12. 日期转周函数: weekofyear
13. 日期比较函数: datediff
14. 日期增加函数: date_add
15. 日期减少函数: date_sub

十、条件函数

1. If函数: if
2. 非空查找函数: COALESCE
3. 条件判断函数：CASE

十一、字符串函数

1. 字符ascii码函数：ascii
2. base64字符串
3. 字符串连接函数：concat
4. 带分隔符字符串连接函数：concat_ws
5. 数组转换成字符串的函数：concat_ws
6. 小数位格式化成字符串函数：format_number
7. 字符串截取函数：substr,substring
8. 字符串截取函数：substr,substring
9. 字符串查找函数：instr
10. 字符串长度函数：length
11. 字符串查找函数：locate
12. 字符串格式化函数：printf
13. 字符串转换成map函数：str_to_map
14. base64解码函数：unbase64(string str)
15. 字符串转大写函数：upper,ucase
16. 字符串转小写函数：lower,lcase
17. 去空格函数：trim
18. 左边去空格函数：ltrim
19. 右边去空格函数：rtrim
20. 正则表达式替换函数：regexp_replace
21. 正则表达式解析函数：regexp_extract
22. URL解析函数：parse_url
23. json解析函数：get_json_object
24. 空格字符串函数：space
25. 重复字符串函数：repeat
26. 左补足函数：lpad
27. 右补足函数：rpad
28. 分割字符串函数: split
29. 集合查找函数: find_in_set
30. 分词函数：sentences
31. 分词后统计一起出现频次最高的TOP-K
32. 分词后统计与指定单词一起出现频次最高的TOP-K

十二、混合函数

1. 调用Java函数：java_method
2. 调用Java函数：reflect
3. 字符串的hash值：hash

十三、XPath解析XML函数

1. xpath
2. xpath_string
3. xpath_boolean
4. xpath_short, xpath_int, xpath_long
5. xpath_float, xpath_double, xpath_number

十四、汇总统计函数（UDAF）

1. 个数统计函数: count
2. 总和统计函数: sum
3. 平均值统计函数: avg
4. 最小值统计函数: min
5. 最大值统计函数: max
6. 非空集合总体变量函数: var_pop
7. 非空集合样本变量函数: var_samp
8. 总体标准偏离函数: stddev_pop
9. 样本标准偏离函数: stddev_samp
10．中位数函数: percentile
11. 中位数函数: percentile
12. 近似中位数函数: percentile_approx
13. 近似中位数函数: percentile_approx
14. 直方图: histogram_numeric
15. 集合去重数：collect_set
16. 集合不去重函数：collect_list

十五、表格生成函数Table-Generating Functions (UDTF)

1．数组拆分成多行：explode(array)
2．Map拆分成多行：explode(map)

Hive自定义函数UDF

当Hive提供的内置函数无法满足业务处理需要时，此时就可以考虑使用用户自定义函数

一个简单的UDF示例

1、先开发一个简单的 Java 类，继承 org.apache.hadoop.hive.ql.exec.UDF ，重载 evaluate 方法

Package com.naixue.hive.udf
import java.util.HashMap;
import org.apache.hadoop.hive.ql.exec.UDF;
public class ToLowerCase extends UDF {
// 必须是public，并且evaluate方法可以重载
public String evaluate(String field) {
String result = field.toLowerCase();
return result;
}
// 根据传入的不同参数，可以执行对应的不同逻辑的方法
public int evaluate(int a, int b) {
return a + b;
}
}

2、打成jar包上传到服务器
3、将jar包添加到 hive 的 classpath

hive> add JAR /home/bigdata/hivejar/udf.jar;
hive> list jar;

4、创建临时函数与开发好的class关联起来

hive> create temporary function tolowercase as
'com.naixue.hive.udf.ToLowerCase';

5、至此，便可以在hql在使用自定义的函数

select tolowercase(name), age from student;

JSON数据解析UDF开发
现有原始 JSON 数据（rating.json）如下：

{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}
{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}
{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}
{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}
.....
{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}
{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}
{"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}
{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"

现在需要将数据导入到hive仓库中，并且最终要得到这么一个结果：

该怎么做？（提示：可用内置 get_json_object 或者 自定义函数完成）

Transform实现

Hive 的 Transform 关键字提供了在 SQL 中调用自写脚本的功能。适合实现 Hive 中没有的功能又不想写 UDF 的情况。
具体以一个实例讲解。
JSON 数据：

{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}

需求：把 timestamp 的值转换成日期编号
1、先加载 rating.json 文件到hive的一个原始表 rate_json

create table rate_json(line string) row format delimited;
load data local inpath '/home/bigdata/rating.json' into table rate_json;

2、创建 rate 这张表用来存储解析 json 出来的字段：

create table rate(movie int, rate int, unixtime int, userid int) row format
delimited fields terminated by '\t';

解析 json，得到结果之后存入 rate 表：

insert into table rate select
get_json_object(line,'$.movie') as moive,
get_json_object(line,'$.rate') as rate,
get_json_object(line,'$.timeStamp') as unixtime,
get_json_object(line,'$.uid') as userid
from rate_json;

3、使用 transform+python 的方式去转换 unixtime 为 weekday
先编辑一个 python 脚本文件：weekday_mapper.py

vi weekday_mapper.py

代码如下:

#!/bin/python
import sys
import datetime
for line in sys.stdin:
line = line.strip()
movie,rate,unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movie, rate, str(weekday), userid])

保存文件
然后，将文件加入 hive 的 classpath：

hive> add file /home/bigdata/weekday_mapper.py;
hive> insert into table lastjsontable select
transform(movie,rate,unixtime,userid)
using 'python weekday_mapper.py' as(movie,rate,weekday,userid) from rate;

创建最后的用来存储调用python脚本解析出来的数据的表：lastjsontable

create table lastjsontable(movie int, rate int, weekday int, userid int) row
format delimited fields terminated by '\t';

最后查询看数据是否正确：

select distinct(weekday) from lastjsontable;

HIVE特殊分隔符处理

补充：Hive读取数据的机制：
1、首先用 InputFormat <默认是：org.apache.hadoop.mapred.TextInputFormat> 的一个具体实现类读入文件数据，返回一条一条的记录（可以是行，或者是你逻辑中的"行"）
2、然后利用 SerDe <默认：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe> 的一个具体实现类，对上面返回的一条一条的记录进行字段切割
了解SerDe的详细，请看这儿：https://cwiki.apache.org/confluence/display/Hive/SerDe
3、InputFormat 和 SerDe 联合起来工作：

HDFS files –> InputFileFormat –> <key, value> –> Deserializer –> Row object
Row object –> Serializer –> <key, value> –> OutputFileFormat –> HDFS files

4、例子测试
Hive对文件中字段的分隔符默认情况下只支持单字节分隔符，如果数据文件 multi_delim.txt 中的分隔符是多字符的，如下所示：

01||huangbo
02||xuzheng
03||wangbaoqiang

请注意：如果你使用||作为分隔符，建表不会出错，但是数据是解析不正常的。是因为Hive默认的SERDE不支持多字节分隔符。支持的分隔符类型是char

使用 RegexSerDe 通过正则表达式来抽取字段

创建表：

drop table if exists multi_delim1;
create table multi_delim(id string,name string)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties('input.regex'='(.*)\\|\\|(.*)','output.format.string'='%1$s
%2$s')
stored as textfile;

multi_delim.txt 数据如下：

01||huangbo
02||xuzheng
03||wangbaoqiang

导入数据：

load data local inpath '/home/bigdata/hivedata/multi_delim.txt' into table
multi_delim;

使用 MultiDelimitSerDe 解决多字节分隔符

创建表：

drop table if exists multi_delim2;
CREATE TABLE multi_delim2 (id STRING, name STRING, city STRING) ROW FORMAT SERDE
'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' WITH
SERDEPROPERTIES("field.delim"="^|~");

导入数据：

load data local inpath '/home/bigdata/hivedata/multi_delim2.txt' into table
multi_delim2;

multi_delim2.txt 数据格式为：

1^|~huangbo^|~beijing
2^|~xuzheng^|~shanghai
3^|~wangbaoqiang^|~tianjin

查询数据结构：

select id, name, city from multi_delim2;

查询结果：

通过自定义 InputFormat 解决特殊分隔符问题

其原理是在 InputFormat 读取行的时候将数据中的“多字节分隔符”替换为 hive 默认的分隔符（ctrl+A亦即\x01）或用于替代的单字符分隔符，以便 hive 在 serde 操作时按照默认的单字节分隔符进行字段抽取
com.naixue.hive.delimit2.BiDelimiterInputFormat 代码如下：

package com.naixue.hive.delimit2;
import java.io.IOException;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapred.FileSplit;
import org.apache.hadoop.mapred.InputSplit;
import org.apache.hadoop.mapred.JobConf;
import org.apache.hadoop.mapred.RecordReader;
import org.apache.hadoop.mapred.Reporter;
import org.apache.hadoop.mapred.TextInputFormat;
public class BiDelimiterInputFormat extends TextInputFormat {
@Override
public RecordReader<LongWritable, Text> getRecordReader(InputSplit
genericSplit, JobConf job, Reporter reporter)throws IOException {
reporter.setStatus(genericSplit.toString());
BiRecordReader reader = new BiRecordReader(job,(FileSplit)genericSplit);
// MyRecordReader reader = new MyRecordReader(job,(FileSplit)genericSplit);
return reader;
}
}

com.naixue.hive.delimit2.BiRecordReader 代码如下：

package com.naixue.hive.delimit2;
import java.io.IOException;
import java.io.InputStream;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.FSDataInputStream;
import org.apache.hadoop.fs.FileSystem;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.fs.Seekable;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.io.compress.CodecPool;
import org.apache.hadoop.io.compress.CompressionCodec;
import org.apache.hadoop.io.compress.CompressionCodecFactory;
import org.apache.hadoop.io.compress.Decompressor;
import org.apache.hadoop.io.compress.SplitCompressionInputStream;
import org.apache.hadoop.io.compress.SplittableCompressionCodec;
import org.apache.hadoop.mapred.FileSplit;
import org.apache.hadoop.mapred.LineRecordReader;
import org.apache.hadoop.mapred.RecordReader;
public class BiRecordReader implements RecordReader<LongWritable, Text> {
private static final Log LOG =
LogFactory.getLog(LineRecordReader.class.getName());
private CompressionCodecFactory compressionCodecs = null;
private long start;
private long pos;
private long end;
private LineReader in;
int maxLineLength;
private Seekable filePosition;
private CompressionCodec codec;
private Decompressor decompressor;
/**
* A class that provides a line reader from an input stream.
* @deprecated Use {@link org.apache.hadoop.util.LineReader} instead.
*/
@Deprecated
public static class LineReader extends org.apache.hadoop.util.LineReader {
LineReader(InputStream in) {
super(in);
}
LineReader(InputStream in, int bufferSize) {
super(in, bufferSize);
}
public LineReader(InputStream in, Configuration conf)
throws IOException {
super(in, conf);
}
}
public BiRecordReader(Configuration job, FileSplit split) throws IOException
{
this.maxLineLength = job.getInt("mapred.linerecordreader.maxlength",
Integer.MAX_VALUE);
start = split.getStart();
end = start + split.getLength();
final Path file = split.getPath();
compressionCodecs = new CompressionCodecFactory(job);
codec = compressionCodecs.getCodec(file);
// open the file and seek to the start of the split
FileSystem fs = file.getFileSystem(job);
FSDataInputStream fileIn = fs.open(split.getPath());
if (isCompressedInput()) {
decompressor = CodecPool.getDecompressor(codec);
if (codec instanceof SplittableCompressionCodec) {
final SplitCompressionInputStream cIn =
((SplittableCompressionCodec) codec)
.createInputStream(fileIn, decompressor, start, end,
SplittableCompressionCodec.READ_MODE.BYBLOCK);
in = new LineReader(cIn, job);
start = cIn.getAdjustedStart();
end = cIn.getAdjustedEnd();
filePosition = cIn; // take pos from compressed stream
} else {
in = new LineReader(codec.createInputStream(fileIn,
decompressor), job);
filePosition = fileIn;
}
} else {
fileIn.seek(start);
in = new LineReader(fileIn, job);
filePosition = fileIn;
}
// If this is not the first split, we always throw away first record
// because we always (except the last split) read one extra line in
// next() method.
if (start != 0) {
start += in.readLine(new Text(), 0, maxBytesToConsume(start));
}
this.pos = start;
}
private boolean isCompressedInput() {
return (codec != null);
}
private int maxBytesToConsume(long pos) {
return isCompressedInput() ? Integer.MAX_VALUE : (int) Math.min(
Integer.MAX_VALUE, end - pos);
}
private long getFilePosition() throws IOException {
long retVal;
if (isCompressedInput() && null != filePosition) {
retVal = filePosition.getPos();
} else {
retVal = pos;
}
return retVal;
}
public BiRecordReader(InputStream in, long offset, long endOffset,
int maxLineLength) {
this.maxLineLength = maxLineLength;
this.in = new LineReader(in);
this.start = offset;
this.pos = offset;
this.end = endOffset;
this.filePosition = null;
}
public BiRecordReader(InputStream in, long offset, long endOffset,
Configuration job) throws IOException {
this.maxLineLength = job.getInt("mapred.linerecordreader.maxlength",
Integer.MAX_VALUE);
this.in = new LineReader(in, job);
this.start = offset;
this.pos = offset;
this.end = endOffset;
this.filePosition = null;
}
public LongWritable createKey() {
return new LongWritable();
}
public Text createValue() {
return new Text();
}
/** Read a line. */
public synchronized boolean next(LongWritable key, Text value)
throws IOException {
// We always read one extra line, which lies outside the upper
// split limit i.e. (end - 1)
while (getFilePosition() <= end) {
key.set(pos);
// 重点代码处
int newSize = in.readLine(value,
maxLineLength,Math.max(maxBytesToConsume(pos), maxLineLength));
String str = value.toString().replaceAll("\\|\\|", "\\|");
value.set(str);
pos += newSize;
if (newSize == 0) {
return false;
}
if (newSize < maxLineLength) {
return true;
}
// line too long. try again
LOG.info("Skipped line of size " + newSize + " at pos "
+ (pos - newSize));
}
return false;
}
/**
* Get the progress within the split
*/
public float getProgress() throws IOException {
if (start == end) {
return 0.0f;
} else {
return Math.min(1.0f, (getFilePosition() - start)
/ (float) (end - start));
}
}
public synchronized long getPos() throws IOException {
return pos;
}
public synchronized void close() throws IOException {
try {
if (in != null) {
in.close();
}
} finally {
if (decompressor != null) {
CodecPool.returnDecompressor(decompressor);
}
}
}
}

注意：
1、上述代码中的 API 全部使用 Hadoop 的老 API 接口 org.apache.hadoop.mapred…。然后将工程打包，并拷贝至hive安装目录的lib文件夹中，并重启hive，使用以下语句建表

hive> create table new_bi(id string,name string) row format delimited fields
terminated by '|' stored as inputformat
'com.naixue.hive.delimit2.BiDelimiterInputFormat' outputformat
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
hive> load data local inpath '/home/bigdata/bi.dat' into table new_bi;
hive> select * from new_bi;
OK
01 huangbo
02 xuzheng
03 wangbaoqiang

2、还需要在 Hive 中使用 add jar，才能在执行 HQL 查询该表时把自定义 jar 包传递给 mapTask

hive> add jar /home/bigdata/apps/hive/lib/myinput.jar;