3

用Hive玩转维基百科

众所周知,Hadoop提供了基于廉价硬件实现大规模并行处理的能力,利用这套技术模仿谷歌存储并计算整个互联网也不是难事。不过,简单的查询都要写MapReduce代码,对于商业用户实在不怎么友好,毕竟,程序猿不是谁都可以当的。为了解决这个问题,Hive应运而生。本质上,Hive是一个类似关系数据仓库,用户可以方便地利用类似SQL语言查询数据,而Hive会自动把SQL语言转换成MapReduce代码交给Hadoop处理,把玩大数据连产品狗也可以完成啦

当然,由于Hadoop本身更擅长批处理,不能期望Hive查询能立即返回结果,非常适合学习培养耐心。同时,Hive也不是一个在线事务处理(OLTP)系统,它并不提供类似关系数据库对数据的增删改操作。一言蔽之,Hive更擅长对大规模只读数据的查询,比如网页日志分析、用户行为挖掘等等

hdinsight-hive-01

百闻不如一见,让我们使用Hive来统计Wikimedia网页访问信息。Wikimedia包含了Wikipedia、Wikidata等在内的一系列网站的集合,Page view statistics for Wikimedia projects提供原始访问数据下载,数据是按照小时分割存储在单独的文本文件中。以2015年5月1日为例,数据保存在pagecounts-20150501-000000.gz、…、pagecounts-20150501-230000.gz共24个文件中,每个GZIP文件大约在100MB左右(压缩前500MB左右)。每行数据有四项,分别是网站名称、页面标题、访问数量、数据大小。

准备

如今许多云供应商都提供了对Hive的支持,与其自己从头搭建Hive运行环境,不如到云供应商那里租用Hive实例,召之即来挥之即去,帝王般的待遇。Azure上的HDInsight就是这么一个选项。别看名字起得这么诡异,HDInsight不但提供了完整的Hadoop体验,还可以直接处理保存在Azure Blob Storage上面的数据而不需要把数据先导入HDFS,非常方便。

仔细的读者会产生疑问,Hadoop的一大优势不就是让计算靠近数据,而HDInsight却允许数据从Blob Storage上搬运出来是几个意思么?简而言之,这都归功于低延迟高带宽的Azure Flat Network Storage,使得数据传输不再是一个瓶颈,具体可以参考Why use Blob Storage with HDInsight on Azure

首先需要在HDInsight创建一个Hadoop的集群,只需要提供集群名字、节点个数、密码以及相关的Blob账号即可。创建完成以后便可以通过HDInsight的网站与Hive交互:

hdinsight-hive-02

创建

Hive之所以称之为类似关系数据仓库,主要是因为它要求用户预先定义schema。当把Wikimedia的数据保存在Blob Storage中之后,便可通过以下命令把Wikimedia的数据做成一张Hive表:

