Hive简易教程 - 数据分析

Hive是一个HDFS上的sql执行引擎，它将sql语句转化为Hadoop上的map-reduce任务来执行。由于是写sql，所以使用Hive进行数据分析的好处是没有什么额外的学习成本，但是它是批量式处理的，可能会比较慢。本文将通过几个案例来简单介绍如何使用Hive。

随机生成一批订单数据(order_id, price, tag, order_date)

存储数据到Hive

统计出近一周每天成功支付的订单总数，gmv，客单价

统计出近一周每天成功支付 及支付失败 各自的订单总数，gmv，客单价

count函数和if条件组合，而不是两个sql join

挑选出近一周gmv>1000并且订单量>2单的卖家ID及其订单

  在使用group by之后只能select出group key以及相关的统计数字，但也可以以集合的形式select出任何其他的非group key，比如按卖家ID聚合之后又想查看在这个卖家下单的买家ID：sellect collect_set(buyer_id) from t group by seller_id。

  与collect_set类似，元素可重复

  explode函数可以把一个array类型的数据扁平化。比如，现在每行是一个seller_id集合，使用explode可以扁平化为每行一个seller_id。但explode不可以直接与group by一起使用，比如我想按某些条件筛选一些卖家然后在查看该店铺的买家的情况：select explode(bbuyer_ids) from (select collect_set(buyer_id) as buyer_ids from t group by seller_id) b;

  当前时间

  将系统时间戳转化为人可读的数据格式 如：select from_unixtime(unix_timestamp(), 'yyyy-MM-dd');

  求几天前的日期

  nvl函数用于处理null值，当一个字段是null时，这个字段和其它字段进行算术运算时的结果依然为null。这时可以使用这个函数为值可能为null的字段赋予一个默认值，即v2

  判断字符串'xxx'是否出现在str1中，如果str1是null或者不存在xxx返回值都是0

  返回数组a1的大小

  合并两个查询结果，但结果的列数需要一致！！！

行转列：

定义：就是把形如

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

的数据转换为

id ------------- name

1，2，3，4 --------- a

这里把四行的数据变成了由一行显示，这就是行转列。

首先要用到的是collect_set或者collect_list，前者去重，后者不去重，一般配合group使用，能把形如：

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

转换为

id --------- name

["1","2","3","4"] --------- a

然后需要用到的是concat_ws函数，这个函数需要传递指定分隔符，以及字符串或者字符串数组。能起到把多个字符串拼接到一起的作用。

形如：

id --------- name

["1","2","3","4"] --------- a

通过concat_ws(',',[1,2,3,4])转换为

id --------- name

1，2，3，4 --------- a

连起来就完成了行转列

一般sql为:

列转行

定义：就是把形如

id ------------- name

1，2，3，4 --------- a

的数据转换为

id --------- name

1 --------- a

2 --------- a

3 --------- a

4 --------- a

这里把一行的数据变成了由四行显示，这就是列转行。

首先要用到split函数，这个行数可以把字符串按照指定的规则切分为字符串数组。

形如：

id --------- name

1，2，3，4 --------- a

通过split('1,2,3,4',',')转换为

id --------- name

["1","2","3","4"] --------- a

然后是explode函数，这个行数可以把数组的每个元素转换为一行。

形如

id

["1","2","3","4"]

通过explode(["1","2","3","4"] )转换为

id

1

2

3

4

最后为了拼接其它字段，还需要用到lateral view把explode获得的行当虚拟表来用。

使用方法为 lateral view explode(split(["1","2","3","4"] , ',')) tmptable as new_id ;

其中new_id元素为转换为行后的字段名。

把以上综合起来就完成了列转行

一般sql为:

本次实战的数据来自于"YouTube视频统计与社交网络"的数据集，是西蒙弗雷泽大学计算机学院在2008年所爬取的数据

数据集地址

数据之间采用"\t"作为分隔符

具体数据如下：

数据量大小为1G，条数为500万+

使用环境为

hive-110-cdh545

hadoop-260-cdh545

演示形式为使用hive shell

我们一起来看看数据

主要的问题在于category和relatedIDs处理，由于Hive是支持array格式的，所以我们想到的是使用array来存储category和relatedIDs，但是我们发现category的分割符是"&"而realatedIDs的分隔符是"\t"，我们在创建表格的时候能够指定array的分隔符，但是只能指定一个，所以再将数据导入到Hive表格之前我们需要对数据进行一定转换和清洗

并且数据中肯定会存在一些不完整数据和一些奇怪的格式，所以数据的清洗是必要的，我在这里所使用的数据清洗方式是使用Spark进行清洗，也可以使用自定义UDF函数来进行清洗

数据清洗注意点

1）我们可以看到每行数据以"\t"作为分隔符，每行有十列数据，最后一列关联ID可以为空，那么我们对数据进行split之后数组的大小要大于8

2）数据中存在 "uNiKXDA8eyQ KRQE 1035 News & Politics 107" 这样格式的数据，所以在处理category时需要注意 News & Politics中间的 &

处理后的数据如下：

下面的实战都是基于数据清洗后的数据进行的

1）youtube1的创建，文件格式为textfile

create table youtube1(videoId string, uploader string, age int, category array<string>, length int, views int, rate float, ratings int, comments int,relatedId array<string>)

row format delimited

fields terminated by "\t"

collection items terminated by "&"

stored as textfile;

2）youtube2的创建，文件格式为orc

