Creating a Graph Database for a Social Network with Apache AGE

Intro

In this tutorial I'll be showing you guys how to create a social network graph, add nodes, edges and analyze the degree of centrality from the graph. For this, we'll use Apache AGE, a graph database extension for Postgres.

Creating the graph

To create the graph, we can execute this query:

SELECT create_graph('SocialNetwork');

Adding user's data

Now, let's add some folks to our social network:

SELECT * FROM cypher('SocialNetwork', $$
CREATE (:Person {name: 'Alice', age: 23, interests: ['hiking', 'cooking']}),
(:Person {name: 'Bob', age: 25, interests: ['photography', 'music']}),
(:Person {name: 'Charlie', age: 30, interests: ['reading', 'history']}),
(:Person {name: 'David', age: 26, interests: ['gaming', 'movies']}),
(:Person {name: 'Emma', age: 22, interests: ['dancing', 'travel']}),
(:Person {name: 'Frank', age: 27, interests: ['sports', 'camping']}),
(:Person {name: 'Grace', age: 29, interests: ['painting', 'fashion']})
$$) as (v agtype);
 v 
---
(0 rows)

Adding edges

Let's say that Alice is friends with everyone bellow the age of 26 and Charlie is friends with everyone at the age of 26 and above.

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Alice' AND b.age < 26 AND b.name <> 'Alice'
CREATE (a)-[e:FRIENDS]->(b)             
RETURN e
$$) as (FRIENDS agtype);

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Charlie' AND b.age > 25 AND b.name <> 'Charlie'
CREATE (a)-[e:FRIENDS]->(b)             
RETURN e
$$) as (FRIENDS agtype);

Groups and members

Alice and Charlie are in a hiking group together, Bob and David are in a photography club, Charlie and Emma are part of a book club, David and Frank are part of a gaming group, Emma and Grace are in a dance class, Frank and Grace support the same basketball team.

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Alice' AND b.name = 'Charlie'
CREATE (a)-[:MEMBER_OF]->(g:Group {name: 'Hiking Group'})<-[:MEMBER_OF]-(b)
RETURN g
$$) as (g agtype);

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Bob' AND b.name = 'David'
CREATE (a)-[:MEMBER_OF]->(g:Group {name: 'Photography Club'})<-[:MEMBER_OF]-(b)
RETURN g
$$) as (g agtype);

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Charlie' AND b.name = 'Emma'
CREATE (a)-[:MEMBER_OF]->(g:Group {name: 'Book Club'})<-[:MEMBER_OF]-(b)
RETURN g
$$) as (g agtype);

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'David' AND b.name = 'Frank'
CREATE (a)-[:MEMBER_OF]->(g:Group {name: 'Gaming Group'})<-[:MEMBER_OF]-(b)
RETURN g
$$) as (g agtype);

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Emma' AND b.name = 'Grace'
CREATE (a)-[:MEMBER_OF]->(g:Group {name: 'Dance Class'})<-[:MEMBER_OF]-(b)
RETURN g
$$) as (g agtype);

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Frank' AND b.name = 'Grace'
CREATE (a)-[:LIKES]->(p:Page {name: 'Chicago Bulls'})<-[:LIKES]-(b)
RETURN p
$$) as (p agtype);

Friend requests

Grace and Frank went to see a match of the Chicago Bulls and ended up knowing each other there. After the game was over, Frank sent a friend request to her in our social network.

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Frank' AND b.name = 'Grace'
CREATE (a)-[e:FRIEND_REQUEST {message: 'Hey Grace, it was great to meet you at the Chicago Bulls game! 
I had a lot of fun watching the game with you and chatting about basketball. 
I hope you enjoyed the game as much as I did. 
I thought it would be nice to stay in touch and get to know each other better, so I sent you a friend request. 
Looking forward to talking to you soon!'}]->(b)
RETURN e
$$) as (p agtype);

Charlie saw that Emma wrote a book review on "A Little Life", a book written by Hanya Yanagihara, he wants to be friends with her.

SELECT * FROM cypher('SocialNetwork', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Charlie' AND b.name = 'Emma'
CREATE (a)-[e:FRIEND_REQUEST {message: 'Hello Emma, my name is Charlie and I have just read your review on the book A Little Life, and I adored it.'}]->(b)
RETURN e
$$) as (p agtype);

Visualizing The Graph with AGE Viewer

Let's take a look at our graph

SELECT * from cypher('SocialNetwork', $$
        MATCH (V)-[R]-(V2)
        RETURN V,R,V2
$$) as (V agtype, R agtype, V2 agtype);

Degree of Centrality (incoming or outgoing edges)

The degree of centrality in a graph is a count of the number of edges that a node posses and it can be used to find the most influential people in a social network.

-- incoming edges
SELECT * FROM cypher('SocialNetwork', $$
MATCH in_edge = (a)<-[ie]-(c)
RETURN a.name, label(a), count(in_edge)
$$) AS (id agtype, label agtype, in_edges agtype);

         id         |  label   | in_edges 
--------------------+----------+----------
 "Photography Club" | "Group"  | 2
 "Hiking Group"     | "Group"  | 2
 "Chicago Bulls"    | "Page"   | 2
 "David"            | "Person" | 1
 "Book Club"        | "Group"  | 2
 "Emma"             | "Person" | 2
 "Dance Class"      | "Group"  | 2
 "Frank"            | "Person" | 1
 "Bob"              | "Person" | 1
 "Gaming Group"     | "Group"  | 2
 "Grace"            | "Person" | 2
(11 rows)

-- Outgoing edges
SELECT * FROM cypher('SocialNetwork', $$
MATCH out_edge = (a)-[oe]->(b)                         
RETURN a.name, label(a), count(out_edge)                
$$) AS (id agtype, label agtype, out_edges agtype);

    id     |  label   | out_edges 
-----------+----------+-----------
 "Charlie" | "Person" | 6
 "David"   | "Person" | 2
 "Emma"    | "Person" | 2
 "Alice"   | "Person" | 3
 "Frank"   | "Person" | 3
 "Bob"     | "Person" | 1
 "Grace"   | "Person" | 2
(7 rows)

-- People that have both
SELECT * FROM cypher('SocialNetwork', $$
MATCH out_edge = (a)-[oe]->(b), in_edge = (a)<-[ie]-(c)
RETURN a.name, label(a), count(out_edge)
$$) AS (id agtype, label agtype, in_and_out_edges agtype);

   id    |  label   | in_and_out_edges 
---------+----------+------------------
 "David" | "Person" | 2
 "Emma"  | "Person" | 4
 "Frank" | "Person" | 3
 "Bob"   | "Person" | 1
 "Grace" | "Person" | 4
(5 rows)

We can see that Charlie is the one with most outgoing edges, Emma and Grace both have the greatest amount of incoming and outgoing edges summed up.

We can see that graph databases are a good tool to store users data and analyze who is giving the most impact on a social media. If you liked this tutorial, give it a like, share with your friends and try out Apache AGE, the best graph extension for PostgreSQL!

Apache AGE Website: https://age.apache.org
Apache AGE GitHub Repository: https://github.com/apache/age