Zeppelin框架及Hive查询操作

1）、介绍

Apache Zeppelin是一款基于Web交互式框架，支持多种语言，Scala、SparkSQL、Markdown，SQL、Shell、Python等。

Zeppelin提供数据分析、数据可视化。

可以使用Zeppelin链接SparkSQL

Zeppelin安装和使用

1）、安装

2）、使用

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

cd /export/onekey

./start_all.sh

打开浏览器 访问，xxxx:8090

4)使用

1、创建note

2、编写SparkSQL

3、通过设置Zepplin的一些参数，方便使用

Hive查询操作

1）、基本语法

select [distinct]select_expr, select_expr, ... 
from table_reference 
[where where_condition] 
[group by col_list] 
[having where_condition] 
[order by col_list] ... 
[limit number]


解释:
 select 查询关键字
 distinct 去重
 from 指定要查询的表
 where 指定查询条件
 group by  分组查询
 having 对分组后的结果进行条件筛选
 order by  排序
 limit  查询指定的记录数

2）、全表查询

select * from score;

3)、选择特定列查询

select sid, cid from score;

4)、使用别名查询

select sid as stu_id , cid course_id from score;

Hive查询操作-聚合函数

1）、介绍SparkSQL提供的聚合函数可以用来统计、求和、求最值等等

count：统计行数量

sum：获取单个列的合计值

avg：计算某个列的平均值

max：计算列的最大值

min：计算列的最小值

2）、应用

求总行数（count）

select count(1) from score;

select count(*) from score;

求分数的最大值（max）

select max（sscore）from score；

求分数的最小值（min）

select min（sscore）from score；

求分数的总和（sum）

select sum（sscore）from score；

求分数的平均值（avg）

select avg（sscore）from score；

Hive查询操作-where语句

1）、介绍

使用where语句，可以查询满足条件的数据，where语句紧随from关键字，where条件语句写法丰富，是Hive数据分析的灵魂之一。

2）、应用

# 查询出分数大于60的数据
select * from score where sscore > 60

Hive查询操作-操作符

1）、介绍

SparkSQL中提供了很多的操作运算符

2）、比较运算符

 

 Hive查询操作-比较运算符

1)、实例1

（1）查询分数等于80的所有数据
select * from score where sscore=80;
 (2) 查询分数在80到100的所有数据
select * from score where sscore between 80 and 100；
（3）查询成绩为空的所有数据
select * from score where sscore is null;
(4) 查询成绩是80 或90 的数据
select * from score where sscore in(80,90);

2）、应用2-like

使用like运算选择类似的值

选择条件可以包含字符或数字

%代表零个或多个字符（任意个字符）

_代表一个字符

（1）查找姓“李”的学生信息
select * from student where sname like '李%';
（2）查询名字第二个字是“兰”的学生信息
select * from student where sname like '_兰%';

Hive查询操作-操作符

1）、逻辑运算符

2）、实例

（1）查询成绩大于80 ，并且sid是01的数据
select * from score where sscore>80 and sid ='01';
(2)查询成绩大于80， 或者sid是01的数据
select * from score where sscore >80 or sid = '01';
(3)查询s_id不是01 和02的学生
select * from score where sid not in('01', '02');

Hive查询操作-分组查询

1）、介绍

分组关键字是group by，该语句通常会和聚合函数一起使用，按照一个或多个列对结果进行分组，然后对每个组执行聚合操作，注意使用group by分组之后，select 后面的字段只能是分组字段和聚合函数。

2）、实例

（1）计算每个学生的平均分数
select sid ， avg（sscore） from score group by sid;
(2)计算每个学生的最高成绩
select sid, max(sscore)  from score group by sid;

Hive查询操作-Having语句

1）、介绍

Having语句通常于group by语句联合使用，用来过滤由group by语句返回的记录及集。Having语句的存在弥补了where关键字不能与聚合函数联合使用的不足

2）、实例

# 求每个学生平均分数大于85的人
select sid, avg(sccore) avgscore from score group by sid having avgscore > 85;

Hive查询操作-排序

1）、介绍

