GraphScript in SAP HANA: on SFLIGHT and at TechEd

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.

Neighborhood

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?

1. We created TT_NODES table type, to be used to output results: ID and the NAME of airports
2. Then we created a procedure with the LANGUAGE GRAPH definition — meaning it is a GraphScript
3. This procedure receives a code of an airport (ID of the node) as an input, and returns a list of airports as an output
4. In the procedure’s code you can see graph-specific data types, like GRAPH or VERTEX, and collection types, like MULTISET
5. Neighbors() is a HANA’s built-in graph function, which takes graph g and start vertex v_s as 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)
6. At the end results from a collection ms_n are assigned to the result variable res using projection expression SELECT ... FOREACH ... IN statement

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.

More details:

• Barcelona: https://sessioncatalog.sapevents.com/go/agendabuilder.speakers/?l=192&speaker_id=46888

See you there, and – once again, remember – do not use SFLIGHT sample data to schedule your flights!! ?

-Vitaliy, aka @Sygyzmundovych