mysql 多个ip库查询

转载

AI独步天下 2024-11-04 20:32:22

文章标签 mysql 多个ip库查询 SparkSQL join 优化 IP 文章分类 MySQL 数据库

**需要：**各大网站每天都会产生大量的数据，数据中有用户访问网站的时间戳，IP地址，访问的域名，浏览器信息等等，现要求分析各个省份的上网人数（实际上就是在ip规则中查询用户IP的地址，再进行聚合）
实现：
1、加载城市ip段信息，获取ip起始数字和结束数字，经度，维度
2、加载日志数据，获取ip信息，然后转换为数字，和ip段比较
3、比较的时候采用二分法查找，找到对应的经度和维度
4、然后对经度和维度做单词计数
access.log的数据：

20090121000132095572000|125.213.100.123|show.51.com|/shoplist.php?phpfile=shoplist2.php&style=1&sex=137|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://show.51.com/main.php|
20090121000132124542000|117.101.215.133|www.jiayuan.com|/19245971|Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; TencentTraveler 4.0)|http://photo.jiayuan.com/index.php?uidhash=d1c3b69e9b8355a5204474c749fb76ef|__tkist=0; myloc=50%7C5008; myage=2009; PROFILE=14469674%3A%E8%8B%A6%E6%B6%A9%E5%92%96%E5%95%A1%3Am%3Aphotos2.love21cn.com%2F45%2F1b%2F388111afac8195cc5d91ea286cdd%3A1%3A%3Ahttp%3A%2F%2Fimages.love21cn.com%2Fw4%2Fglobal%2Fi%2Fhykj_m.jpg; last_login_time=1232454068; SESSION_HASH=8176b100a84c9a095315f916d7fcbcf10021e3af; RAW_HASH=008a1bc48ff9ebafa3d5b4815edd04e9e7978050; COMMON_HASH=45388111afac8195cc5d91ea286cdd1b; pop_1232093956=1232468896968; pop_time=1232466715734; pop_1232245908=1232469069390; pop_1219903726=1232477601937; LOVESESSID=98b54794575bf547ea4b55e07efa2e9e; main_search:14469674=%7C%7C%7C00; registeruid=14469674; REG_URL_COOKIE=http%3A%2F%2Fphoto.jiayuan.com%2Fshowphoto.php%3Fuid_hash%3D0319bc5e33ba35755c30a9d88aaf46dc%26total%3D6%26p%3D5; click_count=0%2C3363619
20090121000132406516000|117.101.222.68|gg.xiaonei.com|/view.jsp?p=389|Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; CIBA)|http://home.xiaonei.com/Home.do?id=229670724|_r01_=1; __utma=204579609.31669176.1231940225.1232462740.1232467011.145; __utmz=204579609.1231940225.1.1.utmccn=(direct)

IP.txt的数据：

202.98.58.174|202.98.58.252|3395435182|3395435260|亚洲|中国|重庆|重庆||电信|500100|China|CN|106.504962|29.533155
202.98.58.253|202.98.58.253|3395435261|3395435261|亚洲|中国|重庆|重庆|江北|电信|500105|China|CN|106.57434|29.60658
202.98.58.254|202.98.63.0|3395435262|3395436288|亚洲|中国|重庆|重庆||电信|500100|China|CN|106.504962|29.533155
202.98.63.1|202.98.63.1|3395436289|3395436289|亚洲|中国|重庆|重庆|渝北|电信|500112|China|CN|106.6307|29.7182

实现：
注意：往Mysql中写数据的时候有两种方式
（1）调用RDD的方法（这里按照每个分区写数据）
（2）调用SparkSQL自己的方法，使用这种方法的时候不需要创建表和定义表的结构，直接使用就行，系统会进行自动的创建。

package day04

import ip.TestIP
import org.apache.spark.sql.{DataFrame, Dataset, SparkSession}


/**
  * @author WangLeiKai
  *         2018/9/22  9:32
  */
object IPLocationSQL {
  def main(args: Array[String]): Unit = {
    //spark2.x支持  如果程序有SparkContext，则直接调用，如果没有，则创建
    val spark = SparkSession
      .builder()
      .appName("IPLocationSQL")
      .master("local[*]")
      .getOrCreate()
    //导入该对象的隐式转换
    import spark.implicits._
    //读规则文件
    val ruleLines: Dataset[String] = spark.read.textFile("d://data//ip.txt")
    //取到想要的字段，放在一个dataframe中
    val ruleDataFrame: DataFrame = ruleLines.map(line => {
      val fields = line.split("[|]")
      val startNum = fields(2).toLong
      val endNum = fields(3).toLong
      val province = fields(6)
      (startNum, endNum, province)
    }).toDF("snum","enum","province")
    //读访问日志文件
    val accessline: Dataset[String] = spark.read.textFile("d://data/access.log")
    //取到想要的字段，放进一个dataframe
    val ipDataFrame: DataFrame = accessline.map(log => {
      //将log日志的每一行进行切分
      val fields = log.split("[|]")
      val ip = fields(1)
      //将ip转换成十进制
      val ipNum = TestIP.ip2Long(ip)
      ipNum
    }).toDF("ip_num")

    //创建临时视图
    ruleDataFrame.createTempView("v_rules")
    ipDataFrame.createTempView("v_ips")

    //执行sql语句
    val result: DataFrame = spark.sql("select province,count(*) counts from v_ips join v_rules ON (ip_num >= snum AND ip_num <= enum) group by province")
    //调用action  触发sql
        result.foreachPartition(it => {
      val conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?characterEncoding=UTF-8","root","root")
      val pstmt = conn.prepareStatement("INSERT INTO log VALUES (?, ?)")
      it.foreach(tp => {
        pstmt.setString(1,tp.getString(0))
        pstmt.setLong(2,tp.getLong(1))
        pstmt.executeUpdate()
      })
      pstmt.close()
      conn.close()
    })
    //注意：这种方法的时候，数据库中不能有这张表
    //val properties = new Properties()
    //properties.setProperty("user","root")
    //properties.setProperty("password","root")

    //result.write.jdbc("jdbc:mysql://localhost:3306/user?characterEncoding=UTF-8","v_log",properties)

    //释放资源
    spark.stop()

  }

}