create table youtube2(videoId string, uploader string, age int, category array<string>, length int, views int, rate float, ratings int, comments int,relatedId array<string>)

row format delimited

fields terminated by "\t"

collection items terminated by "&"

stored as orc;

3）youtube3的创建，文件格式为orc，进行桶分区

create table youtube3(videoId string, uploader string, age int, category array<string>, length int, views int, rate float, ratings int, comments int,relatedId array<string>)

clustered by (uploader) into 8 buckets

row format delimited

fields terminated by "\t"

collection items terminated by "&"

stored as orc;

数据导入：

1）load data inpath "path" into table youtube1;

2）由于无法将textfile格式的数据导入到orc格式的表格，所以数据需要从youtube1导入到youtube2和youtube3：

insert into table youtube2 select from youtube1;

insert into table youtube3 select from youtube1;

1）user_tmp的创建，文件格式textfile，24buckets

create table user_tmp(uploader string,videos int,friends int)

clustered by (uploader) into 24 buckets

row format delimited

fields terminated by "\t"

stored as textfile;

2）user的创建，文件格式orc，24buckets

create table user(uploader string,videos int,friends int)

clustered by (uploader) into 24 buckets

row format delimited

fields terminated by "\t"

stored as orc;

user表的数据导入也是同理

数据导入：

1）load data inpath "path" into table user_tmp;

2）由于无法将textfile格式的数据导入到orc格式的表格，所以数据需要从user_tmp导入到user：

insert into table user select from user_tmp;

1）统计出观看数最多的10个视频

2）统计出视频类别热度的前10个类型

3）统计出视频观看数最高的50个视频的所属类别

4）统计出观看数最多的前N个视频所关联的视频的所属类别排行

5）筛选出每个类别中热度最高的前10个视频

6）筛选出每个类别中评分最高的前10个视频

7）找出用户中上传视频最多的10个用户的所有视频

8）筛选出每个类别中观看数Top10

select from youtube3 order by views desc limit 10;

结果如下：

select tagId, count(avideoid) as sum from (select videoid,tagId from youtube3 lateral view explode(category) catetory as tagId) a group by atagId order by sum desc limit 10;

结果：

select tagId, count(avideoid) as sum from (select videoid,tagId from (select from youtube3 order by views desc limit 20) e lateral view explode(category) catetory as tagId) a group by atagId order by sum desc;

结果：

思路：

结果：

思路：

结果如下：

select from youtube_category where categoryId="Music" order by ratings desc limit 10;

结果如下：

思路：

结果如下：

如下图：

1、列转行，即table1查询出table2

select

    `order_id`,

    concat_ws(',', collect_list(`item_sku_id`))

from

    table1

group by

    `order_id`;

2、行转列，即table2查询出table1

select

    `order_id`,

    `item_sku_id`

from

    table2 lateral view explode(split(item_sku_id), ',') as table as item_sku_id;

解决方案

MapReduce运行队列的指定是通过配置（Configuration）属性“mapreducejobqueuename”指定的。

大家可能首先想到的是通过“set mapreducejobqueuename=queueName”的方式来选取运行队列，这在手动任务（临时分析需求）的场景下是不可取的，如前所述，我们为这类似的任务专门分配了相应的队列资源“hivetemporary”，我们必须能够保证用户通过Beeline连接HiveServer2后提交的Hive SQL语句运行在指定的队列“hivetemporary”中，而且用户无法随意更改运行队列，即无法随意更改属性“mapreducejobqueuename”。

目前HiveServer2使用的权限控制策略为SQL Standard Based Hive Authorization和Storage Based Authorization in the Metastore Server。其中SQL Standard Based Hive Authorization会对Hive终端命令“set”做出限制：只能针对白名单（hivesecurityauthorizationsqlstdconfwhitelist）中列出的属性进行赋值。白名单默认包含一批属性，其中就包括“mapreducejobqueuename”，我们需要通过配置文件hive-sitexml或者启动HiveServer2时通过参数“--hiveconf”设置白名单“hivesecurityauthorizationsqlstdconfwhitelist”的值，排除属性“mapreducejobqueuename”，使得我们的用户通过Beeline连接至HiveServer2之后，无法随意更改“mapreducejobqueuename”的值。

既然用户无法更改属性“mapreducejobqueuename”，那么HiveServer2启动之后属性“mapreducejobqueuename”必须具有一个默认值，即“hivetemporary”，这样用户通过Beeline连接HiveServer2之后提交的Hive SQL就会运行在队列“hivetemporary”中。那么，接下来的问题就是如果完成这个默认设定？

一般情况下，我们会这样认为，HiveServer2的运行至少涉及到两份配置文件：

（1）Hadoop：core-sitexml、hdfs-sitexml、mapred-sitexml、yarn-sitexml

（2）Hive：hive-sitexml

这些配置文件中的属性值都会“打包”到MapReduce任务的配置属性中去。我们自然会想到在mapred-sitexml或者hive-sitexml中指定“mapreducejobqueuename”即可，然而实际验证之后发现情况并不是这样的。

（1）在hive-sitexml（mapred-sitexml）中指定“mapreducejobqueuename”；

Hive中的 collect_set() 函数功能，将某一列中的value取值转换成一个 set ；

在 ClickHouse 中可以通过 groupUniqArray() 来实现。

以上就是关于Hive简易教程 - 数据分析全部的内容，包括:Hive简易教程 - 数据分析、hive关于行转列，列转行的使用、Hive实战之Youtube数据集等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！