ReBeerGraph: importing the Belgian BeerGraph straight from a Wikipedia HTML page

I have written about beer a few times, also on this blog. I have figured out a variety of ways to import The Wikipedia Page with all the belgian beers into Neo4j over the years. Starting with a spreadsheet based approach, then importing it from a Google Sheet (with it's great automated .csv export facilities), and then building on that functionality to automatically import the Wikipedia page into a spreadsheet using some funky IMPORTHTML() functions in Google sheets.

But: all of the above have started to crumble recently. The Wikipedia page, is actually kind of a difficult thing to parse automatically, as it splits up the dataset into many different HTML tables (which makes me need to import multiple datasets, really), and then it also seems like Wikipedia has added an additional column to it's data (the "Timeframe" or "Period" in which a beer had been brewn), which had lots of missing, and therefore empty cells. All of that messes up the IMPORTHTML() Google sheet that I had been using to automatically import the page into a gsheet.

So: I had been on the lookout for a different way of doing the automated import. And recently, while I was working on another side project (of course), I actually bumped into it. That's what I want to cover and demonstrate here: importing the data, directly from the page, without intermediate steps, automatically, using the apoc.load.html functionality.

Securing my Beergraph with Neo4j 4.0

Not sure if you have realised, but Neo4j has actually recently made the 4.0 version of the most fantastically awesome graph database on the planet available. You can get it ahead of the big launch event (on February 4th, 2020 - in case you were wondering!) from the Download Center and take it for a spin.

In this unbelievable release, there are so many new features, it's kind of hard to keep track of everything. But the ones that I can most easily get my head around are clearly

1. multi-database support - finally, Neo4j actually has this concept of running multiple databases on one database server. A multi-tenancy solution, that has been requested and anticipated by many of our users and customers. 
2. a VERY advanced schema-based security module, that allows people to extend the existing role-based security model of Neo4j even further - and make it crazy powerful. We'll spend a lot of time on that in this blogpost.

Readers of this blog probably know that I am a big fan of getting my feet down and dirty with our products, so this evening - with a couple of hours to spare, so to speak - I decided to try out the shiny new release. I spun up my Neo4j Desktop, and started reading some manual pages where stuff was explained. Specifically, I loved

• the manual pages on managing multiple databases
• the pages on authentication and authorisation
• a great video by Louise Söderström from Neo4j engineering:

Soon after flipping through this, I was on my way.

Exploring new datatypes in Neo4j 3.4 and the Open Beer Database - part 2/2

In the previous blogpost I imported the Open Beer Database into Neo4j and added some new fancy spatial data to it. Now in this post I would like to explore that data. As a reminder, you can find the full

• load script over here, and 
• some query examples over here.

Let's take a look.

First we will just look at the basic OpenBeerDB data. The schema is quite straightforward:

Exploring new datatypes in Neo4j 3.4 and the Open Beer Database - part 1/2

Recently, I gave a talk at the Amsterdam, Brussels and London Neo4j meetups about some of the new and exciting features in Neo4j 3.4. While preparing for it, I was looking for material and I found some very cool stuff that powerfully explains the new features. The best resource is probably this post by Ryan Boyd, and the video that goes with it:

Ryan does a great job at explaining the new features, and goes into some detail on the new temporal and spatial data types that you can now use in Neo4j 3.4. You can explore these new features yourself by accessing the Neo4j Sandbox developed specifically for this purpose. Or you can just do what I did, and use the Neo4j Desktop to spin up a Neo4j instance, and access the "guide". You do that by typing

:play https://guides.neo4j.com/sandbox/3.4/index.html

into the Neo4j browser, and then you can access the entire guide, add some data to your dataset, and play around.

Graphs are blooming - again!

A few weeks ago, I wrote a two part blog post about Neo4j Bloom and how I was playing around with my BeerGraph and figuring out some cool features of the new Neo4j product. You can find these posts over here

• part 1
• part 2

It included a first little demo video,

that seems to have been liked by a bunch of people :) ... thanks for that.

Part 2/2: Graphs are Bloom-ing

Earlier I wrote about how I connected the newly announced preview version of Neo4j Bloom to my good old faithful Belgian BeerGraph. See part 1 of this 2-part series for that story. I actually split up the story into two parts, because I feel like there's a super interesting and powerful part to Bloom that deserves a bit more attention: the mechanism of the custom Search Phrases.

