Connect the Tables: How to Graph Relational Databases

You might have heard about graphs, but what if you already have a relational database? How do you incorporate something new? This session will show you how to translate part of all data from a relational format into a graph.

We will cover the basics of migrating the model and practical steps for actually implementing it. We will walk through naming and case conventions for graphs, as well as some principles for a successful graph model. At each step, we will show live code for making this work in the real world. Come to this session to get concrete steps for graphing relational databases.

Code: https://github.com/JMHReif/connect-the-tables

Transcript

1. Photo by Shannon Potter on Unsplash Jennifer Reif Developer Advocate, Neo4j Connect the Tables: How to Graph Relational Databases jennifer.reif@neo4j.com @JMHReif github.com/JMHReif jmhreif.com linkedin.com/in/jmhreif
2. Who is Jennifer Reif? Developer Advocate, Neo4j • Continuous learner • Conference speaker • Creator: Tech blogger + podcaster • Other: geek Jennifer Reif jennifer.reif@neo4j.com @JMHReif github.com/JMHReif jmhreif.com linkedin.com/in/jmhreif
3. Data set
4. Kaggle - public data Co ff ee Shop https://www.kaggle.com/datasets/ylchang/co ff ee-shop-sample-data-1113 https://github.com/JMHReif/graphing-relational-dbs-data-import/tree/main Cleaned —>>
5. Demo 1 - Look at CSVs Live! • customer.csv • order.csv • product.csv • sales_outlet.csv • sta ff .csv • product_type.csv
6. Demo 1 - Model it Live! https://arrows.app
7. Relational
8. Data set sample (core entities) Order Product Customer Building Sta f
9. Data Normalization 3rd Normal Form (3NF) • Unique row, low data duplication • Primary key or composite key (where necessary) • Foreign keys to link tables • Many-to-many relationships -> JOIN tables • JOINs on query-time, from expert knowledge https://dzone.com/articles/do-we-need-data-normalization-anymore
10. Relational ERD Co ff ee Shop
11. Graph
12. What is a graph? Connected data • Store relationships -> separate entities • JOINs on write, not read • Flexible schema • Query language: Cypher
13. Still get core features No FOMO here :) • Physical storage • Transactional + analytical workloads • ACID transactions • Indexes / constraints • Security features • Cloud hosting • Free -> enterprise solutions
14. Graph components Graph theory foundations • Node (vertex) • Relationship (edge)
15. Nodes Graph components • Represent objects or entities • Can be labeled • May have properties Order Product Employee orderId: 162468 orderDate: 2019-04-01 productId: 08746589 name: “Ipoh Coffee” id: 247924 startDate: 2016-05-04 position: “Barista”
16. Relationships Graph components • Must have a type • Must have a direction • May have properties • Nodes can have multiple Order Product Employee orderId: 162468 orderDate: 2019-04-01 productId: 08746589 name: “Ipoh Coffee” id: 247924 startDate: 2016-05-04 position: “Barista” CONTAINS date: 2022-08-16 tip: 1.00 SOLD CONTACT CREATED
17. Label Graph components • A group of nodes • Like a category Person Employee Customer
18. Property Graph components • Key-value pair • Associated with node or relationship • Like a variable name and value Order Employee orderId: 162468 id: 247924 SOLD orderDate: 2019-04-01 date: 2022-08-16 tip: 1.00 startDate: 2016-05-04 position: “Barista”
19. Naming conventions Relational vs Graph • Recommendations vary! • Lowercase • Separator: _ • Avoid keywords • Capitalize SQL keywords • Might change with GQL standard • camelCase • UPPERCASE • Separator: _ • Avoid keywords • Capitalize Cypher keywords https://brainstation.io/learn/sql/naming-conventions https://neo4j.com/docs/cypher-manual/current/syntax/naming/ SQL Cypher
20. Demo 2 - Translate the model
21. Relational -> Graph Data model • Tables -> Labels • Row = Node • FK = Relationship • JOIN table = Relationship
22. • Tables = Labels • Row = Node • FK = Relationship • JOIN table = Relationship https://arrows.app
23. • Tables = Labels • Row = Node • FK = Relationship • JOIN table = Relationship
24. • Relationship direction • Type/Name • No right or wrong! • Sensible for your model
25. Modeling decisions
26. What to do… When things are not cut and dry? • Property on node or relationship? • Promo • Property or node? • Line item • More than 1 label? • Seasonal drink
27. - Every data modeler ever “It depends…”
28. Tips and Tricks General architecture • De fi ne use case -> • Know what problem you’re solving • Outline key queries -> • Optimize model to answer those • Limitations are di ff erent • More entities <> bad • Model can change as-needed
29. Tips and Tricks General practices • Frequent fi lter -> • Opt for separate node/rel • Props with few/unchanging values -> • Consider label • Dense nodes (hypernodes) -> • Consider more relationship types to fi lter
30. Data export / import
31. MariaDB Export / Import • SQL dump fi le (entire db) • SQL statement to CSV
32. Neo4j Export / Import • Cypher + APOC • ETL Tool • Dump fi le (*note)
33. Other options for both Export + Import + Sync • Driver • Messaging platform • Scheduled task
34. Demo 3 - Import to Neo4j
35. Setup Load components • Neo4j Docker container • Shell script -> cypher-shell • Cypher script • LOAD CSV
36. Draft data model = real-world model Recap!
37. Draft = Graph model Co ff ee Shop
38. All or nothing?
39. Polyglot Graphy data -> graph db • Store data with complicated JOINs in a graph • E.g. Customer -> Order -> Product • Eval: • JOINs • Frequency • Performance criticality
40. Cypher
41. Cypher • Functional and visual • Based on ASCII-art • Declarative query language • Focuses on what to retrieve • Not how A B LIKES MATCH (A)-[:LIKES]->(B)
42. Cypher: powerful and expressive Jennifer Neo4j WORKS_FOR CREATE (:Person { name: ‘Jennifer’}) -[:WORKS_FOR]-> (:Company { name: ‘Neo4j’}) NODE PROPERTY NODE PROPERTY LABEL LABEL
43. Cypher: read Jennifer Neo4j WORKS_FOR MATCH (:Person { name: ‘Jennifer’} ) -[:WORKS_FOR]-> ( whom ) RETURN whom
44. Translating queries • SELECT • Property list • FROM <table> • JOIN <table> ON <property> • WHERE • GROUP BY • HAVING • MATCH • RETURN entities/props • Pattern (node)-[rel]-(node2) • -[var:REL_TYPE]-> • WHERE • Implicit in RETURN • WHERE on aggregations SQL Cypher
45. Demo 4 - SQL vs Cypher
46. Ex 1 - Find products SQL Cypher MATCH (p:Product) RETURN p LIMIT 10; product Product SELECT * FROM product LIMIT 10;
47. Ex 2 - Find staff working in each building SQL Cypher MATCH (b:Building)<-[r]-(s:Staff) RETURN *; building Building SELECT b.building_id, b.building_type, b.street_address, b.city, s.staff_id, s.first_name, s.last_name, s.position FROM building b, staff s WHERE CAST(s.building_id AS INTEGER) = b.building_id; staff Staff
48. Ex 3 - Find staff (NOT managers) for each building SQL Cypher MATCH (b:Building)<-[r:WORKS_IN]-(s:Staff) RETURN *; building Building SELECT b.building_id, b.building_type, b.street_address, b.city, s.staff_id, s.first_name, s.last_name, s.position FROM building b, staff s WHERE CAST(s.building_id AS INTEGER) = b.building_id AND s.staff_id <> b.manager_staff_id; staff Staff
49. Ex 4 - Find orders a staff member sold SQL Cypher MATCH (s:Staff {staffId: 13})-[r:SOLD]->(o:Order) RETURN * LIMIT 10; SELECT s.staff_id, s.first_name, s.last_name, o.transaction_id, o.transaction_date, o.transaction_time FROM staff s, coffee_order o WHERE s.staff_id = 13 AND o.staff_id = s.staff_id LIMIT 10; staff Staff coffee_order Order
50. Ex 5 - Find customers purchased >1 latte SQL Cypher MATCH (c:Customer)-[r:BOUGHT]->(o:Order)-[r2:CONTAINS]->(p:Product {productName: “Latte”}) WITH c, COUNT(p) as numberOrdered WHERE numberOrdered > 1 RETURN c.customerId, c.name, numberOrdered ORDER BY numberOrdered DESC; coffee_order product order_line_item Order Product SELECT c.customer_id, c.customer_name, COUNT(p.product_name) AS number_ordered FROM customer c INNER JOIN coffee_order o ON c.customer_id = o.customer_id INNER JOIN order_line_item ol ON o.transaction_id = ol.transaction_id AND o.transaction_date = ol.transaction_date AND o.transaction_time = ol.transaction_time INNER JOIN product p ON p.product_id = ol.product_id WHERE p.product_name = “Latte” GROUP BY c.customer_id HAVING number_ordered > 1 ORDER BY number_ordered DESC; customer Customer
51. Refactoring
52. Relational vs Graph Designed di ff erently • DDL / DML • Strict schema + data types • Structure-enforced • Data integrity • Storage/query optimization • Changes = dependency impacts • Schema- fl exible • Restrictions set as-needed • Governance-enforced • Data integrity • Storage/query optimization • Changes = less cascading impacts Relational Graph
53. This means… Refactoring for graphs • Streamlined • Faster • More responsive • Less invasive
54. Tools For refactoring • Cypher + APOC • Neo4j Migrations (https://neo4j.com/labs/neo4j-migrations/) • 3rd party tools (Kettle, etc)
55. But wait…there’s more!
57. Resources Questions? • Github repo: github.com/JMHReif/connect-the-tables • GraphAcademy: graphacademy.neo4j.com/ • NODES 2024: https://dev.neo4j.com/nodes24 Jennifer Reif jmhreif.com github.com/JMHReif jennifer.reif@neo4j.com @JMHReif linkedin.com/in/jmhreif