markhneedham/blog huggingface dataset:
Neo4j 2.0.0: Cypher - Index Hints and Neo.ClientError.Schema.NoSuchIndex
Date: 2014-01-31 07:14:53

One of the features added into the more recent versions of Neo4j's cypher query language is the ability to http://docs.neo4j.org/chunked/stable/query-using.html[tell Cypher which index you'd like to use in your queries].

We'll use the football dataset, so let's start by creating an index on the 'name' property of nodes labelled 'Player':

[source,cypher]
----

CREATE INDEX ON :Player(name)
----

Let's say we want to write a query to find 'Wayne Rooney' while explicitly using this index. We might start with the following query:

[source,cypher]
----

MATCH p
USING INDEX p:Player(name)
WHERE p.name = "Wayne Rooney"
RETURN p
----

If we run that we'll see this error:

[source,text]
----

Cannot use index hint in this context. The label and property comparison must be specified on a non-optional node
Label: `Player`
Property name: `name`
Neo.ClientError.Schema.NoSuchIndex
----

We need to specify the label on the node in the 'MATCH' part of the query for our hint to work e.g.

[source,cypher]
----

MATCH (p:Player)
USING INDEX p:Player(name)
WHERE p.name = "Wayne Rooney"
RETURN p
----

Now we might decide that we want to find 'Wayne Rooney' or 'Robin Van Persie' so we change our query slightly:

[source,cypher]
----

MATCH (p:Player)
USING INDEX p:Player(name)
WHERE p.name = "Wayne Rooney" OR p.name = "Robin Van Persie"
RETURN p
----

But this one fails too!

[source,cypher]
----

Cannot use index hint in this context. The label and property comparison must be specified on a non-optional node
Label: `Player`
Property name: `name`
Neo.ClientError.Schema.NoSuchIndex
----

The problem here is that when you use 'OR' it's currently not using an index but rather a label scan so the hint to use the index doesn't make sense to Cypher.

The final way I've seen people get confused using index hints is when matching node properties inline e.g.

[source,cypher]
----

MATCH (p:Player {name: "Wayne Rooney"})
USING INDEX p:Player(name)
RETURN p
----

If we run that we'll be back in the land of exceptions:

[source,text]
----

Cannot use index hint in this context. The label and property comparison must be specified on a non-optional node
Label: `Player`
Property name: `name`
Neo.ClientError.Schema.NoSuchIndex
----

We can work around that by pulling out the inline matching of the 'name' property":

[source,cypher]
----

MATCH (p:Player)
USING INDEX p:Player(name)
WHERE p.name = "Wayne Rooney"
RETURN p
----
Neo4j HA: Election could not pick a winner
Date: 2014-01-24 10:30:41

Recently I've been spending a reasonable chunk of my time helping people get up and running with their http://docs.neo4j.org/chunked/stable/ha-setup-tutorial.html[Neo4j High Availability cluster] and there's sometimes confusion around how it should be configured.

A Neo4j cluster typically consists of a master and two slaves and you'd usually have it configured so that any machine can be the master.

However, there is a http://docs.neo4j.org/chunked/stable/ha-configuration.html[configuration parameter] 'ha.slave_only' which can be set to 'true' to ensure that a machine will never be elected as master when an election takes place.

We might configure our machine with that setting if it's acting as a reporting instance but we need to make sure that 2 members don't have that setting otherwise we won't have any failover in the cluster.

For example, if we set two of the machines in the cluster to be slave only and then stop the master we'll see output similar to the following in +++<cite>+++data/graph.db/messages.log+++</cite>+++:

[source,text]
----

