Graphconnect Europe 2017 schedule graph

Countdown has begun! Two weeks from now we'll be bringing together the entire European Graph Community in London again, for the annual Graphconnect Conference. Every year, it's something to really live up to, to rally our customers and users to attend as we really believe in the "power of relationships" that are formed and strengthened at conferences like this.

So of course, we had to pull out the old trick (started at Oredev 2014 actually - so quite some time ago!) of creating a "Conference Schedule Graph" for everyone to explore.

GraphConnect Europe 2016 - of course we have a Schedule-graph

It's that time of the year again - GraphConnect Europe 2016 really is around the corner now. We have so much to look forward to - great users, community, customers, developers, engineers, speakers, founders - and everyone with a graphista-bone in their body is flocking to London on April 25th-26th. So of course, on this blog, I had to have some kind of little contribution to make - and traditionally (like I have done for many other conferences in different blogposts), I have to make an improvement on this "ugly" table:

This is: I really want to look at this data as a GRAPH - not a table. Of course, you say...

The GraphConnect Schedule Spreadsheet

In orde to do that, we create a nice little spreadsheet version of the schedule first. Simple, in a Google Sheet: here it is. It really is pretty simple:

Qcon 2016 obviously has a SCHEDULE GRAPH

Today is the start of an ANNIVERSARY edition of a great developer conference in London that most of us at Neo4j really like: QCON London is a yearly gathering of all kinds of software professionals in London, and is usually a great place to meet and learn lots of cool new things. It also marks that anniversary of our Graphistania podcast (which we started last year at the conference!), so I can honestly say that I am really looking forward.

Just like last year of course, I also wanted to share a "Schedule graph" of the conference schedule. As you can tell from the tabular webpage, we would need to do some conversions here and there to make it work:

But of course, we made it work. You can find all of the queries github of course.

Loading the QCON Schedule Graph

In order to get the data loaded, I first put everything into a nice little google sheet, with some furious copying and pasting:

It worked really well, and you can view the data over here now.

Then of course, I had to write a couple of queries to load the data. On github you will find two versions of these "load queries", and I am including the concise, all-i-one-go-version below:

load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=1550249063" as hosts
merge (p:Person {name: hosts.Name, title: hosts.Title})
merge (c:Company {name: hosts.Company})
merge (p)-[:WORKS_FOR]->(c)
with hosts
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
merge (d:Day {date: toInt(csv.day)})
with csv
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2)
with csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c)
with csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d)
with csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title}), (h:Person {name: csv.host})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t)
merge (h)-[:HOSTS]->(t);

Once I ran that query, and added the meta-graph, I had the following model lined up:

Obviously it is not a very big graph, but now we can do some exploring.

Exploring the QCON Schedule Graph

Let's take a look at some queries here:

//Look at day 1
match (d:Day {date:20160307})<--(t:Time)<--(s:Session)--(connections)
return d,t,s,connections
limit 50

gives us the following graph.

Then we can also start looking at some connections between people, and explore the surrounding area of the graph:

//Look at two people

match (p1:Person), (p2:Person),

path = allshortestpaths( (p1)-[*]-(p2) )

where p1.name contains "Hunger"

and p2.name contains "Webber"

return path

Then we start seeing some interesting links:

And then we can of course also explore similar links between people and companies:

//Look at a person and a company

match (c:Company {name:"ThoughtWorks"}), (p:Person {name:"Jim Webber"}),

path = allshortestpaths( (c)-[*]-(p) )

return path

Gets us the "conference"-style links between Jim Webber and his former and current employer:

And then finally, a query that I always tend to run for the heck of it - the conference session that have more than one speaker:

//Look at sessions with more than one speaker

match (s:Session)-[r:SPEAKS_IN]-(p:Person)

with s, collect(p) as person, count(p) as count

where count > 1

return s,person

Looks like this - we can obviously explore this a lot further.

Cool. Again - this is just an example of the type of nice little graph explorations that you can easily set up on your local machines. If you have any questions or want to explore this further, then please don't hesitate to drop by the Neo4j booth!

Have fun at the conference!

Cheers

Rik

The making of The GraphConnect Graph

Next month is GraphConnect London, our industry's yearly High Mass of Graphiness. It's going to be a wonderful event, and of course I wanted to chip in to promote it in any way that I can. So I did the same thing that I did for Øredev and Qcon before: importing the schedule into Neo4j.

I actually have already published an GraphGist about this already. But this post is more about the making of that database - just because I - AGAIN - learnt something interesting while doing it.

The Source Data

My dear Marketing colleague Claudia gave me a source spreadsheet with the schedule. But of course that was a bit too... Marketing-y. I cleaned it up into a very simple sheet that allowed me to generate a very simple CSV file:

