hive测试数据洗刷

hive测试——HIVE数据分析

测试使用虚拟机中的hive

环境：虚拟机+jdk+hadoop+hive+mysql

题目：

1、数据导入：

   要求将样表文件中的（sales_sample_20170310）数据导入HIVE数据仓库中。

2、数据清洗：

   要求将day_id一列中的数值清洗为真实的日期格式，可用字符串表示。

      数据1对应日期2022-10-01，依次类推，15对应日期2022-10-15

3、数据分析处理：

   （1）统计每天各个机场的销售数量和销售金额。

要求的输出字段

day_id,sale_nbr,,cnt,round

日期编号，卖出方代码，数量，金额

   （2）统计每天各个代理商的销售数量和销售金额。

要求的输出字段

day_id,sale_nbr,,cnt,round

日期编号，卖出方代码，数量，金额

（3）统计每天各个代理商的销售活跃度。

要求的输出字段

day_id,sale_nbr, sale_number

日期编号，卖出方代码，交易次数（买入或者卖出均算交易次数）

（4）汇总统计10月1日到10月15日之间各个代理商的销售利润。

编号，卖出方代码，买入数量，买入金额，卖出数量，卖出金额，销售利润（卖出金额-买入金额）

4、处理结果入库：

   将上述统计分析的结果数据保存到mySQL数据库中。

5、数据可视化展示：

   利用Echarts将上述统计结果以图形化展示的方式展现出来：饼图、柱状图、地图、折线图等。

1、数据导入

#建表
Create table test2(day_id varchar(30), sale_nbr varchar(30), buy_nbr varchar(30), cnt varchar(30), round varchar(30)) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES

    (

        "separatorChar"=","

    )

STORED AS TEXTFILE;

#数据导入
load data local inpath '/export/software/sales.csv' into table test2;

2、数据清洗

#重新建一个表
Create table test3(day_id varchar(30), sale_nbr varchar(30), buy_nbr varchar(30), cnt varchar(30), round varchar(30)) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES

    (

        "separatorChar"=","

    )

STORED AS TEXTFILE;

#导入清洗后的数据
insert overwrite table test3 select date_add('2022-10-00',cast(day_id AS INT)) as day_id,sale_nbr,buy_nbr,cnt,round from test2;

3-1、统计每天各个机场的销售数量和销售金额

#建表
Create table text3_1(day_id varchar(30), sale_nbr varchar(30), cnt varchar(30), round varchar(30)) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES

    (

        "separatorChar"=","

    )

STORED AS TEXTFILE;

#数据导入

insert overwrite table text3_1

SELECT

  day_id,

  sale_nbr,

  SUM(cnt),

  SUM(round)

FROM

  test3

WHERE sale_nbr LIKE 'C%'

GROUP BY day_id,sale_nbr;

3-2、统计每天各个代理商的销售数量和销售金额

#建表
Create table text3_2(day_id varchar(30), sale_nbr varchar(30), cnt varchar(30), round varchar(30)) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES

    (

        "separatorChar"=","

    )

STORED AS TEXTFILE;

#数据导入

insert overwrite table text3_2

SELECT

  day_id,

  sale_nbr,

   SUM(cnt),

  SUM(round)

FROM

  test3

WHERE sale_nbr LIKE "O%"

GROUP BY day_id,sale_nbr;

3-3、统计每天各个代理商的销售活跃度

#建表
Create table text3_3(day_id varchar(30), sale_nbr varchar(30), sale_number varchar(30)) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES

    (

        "separatorChar"=","

    )

STORED AS TEXTFILE;

#导入数据

insert overwrite table text3_3

SELECT

  day_id,

  sale_nbr,

  COUNT(sale_nbr)

FROM

  test3

WHERE sale_nbr LIKE "O%"

GROUP BY day_id,sale_nbr;

3-4、汇总统计10月1日到10月15日之间各个代理商的销售利润（先将数据导入，然后重新建表，取出部分数据）

create table daiout as select day_id as day_id,sale_nbr as sale_nbr,sum(cnt)as cnt,sum(round)as round from test3 where sale_nbr like 'O%' group by day_id,sale_nbr;

create table daiin as select day_id as day_id,buy_nbr as buy_nbr,sum(cnt)as cnt,sum(round)as round from test3 where buy_nbr like 'O%' group by day_id,buy_nbr;

create table daili as select daiin.day_id as day_id,daiout.sale_nbr as sale_nbr,daiin.cnt as incnt,daiin.round as inround,daiout.cnt as outcnt,daiout.round as outround,daiout.round-daiin.round as li from daiin join daiout on (daiin.buy_nbr=daiout.sale_nbr);

create table profit(day_id varchar(200),sale_nbr VARCHAR(200),incnt double,inround double,outcnt double,outround double,li double);

insert overwrite table profit select * from daili where day_id between '2022-10-01' and '2022-10-15';