Hive基础理解

Hive基础理解 Hive基础

Hive是Hadoop生态系统中必不可少的一个工具。它可以将存储在HDFS中的结构化数据映射为数据库中的一张表，并提供了一种SQL方言对其进行查询。这些SQL语句最终会翻译成MapReduce程序执行。Hive的本质就是为了简化用户编写MapReduce程序而生成的一种框架，它本身并不会存储和计算数据，完全依赖于HDFS和MapReduce。

Hive架构

显示了Hive的主要模块以及Hive是如何和Hadoop进行交互工作的。
Hive中附带的客户端有三种：CLI（命令行客户端）、HWI（网页客户端）和Thrift Server（编程客户端）
Hive中附带的客户端有三种：CLI（命令行客户端）、HWI（网页客户端）和Thrift Server（编程客户端）

Hive基础命令

1.创建数据库：
CREATE DATAbase school;
2.如果数据库school已经存在的话，那么会抛出一个错误信息。使用以下语句可以避免这种情况下抛出错误信息：
CREATE DATAbase IF NOT EXISTS school;
3.查看Hive中所包含的数据库：
SHOW DATAbaseS;
4.将某个数据库设置为用户当前的工作数据库
USE school;
5.删除数据库：
DROp DATAbase IF EXISTS school CASCADE;
6.创建表并定义表的数据文件存储在什么位置、使用什么样的分隔符
CREATE TABLE IF NOT EXISTS teacher( id INT COMMENT '编号', name STRING COMMENT '名字' ) COMMENT '教师表' ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ';

增加上IF NOT EXISTS选项，若表已经存在了，Hive就会忽略掉后边的执行语句，而且不会有任何提示。

7.装载数据
LOAD DATA INPATH '/hive/teacher' INTO TABLE teacher;

这句话的意思就是，把hdfs上/hive/teacher/teacher.txt文件中的数据装载进teacher表

LOAD DATA LOCAL INPATH '/home/hadoop/hive/teacher.txt' INTO TABLE teacher;

在LOAD后添加LOCAL关键字，那么这个路径就是本地（Linux）文件系统路径，文件将会被上传到表目录中。

LOAD DATA LOCAL INPATH '/home/hadoop/hive/teacher.txt' OVERWRITE INTO TABLE teacher;

在INTO后添加OVERWRITE关键字，那么表目录中的数据会先被删除。也就是说没有OVERWRITE时装载数据是进行追加 *** 作，有OVERWRITE时装载数据时进行覆盖 *** 作。

8.外部表内部表
-------------------------外部表，内部表
–teacher 叫做内部表或者管理表
–内部表控制着数据的生命周期，当内部表删除的时候，目录和其他的文件也会被删除

–如果需要和其他系统共享数据，可以创建外部表
–EXTERNAL 外部表的关键字，外部表不会在school.db 目录下创建子目录
–LOCATION 设置外部表数据文件的位置，外部表不需要导入数据
–LOCATION 需要指定为数据文件所在的目录，会把目录中的所有文件加载到表中

