这次的hive测试的流程主要分为以下几个步骤
1、数据导入到hive数据库里
2、对数据清洗
3、数据分析处理
4、将处理后的数据导出到MySQL
5、 通过网页进行数据可视化
一．数据导入到hive数据库里
1）建立初始表
create table sales_sample_20214044 ( `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';
2）将数据导入到表中
load data local inpath '/root/hivedata/sales_sample_20170310.csv' into table sales_sample_20214044;
二、 对数据清洗
将id转换成真是日期
insert overwrite table sales_sample_20214044
select concat('2022-09-',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_20214044;
三、数据分析处理
1）统计每天各个机场的销售数量和销售金额
create table sale_hangkong as
select
day_id,sale_nbr,sum(cnt) as cnt,sum(round) as round from sales_sample111 where sale_nbr like 'C%' group by day_id, sale_nbr;
2）统计每天各个代理商的销售数量和销售金额。
create table day_sale as
select
day_id,
sale_nbr,
sum(cnt) as cnt_max,
sum(round) as round_max
from sales_sample111
where sale_nbr like 'O%'
group by sale_nbr,day_id;
3）统计每天各个代理商的销售活跃度。
create table huoyuedumaichu as
select
day_id,
sale_nbr,
count(*) as sale_number
from sales_sample111
where sale_nbr like 'O%'
group by sale_nbr,day_id;
create table huoyuedumairu as
select
day_id,
buy_nbr,
count(*) as sale_number
from sales_sample111
where buy_nbr like 'O%'
group by buy_nbr,day_id;
create table huoyuedu as
select
a.day_id as day_id,
a.sale_nbr as sale_nbr,
a.sale_number+h.sale_number as sale_number
from huoyuedumaichu a join huoyuedumairu h on a.day_id = h.day_id and a.sale_nbr=h.buy_nbr;
4）汇总统计10月1日到10月15日之间各个代理商的销售利润。
--计算代理商买入数量金额
drop table mairu;
create table mairu as
select
day_id,
buy_nbr,
sum(cnt) as cnt,
sum(round) as round
from sales_sample111
where buy_nbr like 'O%'
group by day_id, buy_nbr;
create table maichu as
select
day_id,
sale_nbr,
sum(cnt) as cnt,
sum(round) as round
from sales_sample111
where sale_nbr like 'O%'
group by day_id, sale_nbr;
create table lirun as
select a.day_id as day_id,
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_id = b.day_id
where a.day_id between '2022-10-01' and '2022-10-15';
四、将处理后的数据导出到MySQL
1）统计每天各个机场的销售数量和销售金额
建表
create 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/myhive\
--username root \
--password 123456 \
--table sale_hangkong \
--columns day_id,sale_nbr,cnt,round \
--export-dir /user/hive/warehouse/myhive.db /sales_hangkong \
--input-fields-terminated-by "\001"
2）统计每天各个代理商的销售数量和销售金额。
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/myhive \
--username root \
--password 123456 \
--table day_sale \
--columns day_id,sale_nbr,cnt_sum,round_sum \
--export-dir /user/hive/warehouse/myhive.db/day_sale \
--fields-terminated-by "\001" \
--input-null-non-string '\\N'
3）统计每天各个代理商的销售活跃度。
create table huoyuedu(
day_id varchar(50) ,
sale_nbr varchar(20),
sale_number int
);
bin/sqoop export \
--connect jdbc:mysql://node1:3306/myhive \
--username root \
--password 123456 \
--table huoyuedu \
--columns day_id,sale_nbr,sale_number \
--export-dir /user/hive/warehouse/myhive.db/huoyuedu \
--fields-terminated-by "\001" \
--input-null-non-string '\\N'
4）汇总统计10月1日到10月15日之间各个代理商的销售利润。
--mysql
create 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/myhive \
--username root \
--password 123456 \
--table lirun \
--columns day_id,nbr,cnt_buy,rount_buy,cnt_sale,round_sale,lirun \
--export-dir /user/hive/warehouse/myhive.db/lirun \
--fields-terminated-by "\001" \
--input-null-non-string '\\N'
五、通过网页进行数据可视化
通过srpingboot+vue,整合echarts实现的表格可视化展示
标签：cnt,--,nbr,sale,hive,day,测试,id From： https://www.cnblogs.com/qwb0614/p/17727749.html