⚠️ This required Docker and docker-compose installed on your system
- Define a
docker-compose.yml
file with the following content
services:
neo4j:
image: neo4j:latest
hostname: neo4j
container_name: neo4j
ports:
- "7474:7474" # port mapping for browser interface
- "7687:7687" # port mapping for Bolt protocol
volumes:
- ./neo4j/data:/data
- ./neo4j/logs:/logs
environment:
- NEO4J_AUTH=$DB_USER/$DB_PASSWORD
- NEO4J_PLUGINS=["apoc"] # plugin needed to connect with Tableau
volumes:
neo4j:
- Create a
.env
file to initialize$DB_USER
$DB_PASSWORD
environment variables. This file has also to be put in the.gitignore
to not be pushed on remote repository.
DB_USER=****
DB_PASSWORD=*****
- Initialize the DBMS with docker-compose
docker-compose up
- Open your the browser interface with this URL:
http://localhost:7474/browser/
- Connect to the Neo4j DBMS
- We used
neo4j:latest
docker image - For now the neo4j version is5.18.1
- Databases
system
andneo4j
are created by default
- We can use the Neo4j shell to see server status using this command:
:server status
- Open Neo4j browser
- Create "employee "
Employee
nodes with first column
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/veben/neo4j_employees/main/resources/data/employees-and-their-boss.csv' AS row
MERGE (:Employee {name: row.`employee name`})
💡 Added 155 labels, created 155 nodes, set 155 properties, completed after 79 ms.
- Create "boss "
Employee
nodes with second column
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/veben/neo4j_employees/main/resources/data/employees-and-their-boss.csv' AS row
MERGE (:Employee {name: row.`has boss`})
💡 Added 1 label, created 1 node, set 1 property, completed after 89 ms. I used
MERGE
and notCREATE
. Otherwise it would create duplicate employees
- Look at the inserted nodes
MATCH (n:Employee) RETURN n
- Create the relashionships between employees and their boss
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/veben/neo4j_employees/main/resources/data/employees-and-their-boss.csv' AS row
MATCH (employee:Employee {name: row.`employee name`})
MATCH (boss:Employee {name: row.`has boss`})
WITH employee, boss
WHERE employee.name <> boss.name
CREATE (employee)-[:REPORTS_TO]->(boss)
💡 Created 155 relationships, completed after 106 ms.
- Look at the result
MATCH (n) RETURN n
- See who is the employee that reports to no one:
MATCH (employee:Employee)
WHERE not (employee)-[:REPORTS_TO]->(:Employee)
RETURN employee
- Let's try to do the 3 steps in 1
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/veben/neo4j_employees/main/resources/data/employees-and-their-boss.csv' AS row
MERGE (employee:Employee {name: row.`employee name`})
MERGE (boss:Employee {name: row.`has boss`})
WITH employee, boss
WHERE employee.name <> boss.name
MERGE (employee)-[:REPORTS_TO]->(boss)
- The following message is displayed:
The execution plan for this query contains the Eager operator, which forces all dependent data to be materialized in main memory before proceeding Using LOAD CSV with a large data set in a query where the execution plan contains the Eager operator could potentially consume a lot of memory and is likely to not perform well. See the Neo4j Manual entry on the Eager operator for more information and hints on how problems could be avoided.
- We can try to dive in the execution of the query using
EXPLAIN
orPROFILE
keywords on top of the query - If we validate the query:
💡 Added 156 labels, created 156 nodes, set 156 properties, created 155 relationships, completed after 261 ms.
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/veben/neo4j_employees/main/resources/data/employees-and-their-friends.csv' AS row
MERGE (employee:Employee {name: row.employee_name})
MERGE (friend:Employee {name: row.is_friends_with})
WITH employee, friend
WHERE employee.name <> friend.name
MERGE (employee)-[:FRIENDS_WITH]->(friend)
💡 Created 447 relationships, completed after 185 ms.
- Look at employees friend with themselves
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/veben/neo4j_employees/main/resources/data/employees-and-their-friends.csv' AS row
MATCH (employee:Employee {name: row.employee_name})
MATCH (friend:Employee {name: row.is_friends_with})
WITH employee, friend
WHERE employee.name = friend.name
RETURN employee.name, friend.name
- Look at the
FRIENDS_WITH
relationships
MATCH p=()-[:FRIENDS_WITH]->()
RETURN p
- If you want to display a table with employees and friends
MATCH (employee)-[:FRIENDS_WITH]->(friend)
RETURN employee.name, friend.name
- Delete all relashionships and node
MATCH (n)-[r]-() DELETE r
MATCH (n) delete n
- In one time
MATCH (n)
DETACH DELETE n;
MATCH (employee:Employee)-[:REPORTS_TO*]->(boss:Employee {name: "Darth Vader"})
RETURN boss, employee
- Show all employee directed linked to him (team 1)
MATCH (jacob:Employee {name: "Jacob"})-[:REPORTS_TO]-(employee:Employee)
RETURN jacob, employee AS covorker
- Show all employee directed linked to him (team 1 bis)
MATCH (jacob:Employee {name: "Jacob"})
MATCH (employee)-[:REPORTS_TO]->(jacob)
RETURN jacob, employee AS covorker
UNION
MATCH (jacob:Employee {name: "Jacob"})
MATCH (jacob)-[:REPORTS_TO]->(employee)
RETURN jacob, employee AS covorker
- Show people working directly under him (team 2)
MATCH (employee)-[:REPORTS_TO]->(jacob:Employee {name: "Jacob"})
RETURN jacob, employee
- Show Jacob's friends
MATCH (friend:Employee)-[:FRIENDS_WITH]->(jacob:Employee {name: "Jacob"})
RETURN jacob, friend
- Show employees that are not friend with Jacob
MATCH (employee:Employee)
MATCH (jacob:Employee {name: "Jacob"})
WHERE NOT (employee)-[:FRIENDS_WITH]->(jacob) AND employee.name <> jacob.name
RETURN jacob.name, employee.name
- Final query
MATCH (employee:Employee)-[:REPORTS_TO]->(jacob:Employee {name: 'Jacob'})
WHERE NOT (employee)-[:FRIENDS_WITH]->(jacob)
RETURN jacob, employee
Here is a new dataset adding skill list to the employees:
employee_name,skills
Bradley,"Python,Java,C++,SQL"
Meagan,"Java,HTML,CSS,Javascript"
Wayne,"C++,Python,R,SQL"
Annie,"Rust"
Sylvester,"C#,Go"
Ferrari,"Cypher,SQL"
Gavin,"Java,Fortan,Cobol"
Diane,"CSS,HTML,Javascript"
Morgan,"Java"
Mindy,"Go,Java"
Clyde,"C#,C,C++"
Clyde,"Java"
Thad,
Steve,"Cypher,Go,Kotlin,Java"
Wilson,"C#,Go,Java"
This allow to test some behaviours of the loading:
- multiple values for
skills
column- Clyde is present 2 times with different skills
- Thad does not have skill
- Loading in one time:
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/veben/neo4j_employees/main/resources/data/employees-and-their-skills.csv' AS row
WITH row, split(row.skills, ",") AS skillList
UNWIND skillList AS skill
MERGE (e:Employee {name: row.employee_name})
MERGE (s:Skill {name: skill})
MERGE (e)-[:HAS_SKILL]->(s)
- Show the graph:
MATCH p=()-[r:HAS_SKILL]->() RETURN p
- I want to know everything about Bradley
OPTIONAL MATCH (brad:Employee {name: "Bradley"})-[:REPORTS_TO]->(boss)
OPTIONAL MATCH (brad)-[:FRIENDS_WITH]-(friend)
OPTIONAL MATCH (brad)-[:HAS_SKILL]->(skill)
RETURN brad.name AS Employee,
boss.name AS Boss,
collect(DISTINCT friend.name) AS Friends,
collect(DISTINCT skill.name) AS Skills
- Retrieve employees which have more than 2 skills, order by number of skills and then by employee name
MATCH (employee)-[:HAS_SKILL]->(skill:Skill)
WITH employee, COLLECT(DISTINCT skill.name) AS skillList
WHERE size(skillList) > 2
RETURN employee.name as employeeName, skillList
ORDER BY size(skillList) DESC, employeeName
- Retrieve employees who have skills in common with their friends, and also have the same boss:
MATCH (employee:Employee)-[:FRIENDS_WITH]->(friend:Employee)
MATCH (employee)-[:HAS_SKILL]->(skill:Skill)<-[:HAS_SKILL]-(friend)
MATCH (employee)-[:REPORTS_TO]->(boss:Employee)<-[:REPORTS_TO]-(friend)
RETURN employee.name, friend.name, boss.name, COLLECT(skill.name)
⚠️ This required Golang installed on your system
- Go to
src
folder - Download the needed libraries:
go mod tidy
- Launch the
insert_data.go
script:
go run insert_data.go
- Download Tableau desktop: https://www.tableau.com/fr-fr/support/releases/desktop/2024.1#esdalt
- Install it
- Download Neo4j ODBC connector: https://neo4j.com/bi-connector/
- Install the connector
- Open ODBC Data Sources program
- Go to the "System DSN" tab and click "Add".
- Select the Neo4j ODBC Driver from the list of available drivers.
- Configure the driver settings, including the server address (localhost), port number (default is usually 7687), and authentication details (username/password).
- Test the connection
This leads to this error
[Simba][Neo4j] (22) An error has been thrown from the Neo4j client: 'could not run query: Neo4jError: Neo.ClientError.Statement.SyntaxError (Unknown function 'apoc.version' (line 1, column 8 (offset: 7))
"RETURN apoc.version()"
^)'
- Install APOC plugin
- Restart Neo4j DBMS
- Test again the connection
It works
- Open Tableau desktop
- Go to the "Data" menu and select "Connect to Data"
- Choose "Other Databases (ODBC)" from the list of available connectors
- Select the Neo4j ODBC DSN you configured earlier
- Fill the connection details
- Create a simple text table with employees and skills
⌛ TODO: Create charts based on queries
- Go to
neo4j_employees
folder - Destroy docker container and associated components:
docker-compose down