2014-01-23 11:17:24.510+0000 INFO  [o.n.c.p.a.m.MultiPaxosContext$ElectionContextImpl]: Doing elections for role coordinator
2014-01-23 11:17:24.510+0000 DEBUG [o.n.c.p.e.ElectionState$2]: ElectionState: election-[performRoleElections]->election from:cluster://10.239.8.251:5001 conversation-id:3/13#
2014-01-23 11:17:24.513+0000 DEBUG [o.n.c.p.e.ElectionState$2]: ElectionState: election-[vote:coordinator]->election from:cluster://10.151.24.237:5001 conversation-id:3/13#
2014-01-23 11:17:24.515+0000 DEBUG [o.n.c.p.e.ElectionState$2]: ElectionState: election-[voted]->election from:cluster://10.138.29.197:5001 conversation-id:3/13#
2014-01-23 11:17:24.516+0000 DEBUG [o.n.c.p.e.ElectionState$2]: ElectionState: election-[voted]->election from:cluster://10.151.24.237:5001 conversation-id:3/13#
2014-01-23 11:17:24.519+0000 DEBUG [o.n.c.p.a.m.MultiPaxosContext$ElectionContextImpl$2]: Elections ended up with list []
2014-01-23 11:17:24.519+0000 WARN  [o.n.c.p.e.ElectionState]: Election could not pick a winner
----

This message initially looks confusing but what it's telling us is that the cluster was unable to elect a new master, in this case because there were no machines that could be elected as master.

So if you see that message in your logs, check your config to make sure that there are actually machines to choose from.
Neo4j: Set Based Operations with the experimental Cypher optimiser
Date: 2014-06-29 08:45:34

A few months ago I wrote about http://www.markhneedham.com/blog/2014/02/20/neo4j-cypher-set-based-operations/[cypher queries which look for a missing relationship] and showed how you could optimise them by re-working the query slightly.

To refresh, we wanted to find all the people in the London office that I hadn't worked with given this model\...

image:{{<siteurl>}}/uploads/2014/02/2014-02-18_17-04-01.png[]</img>

\...and this initial query:

[source,cypher]
----

MATCH (p:Person {name: "me"})-[:MEMBER_OF]->(office {name: "London Office"})<-[:MEMBER_OF]-(colleague)
WHERE NOT (p-[:COLLEAGUES]->(colleague))
RETURN COUNT(colleague)
----

This took on average 7.46 seconds to execute using https://github.com/mneedham/cypher-query-tuning[cypher-query-tuning] so we came up with the following version which took 150 ms on average:

[source,cypher]
----

MATCH (p:Person {name: "me"})-[:COLLEAGUES]->(colleague)
WITH p, COLLECT(colleague) as marksColleagues
MATCH (colleague)-[:MEMBER_OF]->(office {name: "London Office"})<-[:MEMBER_OF]-(p)
WHERE NOT (colleague IN marksColleagues)
RETURN COUNT(colleague)
----

With the http://neo4j.com/blog/neo4j-2-1-graph-etl/[release of Neo4j 2.1] we can now make use of Ronja - the experimental Cypher optimiser - which performs much better for certain types of queries. I thought I'd give it a try against this one.

We can use the experimental optimiser by prefixing our query like so:

[source,cypher]
----

cypher 2.1.experimental MATCH (p:Person {name: "me"})-[:MEMBER_OF]->(office {name: "London Office"})<-[:MEMBER_OF]-(colleague)
WHERE NOT (p-[:COLLEAGUES]->(colleague))
RETURN COUNT(colleague)
----

If we run that through the query tuner we get the following results:

[source,bash]
----

$ python set-based.py

cypher 2.1.experimental MATCH (p:Person {name: "me"})-[:MEMBER_OF]->(office {name: "London Office"})<-[:MEMBER_OF]-(colleague)
WHERE NOT (p-[:COLLEAGUES]->(colleague))
RETURN COUNT(colleague)
Min 0.719580888748 50% 0.723278999329 95% 0.741609430313 Max 0.743646144867


MATCH (p:Person {name: "me"})-[:COLLEAGUES]->(colleague)
WITH p, COLLECT(colleague) as marksColleagues
MATCH (colleague)-[:MEMBER_OF]->(office {name: "London Office"})<-[:MEMBER_OF]-(p)
WHERE NOT (colleague IN marksColleagues)
RETURN COUNT(colleague)
Min 0.706955909729 50% 0.715770959854 95% 0.731880950928 Max 0.733670949936
----

As you can see there's not much in it - our original query now runs as quickly as the optimised one. Ronja #ftw!

