大数据hive个人学习总结

一、HIve概述

hive是一个可以把数据用sql处理的工具，数据存储再hdfs上，底层处理是用mr，通过用sql的方式通过mr获得需要的数据，执行程序运行再yarn上。

二、HIVE和MYSQL有什么区别

数据存储不同，hive数据存储到hdfs上，用MR处理，mysql存储在磁盘上，可以把hive看成对hdfs上的数据处理的客户端工具，除了语言有一样的地方再无共同之处。

三、hive元数据为啥要存在mysql

mysql存储hive元数据可以启动多个hive客户端。

四、内部表和外部表

外部表不能删除表的原始数据，只可以删元数据。内部表会删除原始数据，另外内部表不能清空表。

五、特殊语法

1、排序：

order by 全局排序

sort by 每个reduce排序

distribute by把map的某个字段归到一个reduce中，通常和sort by一起用一个字段分区一个字段排序

cluster by 当sort by和distributeby字段一样时使用。

2、分桶

clustered by 根据某个字段分桶，创建分桶表

分桶一般用于抽样获取数据

3、创建表字段说明

create table 创建一个指定名的table

external 创建外部表

comment 为表和列添加注释

partitioned by 为表根据字段分区

clustered by 为表根据字段分桶

sorted by 对桶中一个或多个列排序

4、分区

分区对应了hdfs上不同的文件夹，但数据都在一张Hive表里，hive中的分区就是分目录

5、其他函数

nvl 给Null的字段赋值nvl(null,value)

case when :

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;

行转列：

concat(stringA,stringB) 行转列，可把多行数据合成一列

CONCAT_WS(separator, str1, str2,...) 行转列，每行数据按separator隔开

列转行

explode(col)将hive表中array或map拆分成多行

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;



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


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

开窗函数

over()指定使用窗口函数

current now 指定当前行

n preceding 往前n行数据

n following 往后n行数据

unbounded 起点 unbounded preceding 表示从前面的起点，unbounded following表示后面的终点

    business
name orderdate cost
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月份购买过的顾客及总人数
select name,count(name)  over() from business where substring(orderdate,1,7) ='2017-04' group by name


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

Rank

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

自定义函数

1）UDF（User-Defined-Function）

一进一出

（2）UDAF（User-Defined Aggregation Function）

聚集函数，多进一出

类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions）

一进多出

如lateral view explore()