Spark SQL 笔记(15)——实战网站日志分析（5）数据可视化

最新推荐文章于 2024-01-28 14:34:02 发布

一角残叶

最新推荐文章于 2024-01-28 14:34:02 发布

阅读量555

点赞数

分类专栏： # 进入Spark SQL 世界学习笔记文章标签：日志分析数据可视化

本文链接：https://blog.csdn.net/u012292754/article/details/84109336

版权

进入Spark SQL 世界学习笔记专栏收录该内容

20 篇文章 4 订阅

订阅专栏

1 常见的可视化框架

echarts
highcharts
d3.js
HUE
Zeppelin

2 创建 Web 项目

在这里插入图片描述

下载Echarts的文件放到此目录

http://echarts.baidu.com/download.html

在这里插入图片描述

3 饼图测试

http://www.echartsjs.com/examples/editor.html?c=pie-simple
在这里插入图片描述
test.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Echarts HelloWorld</title>

    <!-- 引入 ECharts 文件 -->
    <script src="js/echarts.min.js"></script>
</head>
<body>
    <!-- 为 ECharts 准备一个具备大小（宽高）的 DOM -->
    <div id="main" style="width: 600px;height:400px;"></div>

    <script type="text/javascript">
        // 基于准备好的dom，初始化echarts实例
        var myChart = echarts.init(document.getElementById('main'));
        var option = {
            title : {
                text: '某站点用户访问来源',
                subtext: '纯属虚构',
                x:'center'
            },
            tooltip : {
                trigger: 'item',
                formatter: "{a} <br/>{b} : {c} ({d}%)"
            },
            legend: {
                orient: 'vertical',
                left: 'left',
                data: ['直接访问','邮件营销','联盟广告','视频广告','搜索引擎']
            },
            series : [
                {
                    name: '访问来源',
                    type: 'pie',
                    radius : '55%',
                    center: ['50%', '60%'],
                    data:[
                        {value:335, name:'直接访问'},
                        {value:310, name:'邮件营销'},
                        {value:234, name:'联盟广告'},
                        {value:135, name:'视频广告'},
                        {value:1548, name:'搜索引擎'}
                    ],
                    itemStyle: {
                        emphasis: {
                            shadowBlur: 10,
                            shadowOffsetX: 0,
                            shadowColor: 'rgba(0, 0, 0, 0.5)'
                        }
                    }
                }
            ]
        };

        myChart.setOption(option)


    </script>
</body>
</html>

3.1 配置一个 tomcat

在这里插入图片描述

3.2 测试

http://localhost:8081/test.html

4 饼图动态化展示

4.1 修改 POM

 <dependencies>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
    </dependency>

    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.0</version>
    </dependency>

      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.38</version>
      </dependency>

      <dependency>
          <groupId>net.sf.json-lib</groupId>
          <artifactId>json-lib</artifactId>
          <version>2.4</version>
          <classifier>jdk15</classifier>
      </dependency>

  </dependencies>

4.2 新建 java 文件夹

改为 src 目录
在这里插入图片描述

4.3 源码

4.3.1 `MySQLUtils.java`

package com.weblog.util;

import java.sql.*;

public class MySQLUtils {
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    private static final String DRIVERCLASS = "com.mysql.jdbc.Driver";

    private static final String URL = "jdbc:mysql://localhost:3306/weblog";

