Hive常用系统函数一览
文章摘要
PostChat
此内容根据文章生成,并经过人工审核,仅用于文章内容的解释与总结
Hive 提供了较完整的 SQL 功能,HQL 与 SQL 基本上一致,旨在让会 SQL 而不懂 MapReduce 编程的用户可以调取 Hadoop 中的数据,进行数据处理和分析。
这里记录了个人日常数据分析过程中 Hive SQL 需要的查询函数,方便手头随时查询,定期更新补充。
在 Beeline 或 CLI 中,可以使用以下命令显示Hive最新文档信息
1 | -- 查看Hive系统函数 |
Hive关系运算符
运算符 | 类型 | 功能说明 |
---|---|---|
A = B | 所有类型 | 如果A与B相等,返回TRUE,否则返回FALSE |
A == B | 无 | 效的语法,SQL使用”=”,不使用”==” |
A <> B | 所有类型 | 如果A不等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL” |
A < B | 所有类型 | 如果A小于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL” |
A <= B | 所有类型 | 如果A小于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL” |
A > B | 所有类型 | 如果A大于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL” |
A >= B | 所有类型 | 如果A大于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL” |
A IS NULL | 所有类型 | 如果A值为”NULL”,返回TRUE,否则返回FALSE |
A IS NOT NULL | 所有类型 | 如果A值不为”NULL”,返回TRUE,否则返回FALSE |
A [NOT] LIKE B | 所有类型 | 如果A或B值为”NULL”,结果返回”NULL”。字符串A与B通过sql进行匹配,如果相符返回TRUE,不符返回FALSE。B字符串中 的”_”代表任一字符,”%”则代表多个任意字符. 可以通过使用 NOT 关键字来反转。 |
A [NOT] BETWEEN B AND C | 所有类型 | 如果A或B或C值为”NULL”,结果返回”NULL”。如果 A 大于或等于 B 且 A 小于或等于 C,则为 TRUE,否则为 FALSE。可以通过使用 NOT 关键字来反转。 (从 0.9.0 版开始。) |
1 | -- 1、等值比较 = |
Hive算术运算符
运算符 | 类型 | 功能说明 |
---|---|---|
A + B | 所有类型 | 返回A与B相加的结果,结果的数值类型等于A的类型和B的类型的最小父类型;int+int一般结果为int类型,而int+double一般结果为double类型 |
A – B | 所有类型 | 返回A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型 |
A * B | 所有类型 | 返回A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型 |
A / B | 所有类型 | 返回A除以B的结果。结果的数值类型为double |
A % B | 所有类型 | 返回A除以B的余数 |
A & B | 所有类型 | 回A和B按位进行与操作的结果 |
A | B | 所有类型 |
A ^ B | 所有类型 | 返回A和B按位进行异或操作的结果 |
~A | 所有类型 | 返回A按位取反操作的结果,结果的数值类型等于A的类型 |
1 | -- 1、Hive加法操作: + |
Hive算术运算符
运算符 | 类型 | 功能说明 |
---|---|---|
A AND B | boolean | 如果A和B均为TRUE,则为TRUE;否则为FALSE。如果A为NULL或B为NULL,则为NULL |
A OR B | boolean | 如果A为TRUE,或者B为TRUE,或者A和B均为TRUE,则为TRUE;否则为FALSE |
NOT A | boolean | 如果A为FALSE,或者A为NULL,则为TRUE;否则为FALSE |
A && B | boolean | 等同 A AND B |
A | B | boolean |
!A | boolean | 等同 NOT A |
A IN (val1, val2, …) | boolean | 如果 A 在列表中,则为 TRUE。从 Hive 0.13 开始,IN 语句支持子查询 |
A NOT IN (val1, val2, …) | boolean | 如果 A 不在列表中,则为 TRUE。从 Hive 0.13 开始, NOT IN 语句支持子查询。 |
A [NOT] EXISTS (subquery) | boolean | 如果 A 存在子查询结果中,则为 TRUE。从 Hive 0.13 开始支持 |
1 | -- 1、逻辑与操作: AND |
Hive数值函数
运算符 | 类型 | 功能说明 |
---|---|---|
round(double a) | BIGINT | Hive四舍五入取整 |
round(double a, int d) | DOUBLE | Hive指定保留小数点四舍五入 |
floor(double a) | BIGINT | 对给定数据进行向下取最接近的整数 |
ceil(double a), ceiling(double a) | BIGINT | 参数向上取最接近的整数 |
rand(), rand(int seed) | double | 返回一个0到1范围内的随机数。如果指定种子seed,则会等到一个稳定的随机数序列 |
abs(double a) | double | 取绝对值 |
positive(int a) positive(double a) | double | 取绝对值 |
negative(int a) negative(double a) | double | 返回A的相反数 |
1 | -- 1、取整函数: round |
Hive日期函数
函数 | 类型 | 功能说明 |
---|---|---|
from_unixtime(bigint unixtime[, string format]) | string | 转化UNIX时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间 |
unix_timestamp() | bigint | 获得当前时区的UNIX时间戳 |
unix_timestamp(string date) | bigint | 转换格式为”yyyy-MM-dd HH:mm:ss”的日期到UNIX时间戳 |
unix_timestamp(string date, string pattern) | bigint | 转换指定pattern格式的日期到UNIX时间戳 |
to_date(string timestamp) | string | 返回时间中的年月日 |
year(string date) | int | 返回指定时间的年份 |
month(string date) | int | 返回指定时间的月份 |
day(string date) | int | 返回指定时间的天 |
hour(string date) | int | 返回指定时间的小时 |
minute(string date) | int | 返回指定时间的分钟 |
second(string date) | int | 返回指定时间的秒 |
weekofyear(string date) | int | 返回指定日期所在一年中的星期号,范围为0到53 |
datediff(string enddate, string startdate) | int | 两个时间参数的日期之差 |
date_add(string startdate, int days) | int | 返回开始日期startdate增加days天后的日期。 |
date_sub(string startdate, int days) | int | 返回开始日期startdate减少days天后的日期 |
last_day(STRING date) | int | 指定日期字符所在月份的最后一天 |
TRUNC(date[,fmt]) | int | 截断返回指定格式单位的日期,支持的格式有MONTH/MON/MM, YEAR/YYYY/YY. |
date_format(time, string pattern) | int | 对时间日期进行格式化 |
current_date() | string | 当前日期,yyyy-MM-dd |
current_time() | string | 当前时间,HH:mm:ss |
current_timestamp() | string | 返回当前UTC时间(GMT+0)的时间戳,小于北京时间8小时,就是日期时间yyyy-MM-dd HH:mm:ss返回当前UTC时间(GMT+0)的时间戳,小于北京时间8小时,就是日期时间yyyy-MM-dd HH:mm:ss |
MONTHS_BETWEEN (x, y) | string | 用于计算x和y之间有几个月。如果x在日历中比y早,那么MONTHS_BETWEEN()就返回一个负数 |
1 | -- 1、Hive UNIX时间戳转日期函数: from_unixtime |
Hive条件函数
函数 | 类型 | 功能说明 |
---|---|---|
if(boolean testCondition, T valueTrue, T valueFalseOrNull) | T | 如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull |
isnull( a ) | boolean | 如果a为null就返回true,否则返回false |
isnotnull ( a ) | boolean | 如果a为非null就返回true,否则返回false |
nvl(T value, T default_value) | T | 如果value值为NULL就返回default_value, 否则返回value,HIve 0.11后有 |
COALESCE(T v1, T v2, …) | T | 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL |
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | T | 如果a=b就返回c,a=d就返回e,否则返回f |
CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | T | 如果a=ture就返回b,c= ture就返回d,否则返回e |
nullif( a, b ) | T | 如果a = b,则返回null,否则返回a , CASE WHEN a = b then NULL else a,Hive 2.3.0后有 |
assert_true(boolean condition) | void | 如果condition为真,则返回NULL,否则抛出异常 |
1 | -- 1、If函数: if |
Hive字符串函数
函数 | 类型 | 功能说明 |
---|---|---|
length(string A) | int | 返回字符串A的长度 |
reverse(string A) | string | reverse(string A) |
concat(string A, string B…) | string | 返回输入字符串连接后的结果,支持任意个输入字符串 |
concat_ws(string SEP, string A, string B…) | string | 返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符 |
substr(string A, int start),substring(string A, int start) | string | 返回字符串A从start位置到结尾的字符串 |
substr(string A, int start, int len),substring(string A, int start, int len) | string | 返回字符串A从start位置开始,长度为len的字符串 |
upper(string A) | string | 字符串转大写函数:upper,ucase |
lower(string A) lcase(string A) | string | 字符串转小写函数:lower,lcase |
trim(string A) | string | 去除字符串两边的空格 |
ltrim(string A) | string | 去除字符串左边的空格 |
rtrim(string A) | string | 右边去空格函数:rtrim |
regexp_replace(string A, string B, string C) | string | 将字符串A中的符合java正则表达式B的部分替换为C。注意,在有些情况下要使用转义字符,类似oracle中的regexp_replace函数 |
regexp_extract(string subject, string pattern, int index) | string | 将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符 |
parse_url(string urlString, string partToExtract [, string keyToExtract]) | string | 返回URL中指定的部分。partToExtract的有效值为:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINF |
get_json_object(string json_string, string path) | string | 解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。 |
repeat(string str, int n) | string | 返回重复n次后的str字符串 |
split(string str, string pat) | array | 按照pat字符串分割str,会返回分割后的字符串数组 |
find_in_set(string str, string strList) | int | 返回str在strlist第一次出现的位置,strlist是用逗号分割的字符串。如果没有找该str字符,则返回0 |
locate(string substr, string str[, int pos]) | int | 查找字符串str中的pos位置后字符串substr第一次出现的位置 |
format_number(double str, int long) | string | 转为千分位格式 |
1 | -- 1、字符串长度函数:length |
Hive集合统计函数
函数 | 类型 | 功能说明 |
---|---|---|
count(*), count(expr), count(DISTINCT expr[, expr_.]) | int | count(*)统计检索出的行的个数,包括NULL值的行;count(expr)返回指定字段的非空值的个数;count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数 |
sum(col), sum(DISTINCT col) | double | sum(col)统计结果集中col的相加的结果;sum(DISTINCT col)统计结果中col不同值相加的结果 |
avg(col), avg(DISTINCT col) | double | avg(col)统计结果集中col的平均值;avg(DISTINCT col)统计结果中col不同值相加的平均值 |
min(col) | double | 统计结果集中col字段的最小值 |
max(col) | double | 统计结果集中col字段的最大值 |
collect_set (col) | array | 将 col 字段进行去重,合并成一个数组。 |
collect_list (col) | array | 将 col 字段合并成一个数组,不去重 |
1 | -- 1、 统计个数函数: counthive> select count(*) from tableName; |
Hive集合操作函数
函数 | 类型 | 功能说明 |
---|---|---|
array_contains(Array, value) | boolean | 返回 Array中是否包含元素 value |
size(Map<K.V>) | int | 返回Map的大小 |
size(Array | int | 返回Array的大小 |
map_keys(Map<K.V>) | array | 返回 Map<K.V>中所有 key 的集合 |
map_values(Map<K.V>) | array | 返回 Map<K.V>中所有 value 的集合 |
sort_array(Array) | array | 数组排序 |
1 | -- 1、 判断元素数组是否包含元素:array_contains |
Hive类型转换函数
1 | -- 1、类型转换函数:cast |
Hive类型转换函数
函数 | 类型 | 功能说明 |
---|---|---|
cast(expr as ) | int | Hive类型转换。例如将字符”1″转换为整数:cast(’1′ as bigint),如果转换失败返回NULL。 |
1 | -- 1、类型转换函数:cast |
Hive表格生成转换函数
函数 | 类型 | 功能说明 |
---|---|---|
explode(ARRAY) | 多行 | 将数组中的元素拆分成多行显示 |
explode(MAP) | 多行 | 将数组中的元素拆分成多行显示 |
1 | -- 1、数组拆分成多行:explode |
Hive混合函数
函数 | 类型 | 功能说明 |
---|---|---|
reflect | int |
1 | -- reflect (也可 java_method) 支持调用 java 自带函数,计算一行最高成绩select reflect("java.lang.Math","max", englist, chinese) from tableName |
其他
WITH AS 短语
也叫做子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。一般当我们书写一些结构相对复杂的SQL语句时,可能某个子查询在多个层级多个地方存在重复使用的情况,这个时候我们可以使用 with as 语句将其独立出来,极大提高SQL可读性,简化SQL。目前 oracle、sql server、hive、MySQL8.0 等均支持 with as 用法。
1 | -- 相当于建了 a、b 临时表 |
UNION语句
SQL UNION 可以将两个要连接的 SQL 语句拼接在一起,它们字段个数必须一样,而且字段类型要“相容”(一致)。
1 | -- UNION 操作符合并的结果集,不允许重复值; |
本文是原创文章,采用CC BY-NC-SA 4.0协议,完整转载请注明来自BAIHE
评论 ()