Enhancements to Graph Processing in SAP HANA 2.0 SPS 03

[PhilipMugglestone]

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:

• Getting Started with SPS03


• GraphScript - Shortest Path


• GraphScript - Traversal


• openCypher - Text Predicates and Search


• openCypher - Multiple MATCH Clauses

XS Advanced Model:

• Getting Started with SPS03


• Calculation View Modeler - Shortest Path


• GraphScript - Shortest Path


• GraphScript - Traversal


• openCypher - Text Predicates and Search


• openCypher - Multiple MATCH Clauses

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

• Subscribe to our YouTube channel for updates


• Join us on LinkedIn: linkedin.com/in/saphanaacademy


• Follow us on Twitter: @saphanaacademy


• Google+: plus.google.com/+saphanaacademy


• Facebook:  facebook.com/saphanaacademy