1. 正则函数
rlike 'yoyo|haha'
regexp_replace('foobar','oo|ar','')
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/980f8b999ab46f39564e472df98aaf05.png)
- regexp_extract:正则拆分
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/fdb7eb08f7e6eed62fd979c1d383edba.png)
- 随机函数
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/2a27a2b4b444b49d349ea897246cc6c4.png)
2. 表相关操作
creat tabel facade.traing
(
`年份` int
`月份` int
)row format delimited fields terminated by ","
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/92013c78d702dbe853d408e462ed9f83.png)
with tep as(select * from table)
- union
union: 去重
union all: 不去重
3. 数据格式
select * from table
where app={{输入app名称}}
- Numeric Types
bigint
float
double
decimal
numeric - Date/ Time Types
timestamp
date
translate(p_date,'-'-'') as date
- String Types
- Misc Types
doolean
binary - Complex Types
arrays
maps
structs
union
4. hive其他字符函数
max_pt(`table`)
lpad(string str,int len,string pad)
repeat(string str, int n)
reverse(string str)
split(string str, string pat)
5. hive其他时间函数
trunc(string date, string format)
trunc("2016-06-06","mm")
tuunc(current_timestamp(),'month')
from_unixtime()
unix_timestamp()
unix_timestamp(string date)
unix_timestamp(string date, string pattern)
datediff(string enddate,string startdate)
6. hive其他逻辑函数
coalesce(T v1, T v2,……)
nvl(T v1, T v2)
7. hive集合函数
array_contains(Array<T>,val)
size(Array<T>), size(Map<K.V>)
sort_array(Array<T>)
map_keys(Map<K.V>)
map_values(Map<K.V>)
explode(Array)
8. 相关网址
- 正则函数
https://www.runoob.com/regexp/regexp-syntax.html - clickhouse
http://saoniuhuo.com/question/detail-1911163.html
https://www.cnblogs.com/superdrew/p/11279663.html - posexplode
https://www.jianshu.com/p/cd997449dc46