内置函数帮助文档
- 显示 hive下内置所有函数
show functions;
- 查看函数的详细使用方法
desc function extended 函数名;
字符函数
concat 合并多个字符串
select concat('hello','world');
+-------------+--+
| _c0 |
+-------------+--+
| helloworld |
+-------------+--+
instr
匹配表中name列 字符 i 出现位置大于 2 的列
select name,instr(name,'i') from employee where instr(name,'i')>1;
+----------+------+--+
| name | _c1 |
+----------+------+--+
| Michael | 2 |
| Will | 2 |
+----------+------+--+
length 长度
select length('hello');
+------+--+
| _c0 |
+------+--+
| 5 |
+------+--+
locate 定位
select locate('tu','student');
+------+--+
| _c0 |
+------+--+
| 2 |
+------+--+
lower 小写/upper 大写
select lower('student'),upper('name');
+----------+-------+--+
| _c0 | _c1 |
+----------+-------+--+
| student | NAME |
+----------+-------+--+
regexp_replace 替换
将stu 匹配到的student里的部分替换为fe
stu 可为正则表达式
select regexp_replace('student','stu','fe');
+---------+--+
| _c0 |
+---------+--+
| fedent |
+---------+--+
slipt
按照正则表达式对字符串进行拆分
select split('student','d');
+----------------+--+
| _c0 |
+----------------+--+
| ["stu","ent"] |
+----------------+--+
substr 截取
将字符串从1开始截取长度为3
select substr('student',1,3);
+------+--+
| _c0 |
+------+--+
| stu |
+------+--+
trim 切除字符串两边的空格
切除字符串两边的空格
select trim(' [student name] ');
+-----------------+--+
| _c0 |
+-----------------+--+
| [student name] |
+-----------------+--+
str_to_map
需要在建表语句指定分割符可用 转化为map
select str_to_map(string)
encode 编码
select encode('student','utf-8');
+----------+--+
| _c0 |
+----------+--+
| student |
+----------+--+
类型转换函数
cast
select cast('1' as bigint);
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
binary
数学函数
round
select round(4.55);
+------+--+
| _c0 |
+------+--+
| 5.0 |
+------+--+
select round(4.55,2);
+-------+--+
| _c0 |
+-------+--+
| 4.55 |
+-------+--+
select floor(4.55);
+------+--+
| _c0 |
+------+--+
| 4 |
+------+--+
select rand();
+---------------------+--+
| _c0 |
+---------------------+--+
| 0.4595941000159962 |
+---------------------+--+
select power(2,4);
+-------+--+
| _c0 |
+-------+--+
| 16.0 |
+-------+--+
select abs(-3);
+------+--+
| _c0 |
+------+--+
| 3 |
+------+--+
日期函数
select from_unixtime(1607933910,'yyyy-MM-dd');
+-------------+--+
| _c0 |
+-------------+--+
| 2020-12-14 |
+-------------+--+
select unix_timestamp();
+-------------+--+
| _c0 |
+-------------+--+
| 1607933910 |
+-------------+--+
select unix_timestamp('2020-12-14 12:56:56');
+-------------+--+
| _c0 |
+-------------+--+
| 1607921816 |
+-------------+--+
select to_date('2012-12-25');
+-------------+--+
| _c0 |
+-------------+--+
| 2012-12-25 |
+-------------+--+
select year('2020-12-15');
+-------+--+
| _c0 |
+-------+--+
| 2020 |
+-------+--+
select datediff('2020-11-30','2020-12-14');
+------+--+
| _c0 |
+------+--+
| -14 |
+------+--+
select date_add('2020-12-15',3);
+-------------+--+
| _c0 |
+-------------+--+
| 2020-12-18 |
+-------------+--+
select date_sub('2020-12-15',3);
+-------------+--+
| _c0 |
+-------------+--+
| 2020-12-12 |
+-------------+--+
select current_date;
+-------------+--+
| _c0 |
+-------------+--+
| 2020-12-14 |
+-------------+--+
select current_timestamp;
+--------------------------+--+
| _c0 |
+--------------------------+--+
| 2020-12-14 16:36:12.142 |
+--------------------------+--+
select date_format('2020-06-12','MM-dd');
+--------+--+
| _c0 |
+--------+--+
| 06-12 |
+--------+--+
集合函数
条件函数
聚合函数
count
sum
max
min
avg