hive 使用总结
目录
日期时间相关
固定日期转换成时间戳
select unix_timestamp('2016-08-16','yyyy-MM-dd') --1471276800
select unix_timestamp('20160816','yyyyMMdd') --1471276800
select unix_timestamp('2016-08-16T10:02:41Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") --1471312961
16/Mar/2017:12:25:01 +0800 转成正常格式（yyyy-MM-dd hh:mm:ss）
select from_unixtime(to_unix_timestamp('16/Mar/2017:12:25:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))
时间戳转换程固定日期
select from_unixtime(1471276800,'yyyy-MM-dd') --2016-08-16
select from_unixtime(1471276800,'yyyyMMdd') --20160816
select from_unixtime(1471312961) -- 2016-08-16 10:02:41
select from_unixtime( unix_timestamp('20160816','yyyyMMdd'),'yyyy-MM-dd') --2016-08-16
select date_format('2016-08-16','yyyyMMdd') --20160816
返回日期时间字段中的日期部分
select to_date('2016-08-16 10:03:01') --2016-08-16
取当前时间
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')
select from_unixtime(unix_timestamp(),'yyyy-MM-dd')
返回日期中的年
select year('2016-08-16 10:03:01') --2016
返回日期中的月
select month('2016-08-16 10:03:01') --8
返回日期中的日
select day('2016-08-16 10:03:01') --16
返回日期中的时
select hour('2016-08-16 10:03:01') --10
返回日期中的分
select minute('2016-08-16 10:03:01') --3
返回日期中的秒
select second('2016-08-16 10:03:01') --1
返回日期在当前的周数
select weekofyear('2016-08-16 10:03:01') --33
返回结束日期减去开始日期的天数
select datediff('2016-08-16','2016-08-11')
月份差
months_between(date2,date1)
返回开始日期startdate增加days天后的日期
select date_add('2016-08-16',10)
返回开始日期startdate减少days天后的日期
select date_sub('2016-08-16',10)
返回当天三种方式
SELECT CURRENT_DATE;
--2017-06-15
SELECT CURRENT_TIMESTAMP;--返回时分秒
--2017-06-15 19:54:44
SELECT from_unixtime(unix_timestamp());
--2017-06-15 19:55:04
返回当前时间戳
Select current_timestamp--2018-06-18 10:37:53.278
返回当月的第一天
select trunc('2016-08-16','MM') --2016-08-01
返回当年的第一天
select trunc('2016-08-16','YEAR') --2016-01-01
固定日期格式转换
from_unixtime(to_unix_timestamp(stat_dt,'yyyyMMdd'), 'yyyy-MM-dd')
1. 输出date对应星期几
select pmod(datediff(to_date( date),’1900-01-08’),7)+1 as week_day
2. 输出date上周周日
select date_sub(date,pmod(datediff(date,'1900-01-08'),7)+1) as Last_Sunday;
3. 输出date上周周一
select date_sub(date,pmod(datediff(date,'1900-01-08'),7)+7) as Last_Monday;
4.date所在周的周一
select date_sub(date,pmod(datediff(date,’1900-01-08’),7)) as Monday_Date;
hive日期格式转换为Python日期格式
def convert_hive_format_to_python_format(hive_format: str) -> str:
format_mapping = {
'yyyy': '%Y',
'MM': '%m',
'dd': '%d',
'HH': '%H',
'mm': '%M',
'ss': '%S',
}
for key, value in format_mapping.items():
hive_format = hive_format.replace(key, value)
return hive_format
# 使用示例
hive_time_format = "yyyyMMdd"
python_time_format = convert_hive_format_to_python_format(hive_time_format)
print(python_time_format) # 输出: %Y-%m-%d
hive 窗口函数
http://shzhangji.com/cnblogs/2017/09/05/hive-window-and-analytical-functions/
hive row_number 排序相关用法
https://www.cnblogs.com/Allen-rg/p/9268627.html
计算不同行的差值
使用lead() over() 窗口函数
where for lead(count,1,0) 1 means offset, i.e. 1 row after, and 0 means default value.
字符串比较
按位置逐个比较
一行展开成多行 explode和lateral view
https://cloud.tencent.com/developer/article/1408939
数据插入分区（按插入行的顺序区分）
create table if not exists test.tmp(aa string, bb string)
partitioned by
(date string);
insert overwrite table test.tmp PARTITION (date = '00')
select
'aa' as aa,
'bb' as bb;
insert overwrite table test.tmp PARTITION (date = '01')
select
'bb' as bb,
'aa' as aa;
select * from caijing_test.fxj_tsy_tmp where date >='00'
aa bb date
bb aa 01
aa bb 00
where max date
select *
from aaa.bbb
where date = sort_array(array('20220601', '${date}'))[1]
limit 10
runtime
date = FROM_UNIXTIME(UNIX_TIMESTAMP() -60 * 60 * 24 * 2, 'yyyyMMdd')
