目录

1. Hive安装和启动
2. Hive表操作-分区表
3. Hive表操作-复杂类型操作
4. Hive 查询语句
5. Zepplin框架
6. 网约车日志分析项目

---

Hive安装和启动

1：启动集群中所有的组件

cd /export/onekey
./start-all.sh

2：使用终端连接Hive

1. 进入到/export/server/spark-2.3.0-bin-hadoop2.7/bin目录中
2. 执行以下命令：./beeline
3. 输入：!connect jdbc:hive2://node1:10000，回车
4. 输入用户名：root
5. 直接回车，即可使用命令行连接到Hive，然后就可以执行HQL了。

3：打开 beeline 前先输入以下命令

`nohup hive --service hiveserver2 &回车`

Hive表操作-分区表

• 一级分区
• 多级分区
• 分区操作（查看、添加、删除）

定义

:star2: 在大数据中，最常用的一种思想就是分治，分区表实际就是对应hdfs文件系统上的的独立的文件夹，该文件夹下是 该分区所有数据文件。

:star2:分区可以理解为分类，通过分类把不同类型的数据放到不同的目录下。

:star2:Hive中可以创建一级分区表，也可以创建多级分区表

:star2:根据内外部表可区分出==分区内部表==、==分区外部表==

一级分区表

1.创建分区表

create table score(sid string,cid string, sscore int) partitioned by (month string) row format delimited fields terminated by '\t';

2.加载数据

load data local inpath '/export/data/hivedatas/score.txt' into table score partition (month='202006');

:sailboat:分区表妙用

1.可见分区字段会显示在表中，但是它并不是真实存在于表的字段

2.加载同一等级不同分区的数据

load data local inpath '/export/data/hivedatas/score.txt' 
into TABLE score partition (month='202007');

3.这时可以指定分区字段值当作筛选条件【分区表和where联动】

多级分区表

1.创建多级分区表

create table score2(sid string,cid string, sscore int) partitioned by (year string,month string, day string) 
row format delimited fields terminated by '\t'; 

2.加载数据

load data local inpath '/export/data/hivedatas/score.txt' 
into TABLE scores partition (year='2020',month='06',day='01');

:sailboat:分区表妙用

1.加载同一等级不同分区的数据

load data local inpath '/export/data/hivedatas/score.txt' 
into TABLE scores partition (year='2020',month='06',day='02');

2.指定分区字段值当作筛选条件【分区表和where联动】

分区操作（查看、添加、删除）

• 查看分区 show partitions tb_name;

  show partitions score;
  

