THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

SQL Server 2017 Graph Database References

This is a list of references I used in creating my SQL Saturday presentation on SQL Server 2017 graph databases. To get started with the graph database features of SQL Server 2017, I recommend that you start with the following official Microsoft documentation:

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-sample it provides code for creating and query a simple graph database

or download all three web pages as this single pdf https://docs.microsoft.com/pdfstore/en-us/SQL.sql-content/live/relational-databases/graphs.pdf

Next I recommend reading this to compare and contrast SQL Server 2017 graph to built for purpose graph databases as well as learn about limitations of graph in SQL Server 2017: https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/graph-data-processing-with-sql-server-2017/ Notice how it mentions directionality of edges.

Once you’re ready for a larger graph database, look at https://blogs.msdn.microsoft.com/sqlcat/2017/04/21/build-a-recommendation-system-with-the-support-for-graph-data-in-sql-server-2017-and-azure-sql-db/. You might want to learn more about graph databases than what you will find in the Microsoft documentation. It’s good to know about built for purpose graph databases.

Here’s a good article to explain SQL Server 2017 graph in more detail and also discuss the broader topic of graph databases outside of just Microsoft. Once again notice that directionality is mentioned. http://www.nikoport.com/2017/06/03/sql-graph-part-i/

If you’d like to see another simple graph database in SQL Server 2017, you might want to look at https://stephanefrechette.com/sql-graph-sql-server-2017/

No discussion of graph databases is complete without mentioning Neo4j. You can download and install the community edition for free. Neo4j is built for purpose. The Cypher query language is used to query Neo4j. Once you familiarize yourself with Cypher you’ll see that Microsoft’s graph extensions to SQL Server are similar to Cypher syntax. Two people who work for Neo4j authored a free O’Reilly ebook on graph databases that you can obtain from http://graphdatabases.com/. The title of the book is The Definitive Book on Graph Databases and Introduction to Neo4j, so it tells you that it is biased toward Neo4j. Once again, you should probably pay attention to edge directionality. If you want to read more but want something short instead of a book, try this Wikipedia page on graph databases.

If you’ve checked out any of these links, I’m confident you’ve seen at least one force-directed graph visualization. You’ll want a software tool to create these. There is a Force-Directed Graph visualization for Power BI. I wasn’t able to get it to work with SQL Server 2017 CTP 2.1. You can download this sample Power BI report and see what the Force-Directed Graph visualization looks like.

image

Figure 1. Force-Directed Graph report in Power BI.

image

Figure 2. Mouseover on the node named Logan.

When you are reading about graph databases, it’s important that you don’t let the terminology confuse you. Sometimes authors use the word connections instead of relationships when referring to graph databases. Just kind in mind that a relationship in a graph database context is different from relationship in a relational database context. Graph nodes represent entities.

Graph databases are good for applications in fraud detection, management of hierarchies such as bill of materials (BOM – don’t pronounce it at an airport) and parts explosions (opposite of a bill of materials, also not good to discuss in the airport lounge), social networks, and purchase recommendations among many other things. I liked reading the academic paper Incremental Anomaly Detection in Graphs. Graph based anomaly detection and description: a survey is another good paper but it is not free to the general public.

If you don’t understand SQL Server columnar indexes, you really need to understand them to have a foundation for tuning your graphs in SQL Server 2017. You can find an introduction to columnar indexes here. Once you understand columnar indexes, you’ll see why they can be useful in improving graph performance.

In the presentation at SQL Saturday today, a few people were unfamiliar with the term ASCII art. (If you like ASCII art, the entire first Star Wars movie can be found as ASCII art here.) Somewhere in the linked documentation above, the operators for the graph queries were described as ASCII art. In other words, the characters – and > are combined together to make a graphic construct. In the code below, that graphic construct is an arrow to indicate directionality of an edge as shown in the following query:

SELECT Restaurant.name
FROM Person, likes, Restaurant
WHERE MATCH (Person-(likes)
->Restaurant)
AND Person.name = 'John';

One person asked about graph queries that don’t return any data. Here’s some code to use with the sample data from the third link on this page. Add a new person to the Person node. Now we have a person who doesn’t like any restaurants because no companion inserts were done for the likes edge.

INSERT INTO Person VALUES (6,'Dakota');

SELECT Restaurant.name
FROM Person, likes, Restaurant
WHERE MATCH (Person-(likes)->Restaurant)
AND Person.name = 'Dakota';

The result is no rows returned. But couldn’t restaurants for Dakota’s friends be returned? No. Dakota doesn’t have any friends. No inserts into the friends edge were made for Dakota. Run the query and you’ll see that no rows are returned.

SELECT Restaurant.name
FROM Person person1, Person person2, likes, friendOf, Restaurant
WHERE MATCH(person1-(friendOf)->person2-(likes)->Restaurant)
AND person1.name='Dakota';

As I pointed out in the presentation, notice that the Person node in the previous query is aliased to person1 and person2 in a manner similar to aliasing relational tables for a self-join. I also said that that I was working on modifying the Northwind database to use SQL Server 2017 graph tables. You can see the first blog post in this series here.

Here’s a good paper explaining how hierarchies are graphs.

Published Saturday, June 17, 2017 1:15 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog

Syndication

Privacy Statement