六阶段大数据--day05--创建数据库与创建数据库表 hive查询方法 hive的FAQ

六阶段大数据--day05--创建数据库与创建数据库表/ hive查询方法/ hive的FAQ ----紧接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的执行引擎默认是mapreducehive 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 表查询