网约车大数据综合项目——数据分析Hive

第1关：Hive储存数据

编程要求

1. 在 hive 中创建数据库 trafficdata
2. 在 trafficdata 中创建 cancelorder 表，将撤销订单清洗后的数据集canceldata.txt导入 cancelorder 表中。注意：数据集所在位置：/data/workspace/myshixun/data/canceldata.txt，数据集文件字段之间以|分割，文件部分数据展示如下：1200DDCX3307|430104|湖南省长沙市岳麓区|17625076885092|2019-03-07 17:32:27|2019-03-07 17:38:33|2|5|未知1100YDYC423D|430602|湖南省岳阳市岳阳楼区|6665578474529331090|2019-03-07 17:28:46|2019-03-07 17:29:09|1|1|第三方接口取消shouyue|430100|湖南省长沙市|P190307171256186000|2019-03-07 17:12:55|2019-03-07 17:13:48|1|1|点击下单120S内没有筛选到司机时, 乘客手动点击取消订单
3. 在 trafficdata 中创建 createorder 表，将成功订单清洗后的数据集createorder.txt导入 createorder 表中。注意：数据集所在位置：/data/workspace/myshixun/data/createorder.txt，数据集文件字段之间以\t分割，文件部分数据展示如下：1200DDCX3307 431081 湖南省郴州市资兴市 17625036018008 2019-03-07 07:32:20 2019-03-07 07:32:20 S213(旧)|威狮轮胎 113.247606 25.968607 资兴市.|唐洞加油站 113.251180 25.9793031200DDCX3307 430111 湖南省长沙市雨花区 17625036099910 2019-03-07 07:31:39 2019-03-07 07:31:39 嘉盛华庭3期(西2门) 113.032280 28.162031 长沙东站树木岭货场 113.010107 28.1661971200DDCX3307 431122 湖南省永州市东安县 35194606833503 2019-03-07 07:32:05 2019-03-07 07:32:06 东安大道.|潇湘第一城南侧 111.327802 26.391911 东安县.人力资源和社会保障局 111.317184 26.395052

#开启Hadoop服务
start-all.sh
#Hive连接MySQl初始化
schematool -dbType mysql -initSchema
#进入hive
hive
#在 hive 中创建数据库 trafficdata 
create database trafficdata;
use trafficdata;
#在 trafficdata 中创建 cancelorder 表
create table cancelorder(companyid string,address string,districtname string,orderid string,ordertime string,canceltime string,operator string,canceltypecode string,cancelreason string) row format delimited fields terminated by '|';
#导入数据
load data local inpath '/data/workspace/myshixun/data/canceldata.txt' into table cancelorder;
#在 trafficdata 中创建 createorder 表
create table createorder(companyid string,address string,districtname string,orderid string,departtime string,ordertime string,departure string,deplongitude string,deplatitude string,destination string,destlongitude string,destlatitude string) row format delimited fields terminated by '\t';
#导入数据
load data local inpath '/data/workspace/myshixun/data/createdata.txt' into table createorder;

第2关：统计撤销订单中撤销理由最多的前 10 种理由

编程要求

1. 在 hive 数据库 trafficdata 中 数据库创建表 cancelreason 。使用 Hive SQL 来统计撤销订单中撤销理由最多的前 10 种理由（因撤销理由为未知的数据过多，统计时不包含撤销理由值未知的数据），并插入到 cancelreason表。
2. 使用 Sqoop 工具将 Hive 中cancelreason 表数据存放至 MySQL 数据库 trafficdata 的 cancelreason 表中。

