In the previous blog Graph algorithms in SAP HANA… we used some most common graph algorithms – Neighborhood, Strongly Connected, Shortest Path – built into SAP HANA to explore the graph workspace
"SFLIGHT"."CONNECTIONS_C" in the Graph Visualizer.
But what if you need to run these algorithms in a programmatic way? One way is to use a graph calculation node that can be included into calculation scenarios. The second way – presented below – is to use GraphScript — a high-level, powerful domain-specific language in SAP HANA.
Let’s have a look how to use GraphScript to return airports with a direct connection.
SET SCHEMA "SFLIGHT"; --DROP TYPE "TT_NODES" CASCADE; CREATE TYPE "TT_NODES" AS TABLE ("ID" NVARCHAR(3), "NAME" NVARCHAR(25)); --DROP PROCEDURE "NHOOD"; CREATE OR REPLACE PROCEDURE "NHOOD"( IN startV NVARCHAR(3), OUT res "TT_NODES") LANGUAGE GRAPH READS SQL DATA AS BEGIN INTEGER minDepth = 1; INTEGER maxDepth = 1; GRAPH g = Graph("CONNECTIONS_C"); VERTEX v_s = Vertex(:g, :startV); MULTISET<VERTEX> ms_n = Neighbors(:g, :v_s, :minDepth, :maxDepth); res = SELECT :v."ID", :v."NAME" FOREACH v IN :ms_n; END;
What just have happened?
- We created
TT_NODEStable type, to be used to output results:
- Then we created a procedure with the
LANGUAGE GRAPHdefinition — meaning it is a GraphScript
- This procedure receives a code of an airport (
IDof the node) as an input, and returns a list of airports as an output
- In the procedure’s code you can see graph-specific data types, like
VERTEX, and collection types, like
Neighbors()is a HANA’s built-in graph function, which takes graph
gand start vertex
v_sas input. It returns a set of vertices that are neighbors within a given depths (in this case only
1degree of separation, i.e. direct connections)
- At the end results from a collection
ms_nare assigned to the result variable
resusing projection expression
SELECT ... FOREACH ... INstatement
Let’s execute this procedure for the same
SIN airport code as in the previous blog.
CALL "NHOOD"('SIN', ?);
This was very simple example, obviously. But GraphScript is really powerful to help you writing code processing connected data in SAP HANA!
Attending SAP TechEd this year…
…and would like to learn more?
For SAP TechEd I prepared hands-on exercises to explore these and other aspects of SAP HANA graph processing based on some real life data. You can join CodeJam, mini-edition in the Developer Garage on the show floor.
We had it already at TechEd in Vegas. And as interest seems to be on a rise I was asked to schedule two sessions for Barcelona next week.
See you there, and – once again, remember – do not use SFLIGHT sample data to schedule your flights!! 😉
-Vitaliy, aka @Sygyzmundovych