Hive知识点总结

什么是Hive

Hive是构建在Hadoop 之上的数据仓库；可以将结构化的数据文件映射成一张表，并提供类SQL查询功能；

使用HQL作为查询接口；
使用HDFS存储；
使用MapReduce计算；

Hive架构

1. 用户接口：Client CLI（command-line interface）、JDBC/ODBC(jdbc访问hive)

2. 元数据：Metastore 元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否 是外部表）、表的数据所在目录等； 默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore。

3. Hadoop 使用HDFS进行存储，使用MapReduce进行计算。

4. 驱动器：Driver

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

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

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

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

HQL转换为MR核心流程

1.进入程序，利用Antlr框架定义HQL的语法规则，对HQL完成词法语法解析，将HQL转换为为AST（抽象语法树）；

2.遍历AST，抽象出查询的基本组成单元QueryBlock（查询块），可以理解为最小的查询执行单元；

3.遍历QueryBlock，将其转换为OperatorTree（操作树，也就是逻辑执行计划），可以理解为不可拆分的一个逻辑执行单元；

4.使用逻辑优化器对OperatorTree（操作树）进行逻辑优化。例如合并不必要的ReduceSinkOperator，减少Shuffle数据量；

5.遍历OperatorTree，转换为TaskTree。也就是翻译为MR任务的流程，将逻辑执行计划转换为物理执行计划；

6.使用物理优化器对TaskTree进行物理优化；

7.生成最终的执行计划，提交任务到Hadoop集群运行。