As we mentioned in the previous post, Bloom structures your exploration and discovery into specific "views" on the graph data, called "Perspectives. You can select the perspective you find most appropriate from a dropdown - and customize/tweak/create perspectives yourself if you are not happy with the auto-generated starting point.

Part 1/2: Graphs are Bloom-ing

Last week something happened that really excited me. We, Neo4j, finally announced Bloom and demonstrated our own Graph Visualisation and Discovery tool, Neo4j Bloom. This is a technology that we have long been pondering, have experimented with in a number of ways, and have long looked to find and develop an offering that would be interesting and differentiated in what is already a very well looked-after marketplace. 

I am not exaggerating when I say that is truly exciting. Not only do many of our customers want to be able to visualise the results of their graph queries, but the graph data model is also unique in the way that it provides such an intuitive, easy to understand data model that lends itself so well to a GRAPH-ical representation. It truly fits into the Graph Platform vision that Neo4j has been advocating since 2017.

Podcast Interview with Gábor Szárnyas, Budapest University of Technology and Economics

Waw. That was probably the longest stretch that I went without publishing blogposts or podcasts over here. I have no real excuse - the start of 2017 has just been super busy and interesting - with a lot of travel that does not really help with quiet "writing" time. But it's all great fun - I just need to get back into the rhythm - and today is the start of that.

Today's podcast is actually super cool. It started at a beautiful Brussels bar after Fosdem. At this conference, there have been "graph devrooms" hosted for the past couple of years - and this year it was a really nice lineup.  One of the speakers, Gábor, did this really interesting talk about "Graph Incremental Queries with OpenCypher", which is really cool. So after the conference, it turned out we share a passion for cycling too - and we decided to get together for a nice recording. Here it is:


Here's the transcript of our conversation:

RVB: 00:04.202 Hello everyone. My name is Rik, Rik Van Bruggen from Neo Technology and I must confess I feel very, very guilty now because this is the first time that I'll be recording a podcast in 2017, so happy new year. In spite of the fact that it's Valentine's Day. But yeah, I was slacking a little bit but I want to bring the podcast back to life and I've lined up a bunch of people to help me with that. And today I've invited someone who I've who only met like two weeks ago at the FOSDEM Conference in Brussels. And that's Gábor Szárnyas from Budapest. Hi Gábor. 

GS: 00:42.680 Hi Rik. Nice to be here. 

RVB: 00:43.500 Hey. Thank you for joining me. It was a great time meeting you in Brussels over some Brussels beer, but yeah we talked to each other about your work and I thought it would be great to have you on the podcast. So my first question is going to be who are you, and what do you do? What's your relationship to the wonderful world of graphs? 

GS: 01:10.158 Okay. So I'm a researcher at Budapest University of Technology and Economics. And also visiting researcher at McGill University in Canada. Now I'm working on finalizing my PhD, so hopefully I will be finish it within a year or a half. And I worked basically on graph- related topics in my PhD. 

RVB: 01:33.134 Oh, very cool. And don't forget you share another passion with me. 

GS: 01:38.380 Yeah, I'm also a cyclist. 

RVB: 01:40.152 Yes, exactly. 

GS: 01:40.729 So I started road cycling three years ago and it absolutely wondered me. I really like cycling-- 

RVB: 01:49.279 Same for me...

GS: 01:50.351 --and that's my main passion. 

RVB: 01:51.948 Same for me. We have a couple of other graphistas that are super passionate about cycling so we'll have to do a ride sometime. But tell us-- 

GS: 01:59.412 I agree. 

RVB: 01:59.558 --a little bit more about your work with graphs. What's it all about, what's your PhD about, and what are you working on? 

GS: 02:07.503 Okay. So my PhD revolves around three topics that are related to graphs. The first one is how to incrementally query graphs. So imagine that you have a complex query and you have a huge graph. Now obviously, it's very difficult to evaluate a query on the graph at a very short amount of time. So basically, as a workaround, we do incremental queries, which means that if your graph changes slightly then we maintain the result sets. And this is useful for a number of scenarios. You can use it for static analysis of code bases, you can use it for runtime modelling, you can use it for fraud detection, and so on. There are many use cases that present this scenario. 

