hive脚本

Hive

注意

hive分区字段不能包含中文字段值

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