Hive SQL基础练习

Hive SQL

准备文件:

mkdir -p /export/temp/
cd /export/temp/
vim student.csv

01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙风	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-01	女
06	吴兰	1992-03-01	女
07	郑竹	1989-07-01	女
08	王菊	1990-01-20	女

vim teacher.csv
01	张三
02	李四
03	王五

vim score.csv

01	01	80
01	02	90
01	03	99
02	01	70
02	02	60
02	03	80
03	01	80
03	02	80
03	03	80
04	01	50
04	02	30
04	03	20
05	01	76
05	02	87
06	01	31
06	03	34
07	02	89
07	03	98

vim course.csv

01	语文	02
02	数学	01
03	英语	03

将这些文件上传到hdfs上一份:

hdfs dfs -put /export/temp /

一. Hive的DDL操作

1.操作数据库

注意: hive的表存放位置模式是由hive-site.xml当中的一个属性指定的

<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>

#创建数据库
create database if not exists myhive;

#使用数据库
use  myhive;

#创建数据库, 并指定在hdfs上的存储位置
create database myhive2 location '/myhive2';

#强制删除数据库
drop database myhive2 cascade;

操作数据库的其他命令(了解)

#查看数据库基本信息
desc database myhive;

#查看数据库更多详细信息
desc database extended myhive;

#可以使用alter  database  命令来修改数据库的一些属性。
#但是数据库的元数据信息是不可更改的，包括数据库的名称
#以及数据库所在的位置
alter  database  myhive  set 
dbproperties('createtime'='20190611');

2.操作表

①管理表

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

insert into stu values(1,"zhangsan");

select * from stu;

②外部表

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉。

操作案例: 分别创建老师与学生表外部表，并向表中加载数据

#创建teacher表
create external table teacher 
(t_id string, t_name string) 
row format delimited fields terminated by '\t';

#创建student表
create external table student 
(s_id string, s_name string, s_birth string, s_sex string) 
row format delimited fields terminated by '\t'; 

#从本地系统向表中加载数据(删除表再重新创建后数据依然存在)
load data local inpath '/export/temp/student.csv' 
into table student;

#加载数据并覆盖已有的数据
load data local inpath '/export/temp/student.csv' 
overwrite into table student;

#从hdfs文件系统向表中加载数据(移动操作)
load data inpath '/temp/teacher.csv' into table teacher;

③分区表

将表数据分成了多个文件夹, 一个分区对应一个文件夹

#创建分区表,只有一个分区字段
create table score 
(s_id string, c_id string, s_score int) 
partitioned by (month string) 
row format delimited fields terminated by '\t';

#加载数据到score表中
load data local inpath '/export/temp/score.csv' 
into table score partition (month='201806');

#创建分区表,有多个分区字段
create table score2 
(s_id string,c_id string,s_score int) 
partitioned by (year string,month string,day string) 
row format delimited fields terminated by '\t';

#加载数据到score2表中
load data inpath '/temp/score.csv' 
overwrite into table score2 
partition (year='2018',month='06',day='01');

#在hdfs中, 在score表下创建一个分区文件夹
hdfs dfs -mkdir -p /temp/score3_data/month=201805

#将score.csv上传一份到这个分区目录中
hdfs dfs -put /export/temp/score.csv /temp/score3_data/month=201805

#创建外部表score3, 并指定在hdfs上的存储位置
create external table score3 
(s_id string, t_id string, s_score string) 
partitioned by (month string) 
row format delimited fields terminated by '\t' 
location '/temp/score3_data/';

#修复表score, 即建立表与数据之间的映射关系
msck repair table score3;

#或者不用上面的方法,手动给score3添加一个moth=201805的分区
alter table score3 add partition (month='201805');

其他操作分区表操作:

#查看分区
show partitions score2;

#添加一或多个分区(可以在hdfs中看到表下面多了文件夹)
alter table score add partition(month='201807') partition(month='201808');

#删除分区
alter table score drop partition (month='201807');

