Let’s revisit modelling and loading data

The write up from our Twitch session on Feb 8 2021 where we take a dataset suggested by a viewer and do walk through of the process.

Ilya Cher on Unsplash

Overview

Here’s a super quick write-up of the session we did on Twitch. For those of you who are looking for the queries and a more leisurely step through, this is for you!

First of all, to re-watch the session in all it’s glory, here’s the video:

And here are all the links:

Data:

• Retail food stores data
• The GitHub upload (on github)

The Arrows modelling tool

The Sandbox (we used a blank one)

And of course, if you have any questions, come join our friendly forum

Modelling

We discussed on the video some of the considerations around modelling, and then built out our initial model in Arrows. If you’d like to see the one we drew during the session, click here.

Loading the data

We used a blank sandbox for loading the data, having uploaded the suggested Retail Food Store data up into GitHub. First of all, we loaded up some indexes:

CREATE INDEX ON :Entity(licenseNumber);
CREATE INDEX ON :State(name);
CREATE INDEX ON :Operation(type);
CREATE INDEX ON :Establishment(type);

Then we created the State, Establishment and Operation nodes:

LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/lju-lazarevic/misc/main/Retail_Food_Stores.csv' AS row
MERGE (s:State {name:trim(row.State)})
MERGE (e:Establishment{type:trim(row.`Establishment Type`)})
MERGE (o:Operation {type:trim(row.`Operation Type`)})

Query the data

We had a quick look to see how many of each node type we had from the load:

MATCH (n)
RETURN labels(n), count(n)

Where we discovered that there was only 1 node for State and Operation, so we decided to only connect Entity to Establishment after we have created the initial entity node. We also decided for brevity, just to load two properties:

LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/lju-lazarevic/misc/main/Retail_Food_Stores.csv' AS row
MATCH (e:Establishment {type:trim(row.`Establishment Type`)})
CREATE (en:Entity {licenseNumber:trim(row.`License Number`), name:trim(row.`Entity Name`), DBAName:trim(row.`DBA Name`) })
CREATE (en)-[:HAS_ESTABLISHMENT_TYPE]->(e)

We discovered the Retail Food Stores Establishment codes, and decided to see which stores had an Establishment type of JAZ:

MATCH (e:Entity)-->(:Establishment {type:'JAZ'})
RETURN e.name, e.licenseNumber order by e.name

We should definitely revisit this and see if we can do some fun stuff on comparing store sizes and addresses. Watch this space!