GS: 02:52.025 The second topic of my PhD is how to benchmark an incremental graph query engine. Because, obviously, once you have an incremental graph query engine, you would like to have some feedback on its performance. And you would like to use that to continuously improve your query engine. So, with my research group, we designed and implemented a framework that allows users to do just that. Compare incremental graph query solutions to each other and to other competitors. 

GS: 03:22.765 And the third one-- yes? 

RVB: 03:22.870 Is that related to the LDBC work, the Linked Data Benchmarking Council, is that related to that? 

GS: 03:30.529 So basically they have similar goals. I was actually at Walldorf last week at LDBC Technical User Community Meeting. And LDBC has a couple of benchmarks, but currently none of those covers incremental graph queries and complex graph pattern matching. I talked to the LDBC guys and also attended the talks, and it seemed that there will be a new LDBC benchmark, which will have similar goal than my benchmark. And that will be called the Business Intelligence workload for the Social Network Benchmark. And the problem with that is that it's not yet ready. So I talked to it's core developer, Alex Averbuch, and he said that it will be ready within half a year but they are still heavily working on it. 

RVB: 04:29.082 Okay. But you had said that you had three goals, right? You had the incremental queries and then the benchmarking and what was the third one? 

GS: 04:34.976 The third one is closely related to network theories. A network theory is something that came up in the late '90s in the early nodes when people started to analyze graphs. So they took a graph of people where the nodes were the people in a community and the relationships were if they were friends or not. Or they took the graph of the World Wide Web where the nodes were the web pages and the relationships were the links between the web pages. So they took all these graphs and started to analyze them, and they derived very interesting properties, chief among which was the scale-free property of graphs. There are many papers on scale-free networks, and they discovered that this is very common in biology, in sociology, also in physics and other sciences. 

RVB: 05:28.488 What does that mean, scale-free networks? What does that mean?
GS: 05:30.744 So basically scale-free network means that the degree of distribution of the nodes follow the so-called power law. So you have very few central hubs. And basically, if you remove these hubs from the network then your network will break down to smaller components. And they discovered that this is how societies are organized, this is how citation networks work, and this is how power grids work as well. 

RVB: 06:00.783 Oh wow. Just like a universal structural characteristic of lots of networks. 

GS: 06:06.958 Yes, lots of networks. Obviously you cannot apply to all of the networks but it was a very big surprise to the scientists who worked on it that a lot of networks exhibited this property. So how does my PhD research relate to that? Well interestingly, there wasn't much work performed on tide graphs. So if you see Neo4j graphs, you obviously see that you don't only have people and websites and books, but you have all these inner single graphs. So you have tide graph, and they also have different relationships between them. And only in the last five to ten years have been there research about how to characterise these graphs. These have many interesting names. Some people call them the multiplex networks, others call them the multidimensional networks or multilayered networks. Analysing these is very tricky because obviously you have another dimension of complexity by having to deal with all the types of the nodes and the relationships in the graphs, but it's kind of a green area and you can do a lot of interesting work in it. I actually applied it to engineering models, so my research group works in model driven engineering. And there are engineering models for software, hardware, state machines, system design and so on. And basically we took all these models and analyzed them and we looked for some interesting properties. 

RVB: 07:58.123 Wow. 

GS: 07:59.168 We didn't find any huge results so we didn't find that these models are scale-free or they follow some very famous distribution. But we did have some interesting results on how to characterize these models. 

RVB: 08:18.190 Wow, very cool. So could you tell us a little bit more about how you got into the graph business, or the graph science if I may call it that way? How did you get into it, and why did you get into? 

GS: 08:35.661 Okay. Well, that's an interesting question. I think it started in 2011 when I had to pick my first individual research topic at my university, and my roommate
suggested that I should give a try to node secure databases. I was already very interested in anything that's related to databases, relational or not. So I started to work on node secure databases. And then I soon discovered Neo4j and the property graph data model. And I think what really struck me is how intuitive the graph data model is. There is actually a paper by Marko Rodriguez, who was the implementer of the TinkerPop framework, and he said that graphs are very intuitive because they describe the way that people use when thinking about the world. So people tend to abstract the world as things that are somehow connected. And you can perfectly describe this with graph nodes and graph relationships. So this is something I really like about graphs. And that's something that you also mentioned in this podcast, I think a couple of times, that you can use a whiteboard and then just start brainstorming, and having ideas, and drawing a graph. And you can use pretty much the same graph in your applications as well. So that's my favourite thing. 

