Hive -- Hive基础

Hive架构原理

1. 用户接口：Client
   CLI（command-line interface）、JDBC/ODBC(jdbc访问hive)、WEBUI（浏览器访问hive）
2. 元数据：Metastore
   元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；元数据默认存储在自带的derby数据库中，推荐使用MySQL存储
3. Hadoop
   使用HDFS进行存储，使用MapReduce进行计算。
4. 驱动器：Driver
   （1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
   （2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。
   （3）优化器（Query Optimizer）：对逻辑执行计划进行优化。
   （4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

  Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口

数据类型

基本数据类型

  对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

集合数据类型

  Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

排序

全局排序（Order By）

Order By：全局排序，只有一个Reducer

select * from emp order by sal desc;

分区内部排序（Sort By）

对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by
Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

set mapreduce.job.reduces=3;
select * from emp sort by deptno desc;

分区（Distribute By）

在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。
distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。 
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

set mapreduce.job.reduces=3;
select * from emp distribute by deptno sort by empno desc;

注意：
  1．distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
  2．Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

Cluster By

当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。
但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

## 以下两种写法等价
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

常用函数

NVL： 为NULL的数据赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。
它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，
如果两个参数都为NULL ，则返回NULL

select comm,nvl(comm, -1) from emp;

时间函数

1. date_format: 格式化时间

hive (default)> select date_format('2019-06-29','yyyy-MM-dd');
OK
_c0
2019-06-29

2. date_add: 时间跟天数相加

select date_add('2019-06-29',5);
OK
_c0
2019-07-04

hive (default)> select date_add('2019-06-29',-5);
OK
_c0
2019-06-24

3. date_sub: 时间跟天数相减

hive (default)> select date_sub('2019-06-29',5);
OK
_c0
2019-06-24

hive (default)> select date_sub('2019-06-29 12:12:12',5);
OK
_c0
2019-06-24

hive (default)> select date_sub('2019-06-29',-5);
OK
_c0
2019-07-04

4. datediff: 两个时间相减

hive (default)> select datediff('2019-06-29','2019-06-24');
OK
_c0
5

hive (default)> select datediff('2019-06-24','2019-06-29');
OK
_c0
-5

hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29');
OK
_c0
-5

hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29
13:13:13');
OK
_c0
-5

5. next_day函数: 得到一个字符串日期的下周几的具体日期

# 得到2020-01-01往后的第一个周五
select next_day('2020-01-01','Fri');  

6. last_day函数: 求当月最后一天日期

select last_day('2020-01-01');  

7. from_unixtime（‘bigint时间戳’，‘pattern’）函数: 把一个大数字时间戳，转换为指定格式的日期字符串

# 7,8在一起用，常用来把非yyyy-MM-dd的日期字符串转为标准的yyyy-MM-dd的日期字符串
select from_unixtime(1517725479,'yyyy-MM-dd HH:dd:ss');
select from_unixtime(1517725479,'yyyyMMdd');

8. unix_timestamp(‘日期字符串’，‘pattern’): 把指定格式的日期转成时间戳

# 7,8在一起用，常用来把非yyyy-MM-dd的日期字符串转为标准的yyyy-MM-dd的日期字符串
select unix_timestamp('2020/01/01','yyyy/MM/dd');

9. current_date(): 得到当前的日期字符串

select current_date();

10. current_timestamp() : 得到当前的时间字符串

select current_timestamp();

11. unix_timestamp(): 得到当前的时间戳

select unix_timestamp();

CASE WHEN， if

select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;
  
## 或者
select 
  dept_id,
  count(case sex when '男' then 1 ) male_count,
  count(case sex when '女' then 1) female_count
from 
  emp_sex
group by
  dept_id;
  
## 或者
select 
  dept_id,
  sum(if(sex = '男', 1, 0)) male_count,
  sum(if(sex = '女', 1, 0)) female_count
from 
  emp_sex
group by
  dept_id;

行转列collect_set, concat_ws

1. CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
2. COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
   

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name
from
    (select
        name,
        concat(constellation, ",", blood_type) base
    from
        person_info) t1
group by
    t1.base;

## 结果
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

列转行 explode

1. EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
2. LATERAL VIEW： LATERAL VIEW udtf(expression) tableAlias AS columnAlias
   解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
   

select
    movie,
    category_name
from 
    movie_info lateral view explode(category) table_tmp as category_name;

## 结果
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

窗口函数（开窗函数） over

  OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
  可以和over结合使用的，用在over之前：

聚合函数：sum、 count、 max、 min等
LAG(col,n,default_val)：往前第n行数据
LEAD(col,n, default_val)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，
		  对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

  窗口中可使用的关键字：

partition by (col)：根绝col字段分组
order by (col)：根据col字段排序
partition by (col) order by (col)：根据col字段分组，排序
distribute by (col) ：等于partition by
sort by (col)：等于order by
distribute by (col)  sort by (col)：等同于partition by (col) order by (col)

CURRENT ROW：当前行：
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

  数据准备

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

  需求：
（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的cost按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前20%时间的订单信息

  解答：

##（1）查询在2017年4月份购买过的顾客及总人数
select name,count(*) over () 
from business 
where substring(orderdate,1,7) = '2017-04' 
group by name;

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

##（3）上述的场景, 将每个顾客的cost按照日期进行累加
select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;
## rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

##（4）查询每个顾客上次的购买时间
select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, 
lag(orderdate,2) over (partition by name order by orderdate) as time2 
from business;

##（5）查询前20%时间的订单信息
select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

排序函数，rank、dense_rank、row_number

1. RANK() 排序相同时会重复，总数不会变
2. DENSE_RANK() 排序相同时会重复，总数会减少
3. ROW_NUMBER() 会根据顺序计算

  数据准备

  需求： 计算每门学科成绩排名。
  解答：

select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

  结果：