DROP TABLE IF EXISTS WikimediaPageViewRawData;
CREATE EXTERNAL TABLE WikimediaPageViewRawData
(
	Project string,
	Title string,
	Number int,
	Size int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
LINES TERMINATED BY '10'
STORED AS TEXTFILE
LOCATION 'wasb://wikimedia@storage.blob.core.windows.net/';

其中:

  • CREATE EXTERNAL TABLE表示数据来自LOCATION所指定的位置,其生命周期不由Hive来管理。换言之,删除这个Hive表的时候,这些数据不会被连坐。
  • 表包含四个字段,分别代表网站名称、页面标题、访问数量、数据大小。
  • 文本文件中的字段是以tab符号分割的。
  • LOCATION指定数据所在的Blob Storage位置,由于Hive知道当前用户与Blob Storage之间的关系,并不需要特意提供Blob的验证信息。

命令提交后,Hive便会产生相应的MapReduce任务执行,从指定位置读取文件并创建表格元数据。虽然Wikimedia提供的都是GZIP格式的数据,Hive会根据扩展名调用相应的codec处理。

顺便提一句,Hive其实很喜欢压缩格式,请参考Compression in Hadoop – Microsoft

查询

以下SQL语言便可以查询哪个项目访问量最大:

SELECT Project, count(*) AS Total
FROM WikimediaPageViewRawData
GROUP BY Project
ORDER BY Total DESC LIMIT 30;

Hive会运用Hadoop集群中的结点并发计算,结果如下:

en	203534620
en.mw	105680191
ja	23782601
de	22999121
ja.mw	20476283
es	20052693
ru	20005613
fr	19540357
zh	16320957
es.mw	16060453
de.mw	15143824
…

果然,英文网站访问量最大,爱学习爱分享的日本人和德国人分列第二第三,由于功夫网和百度百科的存在中文维基的访问量只能呵呵了。

分区

Hive一个非常有用的功能是分区(partition),比如,如果数据根据日期分区,那么对于某些日子的查询便可以针对那个分区,而避免全表检索。使用分区,一般需要先创建支持分区的表,之后再导入数据。

以下命令可以创建分区表:

DROP TABLE IF EXISTS WikimediaPageViewPartitionedData;
CREATE TABLE WikimediaPageViewPartitionedData
(
	Project string,
	Title string,
	Number int,
	Size int
)
PARTITIONED BY (Date date)
STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");

其中,PARTITIONED BY命令会创建一个叫做Date的支持分区的列,而STORED AS会以ORC格式在文件系统上存储数据,并以SNAPPY格式压缩。

值得一提的是,ORC是一种基于列的支持压缩和索引的格式,与纯文本存储方式相比查询速度要快很多,下图是来自Hortonworks的对比图,具体请参考Major Technical Advancements in Apache Hive论文。

hdinsight-hive-03

现在可以通过以下命令从原始数据表导入到支持分区的Hive表格中了:

INSERT OVERWRITE TABLE WikimediaPageViewPartitionedData
PARTITION(Date = '2015-05-01')
SELECT
	Project,
	Title,
	Number,
	Size
FROM WikimediaPageViewRawData
WHERE split(INPUT__FILE__NAME, '-')[1] = '20150501';

这个语句除了使用PARTITION来指定根据日期分区,还调用了split函数根据文件名筛选出相应的文件。

从带分区的Hive表查询不需要特殊的语法,比如:

SELECT Title, sum(Number) AS Total
FROM WikimediaPageViewPartitionedData
WHERE Date='2015-05-01' AND Project='en'
GROUP BY Title
ORDER BY Total DESC LIMIT 30;

查询返回了英文网站受访网页的次数,按照降序排列:

Main_Page	15666832
Malware	812927
Academy_Awards	597934
Special:Search	350746
Two-dimensional_nuclear_magnetic_resonance_spectroscopy	313757
Avengers_(comics)	247555
Floyd_Mayweather,_Jr._vs._Manny_Pacquiao	235536
Manny_Pacquiao	209696
Flash_Boys	201027
Floyd_Mayweather,_Jr.	197249
Avengers:_Age_of_Ultron	183147
May_Day	166512
Labour_Day	157894
Penny_Black	137331
Hypokalemia	128987
…

除了主页,2015年5月1日用户查询最多的是Malware,应该是欧美用户安全意识比较高吧。查询Avengers等比较应景,都懂的。Flash Boys、May Day、Labour Day、以及一些人名都比较好理解,不过那么多对two dimensional nuclear magnetic resonance spectroscopy的访问是几个意思……感兴趣的话可以把一整年的数据都做出来挖掘一下用户兴趣图谱,或许也能带来新的启示。

技巧

高端商务人士还可以通过Excel直接连接Hive集群。方法很简单,首先需要下载并安装Microsoft Hive ODBC Driver,然后在Excel中创建Hive ODBC数据源即可。

最后跟大家分享使用Hive的终极大招,一般人我还不告诉TA:做完实验记得释放集群,否则月底的账单一定让你欲哭无泪……

 



张 琪

3 Comments

  1. 不错。你不要黑HDInsight的服务哦。其实用户不需要拥有自己的集群,只要把自己的hive查询提交给系统,系统算完了把结果返回就可以来,然后按计算量收费。很快就会有了

  2. 哪里有黑HDInsight,除了这个名字,Hadoop,现在不是连Spark都有了么,嘿嘿……当初为了修一个bug,在Mooncake上烧掉我近2000元软妹币,还木有人给我报销……

  3. 真不错,最近也在分析wikipedia的raw数据,楼主用AZURE 我是用AWS,细心看了楼主的代码 有几处不是太明白 想请教下 邮件 已留 希望楼主指教 感谢分享

发表评论

电子邮件地址不会被公开。 必填项已用*标注