Hive总结

Hive总结 Hive：

Hive是Hadoop生态系统中必不可少的一个工具。它可以将存储在HDFS中的结构化数据映射为数据库中的一张表，并提供了一种SQL方言对其进行查询。

Hive架构

Hive中附带的客户端有三种：CLI（命令行客户端）、HWI（网页客户端）和Thrift Server（编程客户端）。
SQL Parser是解析器，用于将SQL语句转换为抽象语法树。Physical Plan是编译器，用于将抽象语法树编译生成逻辑计划。Query Optimizer是优化器，用于对逻辑执行计划进行优化。Execution是执行器，用于把逻辑执行计划转换为可执行的MapReduce程序。

Hive中的数据库

创建数据

CREATE DATAbase school;

查看Hive中的数据库

SHOW DATAbase;

切换数据库：USE 数据库名
删除数据库

	DROp DATAbase IF EXISTS school CASCADE;

IF EXISTS子句是可选的，如果加了这个子句，就可以避免因数据库school不存在而抛出的警告信息。

Hive中的表

数据类型

数据类型长度例子TINYINT1byte有符号整数10SMALINT2byte有符号整数10INT4byte有符号整数10BIGINT8byte有符号整数10BOOLEAN布尔类型，true或者falseTRUEFLOAT单精度浮点数1.0DOUBLE双 精度浮点数1.0STRING字符串“dzqc”TIMESTAMP日期，时间戳或者字符串1327882394或者’2020-5-20 15:30:20’ARRAY一组有序字段，类型必须相同Array(1,2)MAP一组无序的键值对Map(‘a’,1,’b’,2)STRUCT一组命名的字段，字段类型可以不同Struct(‘a’,1,1,0)

Hive中默认的行和列分隔符

分隔符描述n行分隔符^A列分隔符^BARRAY、MAP、STRUCT中元素之间的分隔符^CMAP中KEY和VALUE之间的分隔符

FIELDS TERMINATED BY ’ ’ 这个子句用于指定列分隔符。COLLECTION TERMINATED BY ’ ’ 这个子句用于指定ARRAR、STRUCT和MAP中元素之间的分隔符。MAP TERMINATED BY ’ ’ 这个子句用于指定MAP之间KEY和VALUE之间的分隔符。

装载数据

– 默认导入的是HDFS数据，导入本地数据需要添加LOCAL关键字
–默认导入的是执行追加 *** 作，如果需要执行覆盖 *** 作，需要添加OVERWRITE关键字

外部表

–内部表控制着数据的生命周期，当内部表删除的时候，目录和其中的文件也会被删除

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

--内部表：管理数据，创建目录，剪切文件，随着表的删除，表结构、目录和数据文件都删除
--外部表：共享数据，不创建目录，不 *** 作文件，随着表的删除，只删除表结构，不删除数据文件
--在查询 *** 作上，内部表和外部表没有任何区别

分区表

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

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');

聚合函数 函数描述count(*)计算总行数，包含NULL值的行sum(col)计算指定行的值和行avg(col)计算指定行的值的平均值min(col)计算指定行的最小值max(col)计算指定行的最大值 排序

–ORDER BY全局排序，只使用一个reduce，全局有序，但比较慢
–默认升序（SAC)也可以指定降序（DESC）

例：查询所有学生信息并按照年龄升序排列：

SELECT * FROM student ORDER BY age;
SELECt * FROM student ORDER BY age ASC;

–SORT BY可以使用多个reduce，每个reduce 输出的数据有序，全局无序
–SORT BY 通常搭配DISTRIBUTE BY 使用
–DISTRIBUTE BY 保证列值相同的数据发送到同一个reduce进行处理
例：按每个学科的按照成绩降序排序：

SELECt * FROM score DISTRIBUTE BY course_name SORT BY score DESC;

子查询

子查询是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而完成复合查询的编写形式
–Hive只支持WHERe和FROM子句中的子查询
–并且WHERe子查询只支持IN,NOT IN,EXISTE和NOT EXISTS两种情况
例：–查询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 
);

行转列

COLLECT_SET或COLLECT_LIST：把聚合后的某列的多个值拼装为数组。COLLECT_SET会去除数组中重复的元素，而COLLECT_LIST不会。
CONCAT_WS：取出字符串数组中每一个元素，按照指定分隔符拼接
CAST：转换数据类型

列转行

split:按照指定分隔符切分字符串

select split(text,' ') from doc;

explode：把集合中的每个元素放入单行

select explode( split(text,' ')) from doc;

rank函数

rank值相同时，名次会重复，总数不变；：1，2，2，2，5
dense_rank值相同时，名次会重复，总数变少；：1，2，2，2，3
row_number值相同时，名词按照顺序不重复，总数不变；：1，2，3，4，5
partition by：按照指定字段进行分组，然后组内数据的排名

连接查询

–内连接(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;