Hive
注意
hive分区字段不能包含中文字段值
hive学习网站
新建表
create table kdcloud_cosmic_poa.ztest_poa_1(
time STRING,
num STRING
);
按分区新建表
create table kdcloud_cosmic_poa.ztest_poa_1(
time STRING,
num STRING
)partitioned by(day string);
清空表数据
-- 清空表(保留表结构)
truncate table kdcloud_cosmic_poa.ztest_poa_1;
清空分区表数据
-- 清空分区 2020-04-21 的数据
alter table kdcloud_cosmic.wpoa_mc_tenants drop partition(day='2020-04-21');
修改表名
ALTER TABLE kdcloud_cosmic_poa.ztest_poa_1 RENAME TO kdcloud_cosmic_poa.ztest_poa_t;
删除表
-- 删除表
drop table if exists kdcloud_cosmic_poa.ztest_poa_1;
添加字段
-- 添加字段
ALTER TABLE 表名 add columns(新字段 类型);
alter table kdcloud_cosmic_poa.ztest_poa_1 add columns(equip_type string);
修改字段
-- 修改字段(名称、类型、位置、注释)
ALTER TABLE table_name CHANGE
[CLOUMN] col_old_name col_new_name column_type
[CONMMENT col_conmment]
[FIRST|AFTER column_name]
[CASCADE|RESTRICT];
-- 1.修改字段名称或者类型
ALTER TABLE 表名 CHANGE 旧字段 新字段 类型;
alter table kdcloud_cosmic_poa.ztest_poa_1 CHANGE time newtime STRING;
-- 2.修改字段位置
ALTER TABLE kdcloud_cosmic_poa.ztest_poa_1 CHANGE newtime time STRING AFTER num;
替换表字段
-- 替换表的字段
alter table kdcloud_cosmic_poa.ztest_poa_1 replace columns(equip_type string);
时间戳函数
-- unix_timestamp()
-- 返回值类型为:bigint
-- 获取时间戳的函数,可以获取系统时间戳,也可以将格式化的时间转换为时间戳。
-- 1.获取系统当前时间戳 unix_timestamp()
select unix_timestamp(); -- 返回结果:1585300664
-- 2.将格式化的日期转换为时间戳(格式必须为:yyyy-MM-dd HH:mm:ss),如果格式不对则返回null
select unix_timestamp("2020-03-27 00:00:00"); --返回结果:1585238400
select unix_timestamp("2020-03-27"); --返回结果:NULL 因为格式需要带上时分秒
select unix_timestamp(1585300664); --返回报错,因为参数类型不对
-- 3.将指定格式的时间转换成时间戳
select unix_timestamp("2020-03-27 13:12:14","yyyy-MM-dd HH:mm:ss"); --返回结果:1585285934
select unix_timestamp("2020-03-27","yyyy-MM-dd"); -- 返回结果:1585238400
获取日
-- 获取今日日期 current_date
select current_date;-- 返回结果:2020-03-27
-- 日期比较函数 datediff
select datediff('2016-02-15','2015-12-20'); -- 返回结果:57
-- 日期加函数 date_add
select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);
-- 日期减函数 date_sub
select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);
获取周
-- 获取本周周数 weekofyear()
select weekofyear(current_date); -- 返回结果:13
-- 查询本周周一日期
select date_sub(current_date,pmod(datediff(current_date,"1900-01-08"),7));
-- 查询本周周日日期
select date_add(current_date,(8-pmod(datediff(current_date,"1900-01-08"),7)));
-- 查询上周周一日期
select date_sub(current_date,pmod(datediff(current_date,"1900-01-08"),7)+7);
-- 查询上周周日日期
select date_sub(current_date,pmod(datediff(current_date,"1900-01-08"),7)+1);
-- 获取指定日期所在周一的日期
select date_sub("2020-03-25",pmod(datediff("2020-03-25","1900-01-08"),7));
-- 获取指定日期所在周日的日期
select date_sub("2020-03-25",pmod(datediff("2020-03-25","1900-01-08"),7)-6);
获取月
-- 获取本月月份数 month()
select month(current_date); --返回结果 3
-- 获取当月第几天
select dayofmonth(current_date); --返回结果 31
-- 获取当月第一天
select trunc(current_date,'MM'); -- 返回结果:2020-03-01
-- 获取当月最后一天
select last_day(current_date); -- 2020-03-31
-- 获取上月第一天
select add_months(trunc(current_date,'MM'),-1); -- 返回结果:2020-02-01
-- 获取上月最后一天
select add_months(last_day(current_date),-1); -- 返回结果:2020-02-29
获取年
-- 获取本年
select year(current_date); -- 返回结果:2020
-- 获取本年第一天
select trunc(current_date,'YY'); -- 返回结果:2020-01-01
数据清洗
对于分区表的数据新增必须开启动态分区:set hive.exec.dynamic.partition.mode=nonstrict;
overwrite:代表覆盖写入,也就是覆盖写入到分区中
分区表字段顺序必须和select中字段顺序一致
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table kdcloud_cosmic_poa.t_user_action_activetrend
partition(tenanttype,counttype,startday)
-- 再清洗出租户数
select
t.cloud_name,
t.app_name,
count(t.instenid) tenantnum,
sum(t.users) usernum,
t.endday,
t.tenanttype,
t.counttype,
t.startday
from
(
-- 先清洗出今日租户对应去重后的用户数
select
tuaa.instenid,
tuaa.cloud_name,
tuaa.app_name,
count(distinct(tuaa.uid)) users,
'0' endday,
tuaa.tenanttype,
'daily' counttype,
current_date startday
from
kdcloud_cosmic_poa.t_user_action_all tuaa
where
tuaa.day = current_date
group by
tuaa.instenid,
tuaa.cloud_name,
tuaa.app_name,
tuaa.tenanttype
) t
group by
t.tenanttype,
t.counttype,
t.cloud_name,
t.app_name,
t.startday,
t.endday