Hive常用函数

开窗函数over()

ran()

日期函数

1. date_add()加减day

date_format(date_add(current_date,-10),'yyyy-MM-dd HH:mm:ss')

2. UNIX_TIMESTAMP()加减秒

from_unixtime(UNIX_TIMESTAMP(call_time,'yyyy-MM-dd HH:mm:ss.SSSSS')+duration, 'yyyy-MM-dd HH:mm:ss.S')

3. add_months()加减月

add_months(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), -1)
trunc(add_months(current_date,-1) ,'MM') 

4. current_date()当前时间

yyyy-MM-dd的格式

5.获取年月日时分秒

second(time)  as second           -- 秒钟
minute(time)   as minute           -- 分钟
hour(day)        as hour              -- 小时
day(day)         as day              -- 日期
month(day)     as month            -- 月份lpad(ceil(month(day)/3),2,0)   as season           -- 季度
year(day)    -- 年

建表

CREATE TABLE

Drop table if exists <库名>.<表名>;
create table if not exists <库名>.<表名>(
		id 			int 				comment’注解’,
		name 		string 				comment’注解’,
		age 		int 				comment’注解’,
		likes 		ARRAY<string> 		comment’注解’,
		address 	MAP<string,string> 	comment’注解’,
		Sex_ID 		string 				comment’注解’,
		Dt_ID 		string   --分区字段和表字段不要是同一个
  ) comment’表名注解’
partitioned by (sex string comment’注解’, 
								dt  string comment’注解’) 
row 	format 	delimited --定义切割的规则
NULL 	DEFINED AS ''
fields 	terminated by ','  --字段之间的分割符  fields
collection items terminated by '-'   --集合元素的分割符
MAP 	keys 	terminated by  ':'    --map的分割符
lines 	terminated by '\n'
stored as ORC;     --行数据的分割符

CREATE TEMPORARY TABLE

Drop table if exists <库名>.<表名>;
create TEMPORARY table if not exists <库名>.<表名> stored as ORC as (
		id 			int 				comment’注解’,
		name 		string 				comment’注解’,
		age 		int 				comment’注解’,
		likes 		ARRAY<string> 		comment’注解’,
		address 	MAP<string,string> 	comment’注解’,
		Sex_ID 		string 				comment’注解’,
		Dt_ID 		string   --分区字段和表字段不要是同一个
  ) comment’表名注解’
partitioned by (sex string comment’注解’, 
								dt  string comment’注解’) 
row 	format 	delimited --定义切割的规则
NULL 	DEFINED AS ''
fields 	terminated by ','  --字段之间的分割符  fields
collection items terminated by '-'   --集合元素的分割符
MAP 	keys 	terminated by  ':'    --map的分割符
lines 	terminated by '\n'
stored as ORC;     --行数据的分割符

show - 分区,表结构等.

show create table  --查看表结构
show partitions table
desc table