简介
access.log是电信运营商的用户上网数据;
20090121000138654752000|123.197.46.211|www.45yo.cn|/|Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)||cck_lasttime=1232466678832; cck_count=0 20090121000138986515000|115.120.3.48|www.kujue.com|/modules/article/reader.php?aid=4&cid=71614|Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; QQDownload 1.7; GTB5)||AJSTAT_ok_times=166; jieqiVisitInfo=jieqiUserLogin%3D1232466557%2CjieqiUserId%3D2022; jieqiUserInfo=jieqiUserId%3D2022%2CjieqiUserName%3Dkoay110%2CjieqiUserGroup%3D3%2CjieqiUserPassword%3De10adc3949ba59abbe56e057f20f883e%2CjieqiUserName_un%3Dkoay11%2CjieqiUserLogin%3D1232466557; AJSTAT_ok_pages=14; PHPSESSID=c65bf7fcded83bbba5adec581d016d93 20090121000139005189000|125.213.100.123|city.51.com|/|Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0(Compatible Mozilla/4.0(Compatible-EmbeddedWB 14.59 http://bsalsa.com/ EmbeddedWB- 14.59 from: http://bsalsa.com/ )|http://my.51.com/inbox/mysyslog.php| ......
ip.txt是ip地址和归属地的规则数据;
1.0.1.0|1.0.3.255|16777472|16778239|亚洲|中国|福建|福州||电信|350100|China|CN|119.306239|26.075302 1.0.8.0|1.0.15.255|16779264|16781311|亚洲|中国|广东|广州||电信|440100|China|CN|113.280637|23.125178 1.0.32.0|1.0.63.255|16785408|16793599|亚洲|中国|广东|广州||电信|440100|China|CN|113.280637|23.125178 ......
需求
根据用户上网数据,完成上网IP归属地分析统计,并进行相应排序。
通过计算access.log中的用户行为数据,统计出各个省份访问量(一次请求记作一次独立的访问量), 并按照各个省份的访问量的从高到低进行排序。
思路一:广播变量
特点:广播变量一但广播出去就不能改变.
object IpAndAddressByBroadcast {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setAppName(this.getClass.getCanonicalName)
var isLocal = args(0).toBoolean
if (isLocal) {
conf.setMaster("local[*]")
}
val sc = new SparkContext(conf)
//读取ip规则文件,创建原始RDD
val lines: RDD[String] = sc.textFile(args(1))
//对ip规则数据进行整理
val ipRules: RDD[(Long, Long, String, String)] = lines.mapPartitions(it => {
it.map(line => {
val fields: Array[String] = line.split("[|]")
val startIp: Long = fields(2).toLong //起始ip
val endIp: Long = fields(3).toLong //结束ip
val province: String = fields(6) //省份
val city: String = fields(7) //城市
(startIp, endIp, province, city)
})
})
//将整理好的数据收集到driver端
val driverIpRules: Array[(Long, Long, String, String)] = ipRules.collect()
//sc 将收集到driver端的ip规则数据广播到每一个executor;
val ipRef: Broadcast[Array[(Long, Long, String, String)]] = sc.broadcast(driverIpRules)
//读取用户上网日志文件
val accessLog: RDD[String] = sc.textFile(args(2))
//整理数据,获取ip地址,通过关联ipRules规则,获取所在省份
val result: RDD[(String, Int)] = accessLog.mapPartitions(it => {
//获取广播数据
val refArray: Array[(Long, Long, String, String)] = ipRef.value
it.map(line => {
val fields: Array[String] = line.split("[|]")
val ipAddress: String = fields(1)
val ipNums: Long = IpUtils.ip2Long(ipAddress) //利用工具类,将ip地址转化成十进制
val index: Int = IpUtils.binarySearch(refArray, ipNums) //利用二分查找法,查找ip所在区间的对应编号
var province = "未知"
if (index != -1) {
province = refArray(index)._3
}
(province, 1)
})
}).reduceByKey(_ + _).sortBy(_._2) //聚合,排序
result.collect().toBuffer.foreach(println)
sc.stop()
}
}
思路二:单例对象
特点:单例对象中的内容可以被改变,而且可以创建定时任务,定时更新ip规则
//将ip规则存储在ArrayBuffer中
object IpRulesArray {
//利用scala的IO读取ip规则数据
val source: BufferedSource = Source.fromFile("data/ip.txt")
//获取每一条数据
val lines: Iterator[String] = source.getLines()
//创建一个ArrayBuffer,存储ip规则
var ipArr: ArrayBuffer[(Long, Long, String, String)] = ArrayBuffer[(Long,Long,String,String)]()
//迭代获取每一条数据,并处理
while(lines.hasNext){
val line: String = lines.next()
val fields: Array[String] = line.split("[|]")
val startIp: Long = fields(2).toLong //起始ip
val endIp: Long = fields(3).toLong //结束ip
val province: String = fields(6) //省份
val city: String = fields(7) //城市
ipArr.append((startIp, endIp, province, city))
}
ipArr
}
object IpAndAddressByObject {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setAppName(this.getClass.getCanonicalName)
var isLocal = args(0).toBoolean
if (isLocal) {
conf.setMaster("local[*]")
}
val sc = new SparkContext(conf)
//读取用户上网日志文件
val accessLog: RDD[String] = sc.textFile(args(1))
//整理数据,获取ip地址,通过关联ipRules规则,获取所在省份
val result: RDD[(String, Int)] = accessLog.mapPartitions(it => {
//通过初始化单例对象,触发静态代码块,获得存有ip规则的ArrayBuffer
val refArray: Array[(Long, Long, String, String)] = IpRulesArray.ipArr.toArray
it.map(line => {
val fields: Array[String] = line.split("[|]")
val ipAddress: String = fields(1)
val ipNums: Long = IpUtils.ip2Long(ipAddress) //利用工具类,将ip地址转化成十进制
val index: Int = IpUtils.binarySearch(refArray, ipNums) //利用二分查找法,查找ip所在区间的对应编号
var province = "未知"
if (index != -1) {
province = refArray(index)._3
}
(province, 1)
})
}).reduceByKey(_ + _).sortBy(_._2) //聚合,排序
result.collect().toBuffer.foreach(println)
sc.stop()
}
}
将统计结果输出到mysql中
在pom文件中导入mysql依赖,其他版本亦可.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
此方法为,从executor端直接导入到mysql中.
也可以先将处理结果collect到driver端,再从driver端写出mysql中,但若数据量过大,不适合收集到driver端再写入到mysql中.
object IpAndAddressToMysql {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setAppName(this.getClass.getCanonicalName)
var isLocal = args(0).toBoolean
if (isLocal) {
conf.setMaster("local[*]")
}
val sc = new SparkContext(conf)
//读取用户上网日志文件
val accessLog: RDD[String] = sc.textFile(args(1))
//整理数据,获取ip地址,通过关联ipRules规则,获取所在省份
val sorted: RDD[(String, Int)] = accessLog.mapPartitions(it => {
//通过初始化单例对象,触发静态代码块,获得存有ip规则的ArrayBuffer
val refArray: Array[(Long, Long, String, String)] = IpRulesArray.ipArr.toArray
it.map(line => {
val fields: Array[String] = line.split("[|]")
val ipAddress: String = fields(1)
val ipNums: Long = IpUtils.ip2Long(ipAddress) //利用工具类,将ip地址转化成十进制
val index: Int = IpUtils.binarySearch(refArray, ipNums) //利用二分查找法,查找ip所在区间的对应编号
var province = "未知"
if (index != -1) {
province = refArray(index)._3
}
(province, 1)
})
}).reduceByKey(_ + _) //聚合
//将数据收集到Driver端在写入到MySQL
sorted.foreachPartition(it => {
var index = 0
//和mysql建立连接
val connection: Connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/xiaobai?characterEncoding=UTF-8", "root", "xiaobai")
//准备sql语句
val pstm: PreparedStatement = connection.prepareStatement("insert into ipaddress (dt,province,counts) values (?,?,?)")
it.foreach(x => {
pstm.setDate(1, new Date(System.currentTimeMillis()))
pstm.setString(2, x._1)
pstm.setInt(3, x._2)
pstm.addBatch() //分批处理
if (index % 100 == 0) { //控制批量写入条数
pstm.executeBatch()
}
index += 1
})
pstm.executeBatch()
if (pstm != null){
pstm.close()
}
if(connection != null) {
connection.close()
}
})
sc.stop()
}
}