④分桶表

• 分桶表是对某一列数据进行哈希取值以将数据打散，然后放到不同文件中存储。
• 在hive分区表中，分区中的数据量过于庞大时，建议使用桶。
• 分桶后的查询效率比分区后的查询效率更高
• 只能用insert overwrite 方式给桶表添加数据

#开启hive分桶表的功能
set hive.enforce.bucketing=true;
#设置reduce的个数(不会决定文件分成了几个)
set mapreduce.job.reduces=3;

#创建桶course(课程)表, 3 buckets 决定了文件会分为3个
create table course 
(c_id string, c_name string, t_id string) 
clustered by (c_id) into 3 buckets 
row format delimited fields terminated by '\t';

注意: 只能用insert overwrite 方式给桶表添加数据

#先创建一个临时表course_temp
create table course_temp 
(c_id string,c_name string, t_id string) 
row format delimited fields terminated by '\t';

#向普通表中加载数据
load data inpath '/temp/course.csv' 
overwrite into table course_temp;

#通过insert overwrite 给桶表添加数据(最后的cluster by (c_id) 可不写)
insert overwrite table course 
select * from course_temp 
cluster by (c_id);

⑤修改表

#重命名表
alter table score3 rename to score4;

#添加列
alter table score4 add columns (mycol_1 string, mycol_2 int);

#修改列名
alter table score4 change column s_score s_score string;

⑥向表中加载数据

#先提前创建两个新表
create table score5 like score;
create table score6 like score;

#插入数据 ==> 强烈不推荐使用
insert into score5 
partition (month='201807') 
values ('001','002','003');

#通过load方式从Linux文件系统加载数据  ==>必须掌握
load data local inpath '/export/temp/score.csv' 
into table score5 
partition (month='201806');

#先往hdfs上上传文件
hdfs dfs -put /export/temp/score.csv /temp/
#通过load方式从hdfs文件系统加载数据, overwrite表示覆盖之前数据
load data inpath '/temp/score.csv' 
overwrite into table score5 
partition (month='201805');

#通过查询方式加载数据: 将查询score的结果作为输入添加到score6表中  ==> 必须掌握
insert overwrite table score6 
partition (month='201804') 
select s_id,c_id,s_score from score;

⑦其他表操作

#清空管理表数据  ==>清空外部表会报错
truncate table score6;

#将查询score表结果保存到一张新表当中去
create table score7 as select * from score;

#将查询结果导出本地Linux文件系统
insert overwrite local directory '/export/export_hive/' 
select * from score;

#hive中可执行dfs命令, 比如
dfs -mkdir -p /temp/export/;

=====================================下面的看一下了解即可================================

#export导出hive表数据
export table teacher to '/temp/export/';

#import导入hive表数据
create table teacher2 like teacher;
import table teacher2 from '/temp/export/';

# 了解: 将查询的结果导出到HDFS上(这种方式不能往Linux上导出)
insert overwrite directory '/temp/exporthive/' 
row format delimited fields terminated by '\t' 
collection items terminated by '#' 
select * from score;

#还可使用Sqoop导出

二.hive的DML查询语法

基础的语法就不做演示了

1.limit语句

#查询会返回多行数据, LIMIT子句用于限制返回的行数
select * from score limit 3;

2.条件查询

#查询分数在[80,100]的所有数据
select * from score where s_score between 80 and 100;

#先往表score中添加一行分数为null的数据
insert into score partition(month='201807') values ('003','004',null);

#查询成绩为空的所有数据
select * from score where s_score is null;

#查询成绩是80,90的数据
select * from score where s_score in (80,90);

#查找以8开头的所有成绩数据
select * from score where s_score like '8%';

#查询第二个数字为9分所有成绩数据
select * from score where s_score like '_9%';

#查询成绩中包含数字9的成绩数据(rlike 是Hive中一个扩展功能，
#可以通过Java的正则表达式来匹配条件)
select * from score where s_score rlike '[9]';

