HiveSQL中级进阶常用技巧

今天为你介绍数据分析、数仓开发最常用的数据处理工具 Hive 的一些使用技巧。这些技巧我们在工作中使用得比较频繁，如果运用得当，将为我们省去不少时间精力。

那么首先，我们先来了解下 Hive。Hive 是 Facebook 开源的一款基于 Hadoop 的数据仓库工具，它能完美支持 SQL 查询功能，将 SQL 查询转变为 MapReduce 任务执行。这使得大数据统计得以实现。Hive 是最早的也是目前应用最广泛的大数据处理解决方案。

Hive 的重要性不必多言。数据分析师在工作中使用 Hive SQL 来处理大数据本是家常便饭。

本课时将重点介绍 Hive 在工作中常用的数据处理技巧。

基本操作

这里简单介绍 Hive 最基础的操作，创建表和加载数据。重点说明 Hive 创建内部表和外部表的应用场景。

创建表

创建表的语法如下：

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]   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]]  [LOCATION hdfs_path]

上面的代码看起来不是很容易理解，这里通过两个例子重点讲下如何按照上面语法进行 Hive 表创建，并且重点说下创建内部表和外部表的区别。

• 内部表

第一步，创建一个内部表，按天分区，字段直接以'\t'分割：

第二步，向刚创建的表加载数据，有本机数据加载和 HDFS 路径数据加载两种方式。本机本地数据导入刚创建的表方式如下：

HDFS 路径加载数据方式如下。

• 外部表

外部表创建，即为某路径下的文件指定为一个 Hive 表结构，这种外部表的创建，不涉及数据的移动。

外部表创建有两种方式，第一种，就是在建表的时候就指定外部表的数据依赖路径。下面的代码即做了这方面的展示：

第二种，是先创建一个外部表结构，然后再指定路径。

上面我们通过代码展示了 Hive 表的创建，下面来讲解下 Hive 复合类型数据。

复合类型数据

对于 Hive 的基础数据结果，比如 string、bigint 等比较简单，这里直接跳过。下面经哥讲下 Hive 的复合类型数据用法。即 map、array、json，这三个复合数据类型要相对有一点点难度，可在工作中我们会经常使用。

map

首先是 map。map 的数据结构是 key-value 格式。数据语法为 map(k1,v1,k2,v2,…)。使用给定的 key-value 对，构造一个 map 数据结构。

下面这个例子表现的就是 map 构建。