    public static Connection getConnection() {
        Connection connection = null;

        try {
            Class.forName(DRIVERCLASS);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return connection;
    }

    public static void release(Connection connection, PreparedStatement pstm, ResultSet rs){
        if( rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if( connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if( pstm != null){
            try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args){
        System.out.println(getConnection());
    }
}

4.3.2 `VideoAccessTopN.java`

package com.weblog.domain;

public class VideoAccessTopN {
    private String name;
    private long value;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public long getValue() {
        return value;
    }

    public void setValue(long value) {
        this.value = value;
    }
}

4.3.3 `VideoAccessTopNDAO.java`

package com.weblog.dao;

import com.weblog.domain.VideoAccessTopN;
import com.weblog.util.MySQLUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/*
 * 面向接口编程
 * */
public class VideoAccessTopNDAO {

    static Map<String,String> course = new HashMap<>();

    static{
        course.put("4000","Mysql入门");
        course.put("4500","Java基础");
        course.put("4600","Scala入门");
        course.put("14540","JavaWeb");
        course.put("14704","机器学习");
        course.put("14390","大数据入门");
        course.put("14322","Hadoop实战");
        course.put("14623","C++数据结构");

    }

    /*
    * 根据课程编号获取课程名字
    * */
    public String getCourseName(String id){
        return course.get(id);
    }


    /*
     * 根据 day 查询当天最受欢迎的 Top5 课程
     * */
    public List<VideoAccessTopN> query(String day) {
        List<VideoAccessTopN> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            connection = MySQLUtils.getConnection();
            String sql = "select cms_id,times from day_video_access_topn_stat where day=? order by times desc limit 5";
            psmt = connection.prepareStatement(sql);
            psmt.setString(1, day);

            rs = psmt.executeQuery();

            VideoAccessTopN domain = null;
            while (rs.next()) {
                domain = new VideoAccessTopN();

                //TODO...在页面上应该显示的是课程名称，此时拿到的是编号
                domain.setName(getCourseName(rs.getLong("cms_id") + ""));
                domain.setValue(rs.getLong("times"));
                list.add(domain);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MySQLUtils.release(connection, psmt, rs);
        }

        return list;
    }

    public static void main(String[] args){
        VideoAccessTopNDAO dao = new VideoAccessTopNDAO();

        List<VideoAccessTopN> list = dao.query("20170511");
        for(VideoAccessTopN result:list){
            System.out.println(result.getName()+" "+result.getValue());
        }

    }

}

4.3.4 `VideoAccessTopNServlet.java`

package com.weblog.cn;

import com.weblog.dao.VideoAccessTopNDAO;
import com.weblog.domain.VideoAccessTopN;
import net.sf.json.JSONArray;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

/*
 * 最受欢迎的 TopN 课程
 *
 * Web -> Service -》 Dao
 * */
public class VideoAccessTopNServlet extends HttpServlet {

    private VideoAccessTopNDAO dao;

    @Override
    public void init() throws ServletException {

        dao = new VideoAccessTopNDAO();
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        String day = req.getParameter("day");
        List<VideoAccessTopN> results = dao.query(day);
        JSONArray json = JSONArray.fromObject(results);

        resp.setContentType("text/html;charset=utf-8");

        PrintWriter writer = resp.getWriter();
        writer.println(json);
        writer.flush();
        writer.close();
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doGet(req, resp);
    }
}

4.3.5 `web.xml`

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
  <display-name>Archetype Created Web Application</display-name>

  <servlet>
    <servlet-name>stat</servlet-name>
    <servlet-class>com.weblog.cn.VideoAccessTopNServlet</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>stat</servlet-name>
    <url-pattern>/stat</url-pattern>
  </servlet-mapping>

</web-app>

4.3.6 `testn.html`

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Echarts HelloWorld</title>

    <!-- 引入 ECharts 文件 -->
    <script src="js/echarts.min.js"></script>
    <SCRIPT src="js/jquery.js"></SCRIPT>

</head>
<body>
    <!-- 为 ECharts 准备一个具备大小（宽高）的 DOM -->
    <div id="main" style="width: 600px;height:400px;"></div>

    <script type="text/javascript">
        // 基于准备好的dom，初始化echarts实例
        var myChart = echarts.init(document.getElementById('main'));
        var option = {
            title : {
                text: '主站最受欢迎的 TopN 课程',
                x:'center'
            },
            tooltip : {
                trigger: 'item',
                formatter: "{a} <br/>{b} : {c} ({d}%)"
            },
            legend: {
                orient: 'vertical',
                left: 'left',
                data:[]
            },
            series : [
                {
                    name: '访问次数',
                    type: 'pie',
                    radius : '55%',
                    center: ['50%', '60%'],
                    data:(function () {
                        var courses = [];
                        $.ajax({
                            type:"GET",
                            url:"stat?day=20170511",
                            dataType:'json',
                            async:false,
                            success:function (result) {
                                for(var i=0;i<result.length;i++){
                                    courses.push({"value":result[i].value,"name":result[i].name})
                                }
                            }
                        })
                        return courses;
                    })(),
                    itemStyle: {
                        emphasis: {
                            shadowBlur: 10,
                            shadowOffsetX: 0,
                            shadowColor: 'rgba(0, 0, 0, 0.5)'
                        }
                    }
                }
            ]
        };

        myChart.setOption(option)


    </script>
</body>
</html>