I have shared the CSV file on Github. Nothing really special about it. But let me quickly explain what I did with it.

Choosing a model

Before importing data, you need to think a bit about the model you want to import into. I chose this model:

The right hand part is probably pretty easy to understand. But of course I had to do something special with the days and the timeslots.

• The days are part of the conference, and they are connected:
  
• And the timeslots within a day are also connected:
  

So how to import into that from that simple CSV file. Let's explore.

The LOAD CSV scripts

You can find the full load script - which actually loads from the dataset mentioned above - on github too. It's pretty straightforward: most commands just read a specific column from the csv file and do MERGEs to the graph. Like for example

load csv with headers from "https://gist.githubusercontent.com/rvanbruggen/ff44b7dc37bb4534df2e/raw/aed34a149f04798e351f508a18492237fcccfb62/schedule.csv" as csv
merge (v:Venue {name: csv.Venue})
merge (r:Room {name: csv.Room})
merge (r)-[:LOCATED_IN]->(v)
merge (d:Day {date: toInt(csv.Date)})
merge (tr:Track {name: csv.Track});

Nice and easy. There's a couple of commands that are a bit more special, as they have to check for NULLs before you can do a MERGE. But nothing really complicated. There's two sets of import commands - one for each day - that is a bit more interesting: how do you import the timeslots and create a structure like the one above, where all timeslots are nicely ordered and connected in an in-graph index. That's not that trivial:

• loading the timeslots is easy with MERGE
• sorting them is of course also easy
• but creating the appropriate FOLLOWED_BY relationships between the timeslots to create the in-graph index/timeline, is not that easy.

Luckily I found these two blogposts by Mark Needham that shows me how to do it. Here's the query:

match (t:Time)--(d:Day {date: 20150506})
with t
order by t.time ASC
with collect(t) as times
  foreach (i in range(0,length(times)-2) |
    foreach (t1 in [times[i]] |
      foreach (t2 in [times[i+1]] |
        merge (t1)-[:FOLLOWED_BY]->(t2))));

What this does is the following:

• you match all the timeslots that are part of the specific day that you want to order.
• you pass the ordered timeslots to the next part of the query
• you collect the ordered timeslots into a collection
• you iterate X times (where X is the length of the collection -2, so excluding the start and end position) through the collection with FOREACH
• you iterate through the starting positions (i) and the ending positions (i+1) in the collection
• every time you iterate, you MERGE a FOLLOWED_BY relationship between the starting position and the ending position

And that's it. Job done. We do this for the second day ("20150507") as well, of course.

Hope you enjoyed this as much as I did, and hope to see you at the conference!

Cheers

Rik

The QCON Graph

Next week is QCON, London's holiest of holiest (according to some) developer conferences. It's going to be a ton of fun, and the lineup of speakers and sessions is just... impressive. Take a look at it over here - there's dozens of sessions that I would love to attend, by speakers that I would love to see. Unfortunately, I will probably miss most of it - as Neo4j is sponsoring the event, and that means BOOTH DUTIES! Yej!

But, not to be worried, there's fun to be had - with the schedule. I mean, who can read any of this stuff, really:

Tables, Schmables!!! Let's but it into a graph!

Start with a model!

From the tables above, I distilled the following model:

It's actually pretty rich: 

• Floors, days and times are connected to eachother by "in-graph-index" relationships. 
• Rooms and talks are there
• Talks are part of a track
• Persons can act as speakers and as track hosts.

There's some fun to be had there. 

Importing it into Neo4j

Naturally, I started with a spreadsheet. I needed to do some copy/pasting and cleaning of the data, and that's what I use that for. But once it was there, generating the Cypher to create the graph was trivial. Here's the create script to do that yourself - just clone it if you want.

Once we had that, we could start doing some querying on the graph. I have put some sample queries over here.

Here are some interesting ones exploring the graph:

This is a little overview of the "conference timeline: just exploring the days and then looking at the timeslots available in that day:

Here we go looking at some talks and tracks and how they are connected to eachother:

And this is probably my favourite: looking at the connectivity between the unfollowable Mark Needham and Yan Cui of Gamesys:

Of course there's plenty more to explore. That's why my friend and colleague Michael Hunger was kind enough to put the database (in read-only mode) on one of his servers - you can take a look at it over here.

Hope you found this useful - see you next week at the conference!

Cheers

Rik

Wasting time as a boothbabe @ Oredev

This blogpost essentially references a GraphGist that I created. Look at it in a full window over here, or below: I hope that was interesting - let me know if you have any feedback.

Cheers

Rik