Hive学习总结-基础篇-Hadoop MapReduce

Hive学习总结

跟学尚硅谷Hive，自我总结

第 1 章 Hive 基本概念

优缺点、架构（驱动器部分）、与数据库比较

1） hive 简介

Hive：由 Facebook 开源用于解决海量结构化日志的数据统计工具。 Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并 提供类 SQL 查询功能。

2） Hive 本质：将 HQL 转化成 MapReduce 程序

（1）Hive 处理的数据存储在 HDFS

（2）Hive 分析数据底层的实现是 MapReduce

（3）执行程序运行在 Yarn 上

3）Hive 的优缺点

----------优点 ----------

（1）操作接口采用类 SQL 语法，提供快速开发的能力（简单、容易上手）

（2）避免了去写 MapReduce，减少开发人员的学习成本。

（3）Hive 的执行延迟比较高，因此 Hive 常用于数据分析，对实时性要求不高的场合。

（4）Hive 优势在于处理大数据，对于处理小数据没有优势，因为 Hive 的执行延迟比较 高。

（5）Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

----------缺点 ----------

1）Hive 的 HQL 表达能力有限

（1）迭代式算法无法表达 （2）数据挖掘方面不擅长，由于 MapReduce 数据处理流程的限制，效率更高的算法却 无法实现。

2）Hive 的效率比较低

（1）Hive 自动生成的 MapReduce 作业，通常情况下不够智能化 （2）Hive 调优比较困难，粒度较粗

驱动器：Driver

（1）解析器（SQL Parser）：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用第 三方工具库完成，比如 antlr；对 AST 进行语法分析，比如表是否存在、字段是否存在、SQL 语义是否有误。

（2）编译器（Physical Plan）：将 AST 编译生成逻辑执行计划。

（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。

（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来 说，就是 MR/Spark。

Hive 和数据库比较

由于 Hive 采用了类似 SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。

其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。

数据库可以用在 Online 的应用中，但是 Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性。

----------查询语言 ----------

专门针对 Hive 的特性设计了类 SQL 的查询语言 HQL。熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。

----------数据更新 ----------

由于 Hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive 中不建议对数据的改写，所有的数据都是在加载的时候确定好的。

而sql数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET 修改数据。

----------执行延迟 ----------

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此 在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。

相对的，数据库的执行延迟较低。 当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候， Hive 的并行计算显然能体现出优势。

----------数据规模 ----------

由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

---

第 2 章 Hive 安装

详见尚硅谷Hive

常用交互命令

3）启动hive服务以及hive

[henry@hadoop102 bin]$ myhadoop.sh start
[henry@hadoop102 bin]$ hiveservices.sh start
[henry@hadoop102 hive]$ bin/hive

2）“-e”不进入 hive 的交互窗口执行 sql 语句

[henry@hadoop102 hive]$ bin/hive -e "select id from student;"

3）“-f”执行脚本中 sql 语句