#查询s_id  不是 01和02的学生  ==>  not in 必须搭配使用
select * from score where s_id not in ('01','02');

3.分组查询

注意:
1.having只能用于 group by 分组统计语句中;
2.where针对查询前的表进行条件筛选, having是对分组查询后的结果在进行条件筛选;
3.where后面不能写分组函数，而having后面可以使用分组函数。

#计算每个学生的平均分数
select s_id, avg(s_score) as avg_score from score group by s_id;

# 求每个学生平均分数大于50的且学号不为01,001的人
select s_id , avg(s_score) avg_score 
from score 
where s_id not in('01','001') 
group by s_id having avg_score > 50;

4.Join查询

案例: 查询老师对应的课程

#准备 (向两表中各添加一条数据, 这样可以看出来三种join之间的区别)
insert into course values ('04','大数据','04');
insert into teacher values ('07','田七');

#内连接: Inner join ... on ==> 取两表之间的交集
select * from teacher t inner join course c on t.t_id = c.t_id;

#左外连接: left [outer] join ... on  ==>  以左边的表为主表
select * from teacher t 
left join course c 
on t.t_id = c.c_id;

#右外连接: right [outer] join ... on  ==> 以右边的表为主表
select * from teacher t 
right outer join course c 
on t.t_id = c.t_id;

#满外连接: full [outer] join ... on ==>  去两表之间的并集
select * from teacher t 
full join course c 
on t.t_id = c.c_id;

多表连接: 连接n个表, 至少需要n-1个条件

#查询老师对应的课程，以及对应的分数，对应的学生
select * from teacher t
left join course c
on t.t_id = c.t_id
left join score s
on c.c_id = s.c_id
left join student stu
on s.s_id = stu.s_id;

5.排序查询

①Order By

全局排序，由一个reduce来完成排序任务。

#DESC降序: 查询学生的成绩，并按照分数降序排列
select * from student stu 
left join score s 
on stu.s_id = s.s_id 
order by s.s_score DESC;

#ASC升序: 查询平均分小于80且姓名不为李云的学生, 升序排列
select stu.s_id, stu.s_name, avg(s.s_score) avg_score 
from student stu 
left join score s 
on stu.s_id = s.s_id and stu.s_name not in ('李云') 
group by stu.s_id, stu.s_name 
having avg_score <80 
order by avg_score ASC;

#把平均成绩大于30的学生, 按平均分降序排序 ==>  聚合函数后排序
select stu.s_id, stu.s_name, avg(s.s_score) avg_score 
from student stu 
left join score s 
on stu.s_id = s.s_id 
group by stu.s_id, stu.s_name 
having avg_score >30 
order by avg_score DESC;

#先按照学生id排序, 再按照成绩排序
select s.s_id, s.s_score 
from score s 
order by s.s_id, s.s_score;

②Sort By

局部排序, 由多个reduce的内部进行排序，对全局结果集来说不是排序。

#准备
set mapreduce.job.reduces=3;		#设置reduce个数

#按照成绩进行局部倒序排序
select * from score sort by s_score desc;

#将上面的结果导出, 会发现输出了3个文件
insert overwrite local directory '/export/hivedatas/' 
select * from score 
sort by s_score desc;

③DIstribute By

分区排序, 类似MapReduce中的partition步骤，必须结合sort by使用。

#案例: 先按照学生id进行分区，再按照学生成绩进行排序。
set mapreduce.job.reduces=7;		#一共七个id

#查询结果保存到本地 发现输出了7个文件
insert overwrite local directory '/export/hive_date2/' 
select * from score 
distribute by s_id sort by s_id;

④Cluste By

当distribute by和sort by字段相同时，可用cluster by替代, 只能是默认的倒序。

#以下两种写法等价
select * from score cluster by s_id;

select * from score distribute by s_id sort by s_id;

Hive SQL的一些基础语法就先总结到这里, 下篇将对Hive当中常用的函数进行总结