Hadoop大数据开发基础 9实训指导手册

课程名称：Hadoop大数据开发基础

章节名称：Hive数据仓库项目实战

北京智酷道捷教育科技有限公司

2020年08月

目录

一、实训目的...................................................................2

二、实训方式...................................................................2

三、实训内容...................................................................2

.直HIVE数仓实现...........................................................2

四、实训环境..................................................................92

五、项目答辩..................................................................92

六、评分标准..................................................................94

一、实训目的

了解了Sqoop与Azkaban的基本应用、理解了电商数仓的基本指标等知识后，为了

让学生掌握Hive数仓设计的基本流程及Hive数仓各层导入脚本的开发方法，特制订本项

目实训指导手册。

通过本项目课程的学习，要求学生掌握并达成以下的目标：

＞了解数仓设计的基本流程。

＞掌握数仓设计各层的设计思路。

＞掌握数仓设计各层的开发方法。

二、实训方式

项目实训采取理论+实战的学习辅导形式，先理论后实战，循序渐进，以练为主。

具体分为两个部分：实训基础、实战演练。

实训基础：教师带领学生解析项目案例，回顾之前课堂所学知识，了解Sqoop与

Azkaban的基本应用、电商数仓的基本指标和Hive数仓设计的基本流程等知识。

实战演练：教师带领学生完成Hive数仓建表和Hive数仓各层导入脚本的开发，通过

案例实战,进一步巩固所学知识，快速实现Hive数据仓库项目实战。

三、实训内容

室Hive数仓实现

下面我们综合运用前面所学内容来学习Hive数仓实现。

1.数仓设计

我们先通过如下所示的图来了解一下数据仓库设计的基本流程。

Hive数仓

从该流程图中可以看到，该业务首先是将业务系统中的数据导入Hive数仓，在Hive

数仓中通过层层分析后再将分析后的数据导出到报表系统的一个流程。

Hive数仓分为ODS层、DWD层、DWS层、ADS层，其中ODS层是原始数据层,

存放原始数据所谓的原始数据就是将业务系统的数据直接加载到ODS层数据保持不变，

不做任何处理。DWD层是明细数据层，这层的结构和粒度与ODS层保持一致，它只是对

ODS层的数据进行清洗。所谓清洗就是去掉脏数据，例如做去空格的处理、去掉一些不规

范的数据、去掉一些为空的数据等，类似这样的操作都是在DWD层实现的。DWS层是服

务数据层，它是以DWD层的数据为基础进行汇总，这层通常以某一个维度为线索组成跨越

主题的一个宽表，以方便我们进行分析。所谓宽表，以电商系统中用户的行为信息为例来说

明，在电商系统中用户行为可能包含浏览商品的数量、添加购物车的商品数量、下订单的数

量、支付商品的数量和金额等，这些行为在业务系统中属于不同的表，在数仓中要将这些表

汇总到一起，即形成一个宽表。ADS层是数据应用层，这层为各类统计报表提供数据，例

如电商系统中的一些指标分析、每日活跃的用户数、每日留存的用户数、每日新增的用户数

等。通过数仓分析以后会获得用户期望的报表数据，将报表数据导出到报表系统，最后通过

页面将其展示出来，就完成了数仓的工作流程。

业务系统、Hive数仓、报表系统，三个系统之间数据的导入导出一般是使用Sqoop

来实现的。

了解了数仓设计的基本流程后,下面我们再来看一下数仓的设计说明：

•按照数据的日期创建分区表。之所以要创建分区表，是因为我们处理的是海量数据，

每天处理的数据量非常大,按日期创建分区表后便于数据的管理。

•数据进行压缩处理（snappy%同样是因为我们要处理的数据是海量数据，对数据

进行压缩处理后便于存储和传输。

•数据进行维度退化处理。在业务系统中有些表维度是三级，例如商品分类表和地域

表，在分析时要做维度退化处理。

再来看一下对数仓分层的说明：

•ODS层：与原始业务系统数据保持一致。

•DWD层：对ODS层数据进行过滤，对商品分类表和地域表进行维度退化处理。

•DWS层：对DWD层数据进行聚合，创建用户行为"宽表二

•ADS层：GMV指标分析。ADS层涉及的指标非常多，这里我们要实现的是一个

简易的电商系统，所以只分析GMV指标即可。

2.ODS层设计

了解了数仓的设计流程、设计说明和分层说明后下面我们来详细分析ODS层的设计。

首先我们来了解一下ODS层中包含的表，在该层中设计了订单表、订单明细表、商品

表、用户表、商品一级分类表、商品二级分类表、商品三级分类表、一级地域表、二级地域

表、三级地域表和支付流水表。

