Hive 基本教程

Hive SQL

Hive 笔记

Hive SQL 内置函数全解


Hive SQL 内置支持绝大多数 SQL 的内置函数,其中主要包括数学函数,类型转换函数,条件函数,字符函数,聚合函数,表生成函数等等。

条件函数

Hive SQL 支持如下几种条件函数:

Hive SQL 内置条件函数
函数 返回值 描述
IF(boolean testCondition, T valueTrue, T valueFalseOrNull) T 如果 testConditiontrue,就返回 valueTrue,否则返回 valueFalseOrNull;其中 valueTruevalueFalseOrNull 为泛型。
CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END T 如果 a=ture,就返回 bc=ture 时,就返回 d,否则返回 e
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END T 如果 a=b,就返回 c,若 a=d 就返回 e,否则返回 f
NVL(T value, T default_value) T 如果 value 值为 NULL,就返回 default_value,否则返回自身 value
COALESCE(T v1, T v2, ...) T 返回第一个 非 NULL 的值,如果全部都为 NULL,就返回 NULL
ISNULL(a) boolean 如果 aNULL,就返回 true,否则返回 false
ISNOTNULL(a) boolean 如果 a 为 非 NULL,就返回 true,否则返回 false

聚合函数

Hive SQL 支持如下几种聚合函数:

Hive SQL 内置聚合函数
函数 返回值 描述
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr[, expr...])
BIGINT

COUNT(*):统计总行数,包括含有 NULL 值的行;

COUNT(expr):统计提供非 NULLexpr 表达式值的行数;

COUNT(DISTINCT expr[, expr ...]):统计提供非 NULL 且去重后的 expr 表达式值的行数。

SUM(col)

SUM(DISTINCT col)

Double

SUM(col):统计指定列的总和;

SUM(DISTINCT col):统计去重后指定列的总和。

AVG(col)

AVG(DISTINCT col)

Double

AVG(col):统计指定列的平均值;

AVG(DISTINCT col):统计去重后指定列的平均值。

MIN(col) Double 求指定列的最小值。
MAX(col) Double 求指定列的最大值。

VARIANCE(col)

VAR_POP(col)

Double 求指定列数值的方差。
VAR_SAMP(col) Double 求指定列数值的样本方差。
STDDEV_POP(col) Double 求指定列数值的标准偏差。
STDDEV_SAMP(col) Double 求指定列数值的样本标准偏差。
COVAR_POP(col1, col2) Double 求指定列数值的协方差。
COVAR_SAMP(col1, col2) Double 求指定列数值的样本协方差。
CORR(col1, col2) Double 返回两列数值的相关系数。
PERCENTILE(col, p) Double 返回指定列(col)的 p% 分位数。

字符串函数

Hive SQL 支持如下几种聚合函数:

Hive SQL 内置字符串函数
函数 返回值 描述
GET_JSON_OBJECT(string json_string, string path) string 根据指定的 json 路径从 json 字符串中提取 json 对象,并返回提取的 json 对象的 json 字符串。如果输入的 json 字符串非法,它将返回 null。
ASCII(string str) int 返回 str 的第一个字符的 ASCII 值。
BASE64(binary bin) string 将二进制参数转换为 BASE64 的字符串,从 0.12.0 版本开始支持。
CHARACTER_LENGTH(string str) int 返回 str 中包含的字符数,函数 char_length 是该函数的简写,2.2.0 版本开始支持。
CHR(bigint|double A) string 返回具有与参数 A 等效的二进制值的 ASCII 字符。
CONCAT(string|binary A, string|binary B...) string 返回指定参数的连接字符串,此函数可以接受任意数量的输入字符串。
CONTEXT_NGRAMS(array<array<string>>, array<string>, int K, int pf) array<struct<string,double>> 返回分词后统计与指定单词一起出现频次最高的 TOP-K。
CONCAT_WS(string SEP, string A, string B...) string 类似 CONCAT() 函数,可以自定义分隔符。
CONCAT_WS(string SEP, array<string>) string 等同于上面的 CONCAT_WS(),不一样的是参数是字符串数组,从 0.9.0 版本开始支持
DECODE(binary bin, string charset) string 针对指定的字符串进行解码,从 0.12.0 版本开始支持
ELT(N int,str1 string,str2 string,str3 string,...) string 返回指定索引的字符串,如果 N 小于 1 或者大于指定参数的个数,则返回 NULL。
ENCODE(string src, string charset) binary 针对指定的字符串进行编码,从 0.12.0 版本开始支持
FIELD(val T,val1 T,val2 T,val3 T,...) int 返回 val1,val2,val3,... 列表中 val 的索引,如果未找到,则返回 0。
FIND_IN_SET(string str, string strList) int 返回 str 在 strList 中第一次出现的地方,其中 strList 是逗号分隔的字符串。如果任一参数为 null,则返回 null。如果第一个参数包含逗号,则返回 0。
FORMAT_NUMBER(number x, int d) string 将数字 X 格式化为 '#,###,###。##' 之类的格式,四舍五入到小数点后 D 位,然后将结果作为字符串返回。如果 D 为 0,则结果没有小数点或小数部分。
IN_FILE(string str, string filename) boolean 如果字符串 str 在文件名中显示为整行,则返回 true。
INSTR(string str, string substr) int 返回 substr 中第一次出现 str 的位置。如果任一参数是 null,则返回 0。
LENGTH(string A) int 返回字符串的长度。
LOCATE(string substr, string str[, int pos]) int 返回在位置 pos 之后的 str 中,第一次出现 substr 的位置。
LOWER(string A) lcase(string A) string 返回指定字符串的小写形式。
LPAD(string str, int len, string pad) string 左填充长度为 len 的 pad。
LTRIM(string A) string 去除左边或者说起始处的空格字符,返回处理后的字符串。
NGRAMS(array<array<string>>, int N, int K, int pf) array<struct<string,double>> 从一组标记化的句子中返回前 top-k N-gram。
OCTET_LENGTH(string str) int 返回以 UTF-8 编码保存字符串 str 所需的八位字节数,从 2.2.0 版本开始支持 
PARSE_URL(string urlString, string partToExtract [, string keyToExtract]) string 从 URL 返回指定的部分。partToExtract 的可选值包括 HOST,PATH,QUERY,REF,PROTOCOL,AUTHORITY,FILE 和 USERINFO。
PRINTF(String format, Obj... args) string 返回根据 printf 指定的格式输出字符串,从 0.9.0 版本开始支持 
QUOTE(String text) string 返回带引号的字符串。
REGEXP_EXTRACT(string subject, string pattern, int index) string 返回使用模式提取的字符串。
REGEXP_REPLACE(string INITIAL_STRING, string PATTERN, string REPLACEMENT) string 返回通过将 INITIAL_STRING 中所有与 PATTERN 中定义的 Java 正则表达式语法匹配的子字符串替换为 REPLACEMENT 的实例所产生的字符串。
REPEAT(string str, int n) string 重复指定的字符串 n 次。
REPLACE(string A, string OLD, string NEW) string 替换指定的老的字符串为新字符串。
REVERSE(string A) string 返回反转的字符串。
RPAD(string str, int len, string pad) string 右填充长度为 len 的 pad。
RTRIM(string A) string 去除右边或者说末尾处的空格字符,返回处理后的字符串。
SENTENCES(string str, string lang, string locale) array<array<string>> 针对文本进行分词或短语分开。
SPACE(int n) string 返回 n 个空格的字符串。
SPLIT(string str, string pat) array 根据指定分隔符对指定字符串分隔。
STR_TO_MAP(text[, delimiter1, delimiter2]) map<string,string> 使用两个定界符将文本分成键值对。

SUBSTR(string|binary A, int start)

SUBSTRING(string|binary A, int start)

string 截断指定位置的字符串。

SUBSTR(string|binary A, int start, int len)

SUBSTRING(string|binary A, int start, int len)

string 截断指定位置及指定长度的字符串。
SUBSTRING_INDEX(string A, string delim, int count) string 在计数出现定界符 delim 之前,从字符串 A 返回子字符串。
TRANSLATE(string|char|varchar input, string|char|varchar from, string|char|varchar to) string 通过将字符串中存在的字符替换为 from 字符串中的相应字符来翻译输入 to 字符串。
TRIM(string A) string 去除指定字符串的两边空格。
UNBASE64(string str) binary 将 base64 字符串转为 binary,从 0.12.0 版本开始支持 

UPPER(string A)

UCASE(string A)

string 转换指定字符串为大写形式。
INITCAP(string A) string 返回字符串,每个单词的首字母大写,所有其他字母小写。单词由空格分隔,从 1.1.0 版本开始支持
LEVENSHTEIN(string A, string B) int 返回两个字符串之间的 Levenshtein 距离,从 1.2.0 版本开始支持 
SOUNDEX(string A) string 返回字符串的 soundex 代码,从 1.2.0 版本开始支持 

表生成函数

Hive SQL 内置表生成函数
函数 返回值 描述
EXPLODE(ARRAY<TYPE> a) array 对于 a 中的每个元素,将生成一行且包含该元素。
EXPLODE(ARRAY) N 行 将数组转换成多行,每行对应数组中的一个元素。
EXPLODE(MAP) N 行 将 map 结构的字段转换成多行,每行对应 map 的一个元素。