Hi, I'm Philip from the HANA Academy.
In this series of videos, we're looking at the Graph processing capabilities native to SAP HANA.
In this video, we're going to look at the enhancements
to the way that HANA Graph processing supports openCypher Query Language
when we're working with XS Advanced WebIDE for HANA with HANA 2.0 SPS 01.
We've already discussed openCypher and how we can work with XS Advanced in previous videos in this series
so we'll kind of assume that you're going to know that
and there's also another video in this series that shows what's new in openCypher
but working with classical SQL script stored procedures.
Here we're going to see basically the same thing
but using openCypher from WebIDE for HANA and the account view modeller.
Now, if you want to see the reference of what we can do
then you need to go to 'Graph Algorithms' in the reference guide – you know where to get this by now.
We've got the 'openCypher Query Language' and here you can find the full reference to how you work with it
and it it does actually mention reserved keywords
which is quite important.
So don't use any of these keywords as variable names, for example, when you're working with openCypher
whether you're doing that in classical SQL script or, like here, you're going to be doing it with WebIDE for HANA
in the calc view modeller.
So, let's go ahead and get started with an example.
Now, we've got the core project that we actually built in previous videos set up
we've got a project and then we've got a HANA database and HDI component that's been defined
and then within source we've got a CDS file to create our 'MEMBERS' and 'RELATIONSHIPS' tables
we've used some SQL script to populate those tables
and also we've created a Graph workspace called 'graphw', which, again, belongs in our HDI container
so the syntax is a little different from when we're working with more classical schemas and table names.
So that's all ready and good to go, so we can now choose 'New' and go to 'Calculation View'
and we could maybe call this one 'cypher'.
We're going to create a data category of 'DEFAULT'
hit 'Create'
and now we can see we've got the calc view modeller.
Now, we're going to use the graph node
so we can go there and click on the graph node and drop it somewhere in our pane
and we need to connect that, now, to our Graph workspace.
So we know it's called 'graphw' and it's within 'graph' --> 'graphdb' so we hit the plus sign
we can just search 'gr...' to find
and we've got 'graph.graphdb::graphw'
that's our Graph workspace, so that's the one we want so let's go ahead and select it.
Now, once we've connected to our Graph workspace
then we can go ahead and start choosing which algorithm we want to work with.
Now, for openCypher we go to the 'Pattern Matching' section and then choose the Cypher editor
and what we'll do here is we'll just simply paste in snippets of code that might make sense for us.
So if we go to GitHub
and the HANA Academy and the Graph Repository
you'll find that we've got, actually, 'Graph 23 XSA Pattern Matching Cypher SPS01'.
If we go there we'll see we've got some examples, so there are basically three new capabilities.
One is undirected edges, another one is limited-length paths, and a third one is all edges in a path.
So, let's have a quick look at undirected edges first, so I'm going to copy that and paste it in here.
The first thing you'll notice if you've used regular SQL script is that we just need single quotes around literals.
We actually had to use a single quote twice
because the whole thing, actually the whole Cypher statement, actually was within single quotes already
so we had to escape them.
In this case we can just use regular singles
and we can see here in this example, in the match statement, we haven't actually given a direction.
Now normally you would have, for example, 'from (a)->(b)'
and you'd use an arrow to actually give the direction
but what we now support with SPS 01 is the ability to have undirected edges
meaning that the edge doesn't have to be in the direction from (a) to (b), it could be from (b) to (a)
so basically you'll probably get more results.
It's a way of doing things without having to worry about which direction an edge happens to be connected in.
So if we actually go ahead and want to run this
what we can do now is we've got one output column in the return clause, we need to hit 'Generate Column'
and it will then generate us an output column, which is 'NAME' 'VARCHAR(100)'.
Okay, that's good, so that will be our output, and then we can actually project that result into our projection
and then, of course, we need to make sure that we select that column for our output
or you do whatever you wanna do with it, this is calc view modelling, and then we can save it and just build.
So the build has completed successfully, that's great, so we can now go to the database explorer
and we've already got, from a previous video, the connection to our HDI container.
If not, just click the plus sign and go ahead and select your HDI container
and if we go there, we can then look at column views
and we see that we've got one which is the new Cypher one we've just created
so we can generate a select statement, for example
– and be careful because it will generate you a 'GROUP BY', which you don't necessarily want
because that will actually aggregate results.
So in this case, I just want to select the names
just give me the names, my results basically, from my query, so let's go ahead and run that
and we'll see we get our results shown.
So we can see that we've got, actually, six results
we've even got Gaia two or three times
because actually with Gaia there are multiple directions of connection.
Now, you can always go back to the reference guide, go to the 'SAP HANA Graph Data Model' section
and there you get a nice picture showing
so if you've got this Uranus or whoever
we can see that Uranus to Gaia – yep, there are actually three different edges, that's why we got three back
and it didn't matter which direction the edges were in because we asked for an undirected edge query.
So that basically shows that it's working as we expected.
Now, our second thing that we might want to be able to do, if we just go back here
is to maybe enhance that query
so the second thing would be to choose the limited-length paths
or basically say, "Okay, for the edges it can be one connection, so from (a) to (b) directly
or it could be from (a) to (b) via something else in the middle, so it could be two edges, effectively
to get from one vertex to the other."
So we can see '1..2' would basically be between one and two edges, or '1..3' would be between one and three edges.
So let's go ahead and copy that.
We're going to paste it into here again.
Now, we've just got the same one output column, so we don't need to regenerate or anything.
I'm going to go ahead and, making sure I'm back in the full calc view modeller, I'm going to save that
and I'm going to build.
This time we should actually get a little bit more in terms of results
because we're going, actually, two levels of connection from Uranus, so between one and two edges inclusive.
So that's now built okay
let's go and have a look at the column view
– in fact we've already got the code here so we can just go ahead and run it again.
This time, notice, we get 21 rows back, so we get a lot more.
What we're doing here is actually getting all of the first- and second-level connections from Uranus outwards
so that's what we expected to see
so previously we only got, actually, just one layer of connection, which is the default.
Now, what we can also use, and particularly in conjunction with this new pattern concept
is that we could also say, "Well, we still want to filter on an edge."
Now, we haven't actually mentioned an edge with the word 'e' for example
and we can't, therefore, use it in the where clause
but there is a way that we can actually do that
and if we go back to the code you'll see that we can filter on all edges in a path simply by adding this script.
So we've got this new 'ALL (e IN RELATIONSHIPS', and then referencing '(p)', which is our pattern
so we're saying, "All edges in the whole of this pattern we want only with the attribute 'hasDaughter'
so we only want the daughters of Uranus between one and two levels."
So let's go ahead and just add that to the where clause
so we've now added and we only want to have the daughters.
Again, it's the same single output column, so we should be good just to save it and build.
So the build's run okay, and we can go back again and select the same query from that view, run it
and this time we only get '4' back
because, basically, there are four daughters when we're going between one and two levels.
Again, you can go back to the data and, if you look at Uranus, the daughters are little dots on the edge
so for Uranus we've got Aphrodite, we've got Rhea
and that's at the first level, and then at the second level we've got Hera and Demeter
so that makes sense, that's what we expected to see.
One thing we might want to do just to show the example of how it can be quite flexible to work with
we might say we want to go a third layer of connections
so maybe change this to a limited length up to three, so from one to three or two to three, whatever you want.
So let's save that, and let's build it again
and this time we should get a few more daughters
including, in fact, that third layer of connection, not just the two layers.
So if you go to our code and get ready to run it
so we saw we got four, that's the first two layers
now we run it again, and for the third layer we see that we also get Persephone added into our results set
and if we verify that, yep, we find that Persephone is a daughter, that's a daughter of Demeter
so that's how we get from Uranus all the way down to Persephone, so it's given us that third level.
Okay, but we don't get Athena because, actually, she's a daughter of Zeus
and Zeus is not a daughter of Rhea or anything.
He's not a daughter of anything because Zeus is not a daughter, and we can see that it's working as expected.
So, what we've seen in this video is the new enhancements to the way we support openCypher query language with HANA Graph
and this is specifically how we can access that capability when we're working with XS Advanced and WebIDE for HANA using the calc view modeller.
You can find more HANA video tutorials on our YouTube channel.
If you'd like to be informed as soon as new video tutorials are published, please subscribe to the channel.
You can also connect with us on LinkedIn, follow us on Twitter, and we have pages on Facebook and Google+.
Code snippets for all of our tutorials are published to GitHub.
Thanks for watching.