#（1）在/opt/module/hive/下创建 datas 目录并在 datas 目录下创建 hivef.sql 文件 
[henry@hadoop102 datas]$ touch hivef.sql 
#（2）文件中写入正确的 sql 语句
select *from student; 
#（3）执行文件中的 sql 语句
[henry@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql 
#（4）执行文件中的 sql 语句并将结果写入文件中
[henry@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql >/opt/module/datas/hive_result.txt

第 3 章 Hive 数据类型

---

第 4 章 DDL 数据定义

4.1-4.4 数据库的增删改查

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name 
[COMMENT database_comment] #注释
[LOCATION hdfs_path] #HDFS上的存放路径
[WITH DBPROPERTIES (property_name=property_value, ...)];#分区

查询数据库

hive> show databases like 'db_hive*';#过滤显示查询的数据库
hive> desc database extended db_hive;#显示数据库详细信息，extended
hive (default)> use db_hive;#切换当前数据库

修改数据库

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');#更改数据库的DBPROPERTIES 设置键-值对属性值

删除数据库

hive> drop database db_hive2;#删除空数据库
hive> drop database if exists db_hive2;#if exists 判断数据库是否存在
hive> drop database db_hive cascade;#如果数据库不为空，可以采用 cascade 命令，强制删除

4.5 创建表（重点）

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] #指定存储文件类型 SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
[LOCATION hdfs_path] #指定表在 HDFS 上的存储位置
[TBLPROPERTIES (property_name=property_value, ...)] 
[AS select_statement]#后跟查询语句，根据查询结果创建表

-----------------------------------常用字段说明-----------------------------------

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常； 用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（3）PARTITIONED BY 创建分区表

（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 确定表 的具体的列的数据。

SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。

（5）LOCATION ：指定表在 HDFS 上的存储位置。

（6）AS：后跟查询语句，根据查询结果创建表。

--------------------------------------示例-----------------------------------

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

----------------------------------------管理表与外部表的互相转换-----------------------------------

hive (default)> desc formatted student2;#查询表的类型
alter table student2 set tblproperties('EXTERNAL'='TRUE');#修改内部表 student2 为外部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');#修改外部表 student2 为内部表

4.6-4.7 表的增删改查

ALTER TABLE table_name RENAME TO new_table_name#【语法】
hive (default)> alter table dept_partition2 rename to dept_partition3;#重命名表
#------------------------------------------------增加/修改/替换列信息------------------------------------------------
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]#【更新列语法】
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)#【增加和替换列语法】
hive> desc dept;#查询表结构
hive (default)> alter table dept add columns(deptdesc string);#添加列
hive (default)> alter table dept change column deptdesc desc string;#更新列
hive (default)> alter table dept replace columns(deptno string, dname string, loc string);#替换列

第 5 章 DML数据操作

5.1 数据导入

#------------------------------------------------【常用】load------------------------------------------------
hive> load data [local] inpath '数据的 path' [overwrite] into table student [partition (partcol1=val1,…)];
# local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表; inpath:表示加载数据的路径; overwrite:表示覆盖表中已有数据，否则表示追加
#示例
hive (default)> load data local inpath '/opt/module/hive/datas/student.txt' into table default.student;
hive (default)> load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;
#-------------------------------------通过查询语句向表中插入数据（Insert）--------------------------------
hive (default)> insert into table student_par values(1,'wangwu'),(2,'zhaoliu');#insert into：以追加数据的方式插入到表或分区，原有数据不会删除
hive (default)> insert overwrite table student_par 
select id, name from student where month='201709';#insert overwrite：会覆盖表中已存在的数据
#-------------------------------------查询语句中创建表并加载数据（As Select）--------------------------------
create table if not exists student3 
as select id, name from student;
#-------------------------------------创建表时通过 Location 指定加载数据路径--------------------------------
hive (default)> create external table if not exists student5( id int, name string )row format delimited fields terminated by '\t' location '/student;
#-------------------------------------Import 数据到指定 Hive 表中--------------------------------
hive (default)> import table student2 from '/user/hive/warehouse/export/student';

5.2 数据导出

#------------------------------------------------Insert 导出------------------------------------------------
hive(default)>insert overwrite local directory'/opt/module/hive/data/export/student1' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;# 将查询的结果格式化导出到本地
hive (default)> insert overwrite directory '/user/atguigu/student2' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;# 将查询的结果导出到 HDFS 上(没有 local)
#-------------------------------------Hadoop 命令导出到本地--------------------------------
hive (default)> dfs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt;
#-------------------------------------Hive Shell 命令导出--------------------------------
[henry@hadoop102 hive]$ bin/hive -e 'select * from default.student;' > /opt/module/hive/data/export/student4.txt;
#-------------------------------------Export 导出到HDFS 上--------------------------------
(defahiveult)> export table default.student to '/user/hive/warehouse/export/student';
#-------------------------------------Sqoop 导出--------------------------------
#-------------------------------------清除表中数据（Truncate）--------------------------------
hive (default)> truncate table student;

第 6 章 查询（常用重点）

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference 
[WHERE where_condition] 
[GROUP BY col_list] 
[ORDER BY col_list] 
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list] ] 
[LIMIT number]