RVB: 10:07.046 Jokingly, I always talk about my own acronym, which is WYDIWYS, what you draw is what you store. 

GS: 10:14.439 Yeah, that's a catchy acronym actually. 

RVB: 10:18.913 It's been repeated so many times on this podcast but it is a very big strength of graphs, right? The model is so intuitive and so descriptive, so rich, really. That makes a whole lot of difference, right? So I'm reading that that's also how you got into it, right? That's also why you think it's very valuable? Is that right? 

GS: 10:43.860 Yes. So basically after I got a bit familiar with the topic, I started my master's at university. And already during my master's I was working on the incremental query engine that I'm still working on today. So it's quite a long project. I've been doing this for five-plus years. And I really liked my experience during the master's so I joined the PhD and I just finished PhD school three weeks ago. So now it's only-- 

RVB: 11:11.500 Congratulations [laughter]. 

GS: 11:13.087 Thank you. So it's only up to me to publish some more papers and polish a dissertation. 

RVB: 11:21.283 So what does the future hold, Gabor? Where is it going for you personally? Where is your research taking you, but also how do you look at this taking ground in the broader industry? What's the future hold if you had a crystal ball? 

GS: 11:36.571 So, I would really like to be an academic. I really enjoy working at university because you have so many positive experiences with students. You can pretty much follow your own dreams and do research in almost whatever interests you the most. Obviously you have to fit within your grant proposals and your funding but this still gives you a lot of way to be creative and I would like to be a university lecturer and researcher in the future. So that's my kind of dream career. And-- yes? 

RVB: 12:17.317 And is it lecturing and teaching about graphs then or is it on a broader topic or is it computer science or what will be the topic then? Or topics? 

GS: 12:26.893 Well, I'm pretty much happy to teach anything relates to computer science, so I've taught topics from database theory to automata theory, system modelling, and software engineering topics, and also some laboratories on actual technologies. So our university is a bit of a mix between computer science and computer engineering. So we teach both theoretical and practical stuff and this is something that I also really enjoy. 

RVB: 13:01.647 Super. And what about the wonderful world of graphs and graph databases, is there anything like that in your future you think? 

GS: 13:10.251 Yes. So I really would like to get a version of my graph query engine that can be used by other researchers. I obviously understand that implementing production-grade software is not really possible within the limits of a PhD. But I would like to release a system that can be used at least by other researchers, both in academia and both in industry. I talked to a lot of people about this and it seemed that people would actually be interested in trying such a system, or benchmarking such system, and see how it works for their use cases. 

RVB: 13:49.818 Super. So final question, what's your favourite cycling destination? 

GS: 13:54.706 Ooh, that's a tricky question [laughter]. 

RVB: 13:56.737 Curveball for you. 

GS: 13:56.958 But actually, it's not a very common answer. I live next to the Hungarian-Austrian border, so I do go a lot to Austria because Austria has the best roads in Europe, and also most of the country is the Alps. So I live next to the lower Alps section, but even there you have very nice hills, and drivers are really polite, and you have these super flat tarmac all over the country. And that's what I really enjoy and I'm really looking forward to the summer. So I just usually disappear from the university for a couple of weeks and then go home and cycle. 

RVB: 14:38.375 Excellent. So no cobblestones for you? Unlike Flanders Classics or something like that? 

GS: 14:44.387 I actually really like riding the [inaudible], so I live in the inner historical district of Budapest and we still have a lot of cobblestone roads. And when I just started cycling in Budapest just to get to work and commute I usually tended to avoid those sections. But since I'm more into cycling I just go for the most cobblestoney sections [laughter]. This is something that you learn to enjoy or at least you think you enjoy it. 

RVB: 15:16.963 Yeah, yeah. Exactly. Very, very cool. All right. Well, I hope we get to ride one day together, that would be great. I really enjoyed this conversation. Thank you for taking the time. And I look forward to meeting you again someday, at FOSDEM or somewhere else. 

GS: 15:32.360 Thank you, for an invitation and we should definitely go for a ride. 

RVB: 15:36.138 Absolutely. Thank you, Gábor. 

GS: 15:38.717 Thanks. Bye

