这次测试的流程主要分为五个步骤。
1、数据导入
2、数据清洗
3、数据分析处理
4、Sqoop导入Hive数据到MySQL
5、通过javaweb+echarts进行数据可视化
一、数据导入：
建表：
create table sales_sample_20170310 (
`day_id` string comment '时间',
`sale_nbr` string comment '卖出方',
`buy_nbr` string comment '买入方',
`cnt` int comment '数量',
`round` int comment '金额')
row format delimited
fields terminated by ','
lines terminated by '\n';
csv数据导入：
load data local inpath '/root/hivedata/sales_sample_20170310.csv' into table sales_sample_20170310;
导入成功后验证
select * from sales_sample_20170310 limit 10000;
二、数据清洗
要求将day_id一列中的数值清洗为真实的日期格式，可用字符串表示。数据1对应日期2023-10-01，依次类推，15对应日期2023-10-15
我这里通过Hive内置的函数，通过拼接字符串的形式来写，最后再转换日期格式，就可以完成了。
字符串拼接：
insert overwrite table sales_sample_20170310select concat('2023-10-',day_id),sale_nbr,buy_nbr,cnt,round from sales_sample_20170310 ;
转换格式：
create table sales_sample111 as
select to_date(from_unixtime(UNIX_TIMESTAMP(day_id,'yyyy-MM-dd'))) as day_id,
sale_nbr,
buy_nbr,
cnt,
round
from sales_sample_20170310;
验证：
select * from sales_sample111 limit 1000;
三、数据统计分析
在这里处理的数据100多Mb，文件很小，大约500万条数据。如果处理sql的执行时间超过15分钟，基本可以直接停止执行了，绝对是SQL写错了。检查SQL的限制字段或者分组字段重新查询。
我就有一个SQL写错了，处理出了10GB的数据，很离谱。
①统计每天各个机场的销售数量和销售金额。
-- 统计航空公司create table sale_hangkong asselect
day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sales_sample_20170310 where sale_nbr like 'C%' group by day_id, sale_nbr;
结果验证：
select * from sales_hangkong1 limit 1000;
②统计每天各个代理商的销售数量和销售金额。
create table day_sale as
select
day_id,
sale_nbr,
sum(cnt) as cnt_max,
sum(round) as round_max
from sales_sample111
where day_id between '2023-10-01' and '2023-10-20'
group by sale_nbr,day_id;
结果验证：
select * from day_sale limit 1000;
③统计每天各个代理商的销售活跃度。
create table huoyuedu as
select
day_id,
sale_nbr,
count(*) as sale_number
from sales_sample111
where day_id between '2023-10-01' and '2023-10-20'
group by sale_nbr,day_id;
结果验证：
select * from huoyuedu limit 1000;
④汇总统计10月1日到10月15日之间各个代理商的销售利润。
这个最后要求的表字段较多，需要先建立买入表和卖出表进行辅助。最后通过两表连接查询做出利润表。
计算代理商买入数量金额创建买入表
--计算代理商买入数量金额drop table mairu;create table mairu as
select
day,
buy_nbr,
sum(cnt) as cnt,
sum(round) as round
from sales_sample111
where buy_nbr like 'O%'group by day, buy_nbr;
select * from mairu limit 1000;
计算代理商卖出数量金额创建卖出表
create table maichu as
select
day,
sale_nbr,
sum(cnt) as cnt,
sum(round) as round
from sales_sample111
where sale_nbr like 'O%'group by day, sale_nbr;
select * from maichu limit 1000;
计算利润建立利润表
create table lirun as
select a.day as day,
b.sale_nbr as nbr,
a.cnt as cnt_buy,
a.round as rount_buy,
b.cnt as cnt_sale,
b.round as round_sale,
b.round-a.round as liren
from mairu a join maichu b on a.buy_nbr = b.sale_nbr and a.day = b.day
where a.day between '2023-10-01' and '2023-10-15';
select * from lirun limit 1000;
到这里就完成了第三阶段，第四阶段需要Sqoop安装部署，具体安装可以去bili搜索或者博客查看。
四、Sqoop数据导出
Sqoop进行数据导出的时候，虚拟机的Mysql必须提前建表。
①航空公司表导出
MySQL建表
--mysqlcreate table sale_hangkong(
day_id varchar(50) not null ,
sale_nbr varchar(20),
cnt int ,
round int
);
导出
bin/sqoop export \--connect jdbc:mysql://node1:3306/testdb \
--username root \
--password hadoop \
--table sale_hangkong \
--columns day_id,sale_nbr,cnt,round \
--export-dir /user/hive/warehouse/wjb.db/sales_hangkong \
--input-fields-terminated-by "\001"
倒数第二行是自己的hive数据库文件的位置
倒数第三行是MySQL表的字段名
倒数第一行是选择数据字段的分隔符，hive默认是\001
②代理商每天销售表
MySQL建表
create table day_sale(
day_id varchar(20) not null ,
sale_nbr varchar(20),
cnt_sum int ,
round_sum int
);
导出
bin/sqoop export \--connect jdbc:mysql://node1:3306/testdb \
--username root \
--password hadoop \
--table day_sale \
--columns day_id,sale_nbr,cnt_sum,round_sum \
--export-dir /user/hive/warehouse/wjb.db/day_sale \
--fields-terminated-by "\001" \
--input-null-non-string '\\N'
③活跃度
MySQL建表
create table huoyuedu(
day_id varchar(50) ,
sale_nbr varchar(20),
sale_number int
);
导出
bin/sqoop export \--connect jdbc:mysql://node1:3306/testdb \
--username root \
--password hadoop \
--table huoyuedu \
--columns day_id,sale_nbr,sale_number \
--export-dir /user/hive/warehouse/wjb.db/huoyuedu \
--fields-terminated-by "\001" \
--input-null-non-string '\\N'
④利润
MySQL建表
--mysqlcreate table lirun(
day_id varchar(50) ,
nbr varchar(20),
cnt_buy int,
rount_buy int,
cnt_sale int,
round_sale int,
lirun int
);
导出
bin/sqoop export \--connect jdbc:mysql://node1:3306/testdb \
--username root \
--password hadoop \
--table lirun \
--columns day_id,nbr,cnt_buy,rount_buy,cnt_sale,round_sale,lirun \
--export-dir /user/hive/warehouse/wjb.db/lirun \
--fields-terminated-by "\001" \
--input-null-non-string '\\N'
五、数据可视化
