基于SparkSQL的网站日志分析实战

最新推荐文章于 2019-09-25 15:51:31 发布

zghgchao

最新推荐文章于 2019-09-25 15:51:31 发布

阅读量2.4k

点赞数

分类专栏： spark

本文链接：https://blog.csdn.net/qq_22027637/article/details/78885509

版权

spark 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

基于SparkSQL的网站日志分析实战

用户行为日志概述

用户行为日志：用户每次访问网站时所有的行为数据（访问、浏览、搜索、点击...）

用户行为轨迹、流量日志

为什么要记录用户访问行为日志

网站页面的访问量
网站的黏性
推荐

2.用户行为日志生成渠道

Nginx
Ajax

3.用户行为日志内容

日志数据内容：
1）访问的系统属性：操作系统、浏览器等等
2）访问特征：点击的url、从哪个url跳转过来的(referer)、页面上的停留时间等
3）访问信息：session_id、访问ip(访问城市)等

日志信息如下所示：
2013-05-19 13:00:00     http://www.taobao.com/17/?tracker_u=1624169&type=1      B58W48U4WKZCJ5D1T3Z9ZY88RU7QA7B1        http://hao.360.cn/      1.196.34.243   
4.用户行为日志分析的意义
网站的眼睛（访问者来自什么地方，找什么东西，那些页面最受欢迎，访问者的入口地址是什么等）
网站的神经（网站布局是否合理，导航层次是否清晰，功能是否存在问题，转换路径是否靠谱）
网站的大脑（如何分析目标，如何分配广告预算(广告推广)）

离线数据处理架构

数据处理流程
1）数据采集
Flume： web日志写入到HDFS

2）数据清洗
脏数据
Spark、Hive、MapReduce 或者是其他的一些分布式计算框架
清洗完之后的数据可以存放在HDFS(Hive/Spark SQL)

3）数据处理
按照我们的需要进行相应业务的统计和分析
Spark、Hive、MapReduce 或者是其他的一些分布式计算框架

4）处理结果入库
结果可以存放到RDBMS、NoSQL

5）数据的可视化
通过图形化展示的方式展现出来：饼图、柱状图、地图、折线图
ECharts、HUE、Zeppelin

离线数据处理架构

项目需求

需求一：统计imooc主站最受欢迎的课程/手记的Top N访问次数
需求二：按地市统计imooc主站最受欢迎的Top N课程

（1）.根据IP地址提取出城市信息

（2）.窗口函数在Spark SQL中的使用

按流量统计imooc主站最受欢迎的Top N课程

功能实现

网站主站日志介绍

访问时间
访问URL
访问过程耗费流量
访问IP地址

数据清洗

原始日志

10.100.0.1 - - [10/Nov/2016:00:01:02 +0800] "HEAD / HTTP/1.1" 301 0 "117.121.101.40" "-" - "curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.16.2.3 Basic ECC zlib/1.2.3 libidn/1.18 libssh2/1.4.2" "-" - - - 0.000

第一次清洗后（访问时间主站URL 耗费的流量 ip地址）
2017-05-11 15:07:17     http://www.imooc.com/video/14322        245     202.96.134.133
2017-05-11 06:52:31     http://www.imooc.com/article/17891      535     222.129.235.182
2017-05-11 18:46:43     http://www.imooc.com/article/17898      807     218.75.35.226
代码实现参见gitee地址【https://gitee.com/robei/SparkSQLProject/blob/master/src/main/scala/com/imooc/log/SparkStatFormatJob.scala】

第二次数据清洗

输入：访问时间、访问URL、耗费的流量、访问IP地址信息
输出：URL、cmsType(video/article)、cmsId(编号)、流量、ip、城市信息、访问时间、天

代码实现参见gitee地址【https://gitee.com/robei/SparkSQLProject/blob/master/src/main/scala/com/imooc/log/SparkStatCleanJob.scala】

根据ip地址解析城市信息

使用github上已有的开源项目
1）git clone https://github.com/wzhe06/ipdatabase.git
2）编译下载的项目：mvn clean package -DskipTests
3）安装jar包到自己的maven仓库
mvn install:install-file -Dfile=~/source/ipdatabase/target/ipdatabase-1.0-SNAPSHOT.jar -DgroupId=com.ggstar -DartifactId=ipdatabase -Dversion=1.0 -Dpackaging=jar

数据清洗存储到目标地址（）

【https://gitee.com/robei/SparkSQLProject/blob/e60788c9e0f3c2ffc446d9aa8acaa5a66ac006fc/src/main/scala/com/imooc/log/SparkStatCleanJob.scala】

需求一的实现：统计主站最受欢迎的课程/手记的Top N访问次数