Give it a try on your slow queries and see how it gets on. There'll certainly be some cases where it's slower but over time it should be faster for a reasonable chunk of queries.
Data Science: Mo' Data Mo' Problems
Date: 2014-06-28 23:35:25

Over the last couple of years I've worked on several proof of concept style Neo4j projects and on a lot of them people have wanted to work with their entire data set which I don't think makes sense so early on.

In the early parts of a project we're trying to *prove out our approach* rather than prove we can handle big data - something that http://www.markhneedham.com/blog/2012/09/30/data-science-making-sense-of-the-data/[Ashok taught me a couple of years ago on a project we worked on together].

In a Neo4j project that means coming up with an effective way to model and query our data and if we lose track of this it's very easy to get sucked into working on the big data problem.

This could mean optimising our import scripts to deal with huge amounts of data or working out how to handle different aspects of the data (e.g. variability in shape or encoding) that only seem to reveal themselves at scale.

These are certainly problems that we need to solve but in my experience they end up taking much more time than expected and therefore aren't the best problem to tackle when time is limited. Early on we want to create some momentum and keep the feedback cycle fast.

We probably want to tackle the data size problem as part of the http://thinkrelevance.com/blog/2012/04/04/big-data-reference-model[implementation/production stage] of the project to use Michael Nygaard's terminology.

At this stage we'll have some confidence that our approach makes sense and then we can put aside the time to set things up properly.

I'm sure there are some types of projects where this approach doesn't make sense so I'd love to hear about them in the comments so I can spot them in future.
Neo4j: Cypher - Finding movies by decade
Date: 2014-06-28 11:12:30

I was recently asked how to find the *number of movies produced per decade* in the movie data set that comes with the Neo4j browser and can be imported with the following command:

[source,bash]
----

:play movies
----

We want to get one row per decade and have a count alongside so the easiest way is to start with one decade and build from there.

[source,cypher]
----

MATCH (movie:Movie)
WHERE movie.released >= 1990 and movie.released <= 1999
RETURN 1990 + "-" + 1999 as years, count(movie) AS movies
ORDER BY years
----

Note that we're doing a label scan of all nodes of type +++<cite>+++Movie+++</cite>+++ as there are no indexes for range queries. In this case it's fine as we have few movies but If we had 100s of thousands of movies then we'd want to optimise the WHERE clause to make use of an http://docs.neo4j.org/chunked/stable/query-where.html#query-where-patterns[IN] which would then use any indexes.

If we run the query we get the following result:

[source,bash]
----

==> +----------------------+
==> | years       | movies |
==> +----------------------+
==> | "1990-1999" | 21     |
==> +----------------------+
==> 1 row
----

Let's pull out the start and end years so they're explicitly named:

[source,cypher]
----

WITH 1990 AS startDecade, 1999 AS endDecade
MATCH (movie:Movie)
WHERE movie.released >= startDecade and movie.released <= endDecade
RETURN startDecade + "-" + endDecade as years, count(movie)
ORDER BY years
----

Now we need to create a collection of start and end years so we can return more than one. We can use the http://docs.neo4j.org/chunked/stable/query-unwind.html[UNWIND] function to take a collection of decades and run them through the rest of the query:

[source,cypher]
----

UNWIND [{start: 1970, end: 1979}, {start: 1980, end: 1989}, {start: 1980, end: 1989}, {start: 1990, end: 1999}, {start: 2000, end: 2009}, {start: 2010, end: 2019}] AS row
WITH row.start AS startDecade, row.end AS endDecade
MATCH (movie:Movie)
WHERE movie.released >= startDecade and movie.released <= endDecade
RETURN startDecade + "-" + endDecade as years, count(movie)
ORDER BY years
----

[source,bash]
----

==> +----------------------------+
==> | years       | count(movie) |
==> +----------------------------+
==> | "1970-1979" | 2            |
==> | "1980-1989" | 2            |
==> | "1990-1999" | 21           |
==> | "2000-2009" | 13           |
==> | "2010-2019" | 1            |
==> +----------------------------+
==> 5 rows
----

