graphx项目实战 — 航班飞行网图分析

一.任务描述

需求概述

• 探索航班飞行网图数据
• 构建航班飞行网图
• 使用Spark GraphX完成下列任务
• 统计航班飞行网图中机场的数量
• 统计航班飞行网图中航线的数量
• 计算最长的飞行航线（Point to Point）
• 找出最繁忙的机场
• 找出最重要的飞行航线（PageRank）
• 找出最便宜的飞行航线（SSSP）

二.具体分析

问题分析1：数据探索

• 下载数据链接: https://pan.baidu.com/s/1fubnDM_sggw_MWS9iI1AoQ 提取码: xnxv
• 数据格式:文件格式为CSV，字段之间分隔符为“,”
• 依次为：#日、周#、航空公司、飞机注册号、航班号、起飞机场编号、起飞机场、到达机场编号、到达机场、预计起飞时间（时分）、起飞时间、起飞延迟（分钟）、到达预计时间、到达时间、到达延迟（分钟）、预计飞行时间、飞行距离

问题分析2：构建航班飞行网图

• 创建属性图Graph[VD,ED]
• 装载CSV为RDD，每个机场作为顶点。关键字段：起飞机场编号、起飞机场、到达机场编号、到达机场、飞行距离
• 初始化顶点集airports:RDD[(VertexId,String)]，顶点属性为机场名称
• 初始化边集lines:RDD[Edge]，边属性为飞行距离

//导入包
import org.apache.spark.graphx._
//加载数据
val flights = sc.textFile("file:///data/flight.csv").map(_.split(","))
//机场数据
val airports = flights.flatMap(x => Array((x(5).toLong, x(6)), (x(7).toLong,x(8)))).distinct
//航线数据
val lines = flights.map(x => (x(5).toLong, x(7).toLong, x(16).toInt)).distinct.map(x => Edge(x._1, x._2, x._3))
//构建图
val graph = Graph(airports, lines)

问题分析3：统计航班飞行网图中机场与航线的数量

• 机场数量:求顶点个数：Graph.numVertices
• 航线数量:求边的个数：Graph.numEdges

//机场个数
graph.vertices.count
graph.numVertices
//航线数
graph.numEdges

//补充使用spark_sql
//spark_sql
val df= spark.read.format("csv").option("header","false").option("delimiter",",").load("file:///data/flight.csv").toDF("dom","dow","carrier","tail_num","fl_num","origin_id","origin","des_id","dest","crs_dep_time","dep_time","dep_delay_mins","crs_arr_time","arr_time","arr_delay_mins","crs_elapse_time","dist")
val df2 = df.select("origin_id","origin","des_id","dest")
df2.registerTempTable("flight")
//起飞机场多少个
spark.sql("with t1 as (select origin_id from flight group by origin_id) select sum(1) from t1 ").show//301
df.select("origin_id").distinct.count//301
//目的地机场多少个
spark.sql("with t1 as (select des_id from flight group by des_id) select sum(1) from t1 ").show//301
df.select("des_id").distinct.count//301
//起飞机场和目的地机场多少个
df.select("origin_id").union(df.select("des_id")).distinct.count //301
//航线个数
df.select("origin_id","des_id").distinct.count

问题分析4：计算最长的飞行航线

• 最大的边属性:对triplets按飞行距离排序（降序）并取第一个

graph.triplets.sortBy(_.attr,false).take(1)
graph.triplets.sortBy(_.attr,false).map(t => "The distance is %d from %s to %s.".format(t.attr, t.srcAttr, t.dstAttr)).take(1)(0)
graph.inDegrees.sortBy(_._2,false).take(1)
graph.outDegrees.filter(x=>x._1==10397).collect
val (x,y)= graph.degrees.sortBy(_._2,false).take(1)(0)

//reduce求最大值
lines.reduce((x,y)=>if (x.attr > y.attr) x else y)
//fold求最大值
lines.fold(Edge(0))((x,y)=>if(x.attr> y.attr) x else y)
spark.sql("select * from flight order by cast(dist as int) desc").show(1)

问题分析5：找出最繁忙的机场

• 哪个机场到达航班最多:计算顶点的入度并排序

//sort
graph.inDegrees.sortBy(_._2,false).take(1)(0)
//reduce
graph.inDegrees.reduce((x,y)=>if(x._2>y._2) x else y)

问题分析6：找出最重要的机场

• PageRank:收敛误差：0.05

graph.pageRank(0.05).vertices.join(airports).sortBy(_._2._1,false).map(_._2._2).take(1)

val g = graph.pageRank(0.0001)
g.vertices.take(10)
val iv = g.vertices.reduce((x,y)=>if(x._2>y._2) x else y)

问题分析7：找出最便宜的飞行航线

• 定价模型: price = 180.0 + distance * 0.15
• SSSP问题:从初始指定的源点到达任意点的最短距离

pregel

• 初始化源点与其它顶点（Double.PositiveInfinity）
• 初始消息（Double.PositiveInfinity）
• vprog函数计算最小值
• sendMsg函数计算进行是否下一个迭代
• mergeMsg函数合并接受的消息，取最小值

val count = airports.count//总机场数
var fraction = 1.0//系数
var samples = airports.sample(false, fraction/count, count) //抽样
while ( samples.count < 0 ) {
fraction = fraction + 1
samples = airports.sample(false, fraction/count, count)
}
val source_id: VertexId = samples.first._1//得到源点 14952
//给初始值,转换定价模型
val init_graph = graph.mapVertices((id, _) => if (id == source_id) 0.0 else Double.PositiveInfinity).mapEdges(e => 180.toDouble + e.attr.toDouble*0.15)
//构建pregel模型	
val pregel_graph = init_graph.pregel(Double.PositiveInfinity)(
(id, dist, new_dist) => math.min(dist, new_dist),
triplet => {
if ( triplet.srcAttr + triplet.attr < triplet.dstAttr ) {
Iterator((triplet.dstId, triplet.srcAttr + triplet.attr))
}
else Iterator.empty
},
(a, b) => math.min(a, b)
)
//航线
val cheap_lines = pregel_graph.edges.map { case(Edge(src_id, dst_id, price))
=> (src_id, dst_id, price) }.takeOrdered(3)(Ordering.by(_._3))
//机场
val cheap_airports = pregel_graph.vertices.takeOrdered(3)(Ordering.by(_._2))