/**
    * 需求一：主站最受欢迎的TopN课程统计
    *
    * @param spark
    * @param cleanDF
    */
  def videoAccessTopNStat(spark: SparkSession, cleanDF: DataFrame, day:String): Unit = {

    //------------------使用DataFrame API完成统计操作--------------------------------------------
    /* import spark.implicits._

     val videoAccessTopNDF = cleanDF.filter($"day" === day && $"cmsType" === "video")
       .groupBy("day","cmsId").agg(count("cmsId").as("times")).orderBy($"times".desc)*/

    //    videoAccessTopNDF.printSchema()
    /**
      * root
      * |-- day: string (nullable = true)
      * |-- cmsId: long (nullable = true)
      * |-- times: long (nullable = false)
      */

    //    videoAccessTopNDF.show(10,false)
    /**
      * +--------+-----+------+
      * |day     |cmsId|times |
      * +--------+-----+------+
      * |20170511|14540|111027|
      * |20170511|4000 |55734 |
      * |20170511|14704|55701 |
      * |20170511|14390|55683 |
      * |20170511|14623|55621 |
      * |20170511|4600 |55501 |
      * |20170511|4500 |55366 |
      * |20170511|14322|55102 |
      * +--------+-----+------+
      */

    //-------------------------使用SQL API完成操作-------------------------
    cleanDF.createOrReplaceTempView("access_logs")
    //创建临时表 access_logs
    val videoAccessTopNDF = spark.sql("select day,cmsId,count(1) as times from access_logs" +
      " where day="+day+" and cmsType='video'" +
      " group by day,cmsId order by times desc")

    videoAccessTopNDF.show(10, false)
    /**
      * +--------+-----+------+
      * |day     |cmsId|times |
      * +--------+-----+------+
      * |20170511|14540|111027|
      * |20170511|4000 |55734 |
      * |20170511|14704|55701 |
      * |20170511|14390|55683 |
      * |20170511|14623|55621 |
      * |20170511|4600 |55501 |
      * |20170511|4500 |55366 |
      * |20170511|14322|55102 |
      * +--------+-----+------+
      */
    //-------------------将统计结果写入数据库-------------------
    try {
      videoAccessTopNDF.foreachPartition(partitionOfRecords => {
        val list = new ListBuffer[DayVideoAccessStat]

        partitionOfRecords.foreach(info => {
          val day = info.getAs[String]("day")
          val cmsId = info.getAs[Long]("cmsId")
          val times = info.getAs[Long]("times")

          list.append(DayVideoAccessStat(day, cmsId, times))
        })
        StatDAO.insertDayVideoAccessTopN(list)
      })
    }catch {
      case e:Exception => e.printStackTrace()
    }
    /**
      * 在mysql中创建day_video_access_topn_stat，主站最受欢迎的Top N课程
      *  create table day_video_access_topn_stat (
      *      day varchar(8) not null,
      *      cms_id bigint(10) not null,
      *      times bigint(10) not null,
      *      primary key (day, cms_id)
      *  );
      */
  }

需求二：按地市统计主站最受欢迎的Top N课程