https://twitter.com/apcj[Alistair] pointed out that we can simplify this even further by using the http://docs.neo4j.org/chunked/stable/syntax-collections.html#_collections_in_general[RANGE] function:

[source,cypher]
----

UNWIND range(1970,2010,10) as startDecade
WITH startDecade, startDecade + 9 as endDecade
MATCH (movie:Movie)
WHERE movie.released >= startDecade and movie.released <= endDecade
RETURN startDecade + "-" + endDecade as years, count(movie)
ORDER BY years
----

And here's a http://gist.neo4j.org/?84dd2c6729b08f674b3b[graph gist] for you to play with.
Ruby: Regex - Matching the Trademark ™ character
Date: 2014-06-08 01:34:03

I've been http://gist.neo4j.org/?6e24a3bb09766e62b0b1[playing around with some World Cup data] and while cleaning up the data I wanted to strip out the year and host country for a world cup.

I started with a string like this which I was reading from a file:

[source,text]
----

1930 FIFA World Cup Uruguay ™
----

And I wanted to be able to extract just the 'Uruguay' bit without getting the trademark or the space preceding it. I initially tried the following to match all parts of the line and extract my bit:

[source,ruby]
----

p text.match(/\d{4} FIFA World Cup (.*?) ™/)[1]
----

Unfortunately that doesn't actually compile:

[source,text]
----

tm.rb:4: syntax error, unexpected $end, expecting ')'
p text.match(/\d{4} FIFA World Cup (.*?) ™/)[1]
                                           ^
----

I was initially able to work around the problem by http://books.google.co.uk/books?id=0Msuh5Vq-uYC&pg=PT102&lpg=PT102&dq=ruby+regex+trademark+character&source=bl&ots=fIXwQaJOaK&sig=hH8mmjxhHGe8iyvXT0CakRv8Ods&hl=en&sa=X&ei=_1aTU_aGGcWcyAScuYCYDQ&ved=0CCYQ6AEwAA#v=onepage&q=ruby%20regex%20trademark%20character&f=false[matching the unicode code point] instead:

[source,text]
----

p text.match(/\d{4} FIFA World Cup (.*?) \u2122/)[1]
----

While working on this blog post I also remembered that you can http://stackoverflow.com/questions/1739836/invalid-multibyte-char-us-ascii-with-rails-and-ruby-1-9[specify the character set of your Ruby file] and by default it's ASCII which would explain why it doesn't like the ™ character.

If we add the following line at the top of the file then we can happily use the ™ character in our regex:

[source,ruby]
----

# encoding: utf-8
# ...
p text.match(/\d{4} FIFA World Cup (.*?) ™/)[1]
# returns "Uruguay"
----

This post therefore ends up being more of a reminder for future Mark when he comes across this problem again having forgotten about Ruby character sets!
R: Applying a function to every row of a data frame
Date: 2014-12-04 06:31:02

In my continued exploration of London's meetups I wanted to calculate the distance from meetup venues to a centre point in London.

I've created https://gist.github.com/mneedham/7e926a213bf76febf5ed[a gist containing the coordinates of some of the venues that host NoSQL meetups in London town] if you want to follow along:

[source,r]
----

library(dplyr)

# https://gist.github.com/mneedham/7e926a213bf76febf5ed
venues = read.csv("/tmp/venues.csv")

venues %>% head()
##                        venue      lat       lon
## 1              Skills Matter 51.52482 -0.099109
## 2                   Skinkers 51.50492 -0.083870
## 3          Theodore Bullfrog 51.50878 -0.123749
## 4 The Skills Matter eXchange 51.52452 -0.099231
## 5               The Guardian 51.53373 -0.122340
## 6            White Bear Yard 51.52227 -0.109804
----

Now to do the calculation. I've chosen the Centre Point building in Tottenham Court Road as our centre point. We can use the +++<cite>+++distHaversine+++</cite>+++ function in the +++<cite>+++geosphere+++</cite>+++ library allows us to do the calculation:

[source,r]
----

options("scipen"=100, "digits"=4)
library(geosphere)