Subscribing to the podcast is easy: just add the rss feed or add us in iTunes! Hope you'll enjoy it!

All the best

Rik

Querying the SNAP Beeradvocate dataset in Neo4j - part 3

In the previous part of this blog post series of three posts, I imported the the SNAP Beeradvocate dataset into Neo4j. All good and well, and we now have the following meta-graph:

So now we can start querying the dataset. It's a bit different from the "Belgian Beer" dataset that I worked on previously - this one is a lot bigger, and also a bit more US-focused. But still - we can do some nice queries on it. Let's start with somethng nice and simple:

//where is Duvel 

match (b:Beer {name:"Duvel"}) return b 

//where is Duvel and surroundings 

match (b:Beer {name:"Duvel"})-[r]-()return b,rlimit 50

The result is interesting:

Then we try the same for Orval.

match (b:Beer {name:"Orval"}) return b 

does not return anything. So let's see if we can find it some other way:

The following query:

match (b:Beer)
where left(b.name,5) = "Orval"
return b  

tries to find Beers that have their name START with the word Orval. And yes indeed, we find it immediately.

And I am very happy to report that this query actually taught me something new about Orval. Even though I am a big fan and have been to the brewery multiple times - I had never drank the "Petite Orval", a lighter trappist beer that is only brewn for the monks. See wikipedia for more details. 

So let's take a look at some interesting paths. Here's a path between Duvel and Orval:

match (d:Beer {name:"Duvel"}), (o:Beer {name: "Orval Trappist Ale"}),  path = shortestpath((d)-[*]-(o))  return path  

that gives an (*one*) interesting path:

I really need to find that "Duvel Single" beer and taste it.

Or look at some additional paths, and run this query:

match (d:Beer {name:"Duvel"}), (o:Beer {name: "Orval Trappist Ale"}),
path = allshortestpaths((d)-[*]-(o))
return path
limit 10  

I have put in place the LIMIT to not make the browser blow up. The result is interesting:

There seem to be quite a few REVIEWERS that are reviewing both beers. That's interesting of course, but let's say that I would not want the reviewers/reviews be part of the path? Well, turns that "excluding" nodes from a shortestpath function is not that easy in Cypher - you are better of including the relationship types that you want to have included. Like this:

//link between Duvel and Orval discarding the reviewers and reviews 

match (d:Beer {name:"Duvel"}), (o:Beer {name: "Orval Trappist Ale"}), path = allshortestpaths((d)-[:BREWS | HAS_STYLE*]-(o))  return path  

This query gives yet another interesting result:

Turns out a the brewery seems to be brewing a number of similar beers to Orval! Not sure how true this is - but worth an investigation!

Last but not least, is the search for my favourite beer style - the Trappist beers. Now, this is kind of tricky, as the dataset that we are working with here is kind of American focused - and not all the beer brands or styles are as I would expect them to be. On top of that, we currently don't have "fulltext" search capabilities in the wonderful new "Schema indexes" (we do have them in the legacy indexes, but I am not using those here), so we have to work around that with a regular expression in the query. Let me show you:

//Find the Trappist beers, their breweries and styles 

match (br:Brewery)--(b:Beer)--(s:Style)
where b.name =~ ".*\\bTrappist\\b.*"
OR s.name =~ ".*\\bTrappist\\b.*"
return b,br,s;  

gives us all the beers, breweries and styles that have the word "Trappist" in their beer or style names. It gives us a really interesting subgraph to take a look at:

Seems like the good news is that I have quite a bit of beer exploration to do!

That concludes this third part of the Beeradvocate network dataset exploration in Neo4j. There's a lot of other stuff that we could do with this dataset - but I hope you already found it as interesting as I did - and as always, please send me your feedback!

Cheers

Rik

PS: Here are the links to

• Part 1
• Part 2

Importing the SNAP Beeradvocate dataset into Neo4j - part 2

After the previous post on the SNAP Beeradvocate dataset, we were ready to import the dataset into Neo4j. We had 15 .csv files, perfect for a couple of runs of Load CSV.

The first I needed to do was to create a graph model out of my CSV files. Here's what I picked:

