Hive玩得好，头发就变少(hiv会掉头发吗)

Hive

窗口函数Rank日期函数数据取整函数常用字符串常用集合函数grouping_set压缩和存储TextFileOrc格式Parquet格式

窗口分析函数：窗口函数也称为OLAP函数，是对一组值进行操作，
不需要使用Group by子句对数据进行分组，
还能再同一行返回原来行的列和使用聚合函数得到的聚合列

over():指定分析函数工作的数据窗口大小，这个数据窗口大小可能回随着行的变而变化
current row 当前行
n preceding 往前n行数据
n following 往后n行数据
unbounded 起点
unbounded preceding 表示从前面的起点
following 表示从后面的终点
lag(col,n) 往前第n行数据
lead(col,n) 往后第n行数据
ntile(n) 把有序分区中的行分发到指定数据的组中，
各个组有编号，编号从1开始，
对于每一行，ntile返回此行所谓的组的编号。

查询在2022年3月购买过的顾客及总人数
select name,count(*) over()
from business
where substring(orderdate,1,7)='2022-03'
group by name;

查询顾客的购买明细及月购买总额
select 
	name,
	orderdate,
	cost,
	sum(cost) over(partition by month(orderdate))
from 
	business

每个顾客的cost按照日期进行累加
select
	name.
	orderdate,
	cast,
	sum(cost) over(partiton by name order by orderdate)
from 
	business

统计前面的1行到后面的1行的和 = 3行
between PRECEDING and FOLLOWING
从前面的某行到后面的某行

select
	name.
	orderdate,
	cast,
	sum(cost) over(partiton by name 
					order by orderdate 
					rows between 1 PRECEDING and 1 FOLLOWING)
from 
	business

lag(orderdate,1，orderdate)：获取上一行的数据，没有就给自己
统计顾客上次购买的日期

select
	name,
	orderdate,
	lag(orderdate,1，orderdate) over(partition by name
									 order by orderdate)
from
	business

lead(orderdate,1，orderdate)：获取下一行的数据，没有就给自己
统计顾客下次购买的日期

select
	name,
	orderdate,
	lead(orderdate,1，orderdate) over(partition by name 
									order by orderdate)
from
	business

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值,
如果切片不均匀，默认增加第一个切片的分布

select 
	name,
	orderdate,
	cost,
	ntile(5) over(order by orderdate) groupID
from
	business;

查询前20%时间的订单信息
select 
	name,
	orderdate,
	cost
from 
	(select 
        name,
        orderdate,
        cost,
        ntile(5) over(order by orderdate) groupID
     from
        business) t1
where 
	groupID = 1;

1、Rank()	排序相同时会重复，总数不会变
2、dense_rank()	排序相同时会重复，总数会减少
3、row_number()	会根据顺序计算

select *,rank() over(partiton by subject order by score) from score

select *,dense_rank() over(partiton by subject order by score) from score

select *,row_number() over(partiton by subject order by score) from score

1、hive取得当前日期时间：
1.1) 取得当前日期：
select current_date();

1.2) 取得当前日期时间：
select current_timestamp();

1.3) hive取得当前时间戳：
select unix_timestamp();

1.4) 时间戳转日期：
select from_unixtime(1517725479,‘yyyy-MM-dd HH:dd:ss’);

1.5) 日期转unix时间戳：
select to_nuix_timestamp(‘2017-01-01 12:12:12’,‘yyyy-MM-dd HH:dd:ss’);

1.6) hive取得当前时间：
select from_unixtime(unix_timestamp(),‘yyyy-MM-dd HH:dd:ss’);

2、hive自动计算其他日期（昨天，今天）：
hive中日期加减函数：date_add(start_date,num_days)

2.1) 取得昨天日期：
select date_add(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),-1);
select date_sub(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),1);

2.2) 取得明天日期：
select date_add(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),1);
select date_sub(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),-1);

3、hive取得两个日期之间差值（差值为天数）：
datediff(date1,date2):date1大于date2，返回值为正，否则，返回值为负。
select datediff(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),
date_add(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),-10));

select datediff(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),
date_add(from_unixtime(unix_timestamp(),‘yyyy-MM-dd’),10));

4、字符串转时间（字符串必须为：yyyy-MM-dd格式）
select to_date(‘2017-01-01 12:12:12’);

5、日期、时间戳、字符串类型格式化输出标准时间格式：
select date_format(current_timestamp(),‘yyyy-MM-dd HH:mm:ss’);
select date_format(current_date(),‘yyyy-MM-dd’);
select date_format(‘2017-01-01’,‘yyyy-MM-dd HH:mm:ss’); 
--字符串必须满足yyyy-MM-dd格式

6、utc时间转换:
select from_utc_timestamp(current_timestamp(),8);
select to_utc_timestamp(current_timestamp(),8);

ceil 向上取整 3.14 结果 3	
			3.54	4
			
floor 向下取整 3.14 结果 3
			3.54	3

upper	转大写
lower	转小写
length	长度
trim	前后去空格
lpad（str，10，‘a’）	向左补齐	超出字符串长度时 拼接默认的字符串a
rpad	向右补齐
regexp_replace	
使用正则匹配目标字符串 regexp_replace（2022-03-12，‘-’，‘/’）将 - 替换成 / 

size				集合中元素的个数
map_keys			返回map中的key
map_values			返回map中的value
array_contains		判断array中是否包含某个元素
sort_array			将array中的元素进行排序

多维分析

select 
	deptid,gender,count(*) 
from staff 
group by deptid,gender
grouping sets((deptid,gender),deptid,gender,())

行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，
行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快

列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量，
每个字段的数据类型一定是相同的，列示存储可以针对性的设计更好的设计压缩算法

textFile和Sequencefile的存储格式都是基于行存储的
ORC和Parquet是基于列式存储的

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。
可结合Gzip、Bzip2使用，但使用Gzip这种方式，
hive不会对数据进行切分，从而无法对数据进行并行操作

Orc是Hive引入的新的存储格式

Orc文件由1个或多个stripe组成，每个stripe250MB大小，
这个stripe实际相当于RowGroup概念，
不过大小由4MB->250MB，这样应该能提升顺序读的吞吐率。
每个strope里有三个部分组成，分别是Index data，row Data，Stripe Footer：

1、index data：一个轻量级的index，默认是每隔1w行做一个索引。
这里做的索引应该只是记录某行的各字段在row data中的offset

2、row data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。
对每个列进行了编码，分成多个stream来储存

3、stripe footer：存的是各个stream的类型，长度等信息

parquet是面向分析型业务的列式存储格式，由twitter和cloudera合作开发

parquet文件时以二进制方式存储的，所以是不可以直接读取的，
文件中包含该文件的数据和元数据，因此parquet格式文件是自解析的

通常情况下，在存储parquet数据时候会按照Block大小设置行组的大小，
由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，
这样可以把每个行组由一个Mapper任务处理，增大任务执行并行度。

HIVE 一般使用ORC文件格式配合snappy压缩使用