hive> select map('k1','v1','k2','v2'), map('k1,'v1','k2','v2')['k1'] -- 获取k1对应值, map'k1',v1','k2','v2')['k2'] -- 获取k2对应值from tble_tet;OK{"k2""v","k1:"1"} v1 v2

工作中，map 样式数据被建表的同学声明为 string。这时候如果要使用 map 类型的特性，需要先将 string 转化为 map 类型。那么首先，我们要将下面字符串 'zhang:101&wang:102&li:103' 转化为 map，就需要使用 str_to_map 这个函数：

举个例子，我们可以看一下这个代码：

以上为 map 构建过程。
array

了解过 map 后，我们再来了解一下 array。array 的语法为 array(val1,val2,val3,…)，操作类型为 array。我们可以使用给定的表达式，构造一个 array 数据结构。举例：

刚才我们说到实际工作的表，几乎都是默认使用 string 存储，很少在创建表的时候使用 array，往往都是基于 string 生成 array 进行使用的。那么，什么时候会用到 array 类型呢？下面我们举两个使用 array 类型的实例：

• split 切分字符串返回 array 类型。

第一个例子为 split 切分字符串返回 array 类型。如下代码所示。

• collect_list 聚合某字段返回 array 类型。

我们假设一张记录用户登录使用手机型号的表，如下所示：

随后，我们对不同用户的不同终端类型以及终端数进行统计。

select userId, sort_array(collect_set(phone)) as phone_array   -- 这里将同一个用户使用的终端类型情况聚合到一个数组内并去除重复的手机型号, 最后再排序, size(collect_set(phone)) as phone_size   --  将用一个用户使用手机类型去重后求数组大小, 即为用户使用不同终端数量from table_testgroup by userId

需要注意的是collect_set、collect_list。这里生成 array、sort_array 和 size 函数，是为了对生成的 array 进行处理。
在工作中，这样的应用场景很多。我们可以把手机终端替换成 IP 地址、省份、兴趣标签等，得出不同的业务场景。比如，分析一个用户在 1 天或者 1 分钟内切换 IP 地址的数量，用来作为一个公司反作弊的数据指标；分析下单用户身上的兴趣标签，用来为业务方提供优质的决策。

json

再来看下 json，json 同样是我们使用较多的数据。因为 json 具有良好的可读性和便于快速编写的特点，可以在不同平台之间进行数据交换。所以 json 数据类型在开发接口的时候应用最广。

语法使用 get_json_object(string json_string, string path)。返回值使用 string。json 字符串可被 Hive 存储，解析可以直接使用 get_json_object。需要说明的是，解析 json 的字符串 json_string，则返回 path 指定的内容。如果输入的 json 字符串无效，则返回 NULL。

下面两个例子示范 json 字符串是如何解析使用的。

第一个例子相对简单，只解析 json 第一层：

这个例子目的在于让你了解 get_json_object 的基本用法。

第二个例子相对复杂。这是一个嵌套 json array 的解析过程。需要你花时间学习、理解。以后再遇到 json 嵌套问题，都可以用这种方法解决。

这个例子中的数据如下：

hive> select json_sample from table_test;OK-- 返回json字符串，记录信息为用户在某个时间访问了不同城市旅游产品信息，为方便阅读，已经过json格式化处理：{"time": 1597920700558,"from": "104","uid": "124789","ip": "198.168.0.52","data": [{"post_id": "{\"city_id\":\"10001\",\"time\":1597920700558\"}","type": 3}, {"post_id": "{\"city_id\":\"10002\",\"time\":1597920700558\"}","type": 8}]}

这里的目的是将用户访问过的城市 id 提取出来，并进行行转列操作。

hive> select uid, split(get_json_object(get_json_object(json_sample, '$.post_id'),'$.city_id'),'_')[0] as city_idfrom table_testlateral view explode(split(regexp_replace(regexp_replace(get_json_object(data, '$.data'),'\\[|\\]',''),  --将 json 数组两边的中括号去掉。 这里需要特别注意反斜杠\在hive中的用法。'\\}\\,\\{'    --将 json 数组元素之间的逗号换成分号， 因为data内部是多个 json 串，不同 json 串也是用逗号分隔，这个容易和下一层分隔符混淆,'\\}\;\\{'),'\\;')) tb as json_sample  -- 将 json 数组切分成一个个 json 结构的字符串，以使用 get_json_object 进行解析-- 最后输出结果如下：124789  10001124789  10002

可以看出，上面 SQL 解析出 json 中 uid、city_id， 并将一行转为两行输出。这个案例分享的是对于嵌套 json 的解析过程。如果可以理解并掌握，那么以后 Hive 中涉及 json 数据解析都不会是问题。

特殊数据处理场景

除去上述场景外，还有一些特殊的数据处理场景。下面我们介绍一个较为典型的时间函数。

时间函数

时间类型，应用场景非常广泛，处理也很有技巧性。

针对工作中经常使用的日期转化和运算，我分享一个例子，相信对你很有帮助。

首先，我讲下日期格式转化：

• 时间戳转化日期：from_unixtime(bigint unixtime, string format)。

这里我们需要两个参数。

参数1：unixtime 为时间戳，即从 19700101 开始至今累计的秒数。

参数2：是希望转行日期的格式，比如年月日可表示为"yyyy-MM-dd"。

• 日期转化时间戳：unix_timestamp(string date, string format)。

这里同样需要两个参数。

参数1：date为日期字符串，如"2020-01-01"。

参数2：是对应参数 1 的格式，格式必须保持一致，即前面是年月日，这里也要对应年月日。

-- 根据第一个参数格式, 第二个参数格式也需要一致才可以运行hive> select unix_timestamp('2020-08-23', 'yyyy-MM-dd'), unix_timestamp('20200823', 'yyyyMMdd'), unix_timestamp('2020-08-23 21', 'yyyy-MM-dd'), unix_timestamp('2020-08-23 21:30:30', 'yyyy-MM-dd HH:mm:ss'), unix_timestamp('20200823 21:30:30', 'yyyyMMdd HH:mm:ss')   --  注意与上一行格式上区别, 以及两个参数对应的调整OK1598112000  1598112000  1598112000  1598189430

• yyyyMMdd 转化为 yyyy-MM-dd。

先说下这种转化的应用场景，yyyyMMdd 格式往往会作为 Hive 日期分区常见样式，但是导出的数据在 Excel 表会被认为是 8 位数字，而不是日期格式。这个时候，我们可以在数据导入 Excel 后再手动转化为 yyyy-MM-dd。还有一种方式是 Hive 输出日期格式就是 yyyy-MM-dd， 这也就是这个日期转化的应用场景。

针对这种日期格式转化，我分享下面两种方法给你。

第一种方法：将 from_unixtime 和 unix_timestamp 结合起来。

第二种方法：字符串函数 substr。

hive> select concat(substr('20200823',1,4),"-",substr('20200823',5,2),"-",substr('20200823',7,2)) as dt;OK2020-08-23

• 月份转化季度。即给出 1~12 整数，划分到不同季度，提取月份转化为整数。

hive> select floor((month('2020-08-23')-1)/3)+1                  -- 季度 date=yyyy-MM-dd, floor((cast(substr('20200823',5,2) as int)-1)/3)+1             -- 季度 date=yyyyMMd, pmod(datediff('2020-08-23','2012-01-01'),7)         -- 星期几, 0-6, 0表示周日, 表示周六, weekofyear('2020-08-23')                            -- 一年中第几周, year('2020-08-23')   -- 取出年的数字部分, month('2020-08-23')  -- 取出月的数字部分, day('2020-08-23')  -- 取出日的数字部分, hour('2020-08-23 21:30:35')  -- 取出日的数字部分, minute('2020-08-23 21:30:35')  -- 取出日的数字部分, second('2020-08-23 21:30:35')  -- 取出日的数字部分OK3 3 0 34  2020  8 23  21  30  35

日期运算很重要，相对容易理解。下面直接列举函数再附上实例，大家会一目了然。

• datediff(string enddate, string startdate)函数，用来计算两个日期天数差。

即，返回计算两个参数 enddate-startdate 间隔天数。

例子如下：

hive> select datediff('2020-08-23','2020-08-20')OK3

• date_add(string startdate, int days)的函数例子如下：

hive> select date_add('2020-08-23',3)   -- 注意这里也可以填写负数， 即返回前几天日期OK2020-08-26

• date_sub(string startdate, int days) -- 这个用法和 date_add 相反，只是返回当前日期，并减去几天的日期。

hive> select date_sub('2020-08-23',3)  -- 这里也可以为负数，即返回后几天日期OK2020-08-20

除了以上处理技巧，我们在第一节数据处理十大技能专门讲了 Hive 两个重要技能：行列转化和 row_number 函数。如果你感兴趣，可以去“入行必备：数据处理的十大技巧”进行学习。

查询性能优化

Hive 查询优化功能表现在很多方面，这里主要讲解日常工作中数据分析师应该注意的 case。

合并小文件

Hive SQL 被解析为 MapReduce 执行，一个文件会对应一个 mapper 来处理。如果数据源是大量的小文件，则会启动大量的 mapper 任务。这就好比一辆卡车一次只运送一个西瓜，浪费了大量资源。这时候，我们需要把西瓜聚合起来，让一辆卡车一次至少搬运几百斤西瓜。

对于 Hive 来说，就是将输入的小文件进行合并，从而减少 mapper 任务数量。

例如，当查询数据时，如果已知对应数据源有很多小文件，可以先进行如下设置：

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- Map端输入、合并文件之后按照block的大小分割（默认）set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; -- Map端输入，不合并

当自己在建立中间表时，输出数据为了避免小文件过多，可以进行如下设置：

set hive.merge.mapredfiles = true;set hive.merge.size.per.task=256000000;  -- 256MBset hive.merge.smallfiles.avgsize=256000000;  -- 256MB

数据倾斜

数据倾斜是什么？我同样举一个卡车运西瓜的例子说明。假设现在有 1 万个西瓜，有 100 辆卡车，前 99 辆车每辆车一次只运送 1 个西瓜，剩余 9901 个西瓜都让最后一辆卡车拉。这就是“西瓜倾斜”。同样，在大数据处理过程中，一个数据处理任务的数据量可能有几百 GB 或者几十 TB，对应会有几百甚至几千台机器一起处理。如果某一台机器被分配的数据量比其他机器多几十倍甚至几百倍，那就是我们说的数据倾斜了。

这样的情况造成的结果是，数据处理任务结束时间因为其中一台机器而延后，处理效率被严重降低。避免数据倾斜非常重要。前几年数据分析师面试的时候，面试官经常考应聘者数据倾斜的处理问题。现在解决数据倾斜的方案更加成熟，社区考虑 Hive 使用过程中会出现这种情况，进而提供了比较好的解决方案。现在，仅仅需要你配置如下参数分两部分进行。

第一部分：有些聚合可以先在 Map 端进行，然后进入 Reduce 端，这时数据量会小很多。我们再得出最终结果即可。

set hive.map.aggr=true; -- 开启Map端聚合参数设置set hive.grouby.mapaggr.checkinterval=100000; -- 在Map端进行聚合操作的条目数目

第二部分：如下参数将会添加一个 MapReduce 任务，目的就是让数据平均分配到各个 reduce 上，这样基本能解决数据倾斜问题。

set hive.groupby.skewindata = true; -- 有数据倾斜的时候进行负载均衡（默认是false）

上面通过实例解释了 Hive 进行数据统计中，为什么会遇到数据倾斜，以及如何解决数据倾斜。最后我们来分享下 Hive 如何合理控制 map 和 reduce 数量来优化数据处理效率。

控制 map/reduce 任务数量

控制 map 数量

我们先来了解下什么情况要设置 map 数量。一般来讲，map 数量默认，不需要我们设置。Hadoop 运维老师们会监控和设置一个相对通用的值。一般情况下，默认值就能满足需求了。

但是，当我们明确知道表的数据量不大，而 Hive 运行启动了几千个 map 的时候，就有必要减小 map 的数量了。好比 1000 个西瓜没必要安排 100 辆车去拉，安排 2 辆车就可以搞定了。

另一方面，当我们发现 map 数量不多，但 map 运行速度极慢的时候。这时可以 check 下数据表，看看实际需求是不是很大？如果 Hive 启动的 map 数据比较少，就如同用 2 辆车去拉 10000 个西瓜，明显是不够的。

假设如果真遇到上面情况，那么如何调整 map 数量？我们通常会采用以下两种方式解决。

• 第一种解决办法是增加 mapper 个数。可以设置 set mapred.map.tasks= 一个很大的数值， 需要比系统默认的 map 数量大。

• 第二种解决办法是减少 mapper 个数。set maperd.min.split.size= 一个数字，该数值单位是字节，比如设置 1GB，即为 1024000000，因为默认一个 mapper 是 64MB，这样设置就可以让一个 mapper 处理 1GB 数据，自然 mapper 的数量也就减少了。

上面介绍 Hive 任务在什么情况下需要调整 map 数量， 以及如何设置 map 数量，下面将对 reducer 数量的控制。

控制 reducer 数量

相比调整 mapper 数量，调整 reducer 数量的场景在工作中相对要多一些。当然，一般情况下我们也是不需要调整的。有些同学认为设置更多 reducer 会加快计算任务，但其实结果却不尽人意。

比如：当数据经过 map 操作后输出的数据结果只有 1KB 大小，却要启动 100 个 reducer 去处理。这时候启动和消耗 reducer 的时间就会很多， 最后会形成 100 个非常小的文件落盘，这对以后的任务计算来讲都是负担。相反，如果 mapper 输出的数据结果很大。有 100 GB，却只有 2 个 reducer 去处理，那必然也会浪费很长时间。这个时候，就需要调节 reducer 数量了。

我们来看如何调整 reducer 数量，这里主要有两种调整方式。

方式一：通过设置每个 reducer 处理的数量大小，最多 reducer 数量来间接控制 reducer 数据。

set hive.exec.reducers.bytes.per.reducer=一个数字(默认1GB, 即1024000000)set hive.exec.reducers.max=一个数字(默认为999)

方式二：直接设置 reducer 数量。比如下面设置 reducer 为 500，无论数据大小，都会强制启动 500 个 reducer 处理数据。

set mapred.reduce.tasks=500

上面分享的对 reducer 数量的控制，可能工作中会遇到。尤其在多个大表进行 join 操作的时候，希望你可以掌握使用的场景和方法。

总结

关于 Hive 使用的技巧有很多。这节课给出的技巧主要来自我工作 5 年多的使用经验，挑选出的一些比较有价值的核心处理技巧。我把这些技巧分享给你，这里非常感谢你的学习，之后 Hive 相关问题，可以留言交流学习。