我们再通过如下所示的图来看一下ODS层的业务模型。

orderinfo

payment—inf。sku_lnfo

Kbii?irn(20)<ok>

user_idbigint(20)<akl>idbigint(20)<ok>hibi―nt(20)<Dk〉

spu.id

area3_idbigint(20)<ak2,fk>out_trade_novarchar(20)bigint(20)

pricedeciaaMlO,0)

consigneevarchar(lOO)order.idbigint(20)<akl,fk>

varchar(200)

consignee】elvarchar(20)user.idbigint(20)<ak2>

sku_descvarchar(2000)

total_amountdecimal(10.2)alipay_trade_novarchar(20)

weightdeciBal(10,2)

order_statusvarchar(20)total_amountdecimal(16.2)

tn.id

payment_wayvarchar(20)subjectvarchar(20)bigint(20)

category3,idbigint(20)

mentvarchar(200)payment.typevarchar(20)

sku_default_imgvarchar(200)

out_trade_novarchar(50)payment.tinevarchar(20)

createtinedatetiae

trade_bodyvarchar(200)

create_timeday

operate/imedatetiae

expire/imedatetiae

tracking_novarchar(100)

parent,ordejidbigint(20)base_area3

img_urlvarchar(200)idbitUn[(20>~Sk>biidnt(20)(Dk>

codebigint(20)

delivery_addressvarchar(1000)codebigint(20)<akl>

name

Colu«n19〈Undefined；'namevarchar(50)varchar(50)

areal.codebigint(20)<ak>

orderdetailarea2codebigint(20)zak2

bigint(20)〈Dk〉

orderidbigint(20)<akl>userinfobase_areal

sku.idbigint(20)<ak2：bitnntUO)idbigint(20)<Dk>

sku_naaevarchar(200)varchar(200)codebigint(20)<ak>

img_urlvarchar(200)nick_namevarchar(200)namevarchar(50)

order_pricedeciaaldO.2)passedvarchar(200)

skunumvarchar(200)namevarchar(200)

phone_numvarchar(200)

base_category3emailvarchar(200)

idbigint(20)<Dk)head_imgvarchar(200)

namevarchar(200)user_levelvarchar(200)dtvarchar(200)

category2idbigint(20)<ak.fk>birthdaydategnv-countbigint(20)

gendervarchar(1)gm\-_amountdecimal(16.2)

create]imedatetimegH5'paymentdecimal(16.2)

base_category2

bi£int(20)base_categoryl

namevarchar(200)idb】gint(20),.亦

categoryl_fdbigint(20)<ak.fknaaevarchar(100)

