Skip to Content
Author's profile photo Philip MUGGLESTONE
Philip MUGGLESTONE
April 13, 2018 2 minute read

Enhancements to Graph Processing in SAP HANA 2.0 SPS 03

SAP HANA Graph processing continues to evolve at a rapid pace and new capabilities have recently been introduced with HANA 2.0 SPS 03.

The focus is on enhanced support both for GraphScript and openCypher query language. Additionally an all new graph visualizer has been included with the Database Explorer and shortest path is now available in the calculation view modeler of SAP Web IDE for HANA.

GraphScript has been extended to support shortest path as well as graph traversal to enable Breadth-First search.

CREATE PROCEDURE "TripRoutingSP" (IN airportCodeOrigin NVARCHAR(3), IN airportCodeDestination NVARCHAR(3), OUT totalSegments BigInt, OUT totalDistance Int, OUT totalDuration Int, OUT routing "TripRoutingTT")
 LANGUAGE GRAPH READS SQL DATA AS
  BEGIN
	Graph g = Graph("TRAVEL", "Flights");
	Vertex sourceVertex = Vertex(:g, :airportCodeOrigin);
	Vertex targetVertex = Vertex(:g, :airportCodeDestination);
	WeightedPath<BigInt> p = SHORTEST_PATH(:g, :sourceVertex, :targetVertex);
	totalSegments = Length(:p);
	FOREACH e IN Edges(:p) {
		totalDistance = :totalDistance + :e."distance";
		totalDuration = :totalDuration + :e."duration";
	}
	routing = SELECT :segment, :e."airportCodeOrigin",  :e."airportCodeDestination", :e."airlineName", :e."distance", :e."duration" FOREACH e in Edges(:p) WITH ORDINALITY AS segment;
  END;
CREATE PROCEDURE "AirportDestinations" (IN airportCode NVARCHAR(3), OUT direct Int, OUT oneTransfer Int, OUT twoTransfers Int)
 LANGUAGE GRAPH READS SQL DATA AS
  BEGIN
	Graph g = Graph("TRAVEL", "Flights");
	TRAVERSE BFS :g 
		FROM { 
			Vertex(:g, :airportCode)
		} 
	    ON VISIT VERTEX (Vertex v, BigInt lvl) {
	    	IF (:lvl == 1L) {
	    		direct = :direct + 1;
	    		}
	    	ELSE {
	    		IF (:lvl == 2L) {
	    			oneTransfer = :oneTransfer + 1;
	    		}
	    		ELSE {
	    			IF (:lvl == 3L) {
	    				twoTransfers = :twoTransfers + 1;
	    			}
		    	}
	    	}
		}
	;
  END;

SAP HANA Graph support for openCypher now includes text predicates (STARTS WITH, ENDS WITH etc.), advanced text search (Fuzzy, Linguistic etc.) and multiple match clauses are now supported.

MATCH (a)-[e]->(b)
WHERE e.airlineName STARTS WITH 'KLM'
RETURN a.airportCode AS airportCodeFrom, b.airportCode AS airportCodeTo, e.distance AS distance

MATCH (a)-[e]->(b)
WHERE e.airlineName ENDS WITH 'lines'
RETURN a.airportCode AS airportCodeFrom, b.airportCode AS airportCodeTo, e.distance AS distance

MATCH (a)-[e]->(b)
WHERE e.airlineName SYS.TEXT_CONTAINS(e.airlineName, 'Airline', 'FUZZY(0.4)')
RETURN a.airportCode AS airportCodeFrom, b.airportCode AS airportCodeTo, e.distance AS distance
MATCH (a)-[e1]->(b)
MATCH (b)-[e2]->(c)
MATCH (c)-[e3]->(d)
WHERE a.airportCode = ''$$airportCodeFrom$$'' AND d.airportCode = ''$$airportCodeTo$$'' $$customFilter$$
RETURN e1.airlineName AS airlineName1, b.airportCode AS transferAirportCode1, e2.airlineName AS airlineName2, c.airportCode AS transferAirportCode2, e3.airlineName AS airlineName3

To see these new capabilities in action and get up to speed quickly we’ve added hands-on tutorials to the SAP HANA  Academy.

Videos showing what’s new for graph processing with HANA 2 SPS 03 are:

SQL Script:

XS Advanced Model:

For the bigger picture, the Graph playlist covers all aspects of SAP HANA Graph from introduction and getting started to creating a graph workspace to using both the graph viewer tool, SQL Script, and the graph node of the calculation view modeler in SAP Web IDE for HANA.

Have fun!

–

The SAP HANA Academy provides free online video tutorials for the developers, consultants, partners and customers of SAP HANA.

Topics range from practical how-to instructions on administration, data loading and modeling, and integration with other SAP solutions, to more conceptual projects to help build out new solutions using mobile applications or predictive analysis.

For the full library, see SAP HANA Academy Library – by the SAP HANA Academy

For the full list of blogs, see Blog Posts – by the SAP HANA Academy

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.