上面是每一次查询的时候都会join，并且数据要重复加载
join的代价太昂贵，而且非常慢，解决思路是将表缓存起来（广播变量）
而且只需要一个表就可以实现，不需要进行join

package day04

import ip.TestIP
import org.apache.spark.broadcast.Broadcast
import org.apache.spark.sql.{DataFrame, Dataset, SparkSession}


/**
  * @author WangLeiKai
  *         2018/9/22  9:32
  */
object IPLocationSQL2 {
  def main(args: Array[String]): Unit = {
    //spark2.x支持  如果程序有SparkContext，则直接调用，如果没有，则创建
    val spark = SparkSession
      .builder()
      .appName("IPLocationSQL2")
      .master("local[*]")
      .getOrCreate()
    //导入该对象的隐式转换
    import spark.implicits._
    //读规则文件
    val ruleLines: Dataset[String] = spark.read.textFile("d://data//ip.txt")

    //取到想要的字段，放在一个dataframe中
    val ruleDataFrame= ruleLines.map(line => {
      val fields = line.split("[|]")
      val startNum = fields(2).toLong
      val endNum = fields(3).toLong
      val province = fields(6)
      (startNum, endNum, province)
    })
    //读访问日志文件
    val rows = ruleDataFrame.collect()
    val broadcastRef: Broadcast[Array[(Long, Long, String)]] = spark.sparkContext.broadcast(rows)
    val accessline: Dataset[String] = spark.read.textFile("d://data/access.log")
    //取到想要的字段，放进一个dataframe
    val ipDataFrame: DataFrame = accessline.map(log => {
      //将log日志的每一行进行切分
      val fields = log.split("[|]")
      val ip = fields(1)
      //将ip转换成十进制
      val ipNum = TestIP.ip2Long(ip)
      ipNum
    }).toDF("ip_num")

    //创建临时视图
    ipDataFrame.createTempView("v_log")

    //创建并注册自定义函数
    spark.udf.register("ip2Province",(ipNum:Long) => {
      //接受到driver端的 广播变量
      val ipRulesInExecutor: Array[(Long, Long, String)] = broadcastRef.value
      //根据IP地址对应的十进制查找省份
      val index = TestIP.binarySearch(ipRulesInExecutor,ipNum)
      var province = "未知"
      if (index != -1){
       province = ipRulesInExecutor(index)._3
      }
      province
    })

    //执行sql语句
    val result: DataFrame = spark.sql("select ip2Province(ip_num) province,count(*) counts from v_log group by province order by counts desc")
    //调用action  触发sql
        result.foreachPartition(it => {
      val conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?characterEncoding=UTF-8","root","root")
      val pstmt = conn.prepareStatement("INSERT INTO log VALUES (?, ?)")
      it.foreach(tp => {
        pstmt.setString(1,tp.getString(0))
        pstmt.setLong(2,tp.getLong(1))
        pstmt.executeUpdate()
      })
      pstmt.close()
      conn.close()
    })
        //注意：这种方法的时候，数据库中不能有这张表
    //val properties = new Properties()
    //properties.setProperty("user","root")
    //properties.setProperty("password","root")

    //result.write.jdbc("jdbc:mysql://localhost:3306/user?characterEncoding=UTF-8","v_log",properties)

    //释放资源
    spark.stop()

  }

}

testIP的内容：

package ip

import scala.io.{BufferedSource, Source}

/**
  * @author WangLeiKai
  *         2018/9/21  9:37
  */


object TestIP {

  /**
    * 将字符串的ip转换成十进制的长整形数据
    * @param ip
    * @return
    */
  def ip2Long(ip:String): Long ={
    val fragments: Array[String] = ip.split("[.]")
    var ipNum = 0L
    for (i <- 0 until fragments.length){
      ipNum = fragments(i).toLong | ipNum << 8L
    }
    ipNum
  }
  //1.0.8.0|1.0.15.255|16779264|16781311|亚洲|中国|广东|广州||电信|440100|China|CN|113.280637|23.125178
  /**
    * 从ip文件中找到需要的数据  开始 结束  省份
    * @param path
    * @return
    */
  def readRules(path: String): Array[(Long, Long, String)] = {
    val bf: BufferedSource = Source.fromFile(path)
    val lines: Iterator[String] = bf.getLines()
    val rules: Array[(Long, Long, String)] = lines.map(line => {
      val fields: Array[String] = line.split("[|]")
      val startNum = fields(2).toLong
      val endNum = fields(3).toLong
      val province: String = fields(6)
      (startNum, endNum, province)
    }).toArray
    rules
  }

  def binarySearch(lines: Array[(Long, Long, String)], ip: Long) : Int = {
    var low = 0
    var high = lines.length - 1
    while (low <= high) {
      val middle = (low + high) / 2
      if ((ip >= lines(middle)._1) && (ip <= lines(middle)._2))
        return middle
      if (ip < lines(middle)._1)
        high = middle - 1
      else {
        low = middle + 1
      }
    }
    -1
  }
  def main(args: Array[String]): Unit = {
    val l: Long = ip2Long("114.114.114.114")
    val rules: Array[(Long, Long, String)] = readRules("d:/data/ip.txt")
    val index: Int = binarySearch(rules,l)
    val tp = rules(index)
    val province: String = tp._3

    println(province)
  }

}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。