#先检查是否存在cancelorder和createorder表，若没有则按照第1关方法进行创建
#在 hive 数据库 trafficdata 中  数据库创建表 cancelreason
create table cancelreason(cancelreason string,num int) row format delimited fields terminated by '\t';
#插入查询数据
insert into cancelreason  select cancelreason,count(*) num from cancelorder where cancelreason != '未知' group by cancelreason order by num desc limit 10;
#上传表
export table cancelreason to'/user/hadoop/cancelreason';
#另开命令行
#进入MySQL
mysql -h127.0.0.1 -uroot -p123123
#创建并连接数据库
create database trafficdata;
use trafficdata;
#创建cancelreason表
create table cancelreason(cancelreason varchar(255),num int not null);
#退出MySQL
exit;
#下载数据到MySQL表
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/cancelreason/data/000000_0' --table cancelreason --fields-terminated-by '\t';

第3关：查询出成功订单最多的 10 个行政区名

编程要求

1. 在 hive 数据库 trafficdata 中 数据库创建表 order_district 。使用 Hive SQL 来统计成功订单最多的 10 个行政区名，并插入到 order_district表。
2. 使用 Sqoop 工具将 Hive 中order_district 表数据存放至 MySQL 数据库 trafficdata 的 order_district 表中。

#先检查是否存在cancelorder和createorder表，若没有则按照第1关方法进行创建
#在 hive 数据库 trafficdata 中  数据库创建表 order_district
create table order_district(district string,num int) row format delimited fields terminated by '\t';
#插入查询数据
insert into order_district select districtname,count(*) num from createorder group by districtname order by num desc limit 10;
#上传表
export table order_district to '/user/hadoop/order_district';
#另开命令行
#进入MySQL
mysql -h127.0.0.1 -uroot -p123123
#连接数据库
use trafficdata;
#创建表
create table order_district(district varchar(255),num int not null);
#退出MySQL
exit;
#下载数据到MySQL表
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/order_district/data/000000_0' --table order_district --fields-terminated-by '\t';

第4关：查询湖南省各个市的所有订单总量

编程要求

1. 在 hive 数据库 trafficdata 中 数据库创建表 orderbycity 。使用 Hive SQL 来统计湖南省各个市的所有订单总量(利用 districtname 行政区字段查询各个市的所有订单总量 订单总量=撤销订单+成功订单。例如“湖南省长沙市岳麓区”与“湖南省长沙市雨花区”这些订单都是属于“长沙市”)，并插入到 orderbycity表。
2. 使用 Sqoop 工具将 Hive 中orderbycity 表数据存放至 MySQL 数据库 trafficdata 的 orderbycity 表中。

#先检查是否存在cancelorder和createorder表，若没有则按照第1关方法进行创建
#在 hive 数据库 trafficdata 中  数据库创建表 orderbycity
create table orderbycity(city string,num int) row format delimited fields terminated by '\t';
#插入查询数据
INSERT INTO orderbycity (city, num) SELECT '湖南省长沙市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省长沙市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省长沙市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省株洲市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省株洲市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省株洲市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省湘潭市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省湘潭市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省湘潭市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省衡阳市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省衡阳市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省衡阳市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省邵阳市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省邵阳市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省邵阳市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省岳阳市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省岳阳市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省岳阳市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省常德市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省常德市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省常德市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省张家界市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省张家界市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省张家界市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省益阳市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省益阳市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省益阳市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省娄底市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省娄底市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省娄底市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省郴州市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省郴州市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省郴州市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省永州市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省永州市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省永州市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省怀化市' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省怀化市%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省怀化市%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
INSERT INTO orderbycity (city, num) SELECT '湖南省湘西土家族苗族自治州' AS districtname, SUM(total_num) AS total_num FROM (SELECT districtname, SUM(num) AS total_num FROM (SELECT districtname, COUNT(*) AS num FROM cancelorder WHERE districtname LIKE '湖南省湘西土家族苗族自治州%' GROUP BY districtname UNION ALL SELECT districtname, COUNT(*) AS num FROM createorder WHERE districtname LIKE '湖南省湘西土家族苗族自治州%' GROUP BY districtname) AS combined GROUP BY districtname) AS final_result;
#上传表
export table orderbycity to '/user/hadoop/orderbycity';
#另开命令行
#进入MySQL
mysql -h127.0.0.1 -uroot -p123123
#连接数据库
use trafficdata;
#创建表
create table orderbycity(city varchar(255),num int not null);
#退出MySQL
exit;
#下载数据到MySQL表
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_1' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_2' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_3' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_4' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_5' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_6' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_7' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_8' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_9' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_10' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_11' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_12' --table orderbycity --fields-terminated-by '\t';
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/orderbycity/data/000000_0_copy_13' --table orderbycity --fields-terminated-by '\t';

