spark（26）sparksql处理点击流日志数据案例(★★★★★)
sparksql处理点击流日志数据案例(★★★★★)
需求描述
通过sparsql对用户访问产生点击流日志数据进行分析处理，计算出对应的指标
工具类开发
代码开发——校验日志数据进行字段解析提取的工具类AccessLogUtils
import scala.util.matching.Regex
//定义一个样例类，将切分后的一行数据封装在该类里：
case class AccessLog(
ipAddress: String, // IP地址
clientId: String, // 客户端唯一标识符
userId: String, // 用户唯一标识符
serverTime: String, // 服务器时间
method: String, // 请求类型/方式
endpoint: String, // 请求的资源
protocol: String, // 请求的协议名称
responseCode: Int, // 请求返回值：比如：200、401
contentSize: Long, // 返回的结果数据大小
url:String, //访问的url地址
clientBrowser:String //客户端游览器信息
)
object MyUtils {
val regex:Regex="""^(\S+) (\S+) (\S+) \[([\w:/]+\s[+\-]\d{4})\] "(\S+) (\S+) (\S+)" (\d{3}) (\d+) (\S+) (.*)""".r
//自定义过滤方法来过滤脏数据
def isValidLine(line:String):Boolean={
//findFirstMatchIn 是一个匹配方法，返回值是Some或者None
val options=regex.findFirstMatchIn(line)
if (options.isEmpty){
false
}else{
true
}
}
//定义一个方法，将一行数据切分，并封装在AccessLog类中，并返回AccessLog对象：
def parseLine(line:String): AccessLog ={
val options=regex.findFirstMatchIn(line)
val matcher=options.get
AccessLog(
matcher.group(1), // 获取匹配的字符串中的第一组的值
matcher.group(2),
matcher.group(3),
matcher.group(4),
matcher.group(5),
matcher.group(6),
matcher.group(7),
matcher.group(8).toInt,
matcher.group(9).toLong,
matcher.group(10),
matcher.group(11)
)
}
}
说明：
- 正则表达式：可以利用notepad的正则表达式方式的查找功能来解析每个正则表达式的意思
- ^代表开头的意思
- \S代表非空格和非Tab键 + 代表至少一个字符 (\S+)代表至少一个非空格Tab键的字符
- (\S+)代表至少有一个非空格非Tab键的字符
- \[([\w:/]+\s[+\-]\d{4})\] 中的
- \[代表 [
- \w代表大小写字母，数字和下划线，
- [\w:/]+代表 大小写字母，数字和下划线、冒号:、斜线/中的任意字符至少一位，
- \s代表空格或tab键
- [+\-]代表 +或者-
- \d{4}代表4为数字
- ]代表]
- 匹配举例：[19/Sep/2013:04:08:36 +0000]
- .*代表任意个数的任意字符
- 正则表达式中的括号() 代表一个组，我们的正则表达式有11个括号()，所以有11个组
- 第一个括号就是第1组，依次类推，后期我们可以获取指定第几组的数据，因此，对匹配到的一行数据就不需要split(" ")切分了。
- findFirstMatchIn()是匹配到第一个成功匹配的数据
- val regex:Regex="xxx".r的是字符串上的方法，该方法可以返回一个new Regex对象
- 正则表达式中会出现特殊字符等，Scala中可以使用三对引号来直接输特殊字符，不需要转义符
指标统计
大致步骤：构建sparkSession和sparkContext对象---》加载数据为RDD---》过滤掉脏数据---》将数据转为DataFrame---》将DataFrame注册成一张表---》指标统计分析---》将分析统计结果保存到mysql---》关闭资源
import java.util.Properties
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.SparkSession
object LogAnalysis {
val url="jdbc:mysql://node03:3306/spark"
val properties=new Properties()
properties.setProperty("user","root")
properties.setProperty("password","123456")
def main(args: Array[String]): Unit = {
val spark=SparkSession.builder().appName("demo").master("local[2]").getOrCreate()
val sc=spark.sparkContext
sc.setLogLevel("warn")
val lineRDD=sc.textFile("E:\\LearningAll\\8-HadoopEcosystem-Video\\spark下载资料\\spark_day05\\案例数据\\access.log")
val lineCleanRDD2:RDD[String]=lineRDD.filter(line=>MyUtils.isValidLine(line))
val parseRDD:RDD[AccessLog]=lineCleanRDD2.map(line=>MyUtils.parseLine(line))
//RDD--->DataFrame
import spark.implicits._
val dataDF=parseRDD.toDF()
dataDF.show(5)
//注册
dataDF.createTempView("accesslog")
//开始分析数据
val result1=spark.sql(
"""
|select
|date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) as time,
|AVG(contentSize) as avg_contentSize,
|MAX(contentSize) as max_contentSize,
|MIN(contentSize) as min_contentSize
|from accesslog
|""".stripMargin)
result1.show()
//PV,UV
val result2=spark.sql(
"""
|select
|date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) as time,
|count(*) as PV,
|count(distinct ipAddress) as UV
|from accesslog
|""".stripMargin)
result2.show()
val result3=spark.sql(
"""
|select
|date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) as time,
|responseCode as code,
|count(*) as count
|from accesslog
|group by responseCode
|""".stripMargin)
result3.show()
//求访问url次数最多的前N位
val result4 = spark.sql(
"""
|select
|*,date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) as time
|from (
|select
|url as url,
|count(*) as count
|from accesslog
|group by url) t
|order by t.count desc limit 5
""".stripMargin)
result4.show()
//求各个请求方式出现的次数
val result5 = spark.sql(
"""
|select
|date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) as time,
|method as method,
|count(*) as count
|from accesslog
|group by method
""".stripMargin)
result5.show()
//保存result5数据到mysql
result5.write.jdbc(url,"t_method",properties) //不需要事先自己创建表
spark.stop()
}
}
说明：
- DATE_SUB(date,INTERVAL expr type)是一个将日期减去指定的时间间隔的函数，date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
- 函数：FROM_UNIXTIME
作用：将MYSQL中以INT(11)存储的时间（时间戳）以"YYYY-MM-DD"格式来显示。
语法：FROM_UNIXTIME(unix_timestamp,format) - unix_timestamp()可以获取当前时间的时间戳
- unix_timestamp()也可以传入一个date参数，表示获取date的时间戳，Unix timestamp(date) 中的date需满足格式：yyyy-MM-dd HH:mm:ss或者yyyy-MM-dd
- date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1)表示当前时间减1天，进行减1天的原因大概是在实际工作当中，一般是对昨天的数据进行统计分析的，所以在当前时间减1天
运行结果为：
+--------------+--------+------+--------------------+------+--------------------+--------+------------+-----------+---+--------------------+
| ipAddress|clientId|userId| serverTime|method| endpoint|protocol|responseCode|contentSize|url| clientBrowser|
+--------------+--------+------+--------------------+------+--------------------+--------+------------+-----------+---+--------------------+
|194.237.142.21| -| -|18/Sep/2013:06:49...| GET|/wp-content/uploa...|HTTP/1.1| 304| 0|"-"|"Mozilla/4.0 (com...|
| 163.177.71.12| -| -|18/Sep/2013:06:49...| HEAD| /|HTTP/1.1| 200| 20|"-"|"DNSPod-Monitor/1.0"|
| 163.177.71.12| -| -|18/Sep/2013:06:49...| HEAD| /|HTTP/1.1| 200| 20|"-"|"DNSPod-Monitor/1.0"|
|101.226.68.137| -| -|18/Sep/2013:06:49...| HEAD| /|HTTP/1.1| 200| 20|"-"|"DNSPod-Monitor/1.0"|
|101.226.68.137| -| -|18/Sep/2013:06:49...| HEAD| /|HTTP/1.1| 200| 20|"-"|"DNSPod-Monitor/1.0"|
+--------------+--------+------+--------------------+------+--------------------+--------+------------+-----------+---+--------------------+
only showing top 5 rows
+----------+------------------+---------------+---------------+
| time| avg_contentSize|max_contentSize|min_contentSize|
+----------+------------------+---------------+---------------+
|2020-04-18|15882.708061002179| 432916| 0|
+----------+------------------+---------------+---------------+
+----------+-----+----+
| time| PV| UV|
+----------+-----+----+
|2020-04-18|13770|1027|
+----------+-----+----+
+----------+----+-----+
| time|code|count|
+----------+----+-----+
|2020-04-18| 500| 1|
|2020-04-18| 502| 8|
|2020-04-18| 301| 94|
|2020-04-18| 400| 13|
|2020-04-18| 403| 3|
|2020-04-18| 404| 201|
|2020-04-18| 408| 1|
|2020-04-18| 200|12340|
|2020-04-18| 304| 949|
|2020-04-18| 499| 8|
|2020-04-18| 302| 152|
+----------+----+-----+
+--------------------+-----+----------+
| url|count| time|
+--------------------+-----+----------+
| "-"| 5204|2020-04-18|
|"http://blog.fens...| 547|2020-04-18|
|"http://blog.fens...| 377|2020-04-18|
|"http://blog.fens...| 360|2020-04-18|
|"http://blog.fens...| 274|2020-04-18|
+--------------------+-----+----------+
+----------+------+-----+
| time|method|count|
+----------+------+-----+
|2020-04-18| POST| 449|
|2020-04-18| HEAD| 2941|
|2020-04-18| GET|10380|
+----------+------+-----+
查看保存到mysql的数据：
mysql> show tables;
+-----------------+
| Tables_in_spark |
+-----------------+
| t_method |
| t_students |
| user |
| user2 |
+-----------------+
4 rows in set (0.00 sec)
mysql> select * from t_method;
+------------+--------+-------+
| time | method | count |
+------------+--------+-------+
| 2020-04-18 | POST | 449 |
| 2020-04-18 | HEAD | 2941 |
| 2020-04-18 | GET | 10380 |
+------------+--------+-------+
