Spark sql 连续登录例题

2021/05/18 10.3.9.18 tianqi 1006 error
2021/05/18 10.7.5.56 lisi 1002 warning
2021/05/18 194.23.67.23 wangwu 1003 normal
2021/05/18 111.202.103.60 zhaoliu 1004 normal
2021/05/18 10.3.9.18 zhangsan 1005 warning
2021/05/18 10.3.9.18 lisi 1002 normal
2021/05/18 220.181.90.52 John 1001 normal
2021/05/18 10.7.5.56 lisi 1002 normal
2021/05/19 49.51.8.209 wangwu 1003 error
2021/05/19 66.102.251.33 zhaoliu 1004 normal
2021/05/19 10.3.9.18 zhangsan 1005 normal
2021/05/19 111.202.103.60 tianqi 1006 error
2021/05/19 140.205.94.189 lisi 1002 normal
2021/05/19 69.167.138.34 lisi 1002 normal
2021/05/19 194.23.67.23 tianqi 1006 normal
2021/05/20 111.202.103.60 zhaoliu 1004 normal
2021/05/20 10.3.9.18 tianqi 1006 warning
2021/05/20 66.102.251.33 tianqi 1006 normal
2021/05/20 69.167.138.34 John 1001 normal
2021/05/21 220.181.90.52 lisi 1002 warning
2021/05/21 111.202.103.60 wangwu 1003 normal
2021/05/21 10.7.5.56 zhaoliu 1004 normal
2021/05/21 49.51.8.209 zhangsan 1005 error
2021/05/21 140.205.94.189 lisi 1002 normal
2021/05/21 104.160.171.85 John 1001 normal
2021/05/22 10.3.9.18 lisi 1002 warning
2021/05/22 111.202.103.60 wangwu 1003 normal
2021/05/22 220.181.90.52 zhaoliu 1004 error
2021/05/22 104.160.171.85 zhangsan 1005 normal
2021/05/22 194.23.67.23 tianqi 1006 normal

1）创建sparksession环境
2）创建sparkcontext
3)读取上述文件生成rdd
4)数据格式化成元组类型
5）添加合适的字段名。
6）注册成临时表
7)查询lisi在那一天访问
8)查询连续三天访问用户
9)udf函数把时间转成yyyymmmdd格式

 

package week3

import org.apache.spark.sql.SparkSession

object SparkTest02 extends App {
  //1）创建sparksession环境。
  val spark = SparkSession
              .builder()
              .master("local")
              .appName("test02")
              .getOrCreate()
  //2）创建sparkcontext。
  val sc = spark.sparkContext
  //3)读取上述文件生成rdd 文件为本地文件
  var lineRDD = sc.textFile("data/week3/test02.txt")
  //4)数据格式化成元组类型
  var mapRDD = lineRDD.map(s=>{
    var ss = s.split("\\s+")
    //返回数值
    (ss(0),ss(1),ss(2),ss(3),ss(4))
  })
  //5）添加合适的字段名。
  var logsDF = spark.createDataFrame(mapRDD).toDF("time","ip","name","id","log")
  //6）注册成临时表
  logsDF.createTempView("logs")
  //7)查询李四在哪一天访问
  spark.sql("select distinct l.time from logs l where l.name='lisi'").show()
  //8)查询连续三天访问用户
  //求出日期 和 用户名  并去重   剔除相同时间内的重复人员数据
 // spark.sql("select replace(l.time,'/','-'),l.name from logs l ").show()
  /*spark.sql("select distinct replace(l.time,'/','-') day,l.name from logs l ").createTempView("t1")

  //根据用户  对日期进行排序
  spark.sql("select t1.*,rank() over(partition by t1.name order by t1.day) num from t1").createTempView("t2")

  //将日期与排名相减
  spark.sql("select t.*,date_sub(t.day,t.num) cha from t2 t").createTempView("t3")

  //根据姓名和 差 进行分组
  spark.sql("select t.name ,t.cha ,count(*) cnt from t3 t group by t.name,t.cha having cnt >=3").show()
*/
  //使用一条sql
  var sql =
  """
      | select
      |   t3.name,
      |   t3.cha,
      |   count(*) cnt
      |from
      |   (
      |   select
      |         t2.*,
      |         date_sub(t2.day,t2.num) cha
      |   from
      |       (
      |         select
      |             t1.*,
      |             rank() over (partition by t1.name order by t1.day) num
      |         from
      |               (
      |                  select
      |                        distinct replace(l.time,'/','-') day,
      |                        l.name
      |                  from logs l
      |               )t1
      |       ) t2
      |   ) t3
      |group by
      |       t3.name,
      |       t3.cha
      |having
      |     cnt >=3
      |
      |""".stripMargin

    spark.sql(sql).show()
  //9)udf函数吧时间转成yyyymmmdd格式
    spark.udf.register("MyTime",(str:String)=>{
      //直接将/ 替换成 空字符串
      str.replaceAll("/","")
    })

  spark.sql("select l.*,MyTime(l.time) from logs l").show()
  spark.stop()
}