一、需求描述

统计 Youtube 视频网站的常规指标，各种 TopN 指标：

–统计视频观看数 Top10

–统计视频类别热度 Top10

–统计视频观看数 Top20 所属类别包含这 Top20 视频的个数

–统计视频观看数 Top50 所关联视频的所属类别 Rank

–统计每个类别中的视频热度 Top10

–统计每个类别中视频流量 Top10

–统计上传视频最多的用户 Top10 以及他们上传的视频

–统计每个类别视频观看数 Top10

二、知识储备梳理

2.1、order by，sort by，distribute by，cluster by

背景表结构

在讲解中我们需要贯串一个 例子，所以需要设计一个情景，对应 还要有一个表结构和填充

数据。如下：有 3 个字段，分别为 personId 标识某一个人，company 标识一家公司名称，

money 标识该公司每年盈利收入（单位：万元人民币）

建表导入数据：

create table company_info(

personId string,

company string,

money float

)row format delimited fields terminated by "\t"

load data local inpath “company_info.txt” into table company_info;

2.1.1、order by

hive 中的 order by 语句会对查询结果做一次全局排序，即，所有的 mapper 产生的结果都会

交给一个 reducer 去处理，无论数据量大小，job 任务只会启动一个 reducer，如果数据量巨

大，则会耗费大量的时间。

尖叫提示：如果在严格模式下，order by 需要指定 limit 数据条数，不然数据量巨大的情况下

会造成崩溃无输出结果。涉及属性：set hive.mapred.mode=nonstrict/strict

例如：按照 money 排序的例子

select * from company_info order by money desc;

2.1.2、sort by

hive 中的 sort by 语句会对每一块局部数据进行局部排序，即，每一个 reducer 处理的数据都

是有序的，但是不能保证全局有序。

2.1.3、distribute by

hive 中的 distribute by 一般要和 sort by 一起使用，即将某一块数据归给(distribute by)某一个

reducer 处理，然后在指定的 reducer 中进行 sort by 排序。

尖叫提示：distribute by 必须写在 sort by 之前

尖叫提示：涉及属性 mapreduce.job.reduces，hive.exec.reducers.bytes.per.reducer例如：不同的人（personId）分为不同的组，每组按照 money 排序。

select * from company_info distribute by personId sort by personId, money desc;

2.1.4、cluster by

hive 中的 cluster by 在 distribute by 和 sort by 排序字段一致的情况下是等价的。同时，cluster

by 指定的列只能是降序，即默认的 descend，而不能是 ascend。

例如：写一个等价于 distribute by 与 sort by 的例子

select * from company_info distribute by personId sort by personId;

等价于

select * from compnay_info cluster by personId;

2.2、行转列、列转行（UDAF 与 UDTF）

2.2.1、行转列

表结构：

创建表及数据导入：

create table person_info(

name string,

constellation string,

blood_type string)

row format delimited fields terminated by "\t";

load data local inpath “person_info.tsv” into table person_info;

例如：把星座和血型一样的人归类到一起

select

t1.base,

concat_ws('|', collect_set(t1.name)) name

from

(select

name,

concat(constellation, ",", blood_type) base

from

person_info) t1

group by

t1.base;

2.2.2、列转行

表结构：

创建表及导入数据：

create table movie_info(

movie string,

category array<string>)

row format delimited fields terminated by "\t"

collection items terminated by ",";

load data local inpath "movie_info.tsv" into table movie_info;

例如：将电影分类中的数组数据展开

select

movie,

category_name

from

movie_info lateral view explode(category) table_tmp as category_name;

2.3、数组操作

“fields terminated by”：字段与字段之间的分隔符。

“collection items terminated by”：一个字段中各个子元素 item 的分隔符。

2.4、orc 存储

orc 即 Optimized Row Columnar (ORC) file，在 RCFile 的基础上演化而来，可以提供一种高

效的方法在 Hive 中存储数据，提升了读、写、处理数据的效率。

2.5、Hive 分桶

Hive 可以将表或者表的分区进一步组织成桶，以达到：

1、数据取样效率更高

2、数据处理效率更高

桶通过对指定列进行哈希来实现，将一个列名下的数据切分为“一组桶”，每个桶都对应了

一个该列名下的一个存储文件。

2.5.1、直接分桶

开始操作之前，需要将 hive.enforce.bucketing 属性设置为 true，以标识 Hive 可以识别桶。

create table music(

id int,

name string,

size float)

clustered by (id) sort by (id) into 4 buckets

row format delimited fields terminated by "\t";

该代码的意思是将 music 表按照 id 将数据分成了 4 个桶，插入数据时，会对应 4 个 reduce

操作，输出 4 个文件。

2.5.2、在分区中分桶

当数据量过大，需要庞大分区数量时，可以考虑桶，因为分区数量太大的情况可能会导致文

件系统挂掉，而且桶比分区有更高的查询效率。数据最终落在哪一个桶里，取决于 clustered

by 的那个列的值的 hash 数与桶的个数求余来决定。虽然有一定离散性，但不能保证每个桶

中的数据量是一样的。

create table music2(

id int,

name string,

size float)

partitioned by (date string)

clustered by (id) sorted by(size) into 4 bucket

row format delimited

fields terminated by "\t";

load data local inpath 'demo/music.txt' into table music2 partition(date='2017-08-30');

奈斯

牛牛牛！

优秀