在该业务模型中核心的表有ordejinf。(订单表1paymeritJnfo(支付流水表)、

sku_info(商品表1order_detail(订单明细表\userJnfo(用户表1

下面我们逐一来介绍ODS层各表的详细设计。

(1)订单表

我们通过如下所示的图来了解一下订单表的详情。

NameCommentDataType-Length-

1id编号bigint(20)20

2userjd川户idbigint(20)20

3area3_idM域idbigint(20)20

4consignee收货人varchar(IOO)100

5consignee_tel收件人电话varchar(20)20

6total_amount总金沏decimal(10,2)10

7order_status订单状态varchar(20)20

8payment_way付款方式varchar(20)20

9order_comment订单备注varchar(200)200

10out_trade_no订单交易编号varchar(50)50

11trade_body订单描述varchar(200)200

12create_time创建时间datetime

13operate_time操作时间datetime

14expire_time失效时间datetime

15tracking_no物流单编号varchar(IOO)100

16parent_order_id父订单编号bigint(20)20

17img_url图片路径varchar(200)200

18delivery_address收货地址varchar(WOO)1,000

该表的主要字段如上面图所示，其中的用户id(userjd)关联的是用户表中的id,区

域id(area3_id)关联的是三级地域表中的id。

(2)订单明细表

再通过如下所示的图来了解一下订单明细表的详情。

NameCommentDataType-Length-

1id编号bigint(20)20

2order_id订单编号bigint(20)20

3skujd商品idbigint(20)20

4sku_name商品名称varchar(200)200

5img_ur1图片名称varchar(200)200

6order_price购买价格decimal(10,2)10

7sku_num购买数量varchar(200)200

该表的主要字段如上面图所示，其中的订单编号(ordejid)关联的是订单表中的id。

(3)商品表

通过如下所示的图来了解一下商品表的详情。

NameCommentDataType」Length二

1ididbigint(20)20

2spujdspuidbigint(20)20

3price价格decimal(10,0)10

4sku_name商品名称varchar(200)200

5sku_desc商品规格描述varchar(2000)2,000

6weight重量decimal(10,2)10

7tm_id品牌bigint(20)20

8category3_id二级分类idbigint(20)20

9sku_default_img默认图片varchar(200)200

10create_time创建时间datetime

该表的主要字段如上面图所示，其中的三级分类id(category3_id)关联的是商品三

级分类表中的id。

(4)用户表

通过如下所示的图来了解一下用户表的详情。

NameCommentDataType-Length-

1id编号bigint(20)20

2login_name川户名称varchar(200)200

3nick_name用户昵称varchar(200)200

4passwd用户密码varchar(200)200

5name川户姓名varchar(200)200

6phone_num手机号varchar(200)200

7email邮箱varchar(200)200

8headjmg头像varchar(200)200

9user_level川户级别varchar(200)200

10birthday用户生口date

11gender性别M男,F女varchar(1)1

12create_time创建时间datetime

该表的主要字段如上面图所示其中有几个名称需要区分一下用户名称;login_name),

是用户登录系统时使用的名称；用户昵称(nick_name),一般在页面上显示用户信息时显

示用户昵称；用户姓名(name),是用户的真实姓名。

(5)商品一级分类表

通过如下所示的图来了解一下商品一级分类表的详情。

NameCommentDataType-Length-

1id编号bigint(20)20

2name分类名称varchar(IOO)100

该表比较简单，只有编号(id)和分类名称(name)两个字段。

(6)商品二级分类表

通过如下所示的图来了解一下商品二级分类表的详情。

NameCommentDataType二Length-

1id编号bigint(20)20

2name:级分类名称varchar(200)200

3category1_id一级分类编号bigint(20)20

该表的主要字段如上面图所示，其中一级分类编号(categoryl_id)关联的是商品一

级分类表中的id。

(7)商品三级分类表

再通过如下所示的图来了解一下商品三级分类表的详情。

NameCommentDataType-Length'

1id编号bigint(20)20

2name二级分类名称varchar(200)200

3category2_id二级分类编号bigint(20)20

该表的主要字段如上面图所示，其中二级分类编号(category2_id)关联的是商品二

级分类表中的id。

(8)一级地域表

通过如下所示的来了解一下一级地域表的详情。

NameCommentDataType—Length二

1ididbigint(20)20

2code区域编码bigint(20)20

3name名称varchar(50)50

该表I：匕较简单，只有编号(id\区域编码(code)和名称(name)三个字段。

(9)二级地域表

通过如下所示的图来了解一下二级地域表的详情。

NameCommentDataType-Length'

1ididbigint(20)20

2code区域编码bigint(20)20

3name名称varchar(50)50

4areal_code上级编码bigint(20)20

该表中的上级编码(areal_code)关联的是一级地域表中的编码。

(10)三级地域表

通过如下所示的图来了解一下三级地域表的详情。

NameCommentDataType-Length-

1ididbigint(20)20

2code区域编码bigint(20)20

3name名称varchar(50)50

4area2_code上级编码bigint(20)20

该表中的上级编码(area2_code)关联的是二级地域表中的编码。

(11)支付流水表

通过如下所示的图来了解一下支付流水表的详情。

NameCommentDataType-Length'

1id编号bigint(20)20

2out_trade_no对外业务编号varchar(20)20

3order_id订单编号bigint(20)20

4userjd用户编号bigint(20)20

5alipay_trade_no支付宝交易流水编号varchar(20)20

6total_amount支付金领decimal(16.2)16

7subject交易内容varchar(20)20

8payment_type支付方式varchar(20)20

9paymentjime支付时间varchar(20)20

以上即为ODS层表的设计及表详情的介绍，大家对此要有一个总体的印象，在接下来

的内容中我们会通过实际操作来演示ODS层具体的创建过程。

3.ODS层创建演示

ODS层的创建可以按照以下步骤来实现：

•启动Hadoop集群；

启动Hive;

•创建Hive数据库；

•创建ODS层数据表；

•使用Sqoop将业务数据从MySQL导入到HDFS;

•使用Sqoop将HDFS数据导入到Hive。

下面我们按照上面的步骤来具体演示ODS层创建的过程。

(1)启动Hadoop集群

打开VMwareWorkstationPro,首先在左侧窗口中右键单击hadoopl,在快捷菜单

中选择"电源">"启动客户机"选项；然后在左侧窗口中单击hadoop2,在右侧窗口中

单击"开启此虚拟机"选项；继续在左侧窗口中单击hadoop3,在右侧窗口中单击”开启

此虚拟机"选项，如图所示，依次开启3台虚拟机。

(9hadoop?VMv/yeWorkstation-□X

WOriitdUcn*►*:Q8：二ni刁r-

慝x

hedooplIrtfMKkx^的h...■>

▼।«

-匚翔机依机||~|hadoopS

­।❹

hadoopl

一：

~hadoopSUtt

(5升Shit*用机

匕共方。

S3内存1GB

3处的1

hadoop3,打开3个窗口，在lhadoopl窗口中输入su-hadoop命令，按下Enter键,

切换到hadoop用户；再输入clear命令，按下Enter键清屏；输入start-dfs.sh命令，按

下Enter键，启动HDFS,如图所示。

[root@hadoopl-]#su-hadoop1

Lastlogin:ThuMay2815:32:48CST2020onpts/1

[hadoop@hadoopl-]$clear

ihadoopi

[hadoop@hadoopl-]$start-dfs.sh2

切换至（I2hadoop2窗口，同样输入su-hadoop命令，按下Enter键，切换到hadoop

用户；再输入clear命令，按下Enter键清屏；然后输入start-yarn.sh命令，按下Enter

键，启动Yarn,如图所示。

[hadoop@hadoop2-]$start-yarn.sh

startingyarndaemons

startingresourcemanager,loggingto/opt/module/hadoop-

hadoop3:startingnodemanager,loggingto/opt/module/hc

hadoopl:startingnodemanager,loggingto/opt/module/hc

hadoop2:startingnodemanager,loggingto/opt/module/hc

[hadoop(ahadoop2*]$|

返回lhadoopl窗口，输入jps命令，按下Enter键查看进程，如图所示。

[hadoop(dhadoopl~]$jps

1444NameNode

1948Jps

1581DataNode

1757NodeManager

[hadoo的hadoopl,

切换到2hadoop2窗口，同样输入jps命令，按下Enter键查看进程，如图所示。

[hadoop@hadoop2jps

1440NodeManager

1751Jps

1208DataNode

1320ResourceManager

[hadoop@hadoop2~]$|

切换至！J3hadoop3窗口，输入jps命令，按下Enter键查看进程，如图所示。

[root@hadoop3-]#jps

1188DataNode

1302NodeManager

1370SecondaryNameNode

1502Jps

[root@hadoop3-]#|

可见3台虚拟机的Hadoop都启动成功。

(2)启动Hive并创建数据库

切换回lhadoopl窗口，输入hive命令，按下Enter键，启动Hive,如图所示。

[hadoop@hadoopl-]$hive

Logginginitializedusingconfigurationinjar:file:/opt/modul

properties

SLF4J:ClasspathcontainsmultipleSLF4Jbindings.

SLF4J:Foundbindingin[jar:Tile:/opt/module/hadoop-2.7.2/shc

!/org/slf4j/impl/StaticLoggerBinder.class)

SLF4J:Foundbindingin[jar:file:/opt/module/tez/lib/sl.f4j-Ic

Binder.class]

SLF4J:See/codes.html#niultiple_bindings1

SLF4J:Actualbindingisoftype[org.slf4j.impl.Log4jLoggerFe

hive(default)>|

输入showdatabases;命令,按下Enter键，查看数据库，输出结果显示有default

和mall两个数据库，如图所示。

hive(default)>showdatabases;

OK

databasename

default

mall

Timetaken:0.992seconds,Fetched:2row(s)

hive(default)〉|

输入dropdatabasemallcascade;命令，按下Enter键执行,删除mall数据库,如

图所示。

hive(default)>dropdatabasemallcascade;

OK

Timetaken:0.725seconds

hive(default)〉|

输入createdatabasemall;命令，按下Enter键，重新创建数据库mall输入usemall;

命令，按下Enter键，使用mall数据库；再输入showtables;命令，按下Enter键，输出

结果显示mall数据库中当前没有数据表，如图所示。

hive(defaultAcreatedatabasemall;[

OK

Timetaken:0.128seconds

hive(default)〉usemall;9

OK

Timetaken:0.622seconds

hiveshowtables;

OK

tab_name

Timetaken:6.044seconds

hive|

至此，成功启动了Hive,并且重新创建了mall数据库。

(3)创建ODS层数据表

接下来创建ODS层数据表。相关的建表语句在hive_create.txt文件中已经提供了，右

键单击该文件，在弹出的快捷菜单中选择EditwithNotepad++选项，使用记事本编辑器

查看，如图所示。

8。

37而M一级分类衣

HR

：,droprableifexicdcbanec^tcgcry1;

?'?vrvdleexternal(

i-..../'7I,,一

*name*siringCOMMENT•名称，

)COMMKMT«图All•1分类，

，：PARTTTTONFGRY(dfstring!

IOWiornxxcdoliniltcdfieldsterminatedbv*\t*

location'/warehouae/raall/ods/odsbasecateqoryl/

以上面的建表语句为例来分析一下建表语