----紧接day04笔记继续编辑-----
3.2外部表:
外部表说明:
外部表因为是指定其他的hdfs路径的数据加载到表当中来,所以hive表会认为自己不完全独占这份数据,所以删除hive表的时候,数据仍然存放在hdfs当中,不会删掉。
内部表:当删除表的时候,表结构和表数据全部都会删除掉。
外部表:当删除表的时候,认为表的数据会被其他人使用,自己没有独享数据的权利,所以只会删除掉表的结构(元数据),不会删除表的数据。
操作案例:
分别创建老师与学生表外部表,并向表中加载数据
创建老师表:
create external table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';
创建学生表:
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 '/opt/servers/hivedatas/student.csv' into table student;
加载数据并覆盖已有数据
load data local inpath '/opt/servers/hivedatas/student.csv' overwrite into table student;
第二种:从hdfs文件系统向表中加载数据(需要提前将数据上传到hdfs文件系统,其实就是一个移动文件的操作)
cd /opt/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put teacher.csv /hivedatas/
load data inpath '/hivedatas/teacher.csv' into table teacher;
如果删掉student表,hdfs的数据仍然存在,并且重新创建表之后,表中就直接存在数据了,因为我们的student表使用的是外部表,drop table之后,表当中的数据依然保留在hdfs上面了。
新开窗口上传数据
第一种方式:从本地文件向表中加载数据
测试,可以不用操作下面这个:
注意:如果第一种的load 的方式加载数据的话,它不会检测数据是否重复,而是直接又加载进表中,而且会在数据库中复制一张同样的表出来
所以,如果我们想覆盖掉之前的数据时,就要在加载数据的命令中添加一个关键字 overwrite
第二种方式从hdfs文件系统向表中加载数据(需要提前将数据上传到hdfs文件系统,其实就是一个移动文件的操作)--上传teacher数据
3.3分区表
在大数据中,最常用的一种思想就是分治,我们可以把大的文件切割划分成一个个的小的文件,这样每次操作一个小的文件就会很容易了,同样的道理,在hive当中也是支持这种思想的,就是我们可以把大的数据,按照每天,或者每小时进行切分成一个个的小的文件,这样去操作小的文件就会容易得多了
- 创建分区表语法
create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';
- 创建一个表带多个分区
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';
- 加载数据到分区表中
load data local inpath '/opt/servers/hivedatas/score.csv' into table score partition (month='201806');
- 加载数据到一个多分区的表中去
load data local inpath '/opt/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');
- 查看分区
show partitions score;
- 添加一个分区
alter table score add partition(month='201805');
同时添加多个分区
alter table score add partition(month='201804') partition(month = '201803');
注意:添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹
- 删除分区
alter table score drop partition(month = '201806');
正式操作:
创建分区表语法:
创建一个表带多个分区:
1) 加载数据到一个分区表中:一定要指明哪一个分区
hdfs查看加载上传上去的数据:
2) 加载数据到一个多分区的表中去
查看指定表的分区 :
指定表添加一个分区:
未加载数据的分区,是查不到的分区的:
删除分区:
3.4分桶表-分的是文件,有多少个文件就要多少桶
将数据按照指定的字段进行分成多个桶中去,说白了就是将数据按照字段进行划分,可以将数据按照字段划分到多个文件当中去
开启hive的桶表功能
set hive.enforce.bucketing=true;
设置reduce的个数:几个同设置几个reduce
set mapreduce.job.reduces=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';
桶表的数据加载,由于桶表的数据加载通过hdfs dfs -put文件或者通过load data均不好使,只能通过insert overwrite
创建普通表,并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去
创建普通表:
create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
普通表中加载数据
load data local inpath '/opt/servers/hivedatas/course.csv' into table course_common;
通过insert overwrite给桶表中加载数据
insert overwrite table course select * from course_common cluster by(c_id);
按百分比取样:整表数据的百分之三十
select * from course tablesample(30 percent);
注意:三个桶你也不知道到底文件会放到哪个桶内,所以先创建普通表,并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去
下载查看:
4.hive查询方法-与mysql的sql语句相似
4.1select
全表查询
select * from score;
选择特定列查询
select s_id ,c_id from score;
列别名
1)重命名一个列。
2)便于计算。
3)紧跟列名,也可以在列名和别名之间加入关键字‘AS’
select s_id as myid ,c_id from score;
4.2常用函数
1)求总行数(count)
select count(1) from score;
2)求分数的最大值(max)
select max(s_score) from score;
3)求分数的最小值(min)
select min(s_score) from score;
4)求分数的总和(sum)
select sum(s_score) from score;
5)求分数的平均值(avg)
select avg(s_score) from score;
4.3、LIMIT语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select * from score limit 3;
4.4、WHERE语句
select * from score where s_score > 60;
4.5、分组
GROUP BY语句
GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。
案例实操:
(1)计算每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;
(2)计算每个学生最高成绩
select s_id ,max(s_score) from score group by s_id;
HAVING语句
1)having与where不同点
(1)where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。
(2)where后面不能写分组函数,而having后面可以使用分组函数。
(3)having只用于group by分组统计语句。
2)案例实操:
求每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;
求每个学生平均分数大于85的人
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
5.hive的FAQ
所有的离线数据处理场景都适用hive吗?
并不是所有场景都适合,逻辑简单又要求快速出结果的场景Hive优势更大。但是在业务逻辑非常复杂的情况下还是需要开发MapReduce程序更加直接有效。hsql只能解决大部分的相关问题,但是特别复杂的不行.
Hive能作为业务系统的数据库使用吗?
不能。传统数据库要求能够为系统提供实时的增删改查,而Hive不支持行级别的增删改,查询的速度也不比传统关系型数据库,而是胜在吞吐量高,所以不能作为关系型数据库来使用。
Hive与传统MR方式处理数据相比能够提高运行效率吗?
Hive的使用中需要将HQL编译为MR来运行,所以在执行效率上要低于直接运行MR程序。但是对于我们来说,由于只需要编写调试HQL,而不用开发调试复杂的MR程序,所以工作效率能够大大提高。因为hive执行时的hsql语句会先翻译成mapreduce,再执行.
Hive为什么不支持行级别的增删改?
Hive不支持行级别的增删改的根本原因在于他的底层HDFS本身不支持。在HDFS中如果对整个文件的某一段或某一行内容进行增删改,势必会影响整个文件在集群中的存放布局。需要对整个集群中的数据进行汇总,重新切块,重新发送数据到每个节点,并备份,这样的情况是得不偿失的。所以HDFS的设计模式使他天生不适合做这个事
6 hive数据库总结
1.hive:基于hadoop的数据仓库的工具
- hive的数据是存储的hdfs上, hsql的执行引擎默认是mapreduce
- hive sql (hsql): 是基于hive的数据操作语言.遵循sql92标准
2.hive的安装部署
1) hive是一个工具,单独部署在一个节点即可
2)基于derby的元数据存储
3)基于mysql的元数据存储
注意:在使用mysql管理元数据时:
- 通过hive-site.xml的配置增加mysql的连接属性;
- 通过schemaTool工具初始化元数据;
- bin/hive启动hive
4)元数据:关于hive表描述的数据,描述表的每个字段及类型
3.表操作
3.1表创建
1) 管理表(内部表)
管理表:hive有全权管理的权力,删除表的时候,包括元数据和表中的数据全部删除掉
create table xx (id string,name string) location '文件目录'row format delimited fields terminated by '\t';
2)外部表-关键字external
外部表:删除表时,会保留数据,只删除元数据,下次再创建同名表,数据自动加载进该表
create external table xx (id string,name string) location '文件目录'row format delimited fields terminated by '\t';
3)分区表
分而治之:解决大量数据中检索慢的问题;
分区表分的是文件的目录;
create table xx (id string,name string) partitioned by(month='xxx') row format delimited fields terminated by '\t';
加载数据:
#本地数据加载
load data local inpath 'xxx' into table xxx;
#hdfs加载数据
load data local 'hdfs://hadoop01:8020/xxxx' into table xxx;
在分区表加载数据的时候,必须制定哪一个分区
load data local inpath 'xxx' table xxx partition(month='202110');
4) 分桶表
分桶表: 分的是文件,往往用于数据的采样;
#开启分桶
set hive.enforce.bucketing=true;
#设置reduce的个数(个数和分桶的个数保持一致)
set mapreduce.job.reduces=3;
#创建桶表
create table xxx(id string,name string) clustered by(id) into 3 buckets row format delimited fields terminated by '\t';
注意: 数据不能直接导入到分桶表,需要使用insert overwrite的方式将数据从中间表导入到分桶表
3.2 表查询