6.1 基本查询（Select…From）

#创建部门表
create table if not exists dept
( deptno int, dname string, loc int )
row format delimited fields terminated by '\t';
#创建员工表
create table if not exists emp
( empno int, ename string, job string, mgr int,hiredate string, sal double, comm double, deptno int)
row format delimited fields terminated by '\t';
#导入数据
load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;
#--------------------------------------以上为练习常用的【先创表后load】的数据准备流程------------------------------------------------------
#全表查询
hive (default)> select * from emp;
#特定列查询
hive (default)> select empno, ename from emp;
#可用'AS'或 ' '设置别名
hive (default)> select empno as no, ename name from emp;

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

--------------------------------------------------------------------------------以下皆为与mysql相同的语法------------------------------------------------------------------------------

#常用函数
hive (default)> select count(*) cnt/ max(sal) max_sal/ min(sal) min_sal/ avg(sal) avg_sal/ sum(sal) sum_sal from emp;
# limit
hive (default)> select * from emp limit 5;
# where
hive (default)> select * from emp where sal >1000;
# between
hive (default)> select * from emp where sal between 500 and 1000;
# in
hive (default)> select * from emp where sal IN (1500, 5000);
# like--查找名字以 A 开头的员工信息
hive (default)> select * from emp where ename LIKE 'A%';
# And/Or/Not
hive (default)> select * from emp where sal>1000 or deptno=30;
# group by----eg.计算 emp每个部门中每个岗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;
# having----eg.求每个部门的平均薪水大于 2000 的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
#（1）where 后面不能写分组函数，而 having 后面可以使用分组函数。 （2）having 只用于 group by 分组统计语句。
#（inner）join、left join、right join
hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
# 多表连接----大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务
hive (default)>SELECT e.ename, d.dname, l.loc_name FROM emp e
JOIN dept d ON d.deptno = e.deptno
JOIN location l ON d.loc = l.loc;
# order by----默认升序，desc降序
hive (default)> select * from emp order by sal desc;

6.2 hive中的四种排序

1）Order By：全局排序，只有一个 Reducer；但对于大规模的数据集 order by 的效率非常低。

2）Sort By：在很多情况下，并不需要全局排序，此时可以使用 sort by。 Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。

hive (default)> set mapreduce.job.reduces=3; #设置 reduce 个数
hive (default)> set mapreduce.job.reduces;#查看设置 reduce 个数
hive (default)> select * from emp sort by deptno desc;#sort by
hive (default)> insert overwrite local directory '/opt/module/data/sortby-result' select * from emp sort by deptno desc;

3）Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。

distribute by 类似MR中 partition （自定义分区），进行分区，结合 sort by 使用。

hive (default)> set mapreduce.job.reduces=3; 
hive (default)> insert overwrite local directory '/opt/module/data/distribute-result' 
              > select * from emp distribute by deptno sort by empno desc;

4）Cluster By：当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。 cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。

但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

#-----------以下两种方法等价--------------
hive (default)> select * from emp cluster by deptno; 
hive (default)> select * from emp distribute by deptno sort by deptno;

第 7 章 分区表和分桶表

7.1 分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。

Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。

在查询时通过WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

# 注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列
hive (default)> create table dept_partition( deptno int, dname string, loc string)
partitioned by (day string) #---------------------创建分区表语法------------------
row format delimited fields terminated by '\t';

hive (default)> create table dept_partition2( deptno int, dname string, loc string )
partitioned by (day string, hour string) #---------------------创建二级分区表语法------------------
row format delimited fields terminated by '\t';

# 注意：分区表加载数据时，必须指定分区
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition 
partition(day='20200401');

#分区查询
hive (default)> select * from dept_partition where day='20200401';
#创建分区
hive (default)> alter table dept_partition add partition(day='20200405') partition(day='20200406');
#删除分区
hive (default)> alter table dept_partition drop partition (day='20200404'), partition(day='20200405');

hive> show partitions dept_partition;#查看分区表有多少分区
hive> desc formatted dept_partition;#查看分区表结构

