hive数据分析

HIVE数据分析实验报告

 

1. 数据导入

 将csv文件导入到虚拟机的hive数据库中，并通过datagrip软件实现对数据库的可视化操作

 

1. 数据清洗

在datagrip中进行如下操作，将数值清洗为真实日期

insert overwrite table test2 select date_add('2021-09-00',cast(day_id as int)) as day_id,sale_nbr as sale_nbr,buy_nbr as buy_nbr,cnt as cnt,round as round from test;

3.

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

create table jieguo1(day_id string,sale_nbr string,

cnt string,round string)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

create table jieguo01(day_id string,sale_nbr string,

cnt string,round string)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

insert overwrite table jieguo1  select day_id as day_id ,sale_nbr as sale_nbr,sum(cnt) as cnt ,sum(round) as round from test2 where sale_nbr like 'C%'  group by day_id,sale_nbr;

 

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

create table jieguo02(day_id string,sale_nbr string,cnt string,round string)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

create table jieguo2(day_id string,sale_nbr string,

cnt string,round string)

ROW format delimited fields terminated by ',' STORED AS TEXTFILE;

insert overwrite table jieguo2  select day_id as day_id ,sale_nbr as sale_nbr,sum(cnt) as cnt ,sum(round) as round from test2 where sale_nbr like 'O%'  group by day_id,sale_nbr;

 

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

create table jieguo03(day_id String,sale_nbr String,sale_number String) ROW format delimited fields terminated by ',';

 insert overwrite table jieguo03 select day_id,sale_nbr,COUNT(*) as sale_number from sales3 group by day_id,sale_nbr having sale_nbr like 'O%';

 

（4）汇总统计9月1日到9月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 sales3 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 sales3 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);

 

以上代码均在datagrip软件中实现

 

4、处理结果入库

用datagrip分别连接hive数据库和mysql数据库，将hive数据库中的表复制到mysql数据库中

 

5、数据可视化展示

利用echarts工具进行数据的图形化显示，java部分代码如下

DataDAO.java

package Database;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

 

public class DataDAO {

 

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/t";

    private static final String JDBC_USER = "root";

    private static final String JDBC_PASSWORD = "123456";

 

    public List<DataPoint> getData() {

        List<DataPoint> dataPoints = new ArrayList<>();

        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);

            String query = "SELECT date, value FROM jieguo1";

            PreparedStatement preparedStatement = connection.prepareStatement(query);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                String date = resultSet.getString("date");

                String value = resultSet.getString("value");

                DataPoint dataPoint = new DataPoint(date, value);

                dataPoints.add(dataPoint);

            }

            resultSet.close();

            preparedStatement.close();

            connection.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return dataPoints;

    }

}

 

DataPoint.java

package Database;

 

public class DataPoint {

    private String date;

    private String value;

 

    public DataPoint(String date, String value) {

        this.date = date;

        this.value = value;

    }

 

    public String getDate() {

        return date;

    }

 

    public String getValue() {

        return value;

    }

}

 

Chart.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<%@ page import="Database.DataPoint" %>

<%@ page import="Database.DataDAO" %>

<%@ page import="java.util.List" %>

 

<!DOCTYPE html>

<html>

<head>

    <meta charset="UTF-8">

    <title>柱状图</title>

    <!-- 引入ECharts库 -->

    <script src="js/echarts.min.js"></script>

</head>

<body>

    <!-- Create a container for the bar chart -->

    <div id="barChart" style="width: 1000px; height: 600px;"></div>

   

    <%

        // 获取数据并转换为JSON格式

        DataDAO dataDAO = new DataDAO();

        List<DataPoint> dataPoints = dataDAO.getData();

        String dataJSON = "[";

        for (DataPoint dataPoint : dataPoints) {

            // 在这里，将日期作为柱状图的横坐标，值作为柱状图的数据

            dataJSON += "{'name': '" + dataPoint.getDate() + "', 'value': " + dataPoint.getValue() + "},";

        }

        dataJSON = dataJSON.substring(0, dataJSON.length() - 1) + "]";

    %>

   

    <script>

        var data = <%= dataJSON %>;

       

        // 使用ECharts绘制柱状图

        var myChart = echarts.init(document.getElementById('barChart'));

        var option = {

            title: {

                text: '柱状图'

            },

            xAxis: {

                type: 'category',

                data: data.map(item => item.name) // Use dates as category labels

            },

            yAxis: {

                type: 'value',

                name: 'Value'

            },

            tooltip: {

                trigger: 'axis',

                axisPointer: {

                    type: 'shadow'

                },

                formatter: '{b}: {c}'

            },

            series: [

                {

                    name: '数据',

                    type: 'bar',

                    data: data.map(item => item.value) // Use values for the bar heights

                }

            ]

        };

        myChart.setOption(option);

    </script>

</body>

</html>