Hive使用方式

使用方式

创建数据库操作

创建数据库

create database if not exists myhive;
use myhive;

说明：hive 的表存放位置模式是由 hive-site.xml 当中的一个属性指定的

hive-site.xml

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

创建数据库并指定 hdfs 存储位置

create database mydb02 location '/myhive22';

修改数据库

可以使用 alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

alter database mydb02 set dbproperties('createtime'='20190708');

查看数据库详细信息

查看数据库基本信息

desc database mydb02;

查看数据库更多详细信息

desc database extended mydb02;

删除数据库

删除一个空数据库，如果数据库下面有数据表，那么就会报错

drop database myhive2;

强制删除数据库，包含数据库下面的表一起删除

drop database mydb02; cascade;

创建数据库表操作

创建数据库表语法

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]
 [STORED AS file_format]
 [LOCATION hdfs_path]

说明：
1、 CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2、 EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3、LIKE 允许用户复制现有的表结构，但是不复制数据。
4、ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
5、 STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。

hive 建表

use mydb01;
create table stu(id int,name string);
insert into stu values (1,"Tom");
select * from stu;

创建表并指定字段之间的分隔符

create table if not exists stu2(id int ,name string) row format delimited fields
terminated by '\t' stored as textfile location '/user/stu2';

根据查询结果创建表

create table stu3 as select * from stu;

根据已经存在的表结构创建表

create table stu4 like stu;

查询表的类型

desc formatted stu2;

操作案例

分别创建老师与学生表表，并向表中加载数据
创建老师表：

create table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';

创建学生表：

create 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/servers/hivedatas/student.csv' into table student;

从 hdfs 文件系统向表中加载数据（需要提前将数据上传到 hdfs 文件系统，其实就是一个移动文件的操作）

cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put /export/servers/hivedatas/teacher.csv /hivedatas/
load data inpath '/hivedatas/teacher.csv' into table teacher;

创建普通表，并向表中加载数据

create table course (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
load data local inpath '/export/servers/hivedatas/course.csv' into table course;
create table score (s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';
load data local inpath '/export/servers/hivedatas/score.csv' into table score;

hive 语句综合操作

1、查询姓氏首字母为”M” 的教练的数量

select count(t_id) from teacher where t_name like 'M%';

2、查询学过”MILLER” 教练授课的同学的信息

select stu.* from student stu,score sc where stu.s_id = sc.s_id and sc.c_id in (select c_id from course c, teacher t where t.t_id = c.t_id and t_name = 'MILLER');

3、查询所有同学的学生编号、学生姓名、选课总数、所有运动的总成绩

select stu.s_id,stu.s_name,count(sc.c_id) sum_course,sum(sc.s_score) sum_score from student stu left join score sc on stu.s_id = sc.s_id group by stu.s_id,stu.s_name;

4、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select stu.s_id,stu.s_name,avg(sc.s_score) avg_score from student stu left join score sc on stu.s_id = sc.s_id group by stu.s_id,stu.s_name having avg_score >= 60;
select stu.s_id,stu.s_name,Round(avg(sc.s_score),2) avg_score from student stu left join score sc on stu.s_id = sc.s_id group by stu.s_id,stu.s_name having Round(avg(sc.s_score),2) >= 60;

5、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

select stu.s_id,stu.s_name,avg(sc.s_score) avg_score from student stu left join score sc on stu.s_id = sc.s_id group by stu.s_id,stu.s_name having avg_score < 60;
select stu.s_id,stu.s_name,0 avg_score from student stu where stu.s_id not in(select distinct sc.s_id from score sc);
select stu.s_id,stu.s_name,avg(sc.s_score) avg_score from student stu left join score sc on stu.s_id = sc.s_id group by stu.s_id,stu.s_name having avg_score < 60 union all select stu.s_id,stu.s_name,0 avg_score from student stu where stu.s_id not in(select distinct sc.s_id from score sc);

6、查询学过编号为”01” 并且也学过编号为”02” 的运动的同学的信息

select stu.* from student stu,score sc,score sc2 where stu.s_id = sc.s_id and stu.s_id = sc2.s_id and sc.c_id = '01'and sc2.c_id = '02';

7、查询男生、女生人数

select count(s_sex='female'),count(s_sex='male') from student;

8、查询不及格的课程，并按课程号从大到小排列

select sc.c_id,sc.s_score from score sc where sc.s_score<60 order by sc.c_id desc;

9、查询课程编号为”01” 且课程成绩在 60 分以上的学生的学号和姓名；

select stu.s_id,stu.s_name,sc.s_score,sc.c_id from score sc,student stu where sc.s_id=stu.s_id and sc.c_id = 01 and sc.s_score>60;

10、查询不及格的课程，并按课程号从大到小排列

select sc.c_id,sc.s_score FROM score sc WHERE sc.s_score<60 order by sc.c_id desc;

Hive 使用方式