• 添加分区

  alter table score add partition(month='202008’); 
  alter table score add partition(month='202009') partition(month = '202010');
  

• 删除分区

  alter table score drop partition(month = '202010');
  

Hive表操作-复杂类型操作

• Array类型

Array类型

定义：Array是数组类型，Array中存放相同类型的数据。

• 源数据

  zhangsan    beijing,shanghai,tianjin,hangzhou
  wangwu    changchun,chengdu,wuhan,beijin
  

• 建表语句【这条语句报错】

  create external table hive_array(name string, work_locations array<string>) row format delimited fields terminated by '\t’ 
  collection items terminated by ','; 
  

  Error while compiling statement: FAILED: ParseException line 2:32 cannot recognize input near ',' '<EOF>' '<EOF>' in serde properties specification (state=42000,code=40000)
  

• 数据加载

  load data local inpath '/export/data/hivedatas/array_data.txt' overwrite into table hive_array;
  

• 常用查询

  -- 查询所有数据 
  select * from hive_array; 
  -- 查询loction数组中第一个元素 
  select name, work_locations[0] location from hive_array; 
  -- 查询location数组中元素的个数 
  select name, size(work_locations) location from hive_array; 
  -- 查询location数组中包含tianjin的信息 
  select * from hive_array where  array_contains(work_locations,'tianjin'); 
  

Hive 查询语句

别名大坑：不同于MySQL中用 "别名"，HSQ的别名用

`别名`

多表查询

1.内连接查询

  隐式内连接： 
   select * from A,B where 条件; 
  显示内连接：
   select * from A inner join B on 条件; 

2.外连接【比MySQL多了全外连接】

 # 左外连接：left outer join 
   select * from A left outer join B on 条件; 

 # 右外连接：right outer join 
   select * from A right outer join B on 条件;
 
 # 全外连接：full join(如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。)
   select * from A full join B on 条件;

Hive内置函数

1.字符串函数

字符串连接函数-带分隔符：==concat_ws==

语法: concat_ws(string SEP, string A, string B…)
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：

hive> select concat_ws(',','abc','def','gh’); 
abc,def,gh 

字符串截取函数：==substr==,==substring==

语法:
substr(string A, int start, int len), --start是顺序
substring(string A, int start, int len) -- start可以是逆序【即负数】
返回值:
string 说明：返回字符串A从start位置开始，长度为len的字符串
举例：

hive> select substr('abcde',3,2); 
cd 
hive>select substring('abcde',-2,2);
de

2.日期函数

日期转年函数: ==year==

语法: year(string date)
说明:返回日期中的年。
举例：

hive> select year('2012-12-08’); 
2012

日期增加函数: ==date_add==

语法: date_add(string startdate, int days)
说明:返回开始日期startdate增加days天后的日期。
举例：

hive> select date_add('2012-12-08',10); 
2012-12-18 

日期减少函数: ==date_sub==

语法: date_sub (string startdate, int days)
返回值: string 说明:返回开始日期startdate减少days天后的日期。
举例：

hive> select date_sub('2012-12-08',10); 
2012-11-28

3.行转列操作

行转列是指多行数据转换为一个列的字段。

Hive行转列用到的函数：
concat_ws(sep, str1,str2) --以分隔符拼接每个字符串
collect_set(col) --将某字段的值进行去重汇总，产生array类型字段

示例：

1:建表 
create table emp( deptno int, ename string ) row format delimited fields terminated by '\t’; 

2:插入数据： 
load data local inpath "/export/data/hivedatas/emp.txt" into table emp; 

3:转换 

select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;

3.开窗函数

row_number,rank,dense_rank

生产中只用rank和dense_rank，row_number不合理

rank() 输出的排名 （1，2，3，3，5）

dense_rank() 输出的排名 （1，2，3，3，4）

示例：

-- 创建表，pv是访问次数
create table user_ access (
 user_id string, 
 createtime string, --day 
 pv int 
) 
row format DELIMITED FIELDS TERMINATED BY ','; 

-- 加载数据： 
load data local inpath '/export/data/hivedatas/user_access.txt' into table user_access;

--实现分组排名
  -- ROW_NUMBER()  从1开始，按照顺序，生成分组内记录的序列
  -- RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位  
  -- DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

select user_id, createtime, pv, 
rank() over(partition by user_id order by pv desc) AS rn1, 
dense_rank() over(partition by user_id order by pv desc) as rn2, 
row_number() over(partition by user_id order by pv desc) as rn3 from user_access;

4.sum,avg,min,max

用于==实现分组内所有和连续累积的统计==

:arrow_right:SUM

-- 从开头unbounded preceding累加到当前行current row
select user_id,createtime,pv, 
sum(pv) over(partition by user_id order by createtime rows between unbounded preceding and current row) as pv2 
from user_access; 

-- 从前3行累加到当前行 
select user_id,createtime,pv, sum(pv) over(partition by user_id order by createtime rows between 3 preceding and current row) as pv4 
from user_access; 

-- 从前3行累加到后一行 
select user_id,createtime,pv, sum(pv) over(partition by user_id order by createtime rows between 3 preceding and 1 following) as pv5 from user_access;

:arrow_right:MAX

-- 从开头当前行的最大值
select user_id,createtime,pv, 
max(pv) over(partition by user_id order by createtime rows between unbounded preceding and current row) as pv2 
from user_access; 

-- 从前3行到当前行的最大值
select user_id,createtime,pv, max(pv) over(partition by user_id order by createtime rows between 3 preceding and current row) as pv4 
from user_access; 

-- 从前3行到后一行的最大值
select user_id,createtime,pv, max(pv) over(partition by user_id order by createtime rows between 3 preceding and 1 following) as pv5 from user_access;

Zepplin框架

Zepplin介绍

定义：

Apache Zeppelin是一款基于Web交互式框架，支持多种语言，提供了==数据分析==、==数据可视化==功能。

项目中的作用（两个）：

使用Zeppelin来连接到Spark SQL的Thrift Server，可以以更直观的方式来查看Hive中的数据。而且Zeppelin也可以以图表的方式展示数据。

总结：

使用Zeppelin来连接到Spark SQL的Thrift Server的好处有两个：

1. 以更直观的形式来查看hive中的数据
2. 可以以图表的方式展示数据

Zeppelin安装和使用

安装：黑马课程提供的虚拟机已安装

使用

1. 一键启动集群中所有的组件

   cd /export/onekey 
   ./start_all.sh
   

2. 打开浏览器，输入地址访问：http://192.168.88.100:8090/

网约车日志分析项目

搭建大数据环境并连接各个组件

数仓分层：

1.原始日志数据会存放在临时存储层ODS层

2.预处理数据会放在数据仓库DW层

3.分析得到的结果数据放在应用层APP层

ods层分区表建立与数据加载

dw层的数据预处理及加载

case有两种写法，但是只用记住第二种写法

case when then

when then

...

end

app层的订单指标分析及加载

Sqoop数据导出

Superset数据可视化

:star:

hour() 返回null问题

解决办法来自：https://community.cloudera.com/t5/Support-Questions/hive-date-time-problem/td-p/139953

解决办法是，弃用date_format()，改用==from_unixtime(unix_timestamp(b,'yyyy-MM-dd HH:mm'))==

date_format()

适用于得出单独的年月日，比如hour(date_format(b,'yyyy-MM-dd')) 、year(date_format(b,'yyyy-MM-dd'))、month(date_format(b,'yyyy-MM-dd'))

不适用于时分秒函数内

from_unix_timestamp(unix_timestamp(b,'yyyy-MM-dd HH:mm'))

适用于所有日期的情况，无论是年月日时分秒，在规整时间后就可以得到年月日时分秒