排序关键字order by，用于根据指定的列对结果集进行排序，在排序时，可以指定排序顺序，asc为升序，desc 为降序。

2）、案例

（1）查询学生的成绩，并按照分数升序排列
select * from score order by sscore asc;
（2）按照分数的平均值降序排列
select sid, avg(sscore) avg from score group by sid order by avg desc;

Hive查询操作-limit语句

1）、介绍

limit子句限制查询结果返回的数量

2）、案例

# 查询3条数据
select * from student limit 3;

Hive查询操作-多表操作

1）、介绍

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

(2)外连接查询
左外连接 ：left outer join
select * from A left outer join B on 条件;
右外连接： right outer join
select * from A right outer join B on 条件;

 Hive查询操作-多表查询

1)、内连接案例

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留

# 查询每个老师对应的课程信息
# 隐士内连接
select * from teacher t, course c where t.tid = c.tid;

# 显示内连接
select * from teacher t inner join course c on t.tid = c.tid;
select * from teacheer t jion course c on t.tid = c.tid;

 2）、外连接案例

左外连接（left outer join）

左外连接：join 操作符左边表中符合where子句的所有记录将会被返回。

# 查询老师对应的课程
select * from teacher t left join course c on t.tid = c.tid;

右外连接（right outer join）

右外连接：join 操作符右边表中符合where 子句的所有记录将会被返回。

select * from teacher t right join course c on t.tid = c.tid;

满外连接（full outer join）

满外连接：将会返回所有表中符合where 语句条件的所有记录，如果任一表的指定字段没有符合条件的值的话，那么就是null值代替。

# 查询老师对应的课程
select * from teacher t full join course c on t.tid = c.tid;

Hive 查询操作-子查询

查询允许把一个查询嵌套在另一个查询中，其实就是select的嵌套

1）、将查询结果当成一个值

# 查询成绩最高学生的sid
select sid from score where sscore = (select max(sscore) from score);

2）、将结果当成一张表

# 将查询结果当成一张表
select sid from score a,(select max(sscore) max_score from score) b where a.sscore = b.max_score;

Hive内置函数

1）、介绍

在SparkSQL中提供了很多的内置函数，或者叫内嵌函数，包括聚合函数、数学函数、字符串函数、转换函数、日期函数、条件函数、表生成函数。

2）、数学函数

# 指定精度取整函数：round
语法： round(double a, int b)
说明：返回指定精度d的double类型
举例：hive> select round(3.1415926,4);
3.1416

取随机数函数: rand 
语法: rand(),rand(int seed) 
说明:返回一个0到1范围内的随机数。如果指定种子seed，则会返回固定的随机数 
举例： 
hive> select rand(); 
0.5577432776034763 
hive> select rand(100); 
0.7220096548596434

3）、字符串函数

字符串连接函数-带分隔符：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),
substring(string A, intstart, int len) 
返回值: 
string 说明：返回字符串A从start位置开始，长度为len的字符串 
举例： 
hive> select substr('abcde',3,2); 
cd 
hive>select substring('abcde',-2,2);
de

4）、日期函数

日期转年函数: 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

5）、条件判断函数

语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值: T
说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回


案例:
select 
sid ,
case 
  when sscore>=60 
    then '及格’ 
  when sscore<60 
    then '不及格’
  else '其他’ 
end as flag
from score

6）、行转列操作

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

Hive行转列用到的函数

concat_ws(sep, str1,str2) --以分隔符拼接每个字符串

collect_set(col) --将某字段的值进行去重汇总，产生array类型字段

7）、行转列操作

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;。

Hive内置函数-开窗函数

lrow_number,rank,dense_rank

row_number,rank,dense_rank这三个函数用于数据排名,也可以用于分组排名。

1）、row_number,rank,dense_rank

-- 创建表
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_log;

--实现分组排名
  --  ROW_NUMBER()  从1开始，按照顺序，生成分组内记录的序列
  -- RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位  
  -- ENSE_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;

2）、sum,avg,min,max

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

-- 从开头累加到当前行 
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;

3）、sum,avg,min,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;