HIVE数据仓库工具

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

HIVE数据仓库工具

---

前言

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

---

数据仓库是面向主题的，集成的，相对稳定的，反应历史变化的数据集合，主要用于管理决策。

HIVE查询工具

1.HIVE和RDBMS对比

相同点
查询语言相似
不同点
（1）hive支持海量数据存储，RDBMS处理有限数据集
（2）执行引擎不同
（3）数据存储位置不同
（4）hive执行速度相对较慢
（5）hive支持水平扩展
（6）hive对数据更新不友好，针对读多写少情况。

2.读时模式与写时模式

（1）数据在写入 数据库时对照表模式进行检查，这种模式称为"写时模式"（schema on write）。
（2）加载数据时不进行数据格 式的校验，读取数据时如果不合法则显示NULL。称为写时模式。

3.HQL操作

1.DDL命令（data definition language）

        主要对象：数据库、表
    	内部表：删除表时，同时删除元数据和表数据 					   	      		 
    	外部表：删除表时，仅删除元数据，保留表中数据；生产环境多使用外部表 
    	分区表：按照分区字段将表中的数据放置在不同的目录中，提高SQL查询的 性能 
    	分桶表：按照分桶字段，将表中数据分开。 分桶字段.hashCode % 分桶数 据 主要命令：create、alter 、drop

2.DML（Data Manipulation Language）

数据导入：load data / insert / create table .... as select ..... / import table
数据导出：insert overwrite ... diretory ... / hdfs dfs -get / hive -e "select ..." > a.log / export table ... 	
Hive的数据导入与导出还可以使用其他工具：Sqoop、DataX等；

3.DQL（ Data Query Language）

where子句针对表中的数据发挥作用,where子句中不能使用列的别名。
having针对查询结果（聚组以后的结果） 发挥作用 where子句不能有分组函数；
having子句可以有分组函数 having只用于group by分组统计之后。
Hive总是按照从左到右的顺序执行，Hive会对每对 JOIN 连接对象启动一个 MapReduce 任务。

4.排序子句

（1）order by 子句出现在select语句的结尾；
order by子句对终的结果进行排序； 默认使用升序(ASC)；可以使用DESC，跟在字段名之后表示		降序；
ORDER BY执行全局排序，只有一个reduce；排序字段要出现在select子句中
（2）sort by为每个reduce产生一个排序文件，在reduce内部进行排序，得到局部有序的 结果；
（3）distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序；distribute by 要写在sort by之前；
（4）当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法； cluster by 只能是升序，不能指定排序规则；

5.函数

日期函数

select curent_data --当前日期
select unix _timestamp --返回毫秒数
select current_timestamp --返回当前日期
select from_unixtime(1567879890,'yyyy-MM-dd HH:mm:ss') --时间戳转换为日期
select unix_timestamp('2019-09-15 14:23:00')  --日期转换为时间戳
select datediff('2020-04-20','2019-5-18')  --计算时间差
select dayodmonth(current_date) --计算当月第几天

字符串函数

--字符串拼接  ||  concat
select empno || " " ename idname from emp;
select concat(empno," ",ename) idname from emp;
--指定分隔符 concat_ws()
select concat_ws('.','www',array('baidu',com))
select concat_ws(" ",ename,job) from emp;
--求字串
select substr('www.lagou.com',6)	

数学函数

select round(314.15926,2) --四舍五入
select ceil (3.1415926)   --向上取整
select floor(3.1415926)   --向下取整

条件函数

select sal，if(sal<5000,1,if(sal<10000,2,3)) from emp;
select sal,case when sal<=5000 then 1
						when sal<=10000 then 2
						else 3 end sallevel
from emp;
-- isnull(a) isnotnull(a)
 select * from emp where isnull(comm); 
 select * from 	emp where isnotnull(comm);
 -- nvl(T value, T default_value) 
 select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal  from emp;

UDTF函数

 User Defined Table-Generating Functions。用户定义表生成函数，一行输 入，多行输出。

窗口函数
窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强 大的函数，很多场景都需要用到。窗口函数用于计算基于组的某种聚合值，它和聚合 函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

元数据的管理与存储

Metastore