Hive运行原理

Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据 (MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出 到用户交互接口。 其实，还可以这样理解：Hive要做的就是将SQL翻译成MapReduce程序代码。实际上，Hive内置了很多 Operator，每个Operator完成一个特定的计算过程，Hive将这些Operator构造成一个有向无环图 DAG，然后根据这些Operator之间是否存在shuffle将其封装到map或者reduce函数中，之后就可以提 交给MapReduce执行了。

内部表与外部表

内部表（管理表）

Hive创建内部表时，会将数据移动到数据仓库指向的路径。

删除一个内部表时，内部表的元数据和数据会被一起删除。内部表不适合和其他工具共享数据。

外部表

Hive创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。减少数据传输、数据还能共享。

删除一个外部表时，只删除元数据，不删除数据。相对来说更加安全些，数据组织也更加灵活，方便共享源数据。

分区表和分桶表

分区表

分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

分区建表分为2种

（1）单分区，也就是说在表文件夹目录下只有一级文件夹目录。

（2）多分区，表文件夹下出现多文件夹嵌套模式。

加载数据的方式分为2种

（1）load 方式

（2）insert 方式：动态分区

动态分区与静态分区区别就是不指定分区目录，由系统自己选择。
但是动态分区往往需要先开启，并指定非严格模式

Hive分区又分为单值分区、范围分区。单值分区又分为静态分区和动态分区

单值分区每个分区对应于分区键的一个取值，每个范围分区则对应分区键的一个区间，只要落在指定区间内的记录都被存储在对应的分区下。

分区范围需要手动指定，分区的范围为前闭后开区间 [最小值, 最大值)。最后出现的分区可以使用 MAXVALUE 作为上限，MAXVALUE 代表该分区键的数据类型所允许的最大值。

分桶表

分桶是将数据集分解成更容易管理的若干部分的另一个技术。可以减少全表查询，JOIN时可以提高效率并减少笛卡尔积数量，还能提高抽样的效率。

区别

1）从粒度上：

分区针对的是数据的存储路径，多文件夹（粗粒度）

分桶针对的是数据文件，多文件（细粒度）

2）从创建语句上：

分区表使用partitioned by 子句指定，以指定字段为伪列，需要指定字段类型

分桶表由clustered by 子句指定，指定字段为真实字段，需要指定桶的个数

3）从数量上：

分区表的分区个数可以增长

分桶表一旦指定，不能再增长

4）从作用上：

分区避免全表扫描，根据分区列查询指定目录提高查询速度

分桶保存分桶查询结果的分桶结构（数据已经按照分桶字段进行了hash散列）。分桶表数据进行抽样和JOIN时可以提高MR程序效率

5）从数据分配上：

分桶随机分割数据库，按照列的哈希函数进行分割的，相对比较平均

分区是非随机分割数据库，分区是按照列的值来进行分割的，容易造成数据倾斜

9个By

（1）partitioned by 建立分区（字段值）表。

（2）clustered by 建立分桶（哈希值）表。

（3）sorted by 分桶时对桶中的一个或多个列另外排序。

（4）order by 全局排序，只有一个Reducer。

（5）sort by 分区内排序。

（6）distrbute by 类似MR中Partition，进行分区（哈希值），结合sort by使用。

（7）cluster by 当Distribute by和Sorts by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。 在生产环境中order by用的比较少，容易导致OOM。 在生产环境中sort by+ distrbute by用的多。

（8）partition by 分析函数，窗口函数中使用，进行分区（字段值）。

（9）group by 按照一个或者多个列进行分组，然后对每个组执行聚合操作。

系统函数

1）date_add、date_sub函数（加减日期）

2）next_day函数（周指标相关）

3）date_format函数（根据格式整理日期）

4）last_day函数（求当月最后一天日期）

5）CONCAT、CONCAT_WS、COLLECT_SET

6）EXPLODE

7）collect_set函数

8）get_json_object解析json函数

9）NVL（表达式1，表达式2）

如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。

自定义函数

1）在项目中是否自定义过UDF、UDTF函数，以及用他们处理了什么问题，及自定义步骤？

（1）用UDF函数解析公共字段；用UDTF函数解析事件字段。

（2）自定义UDF：继承UDF，重写evaluate方法

       （3）自定义UDTF：继承自GenericUDTF，重写3个方法：initialize（自定义输出的列名和类型），process（将结果返回forward(result)），close

2）为什么要自定义UDF/UDTF？

因为自定义函数，可以自己埋点Log打印日志，出错或者数据异常，方便调试。

引入第三方jar包时，也需要。

窗口函数

1）Rank

（1）RANK() 排序相同时会重复，总数不会变

（2）DENSE_RANK() 排序相同时会重复，总数会减少

（3）ROW_NUMBER() 会根据顺序计算

2） OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

（1）CURRENT ROW：当前行

（2）n PRECEDING：往前n行数据

（3） n FOLLOWING：往后n行数据

（4）UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

（5） LAG(col,n)：往前第n行数据

（6）LEAD(col,n)：往后第n行数据

（7） NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

Hive优化

小表大表 Join（MapJOIN）

将 key 相对分散，并且数据量小的表放在 join 的左边，可以使用 map join 让小的维度表 先进内存。在 map 端完成 join。

大表 Join 大表

1）空 KEY 过滤

有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同 的 reducer 上，从而导致内存不够。此时我们应该仔细分析这些异常的 key，很多情况下， 这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进行过滤。

insert overwrite table jointable select n.* from (select * from nullidtable where id is not null) n left join bigtable o on n.id = o.id;

2）空 key 转换

有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在 join 的结果中，此时我们可以表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的 reducer 上。

insert overwrite table jointableselect n.* from nullidtable n full join bigtable o on nvl(n.id,rand()) = o.id;

3）SMB(Sort Merge Bucket join)

join on 分桶字段

笛卡尔积

尽量避免笛卡尔积，join 的时候不加 on 条件，或者无效的 on 条件，Hive 只能使用 1 个 reducer 来完成笛卡尔积。

行列过滤

列过滤：在 SELECT 中，只拿需要的列，如果有分区，尽量使用分区过滤，少用 SELECT *。

行过滤：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 Where 后面， 那么就会先全表关联，之后再过滤（谓词下推）

select o.id from bigtable b join bigtable o on o.id = b.id where o.id <= 10;

select b.id from bigtable b join (select id from bigtable where id <= 10) o on b.id = o.id;

分区和分桶

分区和分桶都是为了便于查询，提高查询的效率

合理设置 Map 及 Reduce 数

1）复杂文件增加 Map 数

当 input 的文件都很大，任务逻辑复杂，map 执行非常慢的时候，可以考虑增加 Map 数， 来使得每个 map 处理的数据量减少，从而提高任务的执行效率。

2）小文件进行合并

在 map 执行前合并小文件（在 map-only 任务结束时、在 map-reduce 任务结束时也可以进行合并小文件），减少 map 数：CombineHiveInputFormat 具有对小文件进行合并的功能（系统默认的格式），HiveInputFormat 没有对小文件合并功能

3）合理设置 Reduce 数

过多的启动和初始化 reduce 也会消耗时间和资源；另外，有多少个 reduce，就会有多少个输出文件，如果生成了很多个小文件，那 么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题； 在设置 reduce 个数的时候也需要考虑这两个原则：处理大数据量利用合适的 reduce 数； 使单个 reduce 任务处理数据量大小要合适；

并行执行

Hive 会将一个查询转化成一个或者多个阶段。这样的阶段可以是 MapReduce 阶段、抽 样阶段、合并阶段、limit 阶段。或者 Hive 执行过程中可能需要的其他阶段。默认情况下， Hive 一次只会执行一个阶段。不过，某个特定的 job 可能包含众多的阶段，而这些阶段可能 并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个 job 的执行 时间缩短。不过，如果有更多的阶段可以并行执行，那么 job 可能就越快完成。 通过设置参数 hive.exec.parallel 值为 true，就可以开启并发执行。不过，在共享集群中， 需要注意下，如果 job 中并行阶段增多，那么集群利用率就会增加。

严格模式

Hive 可以通过设置防止一些危险操作：

1）分区表不使用分区过滤

2）使用 order by 没有 limit 过滤

3）笛卡尔积

小文件

1）JVM重用

默认情况下，每个 Task 任务都需要启动一个 JVM 来运行，如果 Task 任务计算的数据 量很小，我们可以让同一个 Job 的多个 Task 运行在一个 JVM 中，不必为每个 Task 都开启 一个 JVM。

2）CombinehiveInputformat 减少切片个数，减少maptask个数

3）merge

如果是maponly任务，默认打开。执行完任务后，会产生大量小文件，默认会开启一个job将小于16m的文件合并到256m

如果是mapreduce任务，需要将该功能开启

压缩与存储

1）开启 Map 输出阶段压缩

开启 map 输出阶段压缩可以减少 job 中 map 和 Reduce task 间数据传输量。

2）开启 Reduce 输出阶段压缩

当 Hive 将 输 出 写 入 到 表 中 时 ， 输出内容同样可以进行压缩。属性 hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false， 这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这 个值为 true，来开启输出结果压缩功能。

3）Hive 支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。

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

4）在实际的项目开发当中，hive 表的数据存储格式一般选择：orc 或 parquet。压缩方式一 般选择 snappy，lzo。

Hive数据倾斜

1.Count(Distinct) 去重统计

数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个 Reduce Task 来完成，这一个 Reduce 需要处理的数据量太大，就会导致整个 Job 很难完成， 一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换,但是需要注意 group by 造成 的数据倾斜问题

select count(id) from (select id from bigtable group by id) a;

2.Group By 数据倾斜

默认情况下，Map 阶段同一 Key 数据分发给一个 reduce，当一个 key 数据过大时就倾斜 了。

并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端进行部分聚合，最后在 Reduce 端得出最终结果。

1）开启 Map 端聚合（set hive.map.aggr = true;）

2）设置map端预聚合的行数阈值，超过该值就会分拆job。

3）开启负载均衡（set hive.groupby.skewindata = true）

当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出 结果会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果 是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二 个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证 相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

3. join基础优化

（1） Hive在解析带join的SQL语句时，会默认将最后一个表作为大表，将前面的表作为小表，将它们读进内存。如果表顺序写反，如果大表在前面，引发OOM。不过现在hive自带优化。

（2）当使用外关联时，先过滤再全表关联（谓词下推）[列过滤]

（3） [map join]:特别适合大小表join的情况，大小表join在map端直接完成join过程，没有reduce，效率很高。

（4）多表join时key相同：会将多个join合并为一个MR job来处理，两个join的条件不相同，就会拆成多个MR job计算。

（5）开启倾斜join优化

# join 的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置

set hive.skewjoin.key=100000;

# 如果是 join 过程出现倾斜应该设置为 true

set hive.optimize.skewjoin=false;

如果开启了，在 Join 过程中 Hive 会将计数超过阈值 hive.skewjoin.key（默认 100000）的 倾斜 key 对应的行临时写进文件中，然后再启动另一个 job 做 map join 生成结果。

通过 hive.skewjoin.mapjoin.map.tasks 参数还可以控制第二个 job 的 mapper 数量，默认 10000。

4. sort by代替order by

将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时 间计算不完。使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部 有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by一同使用。如果不加 distribute by的话，map端数据就会随机分配到reducer。

5. 单独处理倾斜key

一般来讲倾斜的key都很少，我们可以将它们抽样出来，对应的行单独存入临时表中，然后打上随机数前缀，最后再进行聚合。

或者是先对key做一层hash，先将数据随机打散让它的并行度变大，再汇集

自定义分区器，自己控制key的分配

6.手动设定reduce个数

set mapred.reduce.tasks=100 当hive自动判断reduce个数不准确时，可以强制设定reduce个数

7.key类型转换

比如用户表中user_id字段为int，log表中user_id字段string类型。当按照user_id进行两个表的Join操作时，hive进行自动隐式类型转换，有可能String转换后超过int范围取int最大值或最小值，造成数据倾斜。

解决方式：把数字类型转换成字符串类型

select * from users a left outer join logs bon a.usr_id = cast(b.user_id as string)

HQL练习

列转行

SELECT
    t1.c_b,
    CONCAT_WS("|",collect_set(t1.name))
FROM (
    SELECT
        NAME,
        CONCAT_WS(',',constellation,blood_type) c_b
    FROM person_info
)t1
GROUP BY t1.c_b        

CONCAT_WS(separator, str1, str2,...) 返回输入字符串或字符串数组连接后的结果，separator为连接符

COLLECT_SET(col)：将某字段的值进行去重汇总，产生 Array 类型字段。

行转列

SELECT
    movie,
    category_name
FROM
    movie_info
lateral VIEW
    explode(split(category,",")) movie_info_tmp AS category_name;

EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。

LATERAL VIEW

用法：table LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：生成侧写表，lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，再将多行中的每一行与原来行关联，产生一个新的表取代原表，其中tableAlias并不是整张表，而是只有炸裂字段的虚拟表。

TopN

SELECT 
　　cookieid, 
　　vtime,
　　pv 
FROM (
    SELECT 
　　　　cookieid, 
　　　　vtime, 
　　　　pv, 
　　　　RANK() OVER (PARTITION BY cookieid ORDER BY pv DESC, vtime DESC) as pv_rank 
    FROM (
        SELECT 
            cookieid, 
            vtime, 
            COUNT(vtime) AS pv
        FROM tmp_dh_topN
            GROUP BY cookieid, vtime
            ORDER BY cookieid, vtime
    ) tmp_dh_pv
)
WHERE pv_rank <= 3;

RANK()是要和OVER(ORDER BY fieldName)一起来使用的，在(ORDER BY fieldName)中指定用于排序的窗口、排序依据的字段、排序是按照依据字段的升序还是降序。

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算