So then I would need to create a series of Load CSV commands to import these. And this is where it got it got interesting. I created the Cypher queries myself, and found that they worked fine - except for one part. This was the part where I had to add the reviews to the graph. This was my query:

 using periodic commit  
 load csv with headers  
 from "file:/Users/rvanbruggen/Dropbox/Neo Technology/Demo/BEER/BeerAdvocate/real/ba7.csv" as csv  
 fieldterminator ';'  
 with csv  
 where csv.review_profileName is not null  
 match (b:Beer {name: csv.beer_name}), (p:Profile {name: csv.review_profileName})  
 create (p)-[:CREATES_REVIEW]->(r:Review {taste: toFloat(csv.review_taste), appearance: toFloat(csv.review_appearance), text: csv.review_text, time: toInt(csv.review_time), aroma: toFloat(csv.review_aroma), palate: toFloat(csv.review_palate), overall: toFloat(csv.review_overall)})-[:REVIEW_COVERS]->(b);  

On some of the import files (remember I had 15) this query would fail - run out of Heap space. Now this is a very tricky thing to troubleshoot in Neo4j, so I had to call for help. My colleagues all immediately volunteered, and of course within the hour Michael had reengineered everything.

The first thing Michael asked was my query plan (using the EXPLAIN) commando: this was particularly interesting. Michael saw that there was a step in there that was called "Eager". Mark has blogged about this elsewhere already, and it was clear that we had to get rid of this.

Here's the query that Michael suggested:

 //the query below is NO LONGER PROBLEMATIC  
 using periodic commit  
 load csv with headers from "file:/Users/rvanbruggen/Dropbox/Neo Technology/Demo/BEER/BeerAdvocate/real/ba15.csv" as csv fieldterminator ';'  
 with csv where csv.review_profileName is not null  
 create (r:Review {taste: toFloat(csv.review_taste), appearance: toFloat(csv.review_appearance), text: csv.review_text, time: toInt(csv.review_time), aroma: toFloat(csv.review_aroma), palate: toFloat(csv.review_palate), overall: toFloat(csv.review_overall)})  
 with r,csv  
 match (b:Beer {name: csv.beer_name})  
 match (p:Profile {name: csv.review_profileName})  
 create (p)-[:CREATES_REVIEW]->(r)  
 create (r)-[:REVIEW_COVERS]->(b);  
 // takes 13s  

You can find the two import scripts on github:

• this is my old version (which DID NOT WORK, at least not always)
  UPDATE: in the original version of this blogpost, I was working with version 2.2.0 of Neo4j. Recently, 2.2.1 was released - and guess what: the queries run just fine. Apparently the team had made some change to how Neo4j handles composite merge updates - and it now just flies through all queries, even with my old, sub-optimal version of the queries.. Kudos!
• this is Michael's version (which, of course, WORKS)
  UPDATE: I would still recommend using this version of the queries :) 

Let's explore some of the differences.

1. Michael's version included the same indexes as mine - but also included a UNIQUENESS CONSTRAINT. This seems to be a good idea because it makes the MERGE-ing of the data unnecessary - you can just CREATE instead.
2. Michael's version does "one MERGE at a time". Rather than merging in entire patterns like

merge (b)-[:HAS_STYLE]-(s:Style {name: csv.beer_style})

you instead do

merge (s:Style {name: csv.beer_style})

and then merge (b)-[:HAS_STYLE]->(s)

3. you reorder certain parts of the query to come earlier in the sequence. I noticed that he did the CREATE of the review first,  then transferred that result into the next part of the query with WITH, and then did two matches (for Beers and Profiles) to connect the Review to the appropriate Beer and Profile. To be honest, this seems to have been a bit of a trial and error search - but we found out after talking to the awesome devteam that this should no longer be necessary as from the forthcoming version 2.2.1 of Neo4j. 

The result is pretty awesome. After having run the entire import (which means running the same import 15 times - see over here for the complete script) I got a pretty shiny new database to play around with:

In the last part of this blog-series, I will be doing some fancy queries. Really looking forward to it :))

Hope you found this useful.

Cheers

Rik

PS: Here are the links to

• Part 1
• Part 3

Importing the SNAP Beeradvocate dataset into Neo4j - part 1

As you may or may not know, I am a big fan of Beer. And I am a big fan of Neo4j. I did a talk about this at GraphConnect two years ago ...

- and I have been doing that demo a lot with lots of users, customers and meetups. Up to the point where I was really working up a reputation as a "Beer Guy". Not sure if that is good or bad...