第5关：统计湖南省当天的每分钟订单总数量

编程要求

1. 在 hive 数据库 trafficdata 中 数据库创建表 order_province_time 。使用 Hive SQL 来统计湖南省当天的每分钟订单总数量(查询出来的时间格式为 “yyyy-MM-dd HH:mm”如“2019-03-07 15:37”)，并插入到 order_province_time表。
2. 使用 Sqoop 工具将 Hive 中order_province_time表数据存放至 MySQL 数据库 trafficdata 的 order_province_time表中。

#先检查是否存在cancelorder和createorder表，若没有则按照第1关方法进行创建
#在 hive 数据库 trafficdata 中  数据库创建表 order_district
create table order_province_time(`time` string,num int) row format delimited fields terminated by '\t';
#插入查询数据
insert into order_province_time select datetime, sum(num) as total_num from (select date_format(ordertime, 'yyyy-MM-dd HH:mm') as datetime, count(ordertime) as num from cancelorder where districtname like '湖南省%' group by date_format(ordertime, 'yyyy-MM-dd HH:mm') union all select date_format(departtime, 'yyyy-MM-dd HH:mm') as datetime, count(ordertime) as num from createorder where districtname like '湖南省%' group by date_format(departtime, 'yyyy-MM-dd HH:mm')) as combined group by datetime order by datetime;
#上传表
export table order_province_time to'/user/hadoop/order_province_time';
#另开命令行
#进入MySQL
mysql -h127.0.0.1 -uroot -p123123
#连接数据库
use trafficdata;
#创建表
create table order_province_time(times varchar(255),num int not null);
#退出MySQL
exit;
#下载数据到MySQL表
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/user/hadoop/order_province_time/data/000000_0' --table order_province_time --fields-terminated-by '\t';

第6关：湖南省各市级当天各时间段订单总数量

编程要求

1. 在 hive 数据库 trafficdata 中 数据库创建表 order_city_hour 。使用 Hive SQL 来统计湖南省 2019-03-07 日各市级当天每小时订单总数量（以订单时间 ordertime 为依据），并插入到 order_city_hour表。

