[MUSIC]
>> Hi. Welcome. I'm Jeroen ter Heerdt.
I'm a Program Manager in Azure Data.
Today, I'm joined by my colleague Shreya.
>> Hi.
>> Hey Jeroen.
>> Hi. The topic for today is
the shortest path in graph databases.
First of all, what is a graph database?
>> A good question.
Let's quickly look at what is a Graph Database first.
A Graph Database is nothing but a collection of nodes and edges.
Nodes represent your entities
and edges represent your relationships.
>> Okay.
>> Right. You may
ask how is it different from a Relational Database then?
The difference is that edges
represent your many-to-many relationship.
They can flexibly connect
many node to many other node in the database.
Whereas, a foreign key in a Relational Database can
connect one entity type to many other entity type.
Yeah.
So it represents one-to-many relationship.
Then a Graph Database also support navigation through your graphs,
so easy query language for that.
They can do what OLTP and OLAP queries,
just like Relational Databases.
We introduced middle graph database features in SQL Server 2017.
The SQL Server 2017,
you could create a graph,
which was a collection of nodes and edges, node and edge tables.
Again, node represented your entity and edges, your relationship.
To support creation of
these node and edge tables and to
support navigation through your graph,
we introduced DDL Extensions and Query Language Extensions.
>> Okay.
>> That will help you through that.
There was a match build-in that we introduced.
>> That was 2017?
>> That was 2017.
>> Okay.
>> Right.
>> What's new for 2019?
>> In 2019, we have introduced some features.
Let's go over what we have already introduced.
Sure.
The topic for today like you mentioned, is shortest paths.
So we'll go over that later in detail.
The first one is Merge DML.
This was specifically introduced to
enable the merging off edges scenario easier.
Merge DML now supports match in the syntax,
using match in the syntax.
Match is basically,
a three-way joint between node and edge tables.
>> Okay.
>> If let's say, in a graph,
you want to insert an edge
only if it does not already exist between the two nodes,
then you would use the Merge DML and insert it
only if it does not exist and otherwise, just update it.
>> Okay.
>> Update the edge required. The other feature
that we introduced was Edge Constraints.
In SQL Server 2017,
an edge could connect any node to any other node in the database.
With 2019, we have introduced Edge Constraints which
will help you define specific semantics on your edge table.
Now, you can see that a given edge only connects
this particular type of node
to this particular type of node in my graph,
and nothing else is allowed to be connected using that edge.
You can also divide in cascading actions.
If a node is deleted,
what happens to that edge in that case?
You can see it is deleted and
we'll automatically clean up the edges.
>> That's useful.
>> There would be no dangling edges left in there.
>> Exactly. Yeah.
>> All right. Then other feature that we supported
was View or a Derived Table on nodes or edges.
This is basically to support
graph polymers morphism type of queries.
You can now create a View or a Derived Table
on a collection of nodes or edges and then
use that View or Derived Table in
the match cross, in your queries.
>> Okay.
>> But the topic for today like you mentioned, is shortest path.
>> Sure.
>> We're going to look at that feature.
We have just introduced to SQL Server 2019 CTP3.1.
>> But I know I can use
shortest path in typical networking scenarios.
For example, if you do social networking,
to find the distance between you and me,
on let's say LinkedIn or whatever.
>> Right. Exactly.
>> But not everybody is in the business of networking.
>> Right.
>> What other kind of scenario do I use this as a customer?
>> Excellent questions. Today, I'm going
to show you a demo of bill of materials,
or product hierarchy, and how you can use shortest path there.
We created a graph database from Adventure Works Bicycle Company,
bill of materials, product hierarchy.
In the query that I'm going to demo here today,
I'm going to show how you can find
out if a product or a part belongs to a given assembly or not.
>> Okay.
>> What assemblies it belongs to.
>> Okay.
>> Imagine you were the manufacturer
and the owner of Adventure Work Bicycles,
and you know that a bearing ball that you
produce had some problems in the production pipeline, right?
>> Right.
>> Now, you want to find out what all the assemblies
that bearing ball was used in so
that you can see the affected products.
>> Yeah.
>> If you have to recall those products,
you can recall them, right?
>> Yep.
>> To write that type of query,
you can use this new shortest path function.
On screen, you can see the query that can help
you determine all the assemblies where the bearing ball is used.
Let's look at this query in detail.
We have this new function called shortest path,
which you can use inside a match.
>> Okay.
>> It can be used only inside
a match and only on graph node and edge tables.
>> Sure.
>> It takes a pattern as an input.
It's a regulate expression style pattern.
You go from one node to another node, YSH.
This pattern which is inside shortest path,
we'll use defined plus outside it.
This particular pattern, it just
tells us that this is about them that will be repeated.
It's a recursive pattern.
>> Okay.
>> It will keep traversing this pattern,
this set of node and edges until we find the desired node.
>> Basically, that means that no matter how deep the edge,
the graph database or the graph really is,
we'll just keep searching till we
find out bearing ball, wherever it is?
>> Right.
>> It doesn't matter how complex
the product is that we built, right?
>> Yeah.
>> If it's 10 levels deep, we'll find it.
>> Right. Exactly.
>> Cool.
>> This is what the plus here means.
>> Okay.
>> Regulate expression and syntax
plus means one or more repetitions,
so this will go one or more times,
and we'll keep going down
the hierarchy until they find
that particular product that we are looking for.
>> Awesome.
>> Now, when you use the shortest path and you use
this node and edges here with a regular expression.
Like I said, this particular pattern here,
the shortest path pattern will be
repeated recursively again and again.
>> Yeah.
>> Anything coming out of the pattern or the output coming
out of this will not just be a your regular rows.
It's a collection or a set,
ordered set of edges and nodes that are
traversed in that path to get to the particular product.
>> Sure.
>> To tell the engine that
this particular pattern has specific semantics,
that there a ordered
collection of node and edges coming out from it,
we use this "For Path" syntax in the front graph to tell that
this particular node and edge tables have special semantics.
Along with that, you cannot just
select because they're returning a collection.
>> Sure.
>> They are not just the regular rows,
so you cannot directly select like you select from a table.
>> No.
>> To enable selecting from
that collection or predicting
particular attributes from that collection,
we have supported aggregate functions.
>> Okay.
>> You specify this within
Group Graph Path syntax along with aggregate function,
which tells the engine that
this output is coming from a Graph Path.
>> Okay.
>> Right. There are supported
aggregate functions with this first release our String_Agg,
Last value, min, max,
sum, average, and count.
>> So string_Agg will just concatenate everything?
>> Yeah.
>> Okay.
>> String_Agg in this particular example,
it will concatenate the names of the products that
were seen in the assembly line to get to ball bearing.
>> Okay. You'd see the full path?
>> Full path. Exactly.
>> Cool.
>> Yeah. Let's look at a quick demo of this query.
This is the query that we saw there on the slides.
We are trying to find out all the assemblies which have
a Bearing Ball as one of
the parts in their manufacturing pipeline or assembly line.
Let's quickly run this.
Like I mentioned, I've already set
up a graph database in Adventure Works.
>> Okay.
>> I created a product node and it's part of edge.
>> Okay.
>> So it's just a hierarchy of products.
Here, if you can see,
after I ran that query,
it printed me all the assemblies which are using Bearing Ball.
>> Right.
>> It printed the entire path starting with Bearing Balls.
So Bearing Ball is used in BB Ball Bearing assembly,
which is used in this assembly,
which is finally used for this particular bike's assembly.
>> I get every single product and
every sub-product that uses that Bearing Ball?
>> Yes. Exactly.
>> Cool.
>> We saw that there is plus syntax,
which led to traverse.
>> Yeah, exactly.
>> Led to the levels in the hierarchy.
But what if you want to stop at a given level?
>> Okay.
>> What if you want to say find me anybody
who's connected to me 1, 2, 3 hops?
I mean like Linkedin shows you.
>> Yeah.
>> This person is connected to you 1, 2,
3 hops, three indirections.
We have also supported using this in curly braces,
one comma end type of syntax.
Next to your shortest path pattern,
you can see that repeat this pattern recursively 1, 2, 3 hops.
>> Okay.
>> What this means is it will find me
all the assemblies where Bearing Balls is used,
only one, two, three hops sorry.
It'll know how to traverse all the way up to the top.
>> Okay. Cool.
>> Let's quickly run this.
When we run it, we see
that here is Bearing Ball which is used in these assemblies,
and the number of levels that I traverse to
get to this is one, two and three.
>> One, two, three, yeah.
>> I used the count for aggregate function
here to get the number of levels
that were traversed to get to a particular part.
>> Cool.
>> The third thing that I want to show
here that you can do with shortest path is
finding shortest path between two given nodes in a graph.
>> Okay.
>> Under a lot of the queries that we saw,
we started from Bearing Ball and we kept
traversing the graph until we exhausted it.
>> Yeah. We basically searched whole thing.
>> Yes. We basically found the shortest path,
starting from Bearing Ball to every other product that
is in our pipeline, in our database.
>> Sure.
>> But now, let's see if I want to find out
the shortest path between Bearing Ball and
a given bicycle which has ProductID 768.
How do I do that? I want
to provide a terminating condition on the product.
>> Yeah.
>> That was all right. To do that today,
you could have used the last value in the rear graphs
and have a provided terminating condition on that,
but last value is an aggregate function and you
cannot use aggregate functions in the the rear graphs today.
>> Okay.
>> To do that, how you would do is you
would write your shortest path query,
put it inside a subquery,
and then the outer query will have
the filter on the final product.
>> Okay.
>> This is how the query looks, and let's run this.
>> Now, we'll know where in
this one product the Ball Bearing is used.
>> For this particular product.
>> Right. Exactly.
>> 768. Where the Bearing Ball was used and if it was used,
how many levels down in the hierarchy.
>> Very cool.
>> All right. We saw
three ways in which you can use shortest path today in this demo.
All these samples, all these queries that we saw,
and samples for other features are already uploaded on GitHub.
>> Okay.
>> If you would also like to download and play around with them,
they can do that, after a while.
>> Okay. That's very cool.
Well, that's a lot of cool stuff. Thanks a lot Shreya.
>> Oh, yeah. Thank you. Thanks for having me here.
>> Sure. Our pleasure. Thanks also for watching.
Please, don't forget to like and subscribe. See you next time.