Since I seem to frequently acquire orphaned platforms with no documentation, I figure it would be good to figure out how to investigate an unknown NEO4J platform to see what it’s got. “SHOW” is very useful in these cases. The full list of SHOW commands is:

"ALIAS"
"ALIASES"
"ALL"
"BTREE"
"BUILT"
"CONSTRAINT"
"CONSTRAINTS"
"CURRENT"
"DATABASE"
"DATABASES"
"DEFAULT"
"EXIST"
"EXISTENCE"
"EXISTS"
"FULLTEXT"
"FUNCTION"
"FUNCTIONS"
"HOME"
"INDEX"
"INDEXES"
"KEY"
"LOOKUP"
"NODE"
"POINT"
"POPULATED"
"PRIVILEGE"
"PRIVILEGES"
"PROCEDURE"
"PROCEDURES"
"PROPERTY"
"RANGE"
"REL"
"RELATIONSHIP"
"ROLE"
"ROLES"
"SERVER"
"SERVERS"
"SETTING"
"SETTINGS"
"SUPPORTED"
"TEXT"
"TRANSACTION"
"TRANSACTIONS"
"UNIQUE"
"UNIQUENESS"
"USER"
"USERS"

The most useful ones for figuring out what you’ve got … the “show databases” command I know from MySQL/MariaDB does what I expected – you can also include a specific database name, but “show database ljrtest” doesn’t appear to list any more information than the generic show databases command. .

There’s also a “show users” command that outputs the users in the database – although I’m using the community edition without authentication, so there isn’t much interesting information being output here.

And roles, if roles are being used, should be output with “SHOW ALL ROLES” … but mine just says “Unsupported administration command”

Once you know what databases you’ve got and who can log in and do stuff, we’d want to look at the data. There are some built-in functions that will help us out here. The db.labels() function will list the labels in the selected database.

You can also return a distinct list of labels along with the count of nodes with that label:

Since a node can have multiple, comparing that total with a count of nodes would give you an idea if there are many more labels than nodes. In my case, either view shows 156 … so I know there are few (if any) nodes with multiple labels.

To view the types of relationships defined in the selected database, use “CALL db.relationshipTypes()”

Similarly, you can return the relationship types along with counts

There is a function to list the property keys used within the data – interesting to note that keys that were used but the nodes using them were subsequently deleted … they still show up as property keys. Basically, anything that is there is on this list but some things on this list may not be there anymore. In this example, ‘parentm’ and ‘parentf’ were labels I used to build relationships programmatically.

I’ve found db.schema.nodeTypeProperties to be more useful in this regard – it does not appear to list properties that are not in use, and the output includes field types

To see if there are any custom procedures or functions registered, look on the server(s). Use ps -efww to view the running command – there will be some folders listed after “-cp” … you could find procedures or plugins in any of those folders. In my case, the plugins are in /plugins

And the only “custom” things registered are the APOC and APOC-Extended jar’s

 

While I am certain table-based SQL databases required planning to establish a reasonable data model – optimizing storage, defining foreign keys, indexing … I have found it more challenging to create a good data model in Neo4j. Maybe that’s because I normally populate SQL tables with custom scripts that can be modified to handle all sorts of edge cases. Maybe I’m still thinking in tables, but there seems to be more trial and error in defining the data model than I’ve ever had in SQL databases.

In the import-from-html-table example, a candidate often is associated with multiple elections. Storing candidates as nodes and elections as other nodes that contain results (electoral college votes for winner & loser and popular votes for winner & loser) then associating candidates with elections allowed me to store data about US elections in the Graph. I know who ran, who won and who lost, and what the results were for each election.

Associating the results with candidates didn’t work because Franklin D Roosevelt only has one property for “EC_VOTES” … which election does that reflect? I could also have added the vote totals to the relationship but that would either separate the data (the loser’s votes are stored on LOST relationships and the winner’s are stored on WON relationships) or data duplication (both WON and LOST relationships contain the same vote numbers).

Query used to populate the data:

CALL apoc.load.html("https://www.iweblists.com/us/government/PresidentialElectionResults.html",
{electionyear: "#listtable tbody tr td:eq(0)"
, winner: "#listtable tbody tr td:eq(1)"
, loser: "#listtable tbody tr td:eq(2)"
, electoral_win: "#listtable tbody tr td:eq(3)"
, electoral_lose: "#listtable tbody tr td:eq(4)"
, popular_win: "#listtable tbody tr td:eq(5)"
, popular_delta: "#listtable tbody tr td:eq(6)" }) yield value
WITH value, size(value.electionyear) as rangeup

UNWIND range(0,rangeup) as i WITH value.electionyear[i].text as ElectionYear
, value.winner[i].text as Winner, value.loser[i].text as Loser
, value.electoral_win[i].text as EC_Winner, value.electoral_lose[i].text as EC_Loser
, value.popular_win[i].text as Pop_Vote_Winner
, value.popular_delta[i].text as Pop_Vote_Margin

MERGE (election:Election {year: coalesce(ElectionYear,"Unknown")})
SET election.EC_Votes_Winner = coalesce(EC_Winner,"Unknown")
SET election.EC_Votes_Loser = coalesce(EC_Loser,"Unknown")
SET election.Pop_Votes_Winner = apoc.text.replace(Pop_Vote_Winner, ",", "")
SET election.Pop_Votes_Loser = apoc.number.exact.sub(apoc.text.replace(Pop_Vote_Winner, ",", ""), apoc.text.replace(Pop_Vote_Margin, ",", ""))

MERGE (ew:CANDIDATE {name: coalesce(Winner,"Unknown")})
MERGE (el:CANDIDATE {name: coalesce(Loser,"Unknown")})

MERGE (ew)-[:WON]->(election) MERGE (el)-[:LOST]->(election);

 

To get a count of returned records, Cypher uses COUNT pretty much the same way as SQL does

Interestingly, there are other aggregation functions that remind me of using the ELK API — I can get averages, min/max, and standard deviation.

 

Chaining MATCH statements functions similarly to a SQL JOIN — get the items with this label, add in some other stuff. And, just like an INNER JOIN, this means no data is returned when one of the conditions has no matches — Bill Clinton never lost an election, so we get a null data set here:

The equivalent of an outer join is an OPTIONAL MATCH — here, the records from the first MATCH will be returned even if there is no corresponding record matching the second MATCH

ORDER BY also works in the same way it does in SQL. Multiple order parameters are separated by a comma and add DESC to do a DESCENDING ORDER

WHERE can be used to create the equivalent of a LIKE query — the where =~ operator uses regular expression syntax, so you don’t just use % or * as a wildcard. Regex wildcards like .* (match any character zero or more times) are used.

I have encountered another manual reading failure error — if you actually read the Cypher documentation for WITH, it clearly states that entering a WITH block creates a new scope into which previous variables are not imported. Unless you specifically include them. You can individually include variables in this new scope (WITH oldvariable1, oldvariable2, newSomething as newvariable1) or just use * to include all previous variables.

Doing neither will produce an error that a variable that you are absolutely positive exists does not, in fact, exist.

Admittedly, this is not likely to be something you do a lot in production … but while playing around in a sandbox, I have found it useful to have a quick command to “empty” my database

 

MATCH (n)
DETACH DELETE n