centre = c(-0.129581, 51.516578)
aVenue = venues %>% slice(1)
aVenue
##           venue   lat      lon
## 1 Skills Matter 51.52 -0.09911
----

Now we can calculate the distance from Skillsmatter to our centre point:</pr> ~~~r distHaversine(c(aVenue$lon, aVenue$lat), centre) ## [1] 2302 ~~~

That works pretty well so now we want to apply it to every row in the venues data frame and add an extra column containing that value.

This was my first attempt\... ~~~r venues %>% mutate(distHaversine(c(lon,lat),centre)) ## Error in .pointsToMatrix(p1): Wrong length for a vector, should be 2 ~~~

\...which didn't work quite as I'd imagined!

I eventually found my way to the by function which allows you to 'apply a function to a data frame split by factors'. In this case I wouldn't be grouping rows by a factor - I'd apply the function to each row separately.

I wired everything up like so: ~~~r distanceFromCentre = by(venues, 1:nrow(venues), function(row) { distHaversine(c(row$lon, row$lat), centre) }) distanceFromCentre %>% head() ## 1:nrow(venues) ## 1 2 3 4 5 6 ## 2301.6 3422.6 957.5 2280.6 1974.1 1509.5 ~~~ We can now add the distances to our venues data frame: ~~~r venuesWithCentre = venues %>% mutate(distanceFromCentre = by(venues, 1:nrow(venues), function(row) { distHaversine(c(row$lon, row$lat), centre) })) venuesWithCentre %>% head() ## venue lat lon distanceFromCentre ## 1 Skills Matter 51.52 -0.09911 2301.6 ## 2 Skinkers 51.50 -0.08387 3422.6 ## 3 Theodore Bullfrog 51.51 -0.12375 957.5 ## 4 The Skills Matter eXchange 51.52 -0.09923 2280.6 ## 5 The Guardian 51.53 -0.12234 1974.1 ## 6 White Bear Yard 51.52 -0.10980 1509.5 ~~~

Et voila!
R: data.table/dplyr/lubridate - Error in wday(date, label = TRUE, abbr = FALSE) :  unused arguments (label = TRUE, abbr = FALSE)
Date: 2014-12-11 19:03:06

I spent a couple of hours playing around with http://cran.r-project.org/web/packages/data.table/index.html[data.table] this evening and tried changing some code written using a data frame to use a data table instead.

I started off by building a data frame which contains all the weekends between 2010 and 2015\...

[source,r]
----

> library(lubridate)

> library(dplyr)

> dates = data.frame(date = seq( dmy("01-01-2010"), to=dmy("01-01-2015"), by="day" ))

> dates = dates %>% filter(wday(date, label = TRUE, abbr = FALSE) %in% c("Saturday", "Sunday"))
----

\...which works fine:

[source,r]
----

> dates %>% head()
         date
1: 2010-01-02
2: 2010-01-03
3: 2010-01-09
4: 2010-01-10
5: 2010-01-16
6: 2010-01-17
----

I then tried to change the code to use a data table instead which led to the following error:

[source,r]
----

> library(data.table)

> dates = data.table(date = seq( dmy("01-01-2010"), to=dmy("01-01-2015"), by="day" ))

> dates = dates %>% filter(wday(date, label = TRUE, abbr = FALSE) %in% c("Saturday", "Sunday"))
Error in wday(date, label = TRUE, abbr = FALSE) :
  unused arguments (label = TRUE, abbr = FALSE)
----

I wasn't sure what was going on so I went back to the data frame version to check if that still worked\...

[source,r]
----

> dates = data.frame(date = seq( dmy("01-01-2010"), to=dmy("01-01-2015"), by="day" ))