在Hive的具体使用中，首先面临的问题便是如何定义表结构信息，跟结构化的数据映 射成功。所谓的映射指的是一种对应关系。在Hive中需要描述清楚表跟文件之间的映 射关系、列和字段之间的关系等等信息。这些描述映射关系的数据的称之为Hive的元数据。

数据存储格式

Hive支持的存储数的格式主要有：TEXTFILE（默认格式） 、SEQUENCEFILE、 RCFILE、	ORCFILE、PARQUET

行存储与列存储

行式存储下一张表的数据都是放在一起的，但列式存储下数据被分开保存了。
行式存储：
优点：数据被保存在一起，insert和update更加容易
缺点：选择（selection）时即使只涉及某几列，	所有数据也都会被读取
列式存储：
优点：查询时只有涉及到的列会被读取，效率高
缺点：选中的列要重新组装，insert/update比较麻烦

1.三种配置模式

内嵌模式

内嵌模式使用的是内嵌的Derby数据库来存储元数据，也不需要额外起Metastore服 务。数据库和Metastore服务都嵌入在主Hive Server进程中。

本地模式

本地模式采用外部数据库来存储元数据

远程模式

远程模式下，需要单独起metastore服务，然后每个客户端都在配置文件里配置连接 到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。在生 产环境中，建议用远程模式来配置Hive Metastore。

2.HiveServer

HiveServer2是一个服务端接口，使远程客户端可以执行对Hive的查询并返回结果。 

HIVE调优策略

Hive作为大数据领域常用的数据仓库组件，在设计和开发阶段需要注意效率。
影响Hive效率的不仅仅是数据量过大；数据倾斜、数据冗余、job或I/O过多、 MapReduce分配不合理等因素都对Hive的效率有影响。 对Hive的调优既包含对HiveQL语句本身的优化，也包含Hive配置项和MR方面的调 整。

1.架构优化

执行引擎：Hive支持多种执行引擎，分别是 MapReduce、Tez、Spark、Flink。可以通过hivesite.xml文件中的hive.execution.engine属性控制。
分区表：对于一张比较大的表，将其设计成分区表可以提升查询的性能，对于一个特定分区的 查询，只会加载对应分区路径的文件数据，所以执行速度会比较快。
分桶表：与分区表类似，分桶表的组织方式是将HDFS上的文件分割成多个文件。分桶可以加快数据采样，也可以提升join的性能(join的字段是分桶字段)，因为分桶可 以确保某个key对应的数据在一个特定的桶内(文件)。
文件格式：在HiveQL的create table语句中，可以使用stored as ...指定表的存储格式。 
数据压缩：压缩技术可以减少map与reduce之间的数据传输，从而可以提升查询性能，关于压 缩的配置可以在hive的命令行中或者hive-site.xml文件中进行配置。

2.参数优化

本地模式：当Hive处理的数据量较小时，启动分布式去处理数据会有点浪费，因为可能启动的时 间比数据处理的时间还要长。Hive支持将作业动态地转为本地模式。
严格模式：是强制不允许用户执行3种有风险的HiveQL语句，一旦执行会直接 失败。这3种语句是：
查询分区表时不限定分区列的语句； 
两表join产生了笛卡尔积的语句；
用order by来排序，但没有指定limit的语句。
JVM重用:Hadoop会为为一个map或者reduce启动一个JVM，这样可以并行执行 map和reduce。
并行执行 :Hive的查询通常会被转换成一系列的stage，这些stage之间并不是一直相互依赖的， 可以并行执行这些stage.
推测执行：Hadoop采用了推测执行机制，它根据一定的规则推测出 “拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理 同一份数据，并终选用先成功运行完成任务的计算结果作为终结果
合并小文件:在map执行前合并小文件，减少map数.在Map-Reduce的任务结束时合并小文件.
Fetch模式：可以简单地读取表对应的存储目录下的文件，然后输出查询结果到控制台。在开启 fetch模式之后，在全局查找、字段查找、limit查找等都不启动 MapReduce 。

3.SQL优化

列裁剪和分区裁剪：列裁剪是在查询时只读取需要的列；分区裁剪就是只读取需要的分区。
group by 代替 count(distinct）
group by 配置调整。
对于小文件采用的策略是合并。
对于复杂文件采用的策略是增加 Map 数。