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 see how you can take advantage of GraphScript when you're working with XS Advanced or WebIDE for HANA
when you're working with HDI containers, as opposed to classic SQL script.
We've seen GraphScript language in previous videos, we've seen what's new in SPS 00 and 01 of HANA 2.0
so there's the whole GraphScript language, you've got a reference for that here.
It's very important, for example, to look at the list of reserved keywords
and make sure you're not using any of these keywords as variable names in your GraphScript procedures
otherwise you may get into some difficulties.
There's quite a long list, so just be aware of that.
Now, we can actually call it when we're working with the different language
so, typically, you would use classical SQL
and you would do a 'CREATE PROCEDURE' and then use the language Graph
and then between the beginning and end you would put in your GraphScript syntax.
However, that's really for a classical approach, but we can also call it when we're working with XS Advance
and that's what's new with SPS 01 of HANA.
So, in order to do that, what we're going to do is basically create an HDB procedure
and then we will put the same kind of syntax as we've got here
and it's pretty much the same syntax, except it will be part of the HDB procedure concept.
Now, I've got an example available in the GitHub repository for HANA Academy Graph
so it's this one, 'Graph 24 XSA GraphScript SPS01'
so we can go ahead and look at that in a moment, but let's first get started with WebIDE for HANA.
We've got a Graph workspace that's been set up here.
You'll see we've got a project graph with a HDI container or a HANA database component or module
so for the database
and then within there we've got CDS, where we've generated some tables
and we've got a Graph workspace.
Now, just notice that currently, with SPS 01, 'NVARCHAR' is not supported when we're working with keys
so what we've made sure we've used here
is that for our string columns we've explicitly specified we'll use 'hana.VARCHAR'
because if you use 'string' that actually defaults to 'NVARCHAR' when you're working with CDS in HANA
so we've just explicitly referenced 'hana.VARCHAR' here.
Then we've created the Graph workspace.
This has been covered in previous videos, so no need to give details on that.
We've got our Graph workspace, we put some data in there with some insert statements or however you want
now we're ready to get started.
So we want to use GrapScript to do some very specific kind of analysis
that's not really available through the CalcView modeller with the standard algorithms.
Then we can go to 'New', and this case we're going to create a procedure, so it's going to be a stored procedure
so we can call it GraphScript, or whatever you want to call it
and you'll see that you get the normal HANA GraphScript procedure.
Now, what we need to do is make a couple of changes.
The first one is that the language setting, we need to change from 'SQLSCRIPT' to 'GRAPH'
and then we need to enter all of our GraphScript between the 'BEGIN' and 'END'
and it will know it's GraphScript because we've specified it as being part of the language.
Now, to save you writing all of that from scratch we've got an example already here, so let's paste it in here.
So now you can see we've got our procedure
we've got an output parameter, which is 'cnt' for count and then it's type 'INT' for integer.
We're got 'LANGUAGE GRAPH', of course.
We've got the 'BEGIN' and 'END' section and that's where we'll include our GraphScript code.
Also, notice that the procedure name is 'graph.graphdb::graphScript'
that corresponds to the name I gave here.
Of course, in your case, if it's different you would need to change it in the procedure line at the top
and then within the procedure logic
then it's simply a question of you needing to connect to your Graph workspace.
Now, when we're working with HDI containers then the syntax is not the same as classical schemas and table names and workspace names.
We're using double quotes, the name of the namespace, basically the name of the project
and then the HDI container module, so '"graph.graphdb::graphw"'
and that's because that's the name of the Graph workspace that we have created.
After that it's just a question of going ahead and using the syntax. Now we've got a number of examples of this.
This example shows a couple of new things that are available in GraphScript with SPS 01 of HANA 2.0.
We've got the 'neighbors' operator
and we're also using a 'union' operator
– 'union', 'intercept' and 'accept' are new capabilities that were added with SPS 01.
Now, we're going to calculate, basically
the number of neighbours that actually have a residence of our starting vertex
which we've basically hard-coded here to Chaos.
Of course you can make import parameters to make all of that definable at runtime
but it's very powerful, GraphScript.
You can do a lot of complex processing
and do stuff that's, maybe, not easy to do when you're working with the more classical algorithms
that are provided out of the box with graph processing.
Don't worry that in the editor you'll maybe get some odd things or errors
it's just that GraphScript is not something that the editor is yet, really, up to speed with, but it does work.
You should be fine, so let's go ahead and save that
and we'll go and build it, so we'll build our GraphScript stored procedure.
Okay, it's now completed successfully, the build worked okay
so then we can go and look at this.
Now, I've already got a connection to my HDI container in the database explorer.
If not you can just use a plus sign and add the relevant database, the HDI container.
Then in here I would be wanting to look at procedures
and if I load I can see I've now got a procedure called 'graph.graphdb::graphScript'
and I would want to go ahead and run it, so I'll generate a core statement.
There's no input parameters, just one output parameter, so it's very straightforward
so if I now go ahead and make this call, which is what I can embed into my application
then I should be ready to go.
So, I make the call, and we see we get two
so basically that's what we expected, there are two neighbours of Chaos that actually have a residence
they're immediate neighbours.
So we're going to see it's worked as we expected.
So, what we've seen in this video is how we can use GraphScript when we're working with HDI containers
we're working with XS Advanced and WebIDE.
We do that by creating a procedure
and then we're able to call that procedure, just like we'd call any other stored procedure
the difference being that we specify the language of Graph rather than SQL script
and then, of course, we put our specific GraphScript inside the 'BEGIN' and 'END' section
and, of course, the first thing we'll typically do is connect to our Graph workspace
and we're going to use the specific syntax that we have when we work with HDI containers
so using the two colons, for example, it's not classical schema names we use here.
So that's working with GraphScript in XS Advanced with HANA 2.0 SPS 01.
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.