CREATE EXTERNAL TABLE IF NOT EXISTS score (
    id          INT     COMMENT '编号',
    student_id  INT     COMMENT '学生编号',
    course_name STRING  COMMENT '课程名',
    score       INT     COMMENT '成绩',
    teacher_id  INT     COMMENT '教师编号'
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LOCATION '/hive/score';

LOAD DATA local INPATH '/home/hadoop/hive/score/score.txt' INTO TABLE score;
SELECT * FROM score;

–内部表：管理数据，创建目录，剪切文件，随着表的删除，表结构，目录和数据文件都删除
–外部表：共享数据，不创建目录，不 *** 作文件，随着表的删除，只删除表结构，不删除数据文件
–在查询 *** 作上，内部表和外部表没有任何区别
-------------------------------------------------分区表
–分区表，把表中的多个数据文件按照分区字段的值进行组织和管理
–一个分区字段的值就对应一个目录

CREATE TABLE IF NOT EXISTS student(
    id          INT     COMMENT '编号',
    name        STRING  COMMENT '姓名',
    age         INT     COMMENT '年龄',
    birthday    STRING  COMMENT '生日',
    gender      INT     COMMENT '性别'
)PARTITIONED BY (college STRING, clazz STRING) 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '

–分区字段也是表中的列，叫做虚拟列(虚拟字段)
–因为他们的值不在数据文件中，需要在导入数据的时候手动添加

LOAD DATA INPATH '/usr/hive/student/student_cs_a1.txt' INTO TABLE student PARTITION (college='cs', clazz='a1');
LOAD DATA INPATH '/usr/hive/student/student_cs_a2.txt' INTO TABLE student PARTITION (college='cs', clazz='a2');
LOAD DATA INPATH '/usr/hive/student/student_ai_b1.txt' INTO TABLE student PARTITION (college='ai', clazz='b1');

--LOAD DATA LOCAL INPATH '/home/hadoop/hive/student/studednt_ai_b1.txt' INTO TABLE student PARTITION (college='ai, clazz='b1')
SELECt * FROM student;

----------------------------------------聚合函数
– 查询每个学院的学习年龄的总和，最大值，最小值，平均值

SELECt SUM(age), MAX(age), MIN(age), AVG(age) FROM student GROUP BY college;
SELECt SUM(age), MAX(age), MIN(age), AVG(age) FROM student GROUP BY clazz;

–查询学生总人数大于2的学院3

SELECt college, COUNT(*) FROM student GROUP BY college HAVINg COUNT(*) > 2; 

-----------------------------------------排序
–查询所有学生信息并按照年龄升序排序
–ORDER BY:全局排序，只使用一个reduce，全局有序，但是比较慢
–默认升序（ASC），也可以指定降序（DESC）

SELECt  * FROM student 
WHERe age>19
ORDER BY age;

SELECt * FROM student
WHERe age>10
ORDER BY age DESC;

–SORT BY 可以使用多个reduce，每个reduce输出的数据有序，全局无序

SELECt * FROM student SORT BY age;

-------------------------------------------------子查询
–子查询是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而完成复杂查询的便携形式
–Hive 只支持WHERe 和FROM 子句中的子查询
–并且WHERe 子查询只支持IN，NOT IN，EXISTS 和NOT EXTSTS 两种情况

–查询年龄大小20的学生平均年龄

SELECt AVG(age)
FROM (
	SELECt age
	FROM student
	WHERe age > 20
)a;

–查询zhangsan老师教授的课程名称

SELECt a.course_name
FROM score a
WHERe EXISTS (  --EXISTS如果子句存在数据就执行外层，不存在就不执行
	SELECt b.name
	FROM teacher b
	WHERe b.name = 'zhangsan' AND b.id =a.teacher_id 
);

------------------------------------------------------连接查询
–内连接(INNER JOIN)中，只有进行连接的两个表中都存在与连接标准相匹配的数据才会保留下来
–ON子句指定了两个表间数据进行链接的条件
–查询每个学生的编号，名字，课程和成绩

SELECt a.id, a.name, b.course_name, b.score
FROM student a INNER JOIN score b
ON a.id = b.student_id;

–左外连接（LEFT JOIN），JOIN *** 作符左表中符合条件的所有记录将会被返回
–JOIN *** 作符右边表如果没有符合ON后面连接条件的记录时，那么从右边表指定选择的列的值将是NULL
–查询每个教师的编号，名字，课程

SELECt a.id , a.name , b.course_name
FROM teacher a LEFT JOIN score b
ON a.id = b.teacher_id;

–右外连接RIGHT OUTER JOIN(RIGHT JOIN) 会返回右表所有符合条件的记录。左表中匹配不上的字段值用null代替
–查询每个教师的编号，名字，课程

SELECt b.id , b.name , a.course_name
FROM score a RIGHT JOIN teacher b
ON a.teacher_id = b.id;