So some time ago I learned about the SNAP: Stanford Network Analysis Project, who mention an example dataset that is really interesting: 1.5 million Beer-reviews from Beeradvocate between January 1998 and November 2011. Here are some stats:
And then at the bottom of the page it says:

Disappointment!!! But then one simple Google search led me to this blob and a 445 Mbyte download later, we were in business. Unzip the thing, and we have a 1.5Gbyte text file to play with.

Importing the data? Not quite, yet!

Once I had downloaded the data, I found that there were two big "bears" on the road. Two problems that I would have to solve.

• Problem 1: the size of the data. I would not really call this file huge, but on my little laptop (I "only" have 8gbyte of RAM on my machine) it can get kind of tricky to work with biggish files like that... Based on experience - I just know I will get into trouble, and will need to work around that.
• Problem 2: the structure of the data. Here's a look at the file:

This definitely does not look very Neo4j-import friendly. I need to do some transformations there to make the thing easy to import.

So how did I go about solving this? Read on.

Solution 1: splitting the text file

The first thing I set out to do was to split the text file into different parts, so that it would be easier to handle and transform along the way. I looked around for a while, and then found the split bash command - by far the simplest, most performant and straightforward option.  Played around with different options (different sizes for the splits - I first had 6 files, then 3, and ended up choosing to split into 15 100 Mbyte files), and eventualy used this simple command:

split -b 100m beeradvocate.txt

This was the result

Nice - 15 perfectly manageable files! Once done that, needed to assure that the split was not completely arbitrary, and that "whole records" were included in every file. Easy peasy with some simple copying and pasting from the end of each file to the beginning of the next file - done in less than 5 minutes! Job done!

Solution 2: transforming the .txt files

Then I needed to get these manageable files into a .csv format that I could import into Neo4j. This was more complicated. I needed to go 

• from a structure that had records in blocks of 14 lines, with a property field on every line
• to a .csv file that had a row per record, and fields in comma-separated columns

That's quite a transformation, for which I would need some tooling. I decided on trying OpenRefine. I had heard about this tool a couple of years ago already, but never made any serious use of it. Now I thought it would come in handy, as I would have to go through every step of the transformation 15 times - once for every .txt file that we generated above.

So I fired it up, and created the first of 15 "projects" in Refine. This is what it looked like before the transformation :

After one parsing operation using the "line-based text file" transformation, I already got a preview that looked like this:

Already looks kind of right - at least I already have a row per record now.

Now I would need to do some manipulations before the text file became usable. Google refine has this really wonderful transformation tool that allows you to create manipulation steps that you can execute and process step after step. The main steps were:

• extracting the "field name" from every cell, and just leave the data in there. Every cell currently was structured as "<<field name>>: <<field value>>", and I would want every cell to just contain the "<<field value>>".
• renaming the columns so that my .csv file would have nice workable headers. This is not mandatory - but I found that easier.

When you do this manually, it can be a bit cumbersome and repetitive - so you definitely don't want to do this 15 times. That's where Refine is so nice: you can extract a .json file that specifies all the operations, and then apply these operations to forthcoming "projects" afterwards time and time again.

So you do it once, and then you can extract that .json so that you can use it later on the other 14 files - which is exactly what I wanted.

That .json file is also on github.

The result was exactly what we wanted: a comma-separated file that would be ready for import into Neo4j.

Download the file and we are done with the import preparations for this file. Of course you would need to do this 15 time, but thanks to the .json file with the operations steps, that was really easy.

You can find the sample files (of both the .txt file, the Refine operations .json and the resulting .csv file) are on github.

That was it for part 1. We now have everything ready to do an import. In part 2 we will do the actual import into Neo4j and start playing around with it.

Hope you enjoyed this.

Cheers

Rik

PS: Here are the links to

• Part 2
• Part 3

GraphConnect Video treasures!

You should really be taking a look at all the wonderful GraphConnect San Francisco videos on watch.neo4j.org - they are simply awesome. So much detail wonderful information in these talks - it's just such a pleasure to watch. Oh, and in case you're interested, here's my little beertalk:
Graphs and Beer - Rik Van Bruggen @ GraphConnect SF 2013 from Neo Technology on Vimeo.

Hope you enjoy!