/**
    * 需求二：按地市统计主站最受欢迎的Top N课程
    * @param spark
    * @param cleanDF
    */
  def cityAccessTopSata(spark: SparkSession, cleanDF: DataFrame, day:String): Unit = {
    //------------------使用DataFrame API完成统计操作--------------------------------------------
     import spark.implicits._

     val cityAccessTopNDF = cleanDF.filter($"day" === day && $"cmsType" === "video")
       .groupBy("day","city","cmsId").agg(count("cmsId").as("times")).orderBy($"times".desc)

//    cityAccessTopNDF.printSchema()
    /**
      * root
      *    |-- day: string (nullable = true)
      *    |-- city: string (nullable = true)
      *    |-- cmsId: long (nullable = true)
      *    |-- times: long (nullable = false)
      */

//    cityAccessTopNDF.show(false)
    /**
      *  +--------+----+-----+-----+
      *  |day     |city|cmsId|times|
      *  +--------+----+-----+-----+
      *  |20170511|浙江省 |14540|22435|
      *  |20170511|北京市 |14540|22270|
      *  |20170511|安徽省 |14540|22149|
      *  |20170511|广东省 |14540|22115|
      *  |20170511|上海市 |14540|22058|
      *  |20170511|北京市 |4600 |11271|
      *  |20170511|安徽省 |14390|11229|
      *  |20170511|广东省 |14623|11226|
      *  |20170511|上海市 |14704|11219|
      *  |20170511|广东省 |14704|11216|
      *  |20170511|广东省 |4600 |11215|
      *  |20170511|上海市 |4000 |11182|
      *  |20170511|北京市 |14390|11175|
      *  |20170511|广东省 |4000 |11169|
      *  |20170511|上海市 |4500 |11167|
      *  |20170511|安徽省 |14704|11162|
      *  |20170511|北京市 |4000 |11156|
      *  |20170511|浙江省 |14322|11151|
      *  |20170511|上海市 |14623|11149|
      *  |20170511|广东省 |4500 |11136|
      *  +--------+----+-----+-----+
      */
    //-----------Window函数在Spark SQL中的使用--------------------
    val cityTop3DF = cityAccessTopNDF.select(
      cityAccessTopNDF("day"),
      cityAccessTopNDF("city"),
      cityAccessTopNDF("cmsId"),
      cityAccessTopNDF("times"),
      row_number().over(Window.partitionBy(cityAccessTopNDF("city"))
        .orderBy(cityAccessTopNDF("times").desc)).as("times_rank")
    ).filter("times_rank <= 3").orderBy($"city".desc,$"times_rank".asc)

    cityTop3DF.show(false)//展示每个地市的Top3
    /**
      *    +--------+----+-----+-----+----------+
      *    |day     |city|cmsId|times|times_rank|
      *    +--------+----+-----+-----+----------+
      *    |20170511|浙江省 |14540|22435|1         |
      *    |20170511|浙江省 |14322|11151|2         |
      *    |20170511|浙江省 |14390|11110|3         |
      *    |20170511|广东省 |14540|22115|1         |
      *    |20170511|广东省 |14623|11226|2         |
      *    |20170511|广东省 |14704|11216|3         |
      *    |20170511|安徽省 |14540|22149|1         |
      *    |20170511|安徽省 |14390|11229|2         |
      *    |20170511|安徽省 |14704|11162|3         |
      *    |20170511|北京市 |14540|22270|1         |
      *    |20170511|北京市 |4600 |11271|2         |
      *    |20170511|北京市 |14390|11175|3         |
      *    |20170511|上海市 |14540|22058|1         |
      *    |20170511|上海市 |14704|11219|2         |
      *    |20170511|上海市 |4000 |11182|3         |
      *    +--------+----+-----+-----+----------+
      */
    //-------------------将统计结果写入数据库-------------------
    try {
      cityTop3DF.foreachPartition(partitionOfRecords => {
        val list = new ListBuffer[DayCityVideoAccessStat]

        partitionOfRecords.foreach(info => {
          val day = info.getAs[String]("day")
          val cmsId = info.getAs[Long]("cmsId")
          val city = info.getAs[String]("city")
          val times = info.getAs[Long]("times")
          val timesRank = info.getAs[Int]("times_rank")

          list.append(DayCityVideoAccessStat(day, cmsId,city, times,timesRank))
        })
        StatDAO.insertDayCityVideoAccessTopN(list)
      })
    }catch {
      case e:Exception => e.printStackTrace()
    }
    /**
      *   create table day_video_city_access_topn_stat (
      *      day varchar(8) not null,
      *      cms_id bigint(10) not null,
      *      city varchar(20) not null,
      *      times bigint(10) not null,
      *      times_rank int not null,
      *      primary key (day, cms_id, city)
      *   );
      */
  }

需求三：按照流量统计主站最受欢迎的Top N课程

 /**
    * 需求三：按照流量统计主站最受欢迎的Top N课程
    * @param spark
    * @param cleanDF
    */
  def videoTraffsTopStat(spark: SparkSession, cleanDF: DataFrame, day:String): Unit = {
    //------------------使用DataFrame API完成统计操作--------------------------------------------
    import spark.implicits._

    val trafficsTopNDF = cleanDF.filter($"day" === day && $"cmsType" === "video")
      .groupBy("day","cmsId").agg(sum("traffic").as("traffics")).orderBy($"traffics".desc)

     trafficsTopNDF.show()
    /**
      *  +--------+-----+--------+
      *  |     day|cmsId|traffics|
      *  +--------+-----+--------+
      *  |20170511|14540|55454898|
      *  |20170511|14390|27895139|
      *  |20170511| 4500|27877433|
      *  |20170511| 4000|27847261|
      *  |20170511|14623|27822312|
      *  |20170511| 4600|27777838|
      *  |20170511|14704|27737876|
      *  |20170511|14322|27592386|
      *  +--------+-----+--------+
      */
    //-------------------将统计结果写入数据库-------------------
    try {
      trafficsTopNDF.foreachPartition(partitionOfRecords => {
        val list = new ListBuffer[DayVideoTrafficsStat]

        partitionOfRecords.foreach(info => {
          val day = info.getAs[String]("day")
          val cmsId = info.getAs[Long]("cmsId")
          val traffics = info.getAs[Long]("traffics")

          list.append(DayVideoTrafficsStat(day, cmsId,traffics))
        })
        StatDAO.insertDayVideoTrafficsTopN(list)
      })
    }catch {
      case e:Exception => e.printStackTrace()
    }
    /**
      *  create table day_video_traffics_topn_stat (
      *      day varchar(8) not null,
      *      cms_id bigint(10) not null,
      *      traffics bigint(20) not null,
      *      primary key (day, cms_id)
      *   );
      */
  }

【https://gitee.com/robei/SparkSQLProject】

zghgchao

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
基于SparkSQL的网站日志分析实战

基于SparkSQL的网站日志分析实战用户行为日志概述用户行为日志：用户每次访问网站时所有的行为数据（访问、浏览、搜索、点击...）用户行为轨迹、流量日志为什么要记录用户访问行为日志网站页面的访问量网站的黏性推荐 2.
复制链接

扫一扫