时间段处理：
时间段对应的数字[0点-1点）0[1点-2点）1[2点-3点）2......[22点-23点）22[23点-24点）23

1. 使用 Sqoop 工具将 Hive 中order_city_hour 表数据存放至 MySQL 数据库 trafficdata 的 order_city_hour表中。

#先检查是否存在cancelorder和createorder表，若没有则按照第1关方法进行创建
#在hive数据库中创建order_city_hour表
use trafiicdata;
create table order_city_hour(hours string,city string,num int) row format delimited fields terminated by '\t' stored as textfile ;
#筛选cancelorder表中湖南省XX市的数据，将其订单小时数和地区存入t1表
create table t1 as select date_format(ordertime,'H') hour,substr(districtname,1,instr(districtname,'\u5e02')) city from cancelorder where districtname like '湖南省%' and instr(districtname,'\u5e02')==6 or instr(districtname,'\u5e02')==7 ;
#筛选cancelorder表中湖南省XX自治州的数据，将其订单小时数和地区存入t2表
create table t2 as select date_format(ordertime,'H') hour,substr(districtname,1,instr(districtname,'\u5dde')) city from cancelorder where districtname like '湖南省%' and instr(districtname,'\u5dde')==13;
#筛选createorder表中湖南省XX市的数据，将其订单小时数和地区存入t3表
create table t3 as select date_format(ordertime,'H') hour,substr(districtname,1,instr(districtname,'\u5e02')) city from createorder where districtname like '湖南省%' and instr(districtname,'\u5e02')==6 or instr(districtname,'\u5e02')==7 ;
#筛选createorder表中湖南省XX自治州的数据，将其订单小时数和地区存入t4表
create table t4 as select date_format(ordertime,'H') hour,substr(districtname,1,instr(districtname,'\u5dde')) city from createorder where districtname like '湖南省%' and instr(districtname,'\u5dde')==13;
#将t1、t2、t3、t4表进行合并汇总到t1
insert into t1 select * from t2;
insert into t1 select * from t3;
insert into t1 select * from t4;
#同时根据hour和city分组计数，并将结果暂存到tt表中；
create table tt as select hour,city,count(*) num from t1 group by hour,city;
#将最终结果插入order_city_hour表
insert into order_city_hour select * from tt;
#另开命令行进入MySQL
mysql -h127.0.0.1 -uroot -p123123
#创建order_city_hour表
create table order_city_hour(hours varchar(255),city varchar(255),num int);
#退出MySQL
exit;
#将hive表中的数据导出到MySQL中的表
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/opt/hive/warehouse/trafficdata.db/order_city_hour' --table order_city_hour --fields-terminated-by '\t'

第7关：查询成功订单线路中出行次数最多的五条线路

编程要求

1. 在 hive 数据库 trafficdata 中 数据库创建表 orderline 。使用 Hive SQL 来统计成功订单线路中出行次数最多的五条线路（若线路经纬度而线路名一样，取这条线路出现最多次的经纬度），并插入到 orderline表。
2. 使用 Sqoop 工具将 Hive 中orderline 表数据存放至 MySQL 数据库 trafficdata 的 orderline 表中。

#先检查是否存在cancelorder和createorder表，若没有则按照第1关方法进行创建
#在hive数据库中创建orderline表
create table orderline(departure string,deplongitude string,deplatitude string,destination string,destlongitude string,destlatitude string,num int) row format delimited fields terminated by '\t' stored as textfile;
#将符合题意的数据插入到orderline表中
insert into orderline select departure,deplongitude,deplatitude,destination,destlongitude,destlatitude,count from (SELECT t2.name,departure,deplongitude,deplatitude,destination,destlongitude,destlatitude,t1.num,t2.num count,Row_Number() OVER (partition by t1.name order by t1.num) rank from (SELECT (case when departure <= destination then CONCAT(departure,"%%%",destination) else CONCAT(destination,"%%%",departure) end) as name,departure,deplongitude,deplatitude,destination,destlongitude,destlatitude,count(*) as num FROM createorder group by (case when departure <= destination then CONCAT(departure,"%%%",destination) else CONCAT(destination,"%%%",departure) end),departure,deplongitude,deplatitude,destination,destlongitude,destlatitude) as t1 right join (SELECT name ,count(name) as num from(SELECT (case when departure <= destination then CONCAT(departure,"%%%",destination) else CONCAT(destination,"%%%",departure) end) as name FROM createorder) as a GROUP BY name ORDER BY num DESC LIMIT 5) as t2 on t1.name=t2.name )as t where rank=1 order by count desc;
#另开命令行进入mysql命令行
mysql -h 127.0.0.1 -uroot -p123123
#在mysql数据库中创建orderline表
create table orderline(departure varchar(255),deplongitude varchar(255),deplatitude varchar(255),destination varchar(255),destlongitude varchar(255),destlatitude varchar(255),num int);
#退出mysql
exit;
#将hive数据库中的orderline表数据导出到mysql数据库的orderline中
sqoop export --connect jdbc:mysql://127.0.0.1:3306/trafficdata --username root --password 123123 --export-dir '/opt/hive/warehouse/trafficdata.db/orderline' --table orderline --fields-terminated-by '\t'