hive-526互联

hive测试步骤

1.在hive中建表，一个表存放原始数据，一个表存放清洗后的数据

create table sales_initial (

day_id varchar(30),

sale_nbr varchar(30),

buy_nbr varchar(30),

cnt varchar(30),

round varchar(30)

)

row format delimited

fields terminated by ',';

2.将csv文件拖入虚拟机，然后执行下边的语句，将csv文件数据导入hive建的表中

load data local inpath '/opt/module/hadoop-3.1.3/sales_initial.csv' into table sales_initial;

3.清洗日期

insert overwrite table sales_clean
select date_add('2023-8-31', cast(day_id AS INT)) as day_id,
       sale_nbr,
       buy_nbr,
       cnt,
       round
from sales_initial ;

4.根据题目要求输出的字段，建立对应的表

5.题目实现

题目3_1统计每天各个机场的销售数量和销售金额

insert into sales_3_1 (day_id, sale_nbr, cnt, round)
select day_id, sale_nbr, sum(cnt), sum(round)
from sales_clean
where sale_nbr like 'C%'
group by day_id, sale_nbr;

题目3_2统计每天各个代理商的销售数量和销售金额

insert into sales_3_2 (day_id, sale_nbr, cnt, round)

select day_id, sale_nbr, sum(cnt), sum(round)

from sales_clean

where sale_nbr like 'O%'

group by day_id, sale_nbr;

题目3_3统计每天各个代理商的销售活跃度

insert into sales_3_3(day_id, sale_nbr, sale_number)
select day_id, sale_nbr, count(sale_nbr)
from sales_clean
where sale_nbr like 'O%'
group by day_id, sale_nbr;

题目3_4汇总统计9月1日到9月15日之间各个代理商的销售利润

create table buy as select day_id, buy_nbr, sum(cnt) as cnt, sum(round) as round from sales_initial where buy_nbr like 'O%' and day_id >= '2023-09-01' AND day_id <= '2023-09-15' group by day_id, buy_nbr

create table sale as select day_id, sale_nbr, sum(cnt) as cnt , sum(round) as round from sales_initial where sale_nbr like 'O%' and day_id >= '2023-09-01' AND day_id <= '2021-09-15' group by day_id, sale_nbr

create table earned as select a.day_id as day_id, b.sale_nbr as nbr, a.cnt as cnt_buy, a.round as round_buy, b.cnt as cnt_sale, b.round as round_sale, b.round-a.round as money from buy a join sale b on a.buy_nbr = b.sale_nbr and a.day_id = b.day_id

6.将以上统计结果保存到本机的mysql中

实例:

bin/sqoop export \

--connect jdbc:mysql://localhost:3306/test \

--username root \

--password ******** \

--table sales_3_1 \

--export-dir /user/hive/warehouse/sales_3_1 \

--input-fields-terminated-by '\t'

数据表部分展示