spark sql 怎么拼接时间 spark sql -e

001

DataFrame&Dataset

 

Dataset 概述:

Dataset 是从 spark 1.6 后提出的新接口，是一个分布式的数据集合，提供 RDD 的优势以及 Spark SQL 优化执行的特点。

 

DataFrame 转换为 Dataset:

DataFrame 直接调用 as 方法就可以转换为 Dataset。

 

编程代码：

//    定义 case classcase class people(name: String, age: Long, sex: String)val peopleDF = spark.read.format("json").load("/data/people.json")//  导入隐式转换可以调用as方法将dataframe转换为datasetimport spark.implicits._
val peopleDS = peopleDF.as[people]

peopleDS.map(line => {
      line.name
}).show()

输出结果：

+-----+|value|+-----+|   张三||   李四||   王五|+-----+

002

External Data Source

 

操作 Parquet/Json/Csv 文件:

 

• Parquet/Json/Csv 在 Spark SQL 是内部数据源，因此在读取此类文件时，我们只需修改 format() 类型即可完成对此类数据的加载处理。
• 针对一些外部数据源，我们需要加载外部的工具类 —— 外部数据源，然后依然是修改我们的 format() 类型即可。
   

编程代码：

//    指定format类型为parquet/json/csv即可加载相应文件类型的数据本例以parquet为例val userDF = spark.read.format("parquet").load("/data/users.parquet")//    输出结果userDF.show()

输出结果：

+------+--------------+----------------+|  name|favorite_color|favorite_numbers|+------+--------------+----------------+|Alyssa|          null|  [3, 9, 15, 20]||   Ben|           red|              []|+------+--------------+----------------+

003

操作 Hive 数据

 

前提： 需要将 hive 的 site 配置文件拷贝到 spark 的 conf 目录下，并在 Spark SQL 工程中引入 mysql 工具类的依赖。

编程代码：

//    加载hive中的数据val hiveDF = spark.table(hiveTable)//    可以直接调用DataFrame的API也可以通过sql来执行hiveDF.select(...)
spark.sql("select * ...")//    将数据写入到新的table中result.write.saveAsTable(tableName)

004

操作 MySQL 数据

 

编程代码：

// 指定fromat类型为：jdbc    // 配置mysql的url、用户名、密码、database等信息val mysqlDF = spark.read.format("jdbc").options(
  Map(   "url" -> "jdbc:mysql://localhost:3306/databank",   "user" -> "root",   "password" -> "root",   "dbtable" -> "databank_task",   "driver" -> "com.mysql.jdbc.Driver"
     )
   ).load()//    查询数据mysqlDF.select("task_name","camps","platform").show()

输出结果：

+---------+-------+--------+|task_name|  camps|platform|+---------+-------+--------+|  新建任务|    416|  mobile||  新建任务|    416|  mobile||  新建任务|    416|  mobile||      440|    440|  mobile|+---------+-------+--------+

005

操作外部数据源总结

 

• 读取数据范式： spark.read.format(format).load(path)。
• 保存数据范式： spark.write.format(format).save(path) 使用时如果是内部数据源则只需要修改相应的 format 类型即可，如果是外部数据源，则在 option 中添加配置相应的 jar 工具类即可。

006

Spark SQL 实战

 

项目需求：

• 统计曝光最多的广告 TOPN。
• 按地市统计曝光最多的广告TOPN。

 

日志内容构成：

日期，项目 ID，媒体 ID，广告位 ID，平台类型 ID，广告 ID，创意 ID，城市 code。

180730,474,1646,23147,1,1825,1927,1156370100
180730,474,1118,23251,1,1825,1927,1156370300
180730,474,2751,22969,1,1825,1927,1156330100
180730,474,1646,23172,1,1825,1927,1156330200
180730,474,1646,23156,1,1825,1927,1156510100
180730,474,1646,23086,1,1826,1928,1156440300
180730,474,2083,23372,1,1825,1927,1156320400
180730,474,1646,23085,1,1825,1927,1156440100
180730,474,1646,23122,1,1825,1927,1156430400
180730,474,1646,23173,1,1825,1927,1156331000

备注：地域相关信息是通过解析 ip 地址获得，可参考 ipdatabase。