把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

#1.上传数据后修复
hive> msck repair table dept_partition2;
#2.上传数据后添加分区
hive (default)> alter table dept_partition2 add partition(day='201709',hour='14');
#3.创建文件夹后 load 数据到分区
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='15');

动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过， 使用 Hive 的动态分区，需要进行相应的配置(详见尚硅谷hive)

set hive.exec.dynamic.partition.mode = nonstrict; 
hive (default)> insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;

7.2 分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。分桶是将数据集分解成更容易管理的若干部分的另一个技术。

**分桶规则：**根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

分区针对的是数据的存储路径；分桶针对的是数据文件。

create table stu_buck(id int, name string) clustered by(id) into 4 buckets #创建分桶表
row format delimited fields terminated by '\t';

7.3 抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

第 8 章 函数

hive> show functions;#查看系统自带的函数
hive> desc function upper;#显示自带的函数的用法
hive> desc function extended upper;#详细显示自带的函数的用法

空字段赋值、行转列、列转行、窗口函数（开窗函数）、Rank

自定义函数、自定义 UDF函数、自定义 UDTF 函数

详见尚硅谷hive

第 9 章 压缩和存储

文件存储格式

1）行存储

适用于查询满足条件的一整行数据的时候，例如select * ，此时行存储查询的速度更快。

2）列存储

因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

TEXTFILE 和 SEQUENCEFILE 的存储格式都是基于行存储的； ORC 和 PARQUET 是基于列式存储的。

TextFile 格式

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合 Gzip、Bzip2 使用，但使用 Gzip 这种方式，hive 不会对数据进行切分，从而无法对数据进行并行操作。

Orc 格式——先分行，再列储存

Orc (Optimized Row Columnar)是 Hive 0.11 版里引入的新的存储格式。 如下图所示可以看到每个 Orc 文件由 1 个或多个 stripe 组成，每个 stripe 一般为 HDFS的块大小，每一个 stripe 包含多条记录，这些记录按照列进行独立存储，对应到 Parquet 中的 row group 的概念。

1）Index Data：一个轻量级的 index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在 Row Data 中的 offset。

2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个 Stream 来存储。

3）Stripe Footer：存的是各个 Stream 的类型，长度等信息。

每个文件的尾部是一个 PostScript，这里面记录了整个文件的压缩类型以及 FileFooter 的长度信息等—>每个文件有一个 File Footer，这里面存的是每个 Stripe 的行数，每个 Column 的数据类型信息等—>在读取文件时，会 seek 到文件尾部读 PostScript，从里面解析到 File Footer 长度，再读 FileFooter，从里面解析到各个 Stripe 信息，再读各个 Stripe，即从后往前读。

Parquet 格式——先分行，列储存，再分页

Parquet 文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此 Parquet 格式文件是自解析的。

（1）行组(Row Group)：每一个行组包含一定的行数，在一个 HDFS 文件中至少存储一个行组，类似于 orc 的 stripe 的概念。

（2）列块(Column Chunk)：在一个行组中每一列保存在一个列块中，行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的算法进行压缩。

（3）页(Page)：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块的不同页可能使用不同的编码方式。 通常情况下，在存储 Parquet 数据的时候会按照 Block 大小设置行组的大小，由于一般情况下每一个 Mapper 任务处理数据的最小单位是一个 Block，这样可以把每一个行组由一 个Mapper 任务处理，增大任务执行并行度。Parquet 文件的格式。

上图展示了一个 Parquet 文件的内容，一个文件中可以存储多个行组，文件的首位都是 该文件的Magic Code，用于校验它是否是一个 Parquet 文件，Footer length 记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行 组的元数据信息和该文件存储数据的 Schema信息。除了文件中每一个行组的元数据，每一 页的开始都会存储该页的元数据，在 Parquet 中，有三种类型的页：数据页、字典页和索引 页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最 多包含一个字典页，索引页用来存储当前行组下该列的索引，目前 Parquet 中还不支持索引 页。

存储和压缩结合测试详见尚硅谷hive