本章围绕乘用车辆和商用车辆销售数据展开，通过Hive构建数据仓库，实现对汽车销售数据各项指标进行立体化分析。
项目的数据来源于一个真实项目，数据项包括
1 山西省,3,朔州市,朔城区,2013,LZW6450PF,上汽通用五菱汽车股份有限公司,五菱,小型普通客车,个人,非营运,1,L3C,8424,79,汽油,4490,1615,1900,10,45,26,2,3050,1386,175/70R14LT,4,2110,1275,,7,,,,,上汽通用五菱汽车股份有限公司,客车,1913,男性
本节内容包括构建数据仓库、创建原始数据表、加载数据到数据仓库、验证数据结果以及具体的项目需求、设计思路、HiveSQL设计以及HiveSQL运行结果等内容。
1 2 3 4 5 6 7 8 hive (default )> create database if not exists cars; OK Time taken: 7.501 secondshive (default )> use cars; OK Time taken: 0.082 seconds
1 2 3 4 5 6 7 8 9 10 11 12 13 14 hive (cars)> create external table car_2019712(province string comment '省' , month int commet '月' , city string comment '市' , district string comment '区县' , year int comment '年' , modring comment '车辆型号' , manufacturer string comment '制造商' , brand string comment '品牌' , pe string comment '车辆类型' , ownership string comment '所有权' ,nature string comment '使用ity int comment ' 数量', enginemodel string comment ' 发动 > 机型号', displacement int comment ' 排量', power int comment ' 功率', fuel stringt ' 燃料种类', length1 int comment ' 车长', width1 int comment ' 车宽', height1 int comment ' 车h2 int comment '厢长' , width2 int comment '厢宽' , height2 int comment '厢高' , numberofaxles mment '轴数' , wheelbase int comment '轴距' , frontwheelbase int comment '前轮距' , tirespecifistring comment '轮胎规格' , tirenumber int comment '轮胎数' , totalquality int comment '总质量equality int comment ' 整备质量', approvedquality int comment ' 核定载质量', approvedpassengeromment ' 核定载客', tractionquality int comment ' 准牵引质量', chassisenterprise string commen chassisbrand string comment ' 底盘品牌', chassismodel string comment ' 底盘型号', engineenterring comment ' 发动机企业', vehiclename string comment ' 车辆名称', age int comment ' 年龄', ge comment ' 性别') comment ' this is the raw data' row format delimited fields terminated by ' ,location '/cars' ; OK Time taken: 0.766 secondshive (cars)> desc car_2019712; OK col_name data_type comment province string ? month int ? city string ? district string ?? year int ? ···
在HDFS上创建存放数据的目录/cars，如果/cars已存在，则无需创建
1 2 3 [root@master ~]# hadoop fs -ls / Found 6 items drwxr-xr-x - root supergroup 0 2021-12-04 17:54 /cars
将数据文档cars.csv利用Xshell上传到master的/root中
1 2 3 [root@master ~]# ll 总用量 171488 -rw-r--r--. 1 root root 20702466 12月 4 18:02 cars.csv
用Hadoop命令行方式将数据加载到Hive数据仓库监控的目录中
1 [root@master ~]# hadoop fs -put /root/cars.csv /cars
1 2 3 4 5 6 7 hive (cars)> select * from cars.car_2019712 limit 3 ; OK car_2019712.province car_2019712.month car_2019712.city car_2019712.districcar_2019712.year car_2019712.model car_2019712.manufacturer car_2019712.brand car_2019712.vehicletype car_2019712.ownership car_2019712.nature car_2019712.quantitcar_2019712.enginemodel car_2019712.displacement car_2019712.power car_2019712.fuel car_2019712.length1 car_2019712.width1 car_2019712.height1 car_2019712.length2 car_2019712.width2 car_2019712.height2 car_2019712.numberofaxles car_2019712.wheelbase car_2019712.frontwheelbase car_2019712.tirespecification car_2019712.tirenumber car_2019712.totalquality car_2019712.completequality car_2019712.approvedquality car_2019712.approvedpassenger car_2019712.tractionquality car_2019712.chassisenterprise car_2019712.chassisbrand car_2019712.chassismodel car_2019712.engineenterprise car_2019712.vehiclename car_2019712.age car_2019712.gender 山西省 3 朔州市 朔城区 2013 LZW6450PF 上汽通用五菱汽车股份有限公司 五菱小型普通客车 个人 非营运 1 L3C 8424 79 汽油 4490 1615 19010 45 26 2 3050 1386 175 / 70 R14LT 4 2110 1275 NULNULL 上汽通用五菱汽车股份有限公司 客车 1913 男性 山西省 3 晋城市 城区 2013 EQ6450PF1 东风小康汽车有限公司 东风 小型普通客车 个人 非营运 1 DK13-06 1587 74 汽油 4500 1680 19610 45 26 2 3050 1435 185 R14LT 6 PR 4 1970 1290 NULNULL 东风小康汽车有限公司 EQ6440KMF 重庆渝安淮海动力有限公司 客.1929 男性 山西省 12 长治市 长治城区 2013 BJ6440BKV1A 北汽银翔汽车有限公司 北京小型普通客车 个人 非营运 1 BJ415A 1500 75 4440 NULL NUL10 45 26 NULL NULL NULL NULL NULL NULL NULL NULL 北汽银翔汽车有限公司 北京 BJ6440BKV1A 北汽银翔汽车有限公司 1938 男性 Time taken: 2.895 seconds, Fetched: 3 row (s)
根据字段“nature”即“使用性质”来分组统计乘用车辆和商用车辆的总数量，乘用车辆的使用性质为“非营运”，商用车辆的使用性质为“营运”
1 2 3 4 5 6 7 8 9 hive (cars)> select '非营运' , sum (if(a.nature= '非营运' ,a.cnt,0 )),'营运' ,sum (if(a.nature!= '非t,0)) from (select nature, count(*) as cnt from cars.car_2019712 group by nature having nature is not null and nature!='') a; Job running in-process (local Hadoop) 2021-12-04 18:06:17,810 Stage-1 map = 100%, reduce = 0% 2021-12-04 18:06:18,832 Stage-1 map = 100%, reduce = 100% Ended Job = job_local1330125293_0001 OK _c0 _c1 _c2 _c3 非营运 66478 营运 3884 Time taken: 6.564 seconds, Fetched: 1 row(s)
统计出汽车销售总数量
1 2 3 4 5 6 7 hive (cars)> select count (* ) from cars.car_2019712; Job running in - process (local Hadoop) 2021 -12 -04 18 :07 :45 ,241 Stage-1 map = 100 % , reduce = 100 % Ended Job = job_local445756713_0003 OK _c0 70640
计算乘用车辆的销售数量占比
计算商用车辆的销售数量占比
分别统计出山西省2013年每个月的汽车销售数量和山西省2013年年度汽车销售总数量，再用2013年每个月的汽车销售数量除以年度汽车销售总数量。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 hive (cars)> set hive.strict.checks.cartesian.product= false ; hive (cars)> select month ,c1.ss/ c2.sumshu from (select month , sum (quantity) as ss from cars.car_2019712 where province= '山西省' and year = '2013' group by month ) c1, (select sum (quantity) as sumshu from cars.car_2019712 where province= '山西省' and year = '2013' ) c2; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 124235252 HDFS Write: 12420 SUCCESS Stage- Stage-3 : HDFS Read: 165660827 HDFS Write: 39149 SUCCESS Stage- Stage-4 : HDFS Read: 82838654 HDFS Write: 25114 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK month _c11 0.14799181376311077 2 0.05831272561894204 3 0.09306159574770473 4 0.06587362496802251 5 0.0732071288479577 6 0.05547028225462608 7 0.06323015263920867 8 0.06378442909525028 9 0.06948352804070379 10 0.1044882180722549 11 0.10053722179585571 12 0.1045592791563628 Time taken: 24.092 seconds, Fetched: 12 row (s)
分别统计出买车的男性人数、女性人数和总人数，再分别用男性人数和女性人数除以购车总人数。
统计买车的男性占比和女性占比
1 2 3 4 5 6 7 8 9 hive (cars)> select '男性' , a.nan* 1.0 / (a.nan+ a.nv),'女性' , a.nv* 1.0 / (a.nan+ a.nv) from (selec性', sum(if(b.gender=' 男性',b.cnt,0)) as nan, ' 女性', sum(if(b.gender=' 女性',b.cnt,0)) as nvselect gender, count(*) as cnt from cars.car_2019712 group by gender having gender is not null and gender!= '') b)a; MapReduce Jobs Launched: Stage-Stage-1: HDFS Read: 207083230 HDFS Write: 50347 SUCCESS Stage-Stage-2: HDFS Read: 207092711 HDFS Write: 64901 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK _c0 _c1 _c2 _c3 男性 0.70106593239522273 女性 0.29893406760477727 Time taken: 4.188 seconds, Fetched: 1 row(s)
分析男性和女性对车的品牌的选择情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 hive (cars)> select gender,brand,count (* ) from cars.car_2019712 where gender is not null and gender!= '' and age is not null group by gender,brand having brand is not null and brand!= '' ; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 248503192 HDFS Write: 68889 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK gender brand _c2 女性 一汽佳星 1 男性 一汽佳星 2 女性 东南 1 男性 东南 12 女性 东风 1367 男性 东风 3214 女性 中誉 4 男性 中誉 2 男性 中通 1 女性 五菱 12004 男性 五菱 28208 女性 五菱宏光 1057 男性 五菱宏光 2331 女性 众泰 2 男性 众泰 6 女性 依维柯 32 男性 依维柯 64 女性 俊风 1 男性 俊风 4 女性 力帆 27 男性 力帆 84 女性 北京 741 男性 北京 1836 男性 合客 2 女性 吉奥 12 男性 吉奥 30 男性 同心 1 女性 哈飞 4 男性 哈飞 7 女性 大通 7 男性 大通 31 女性 大马 3 男性 大马 7 女性 奥路卡 125 男性 奥路卡 277 女性 宇通 6 男性 宇通 7 女性 少林 28 男性 少林 72 女性 尼桑 2 男性 尼桑 2 女性 开瑞 89 男性 开瑞 231 女性 恒通客车 2 男性 恒通客车 2 女性 昌河 20 男性 昌河 75 女性 昌河铃木 3 男性 昌河铃木 1 女性 松花江 25 男性 松花江 86 男性 柯斯达 6 男性 梅赛德斯- 奔驰 1 女性 欧诺 121 男性 欧诺 239 男性 汇众 3 女性 江淮 7 男性 江淮 13 女性 江铃全顺 84 男性 江铃全顺 200 女性 海格 1 男性 海格 1 女性 神剑 6 男性 神剑 16 女性 福田 17 男性 福田 49 女性 航天 31 男性 航天 93 女性 解放 96 男性 解放 242 女性 通家福 5 男性 通家福 19 女性 野马 8 男性 野马 20 女性 金旅 7 男性 金旅 6 女性 金杯 102 男性 金杯 265 女性 金龙 16 男性 金龙 26 女性 长城 2 男性 长城 18 女性 长安 1628 男性 长安 3679 男性 飞碟 3 男性 黄海 2 Time taken: 3.112 seconds, Fetched: 86 row (s)
统计买车的所有权
1 2 3 4 5 6 7 8 9 10 11 12 hive (cars)> select ownership,count (* ) as cnt from cars.car_2019712 group by ownership order by cnt desc ; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 289915770 HDFS Write: 72901 SUCCESS Stage- Stage-2 : HDFS Read: 289923419 HDFS Write: 83320 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK ownership cnt 个人 60745 单位 9617 NULL 273 5 Time taken: 4.611 seconds, Fetched: 4 row (s)
统计车辆型号
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 hive (cars)> select model,count (* ) as cnt from cars.car_2019712 group by model order by cnt desc limit 10 ; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 331331684 HDFS Write: 114251 SUCCESS Stage- Stage-2 : HDFS Read: 331400570 HDFS Write: 155657 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK model cnt LZW6376NF 13727 LZW6407BAF 6357 LZW6390QF 5967 LZW6388NF 5120 LZW6432KF 4097 LZW6431MF 3622 EQ6361PF6 2509 SC6363B4S 2234 LZW6430KF 1484 LZW6390NF 1396 Time taken: 4.495 seconds, Fetched: 10 row (s)
统计车辆类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 hive (cars)> select vehicletype,count (* ) as cnt from cars.car_2019712 group by vehicletype order by cnt desc limit 10 ; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 372809322 HDFS Write: 156530 SUCCESS Stage- Stage-2 : HDFS Read: 372817774 HDFS Write: 167935 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK vehicletype cnt 小型普通客车 62156 3276 大型普通客车 3275 中型普通客车 1398 NULL 273 大型专用校车 221 中型专用校车 29 小型专用客车 5 大型铰接客车 3 微型普通客车 2 Time taken: 4.15 seconds, Fetched: 10 row (s)
统计所有权、车辆型号和车辆类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 hive (cars)> select a.cnt,count (* ) from (select concat(model,ownership,vehicletype) as cnt from cars.car_2019712) a group by a.cnt; Stage- Stage-1 : HDFS Read: 455821028 HDFS Write: 358380 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK a.cnt _c1 NULL 273 5 BFC6120B-1 单位大型普通客车 7 BFC6120B2单位大型普通客车 1 BFC6900-2 单位大型普通客车 8 BJ6102U8LHB单位大型普通客车 1 BJ6103U7MHB单位大型普通客车 1 BJ6103U8LHB-4 单位大型普通客车 3 BJ6110U7LCB-1 单位大型普通客车 6 BJ6115U7AJB-1 单位大型普通客车 1 ... ZQ6420A73F个人小型普通客车 63 ZQ6420A73F单位小型普通客车 8 ZQ6421A73AF个人小型普通客车 17 ZQ6421A73AF单位小型普通客车 3 ZZY6530A个人小型普通客车 6 ZZY6530A单位小型普通客车 2 Time taken: 1.686 seconds, Fetched: 1290 row (s)
统计不同类型车在一个月的总销量。也就是统计某一年某一个月某一类型的车，如大型普通客车、小型普通客车等，销售了多少辆。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 hive (cars)> select month ,vehicletype,count (* ) from cars.car_2019712 group by vehicletype, month having month is not null and vehicletype is not null and vehicletype!= '' ; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 497227388 HDFS Write: 362693 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK month vehicletype _c21 中型普通客车 157 1 大型专用校车 4 1 大型普通客车 529 ... 12 大型专用校车 8 12 大型普通客车 482 12 小型普通客车 6701 Time taken: 2.054 seconds, Fetched: 64 row (s)
通过不同类型（品牌）车销售情况，统计发动机型号和燃料种类。也就是按车辆的品牌字段brand、发动机型号字段enginemodel和燃油种类字段fuel进行分组统计。
1 2 3 4 5 6 7 8 9 10 11 hive (cars)> select brand,enginemodel,fuel,count (* ) from cars.car_2019712 group by brand,enginemodel,fuel; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 580122636 HDFS Write: 444919 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK brand enginemodel fuel _c3 ... 黄海 YC6J245-30 柴油 17 黄海 YC6J245-42 柴油 2 黄海 YC6L330-30 柴油 1 Time taken: 1.765 seconds, Fetched: 757 row (s)
统计五菱汽车某一年每月的销售量，可以按汽车的品牌brand和月month两个字段进行分组统计，并从结果中将品牌名为“五菱”的数据过滤出来即可。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 hive (cars)> select brand,month ,count (* ) from cars.car_2019712 group by brand,month having brand= '五菱' ; MapReduce Jobs Launched: Stage- Stage-1 : HDFS Read: 621528450 HDFS Write: 445834 SUCCESS Total MapReduce CPU Time Spent: 0 msec OK brand month _c2 五菱 1 5589 五菱 2 2226 五菱 3 3557 五菱 4 2389 五菱 5 3351 五菱 6 2302 五菱 7 2893 五菱 8 2980 五菱 9 3422 五菱 10 5278 五菱 11 4809 五菱 12 4963 Time taken: 1.863 seconds, Fetched: 12 row (s)
本章通过汽车销售数据项目让大家感受了企业大数据项目的开发流程。
