大数据学习——Hive实战（常用操作命令）

启动Hive

启动集群中所有的组件

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

使用终端连接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了。

[root@node1 onekey]# cd /export/server/spark-2.3.0-bin-hadoop2.7/bin
[root@node1 bin]# ./beeline
Beeline version 1.2.1.spark2 by Apache Hive
beeline> !connect jdbc:hive2://node1:10000
Connecting to jdbc:hive2://node1.itcast.cn:10000
Enter username for jdbc:hive2://node1.itcast.cn:10000: root
Enter password for jdbc:hive2://node1.itcast.cn:10000: 直接回车
2021-01-08 14:34:24 INFO  Utils:310 - Supplied authorities: node1.itcast.cn:10000
2021-01-08 14:34:24 INFO  Utils:397 - Resolved authority: node1.itcast.cn:10000
2021-01-08 14:34:24 INFO  HiveConnection:203 - Will try to open client transport with JDBC Uri: jdbc:hive2://node1.itcast.cn:10000
Connected to: Spark SQL (version 2.3.0)
Driver: Hive JDBC (version 1.2.1.spark2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://node1.itcast.cn:10000> 。

Hive数据库操作

• 创建数据库-默认方式

  create database if not exists myhive; show databases; #查看所有数据库
  说明：
  1: if not exists：该参数可选,表示如果数据存在则不创建(不加该参数则报错),不存在则创建。 2: hive的数据库置默认存放在/user/hive/warehouse目录

• 创建数据库-指定存储路径

  create database myhive2 location '/myhive2’;
show databases; #查看所有数据库
  说明：
  1: location ：用来指定数据库的存放目录

• 查看数据库详细信息

  desc database myhive;

• 删除数据库

  drop database myhive;

  drop database myhive2 cascade;

Hive数据表操作

创建数据库表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[LOCATION hdfs_path]

内部表操作

创建表

未被external修饰的是内部表（managed table）,内部表又称管理表。内部部表不适合用于共享数据

create database mytest; #创建数据库 

use mytest; #选择数据库 

create table stu(id int,name string); 

show tables; #查询数据 

内部表操作-查看表结构/删除表

desc stu; #查看表结构基本信息
`desc formatted stu; #查看表结构详细信息``

``drop table stu;`

Hive内部表操作-数据添加

对于Hive中的表，可以通过insert into 指令向表中插入数据。

# 方式1-直接插入数据
use mytest; #选择数据库 

create table stu(id int,name string); #创建表

#向表中插入数据
insert into stu values(1,'zhangsan’); 
insert into stu values(2,'lisi');

select * from stu; #查询数据 

# 方式2-load数据加载
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]

说明:
  LOCAL 表示从本地文件系统加载,否则是从HDFS加载

Hive内部表特点

• Hive是建立在hadoop之上的数据仓库，存在hive里的数据实际上就是存在HDFS上，都是以文件的形式存在

• Hive元数据用用来记录数据库和表的特征信息，比如数据库的名字、存储路径、表的名字、字段信息，表文件存储路径等等。

• hive内部表信息存储默认的文件路径是在/user/hive/warehouse/databasename.db/tablename/目录

• hive内部表在进行drop操作时,其表中的数据以及表的元数据信息均会被删除

• 内部表一般可以用来做中间表或者临时表

Hive外部表操作

创建表

创建表时，使用external关键字修饰则为外部表，外部表数据可用于共享。

#创建学生表
create external table student (sid string,sname string,sbirth string , ss ex string) row format delimited fields terminated by ‘\t’ location ‘/hive_table/student‘;

#创建老师表
create external table teacher (tid string,tname string) row format delimited fields terminated by '\t’ location ‘/hive_table/teacher‘;

加载数据

#给学生表添加数据
load data local inpath '/export/data/hivedatas/student.txt' into table student;

#给老师表添加数据，并覆盖已有数据
load data local inpath '/export/data/hivedatas/teacher.txt' overwrite into table teacher;

#查询数据
select * from student;
select * from teacher;

外部表特点

• 外部表在进行drop操作的时，仅会删除元数据，而不删除HDFS上的文件

• 外部表一般用于数据共享表，比较安全

Hive表操作-分区表

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

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

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

创建一级分区表

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

数据加载

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

创建多级分区表

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

数据加载

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

查看分区

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中存放相同类型的数据。

源数据:

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 ',';

建表语句

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查询操作

分组查询

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

--（1）计算每个学生的平均分数
select sid ,avg(sscore) from score group by sid;

--（2）计算每个学生最高成绩
select sid ,max(sscore) from score group by sid;

Having语句

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

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

排序

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

--(1)查询学生的成绩，并按照分数升序排列
select * from score order by sscore asc;

--(2)按照分数的平均值降序排序
select sid ,avg(sscore) avg from score group by sid order by avg desc;

limit语句

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

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

多表查询

内连接查询

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

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

案例

查询每个老师对应的课程信息

隐式内连接
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 teacher t join course c on t.tid = c.tid;

外连接查询

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

select * from A left outer join B on 条件;

右外连接：right outer join

JOIN操作符右边表中符合WHERE子句的所有记录将会被返回

select * from A right outer join B on 条件;

满外连接 FULL OUTER JOIN

将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

案例

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

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

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

子查询

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

• 将查询结果当成一个值

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

• 将查询结果当成一张表

  #查询成绩最高学生的sid

  select sid from score a,(select max(sscore) max_score from score) b where a.sscore = b.max_score;

Hive内置函数

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

数学函数

指定精度取整函数: round
语法: round(double a, int d)
说明:返回指定精度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

字符串函数

字符串连接函数-带分隔符：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

日期函数

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

条件判断函数

语法: 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

行转列操作

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

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

开窗函数

• row_number,rank,dense_rank

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

  需求：对一下源数据按照最后一列进行分组排名

  1001,2021-04-10,1
  1001,2021-04-11,5
  1001,2021-04-12,7
  1001,2021-04-13,3
  1001,2021-04-14,2
  1001,2021-04-15,7
  1001,2021-04-16,4
  1002,2021-04-10,2
  1002,2021-04-11,3
  1002,2021-04-12,5
  1002,2021-04-13,6
  1002,2021-04-14,3
  1002,2021-04-15,9
  1002,2021-04-16,7

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

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

  -- 从开头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行max到当前行 
  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行max到后一行 
  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;
  

--1、基本查询
  --1.1 全表查询
  select * from score;
  --1.2 指定列查询
  select sid,cid from score;
  --1.3 给列起别名
  select sid as stu_id, cid course_id from score;

--2、聚合函数
  --2.1 求总行数(count)
   select count(1) from score;
  --2.2 求分数的最大值（max）
   select max(sscore) from score;
  --2.3 求分数的最小值（min）
   select max(sscore) from score;
  --2.4 求分数的总和（sum）
   select max(sscore) from score;
  --2.5 求分数的平均值（avg）
   select avg(sscore) from score;

--3、where语句
   --3.1 查询分数大于60分的数据
    select * from score where sscore > 60;
--4、关系运算符操作
 --4.1 查询分数等于80的所有的数据 
    select * from score where sscore = 80;
 --4.2 查询分数在80到100的所有数据 
    select * from score where sscore between 80 and 100;
 --4.3 查询成绩为空的所有数据 
    select * from score where sscore is null;
 --4.4 查询成绩是80或 90的数据
    select * from score where sscore in(80,90);
  --4.5 查找姓李的学生信息
    select * from student where sname like '李%';
  --4.6 查找名字的第二个字是兰的学生信息
    select * from student where sname like '_兰%'

--5、逻辑运算符
 --5.1 查询成绩大于80，并且sid是01的数据 
   select * from score where sscore > 80 and sid = '01';

 --5.2 查询成绩大于80，或者sid 是01的数 
   select * from score where sscore > 80 or sid = '01';
 --5.3 查询sid 不是 01和02的学生 
   select * from score where sid not in('01', '02');

--6、分组查询
  --6.1 计算每个学生的平均分数
  select sid,avg(sscore) from score group by sid;

  --6.2 计算每个学生最高成绩 
  select sid,max(sscore) from score group by sid;

--7、Having语句
  --7.1 求每个学生平均分数大于85的人 
 select sid, avg(sscore) avgscore from score group by sid  having avgscore > 85;

--8、Limit语句
   --8.1 查询Student表3行数据
   select * from student limit 3;
--9、排序语句
 --9.1 查询学生的成绩，并按照分数升序排列 
   select * from score order by sscore;

 --9.2按照分数的平均值降序排序 
  select sid ,avg(sscore) avgscore from score group by sid order by avgscore desc;


--10、多表查询
 --10.1 内连接查询-创建表
    create table course(cid string,cname string,tid string) 
    row format delimited fields terminated by '\t';

    load data local inpath '/export/data/hivedatas/course.txt' into table  course;

--10.2 查询老师对应的课程
  -- 方式1: 隐式内连接
    select * from teacher t , course c  where t.tid = c.tid;

-- 方式2: 显式内连接
    select * from teacher t inner join course c  on t.tid = c.tid;
    select * from teacher t  join course c  on t.tid = c.tid;
 
--10.3 查询老师对应的课程
   -- 左外连接
    select * from teacher t  left join course c on t.tid = c.tid;
--11、子查询
  --11.1 将查询结果当成一个值或者当成多个值 -查询成绩最高的学生的sid
   select sid from score where sscore = (select max(sscore) from score);

   select * from student where sid in (select sid from score where sscore = (select max(sscore) from score));
  --11.2 将查询的结果当成一张表
   select sid from score a,(select max(sscore) max_score from score) b where a.sscore = b.max_score;

    select * from student where sid in(select sid from score a,(select max(sscore) max_score from score) b where a.sscore = b.max_score)

--12、内置函数
  --12.1 条件判断函数-判断学生的成绩是否及格
  select 
   sid,
   case
     when sscore >=60
       then '及格'
     when sscore < 60
       then '不及格'
     else
       '其他'
    end as flag
  from 
    score; 
   --12.2 行转列操作
    20	 SMITH   
    30 	ALLEN   
    30 	WARD    
    20	 JONES   
    30 	MARTIN  
    30 	BLAKE   
    10 	CLARK   
    20	 SCOTT   
    10 	KING    
    30	 TURNER  
    20	 ADAMS   
    30	 JAMES   
    20 	FORD    
    10	 MILLER