Spark Graphx Describes Organization Chart Easy and Fast

GRAPHX

GraphX is a new component in Spark for graphs and graph-parallel computation. At a high level, GraphX extends the Spark RDD by introducing a new Graph abstraction: a directed multigraph with properties attached to each vertex and edge.
To support graph computation, GraphX exposes a set of fundamental operators (e.g., subgraph, joinVertices, and aggregateMessages) as well as an optimized variant of the Pregel API from Google.
In addition, GraphX includes a growing collection of graph algorithms and builders to simplify graph analytics tasks.
Example:
Let’s define a simple org chart:
Let’s add one more person, sherry, Jacks’ wife

Here we have data:

The box, indicates the name and title in the org chart:
Sherry, wife of owner
Jack, owner
George, clerk
Mary, sales
The line, indicates the relationship in the org chart:
Jack, owner is boss of George, clerk
Jack, owner is boss of Mary, sales
Sherry, wife of owner is boss of Jack, owner
George, clerk is coworker of Mary, sales

First, I try it on an SQL database. Any database would do. Just use existing HIVE:

First, create 2 tables:
Vertex: stores the Vertex id (long integer), name, title information.
Vertex stores the box or node
Edge: Stores source vertex id, destination vertex id and relationship.
Edge stores the line, or relationship

Create the 2 tables in hive, under database cstu, that was created before

1
use cstu;
2
​
3
create table vertex
4
(
5
id bigint,
6
property_name string,
7
property_title string
8
);
9
​
10
create table edge
11
(
12
src_id bigint,
13
dest_id bigint,
14
relationship string
15
);
16
​
17
​
Copied!

Then add data into Vertex and Edge tables

The box, indicating person and title of person:
Sherry, wife of owner
Jack, owner
George, clerk
Mary, sales
The line, indicates the relationship in the org chart:
Jack, owner is boss of George, clerk
Jack, owner is boss of Mary, sales
Sherry, wife of owner is boss of Jack, owner
George, clerk is coworker of Mary, sales
1
use cstu;
2
​
3
insert into vertex values (1,'Jack','owner');
4
insert into vertex values (2, 'George', 'clerk');
5
insert into vertex values (3, 'Mary', 'Sales');
6
insert into vertex values (4, 'Shrry', 'wife of owner');
7
​
8
​
9
insert into edge values (1,2,'boss');
10
insert into edge values (1,3,'boss');
11
insert into edge values (2,3,'coworker');
12
insert into edge values (4,1,'boss');
13
​
Copied!

Then I construct the SQL query as below:

select * from vertex;
1
hive> set hive.cli.print.header=true;
2
hive> select * from vertex;
3
OK
4
vertex.id vertex.property_name vertex.property_title
5
1 Jack owner
6
2 George clerk
7
3 Mary Sales
8
4 Shrry wife of owner
9
​
Copied!
select * from edge;
1
hive> set hive.cli.print.header=true;
2
hive> select * from edge;
3
OK
4
edge.src_id edge.dest_id edge.relationship
5
1 2 boss
6
1 3 boss
7
2 3 coworker
8
4 1 boss
9
​
Copied!

Here is the Graph query to describe the org chart.

1
with x as (SELECT e.src_id, e.dest_id, e.relationship,
2
src.property_name src_name,
3
src.property_title src_title,
4
dst.property_name dest_name,
5
dst.property_title dest_title
6
FROM edge AS e LEFT JOIN vertex AS src ON e.src_id = src.id
7
LEFT JOIN vertex AS dst ON e.dest_id = dst.id)
8
select concat_ws('',src_name, ', ', src_title , ', is '
9
, relationship , ' of ' , dest_name , ', '
10
, dest_title
11
) from x;
12
​
Copied!

Run it in hive

1
(spark) [[email protected] ~]$ hive
2
which: no hbase in (/opt/spark/bin:/opt/hadoop/hive/bin:/opt/hadoop/anaconda3/envs/spark/bin:/opt/hadoop/anaconda3/condabin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/hadoop/.local/bin:/opt/hadoop/bin:/usr/java/default/bin:/opt/hadoop/sbin:/opt/hadoop/bin)
3
SLF4J: Class path contains multiple SLF4J bindings.
4
SLF4J: Found binding in [jar:file:/opt/hadoop/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
5
SLF4J: Found binding in [jar:file:/opt/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
6
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
7
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
8
​
9
Logging initialized using configuration in jar:file:/opt/hadoop/hive/lib/hive-common-2.1.0.jar!/hive-log4j2.properties Async: true
10
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
11
hive> use cstu;
12
​
13
hive> with x as (SELECT e.src_id, e.dest_id, e.relationship,
14
> src.property_name src_name,
15
> src.property_title src_title,
16
> dst.property_name dest_name,
17
> dst.property_title dest_title
18
> FROM edge AS e LEFT JOIN vertex AS src ON e.src_id = src.id
19
> LEFT JOIN vertex AS dst ON e.dest_id = dst.id)
20
> select concat_ws('',src_name, ', ', src_title , ', is '
21
> , relationship , ' of ' , dest_name , ', '
22
> , dest_title
23
> ) from x;
Copied!

Here is the result of query, compare with the org chart, it that right?

1
Jack, owner, is boss of George, clerk
2
Jack, owner, is boss of Mary, Sales
3
George, clerk, is coworker of Mary, Sales
4
Shrry, wife of owner, is boss of Jack, owner
5
​
Copied!

Now switch to Spark GraphX library with Scala:

1
import org.apache.spark._
2
import org.apache.spark.graphx._
3
import org.apache.spark.rdd.RDD
4
import org.apache.log4j._
5
import org.apache.spark.sql._
6
import org.apache.spark.graphx.{Graph, VertexRDD}
7
import org.apache.spark.graphx.util.GraphGenerators
8
Logger.getLogger("org").setLevel(Level.ERROR)
9
val spark = SparkSession
10
.builder
11
.appName("graphx")
12
.master("local[*]")
13
.config("spark.sql.warehouse.dir", "file:///tmp")
14
.getOrCreate()
15
val sc=spark.sparkContext
16
val users: RDD[(VertexId, (String, String))] =
17
sc.parallelize(Array((1L, ("jack", "owner")), (2L, ("george", "clerk")),
18
(3L, ("mary", "sales")), (4L, ("sherry", "owner wife"))))
19
val relationships: RDD[Edge[String]] =
20
sc.parallelize(Array(Edge(1L, 2L, "boss"), Edge(1L, 3L, "boss"),
21
Edge(2L, 3L, "coworker"), Edge(4L, 1L, "boss")))
22
val defaultUser = ("", "Missing")
23
val graph = Graph(users, relationships, defaultUser)
24
/*
25
The EdgeTriplet class extends the Edge class by adding the srcAttr and dstAttr members
26
which contain the source and destination properties respectively.
27
We can use the triplet view of a graph to render a collection of strings describing relationships between users.
28
*/
29
val facts: RDD[String] =
30
graph.triplets.map(triplet =>
31
triplet.srcAttr._1 + ", "+ triplet.srcAttr._2 + " is the " + triplet.attr + " of " + triplet.dstAttr._1+", "+triplet.dstAttr._2)
32
facts.collect.foreach(println(_))
Copied!
Run the above Scala code, output below:
1
jack, owner is the boss of george, clerk
2
jack, owner is the boss of mary, sales
3
george, clerk is the coworker of mary, sales
4
sherry, owner wife is the boss of jack, owner
Copied!
If this is a complex charts, Spark Graphx is the perfect platform to compute the inter-relationships because of its distributed, in memory computing nature.
Last modified 1yr ago
Copy link