007

需求实现

 

统计曝光最多的广告 TOPN：

由于已知日志的数据结构，所以本来采用 csv 的方式读取日志数据。

 

编程代码：

//    定义schema信息val structType = StructType(Array(
  StructField("date", StringType, true),
  StructField("camp", StringType, true),
  StructField("media", StringType, true),
  StructField("placement", StringType, true),
  StructField("platform", StringType, true),
  StructField("ad", StringType, true),
  StructField("create", StringType, true),
  StructField("city", StringType, true)
))  // 以csv的方式加载数据val viewLog = spark.read.schema(structType).format("csv").load("/data/view/180730/")// 也可才有另外一种简略写法加载csv数据val viewLog = spark.read.schema(structType).csv("/data/view/180730/") 

// 按照天、广告分组统计曝光最多的广告viewLog.groupBy("date","ad").agg(count("ad")
        .as("times")).show()

输出结果:

+------+----+------+|  date|  ad| times|+------+----+------+|180730|1825|179375||180730|1826| 28803|+------+----+------+

将结果保存到 MySQL 中 - 示例代码如下:

//    调用foreachPartition完成对结果数据的入库操作viewAdTopNDF.foreachPartition(it => {
   val list = new ListBuffer[DayVideoAccess]
   it.foreach(row => {
     val day = row.getAs[String]("day")
     val adId = row.getAs[Long]("adId")
     val times = row.getAs[Long]("times")     list.append(DayVideoAccessStat(day, adId, times))   // 创建一个DAO类操作MySQL实现对结果数据的保存操作
   StatDao.insertDayVideoAccessTopN(list)
})

008

按地市统计曝光最多的广告TOPN

 

此处可以借助 DataFrame 的窗口函数的使用完成。

编程代码：

// 加载数据的操作已经在上一步完成，在此不再多余赘述//    按照日期、城市、广告分组，然后在借助window函数实现分城市topNval cityAdTopNDF = viewLog.groupBy("date","city","ad").agg(count("ad").as("times")).show()
val top2 = cityAdTopNDF.select(
      cityAdTopNDF("date"),
      cityAdTopNDF("city"),
      cityAdTopNDF("ad"),
 row_number().over(Window.partitionBy("city").orderBy(cityAdTopNDF("times").desc)).as("times_rank")
    ).filter("times_rank <= 2").show()

注： 此处主要是理解 row_numbr().over() 函数与 window 的组合使用。

输出结果：

输出每个城市曝光最多的前两只广告
+------+----------+----+----------+|  date|      city|  ad|times_rank|
+------+----------+----+----------+
|180730|1156330200|1825|         1||180730|1156330200|1826|         2|
|180730|1156150100|1825|         1||180730|1156150100|1826|         2|
|180730|1156370100|1825|         1||180730|1156370100|1826|         2|
|180730|1156211400|1825|         1||180730|1156441600|1825|         1|
|180730|1156441600|1826|         2|+------+----------+----+----------+

将结果写入到 MySQL 中的操作可参照上一需求的方法。

009

运行调优建议

 

1. 使用 Spark SQL 加载数据时，可根据集群的 split 大小调整读取数据时切分到单个分片的大小； spark.sql.files.maxPartitionBytes，默认是 134217728 （128 MB）。
2. 使用 Spark SQ 保存数据时，可根据我们的结果数据大小增大活减小 shuffle 的个数；spark.sql.shuffle.partitions，默认是 200。
3. 当我们在使用日期解析工具时，最后使用 FastDateFormat(lang3)，不建议使用 SimpleDateFormat，因为 SimpleDateFormat 是线程不安全的，在数据处理中可能会造成时间解析异常。
4. 当我们读取 parquet 文件时，我们可以将 spark 推导分区字段类型关闭，节省不必要的资源浪费，spark.sql.sources.partitionColumnTypeInference.enabled，默认是 true。
5. 另外，最终写入 mysql 时，不要一条一条写入，应该批量写入数据，可借助 prepareStatement.addBatch() 完成批量执行。
6. 当有较多的 DataFrame join 操作时，最好调大此参数：spark.sql.autoBroadcastJoinThreshold，默认是 10485760（10 MB），可设置为 100 M。