> dates = dates %>% filter(wday(date, label = TRUE, abbr = FALSE) %in% c("Saturday", "Sunday"))
Error in wday(c(1262304000, 1262390400, 1262476800, 1262563200, 1262649600,  :
  unused arguments (label = TRUE, abbr = FALSE)
----

\...except it now didn't work either! I decided to check what +++<cite>+++wday+++</cite>+++ was referring to\...

____
Help on topic 'wday' was found in the following packages: Integer based date class (in package data.table in library /Library/Frameworks/R.framework/Versions/3.1/Resources/library) Get/set days component of a date-time. (in package lubridate in library /Library/Frameworks/R.framework/Versions/3.1/Resources/library)
____

\...and realised that data.table has its own wday function - I'd been caught out by R's global scoping of all the things!

We can probably work around that by the order in which we require the various libraries but for now I'm just prefixing the call to wday and all is well:

[source,r]
----

dates = dates %>% filter(lubridate::wday(date, label = TRUE, abbr = FALSE) %in% c("Saturday", "Sunday"))
----
R: String to Date or NA
Date: 2014-12-07 19:29:01

I've been trying to clean up a CSV file which contains some rows with dates and some not - I only want to keep the cells which do have dates so I've been trying to work out how to do that.

My first thought was that I'd try and find a function which would convert the contents of the cell into a date if it was in date format and NA if not. I could then filter out the NA values using the +++<cite>+++http://www.statmethods.net/input/missingdata.html[is.na]+++</cite>+++ function.

I started out with the +++<cite>+++http://www.statmethods.net/input/dates.html[as.Date]+++</cite>+++ function\...

[source,r]
----

> as.Date("2014-01-01")
[1] "2014-01-01"

> as.Date("foo")
Error in charToDate(x) :
  character string is not in a standard unambiguous format
----

\...but that throws an error if we have a non date value so it's not so useful in this case.

Instead we can make use of the +++<cite>+++http://stackoverflow.com/questions/14755425/what-are-the-standard-unambiguous-date-formats[strptime]+++</cite>+++ function which does exactly what we want:

[source,r]
----

> strptime("2014-01-01", "%Y-%m-%d")
[1] "2014-01-01 GMT"

> strptime("foo", "%Y-%m-%d")
[1] NA
----

We can then feed those values into +++<cite>+++is.na+++</cite>+++..

[source,r]
----

> strptime("2014-01-01", "%Y-%m-%d") %>% is.na()
[1] FALSE

> strptime("foo", "%Y-%m-%d") %>% is.na()
[1] TRUE
----

\...and we have exactly the behaviour we were looking for.
R: dplyr - mutate with strptime (incompatible size/wrong result size)
Date: 2014-12-08 19:02:46

Having worked out http://www.markhneedham.com/blog/2014/12/07/r-string-to-date-or-na/[how to translate a string into a date or NA if it wasn't the appropriate format] the next thing I wanted to do was store the result of the transformation in my data frame.

I started off with this:

[source,r]
----

data = data.frame(x = c("2014-01-01", "2014-02-01", "foo"))
> data
           x
1 2014-01-01
2 2014-02-01
3        foo
----

And when I tried to do the date translation ran into the following error:

[source,r]
----

> data %>% mutate(y = strptime(x, "%Y-%m-%d"))
Error: wrong result size (11), expected 3 or 1
----

As I understand it this error is telling us that we are trying to put a value into the data frame which represents 11 rows rather than 3 rows or 1 row.

It turns out that https://github.com/hadley/dplyr/issues/179[storing POSIXlts in a data frame isn't such a good idea!] In this case we can use the +++<cite>+++as.character+++</cite>+++ function to create a character vector which can be stored in the data frame:</a>

[source,r]
----

> data %>% mutate(y = strptime(x, "%Y-%m-%d") %>% as.character())
           x          y
1 2014-01-01 2014-01-01
2 2014-02-01 2014-02-01
3        foo       <NA>
----

We can then get rid of the NA row by using the +++<cite>+++is.na+++</cite>+++ function:

[source,r]
----

> data %>% mutate(y = strptime(x, "%Y-%m-%d") %>% as.character()) %>% filter(!is.na(y))
           x          y
1 2014-01-01 2014-01-01
2 2014-02-01 2014-02-01
----

And a final tweak so that we have 100% pipelining goodness:

[source,r]
----

> data %>%
    mutate(y = x %>% strptime("%Y-%m-%d") %>% as.character()) %>%
    filter(!is.na(y))
           x          y
1 2014-01-01 2014-01-01
2 2014-02-01 2014-02-01
----