Multi Model - Property Graph

Graphs can model different kinds of networks and linked data coming from many industries, such as logistics and transportation, utility networks, knowledge representation, and text processing.

A graph is made up of a set of vertices and a set of edges. Vertices are the entities in the graph while the edges describe the relationships between vertices.

Each edge connects two vertices - one vertex is denoted as the source and the other as the target. Edges are always directed, but they can be oriented in either direction.

The SAP HANA Cloud Graph Engine applies the use of edges and vertices to analyze complex relationships in business data and processes to boost performance and business understanding. Key features include:

• Graph data processing that enhances relational business data, enabling work analysis
• Built-in graph scripting language
• SAP-delivered graph algorithms that accelerate the development of new applications
• Direct integration into SQL layer and database procedures
• Pattern-matching support for openCypher query language
• Python client for data science

Try it out!

Explore the SAP HANA Cloud Graph engine with the following exercise which uses the dataset from table GX_REVIEWS in the local schema.

Create a Graph Workspace

1. In the Database Explorer, expand the Catalog and select the object Graph Workspaces.

2. Use the Database Explorer SQL Console to create table constraints by running the following statements:

1234567
ALTER TABLE "GX_EDGES"
ADD FOREIGN KEY ("start") REFERENCES "GX_NODES" ("node_id") 
ON UPDATE CASCADE ON DELETE CASCADE ENFORCED VALIDATED;

ALTER TABLE "GX_EDGES"
ADD FOREIGN KEY ("end") REFERENCES "GX_NODES" ("node_id") 
ON UPDATE CASCADE ON DELETE CASCADE ENFORCED VALIDATED;

3. Now create the graph workspace GX_DELIVERIES using the following SQL. The graph will appear in the Graph Workspaces upon successful creation.

1234567
CREATE GRAPH WORKSPACE "GX_DELIVERIES" 
EDGE TABLE "GX_EDGES" 
	SOURCE COLUMN "start" 
	TARGET COLUMN "end" 
	KEY COLUMN "edge_id" 
VERTEX TABLE "GX_NODES" 
	KEY COLUMN "node_id" ;

Now explore the GX_DELIVERIES graph.

4. To explore the graph, select GX_DELIVERIES in the catalog object Graph Workspaces, then right-click on it and select View Graph.

5. The graph is displayed as below:

Analysis of the graph can be enhanced by adding context such as labels and color schemes to highlight relationships. The following steps will change the color of the Vertices (Nodes) for priority deliveries to red.

6. Select the Settings icon in the top right corner to open a configuration menu for the graph.

7. On the Vertex tab, under Highlight Vertex, set the following values to highlight the cities with priority delivery orders to red:

• Column: priority
• Value: TRUE
• Color: Red

8. Select Apply to see the changes on the graph.

It’s also possible to use this method to change the Edge settings on the graph. Alter the Edge configuration to display the delivery method and highlight any drone deliveries to red also.

9. In the graph settings menu, select the Edge tab and make the following changes:

• Edge Label: method
• Column: method
• Value: drone
• Color: Red

10. Select Apply and then Close to return to the graph.

As can be seen on the graph, delivery method edges of type drone are now highlighted in red where applicable.

Explore Graph algorithms

SAP HANA Cloud Graph provides a new graph calculation node that can be used in calculation scenarios.

This node executes one of the available actions on the given graph workspace and provides the result as table output.

Calculation scenarios can be created with plain SQL as shown in the following section, or with tools such as the SAP HANA Modeler or the native SAP HANA Cloud Graph Viewer.

Try it out!

Calculate the shortest path from one point to another in the graph using the built-in Shortest Path algorithm.

1. Select the Algorithms icon beside the settings icon to open the algorithms dialog menu.

2. In the Algorithm drop down option, select Shortest Path.

3. Enter the following values as the inputs to the algorithm:

   • Start Vertex: Miltenberg
   • End Vertex: Nuremburg
   • Direction: Outgoing
   • Weight Column: length

4. Choose Apply to see the result:

Find all nodes which are related to a particular node with the Neighborhood algorithm. The parameters for this algorithm include depth of search, and whether to search for incoming or outgoing edges. Let’s see what other nodes are related to the Mannheim vertex.

5. In the Algorithm drop-down box, select Neighborhood.

6. Enter the following values as inputs:

   • Start Vertex: Mannheim
   • Direction: Incoming
   • Min Depth: 0
   • Max Depth: 1

7. Choose Apply to see the resulting graph.

Algorithms Using GraphScript

GraphScript is a high-level, powerful, domain-specific language. GraphScript eases the development and integration of complex graph algorithms.

Try it out!

1. Call the Nearest-Neighbor built-in algorithm from GraphScript. In the SQL Console paste and execute following code which creates a procedure called SP_NHOOD:

123456789101112131415
CREATE TYPE "TT_NODES" AS TABLE ("node_id" NVARCHAR(50), "name" NVARCHAR(50));


CREATE OR REPLACE PROCEDURE "SP_NHOOD"(
	IN startV NVARCHAR(50),
	IN minDepth INTEGER,
	IN maxDepth INTEGER,
	OUT res "TT_NODES")
LANGUAGE GRAPH READS SQL DATA AS
BEGIN
  GRAPH g = Graph("GX_DELIVERIES");
  VERTEX v_s = Vertex(:g, :startV);
  MULTISET<VERTEX> ms_n = Neighbors(:g, :v_s, :minDepth, :maxDepth);
  res = SELECT :v."node_id", :v."name" FOREACH v IN :ms_n;
END;

2. Now call the procedure SP_NHOOD with different parameters to check results.

1
CALL "SP_NHOOD"('Berlin', 0, 2, ?);

Use GraphScript to create a custom traverse algorithm.

3. In the SQL Console paste and execute following code. It creates a procedure to calculate Shortest-Path distances to cities with high priority status.

123456789101112131415161718192021
CREATE TYPE "TT_PRIORITY" AS TABLE 
("node_id" NVARCHAR(50), "distance" INTEGER, "hops" BIGINT);

CREATE OR REPLACE PROCEDURE "SP_PRIORITY"
(IN startV NVARCHAR(50), OUT res "TT_PRIORITY")
LANGUAGE GRAPH READS SQL DATA AS
BEGIN
	GRAPH g = Graph("GX_DELIVERIES");
	VERTEX v_s = Vertex(:g, :startV);
	MULTISET<Vertex> rests = v IN Vertices(:g) WHERE :v."IN_SCOPE" == 1;
	ALTER g ADD TEMPORARY VERTEX ATTRIBUTE (INT "distance" = 0);
	ALTER g ADD TEMPORARY VERTEX ATTRIBUTE (BIGINT "hops" = 0L);
	FOREACH rest in :rests {
		VERTEX v_rest = Vertex(:g, :rest."node_id");
		WeightedPath<INT> p = Shortest_Path(:g, :v_s, :v_rest, 
		(Edge conn) => INTEGER { return :conn."length"; } );
		rest."hops" = Length(:p);
		rest."distance" = Weight(:p);
	}
	res = SELECT :v."node_id", :v."distance", :v."hops" FOREACH v IN :rests;
END;

4. This code creates an SP_PRIORITY object in Procedures. Right-click on the procedure name and select Generate CALL Statement With UI.

5. The procedure’s call statement is generated. Provide input value Berlin and select Run.

The result of the GraphScript is the shortest distance in meters to each city plus the number of hops to get there.

Pattern Matching Using OpenCypher in SQL

Application builders can also apply pattern matching to their solutions. OpenCypher is a declarative graph query language for graph pattern matching developed by the OpenCypher Implementers Group.

Use OpenCypher directly within a SQL statement:

1. Run the following query from the SQL console:

1234567
SELECT * FROM OPENCYPHER_TABLE( GRAPH WORKSPACE "GX_DELIVERIES" QUERY
    '
    MATCH (point1)
    WHERE point1.fragile = ''TRUE''
    RETURN point1.node_id as destination, point1.fragile as fragile_deliveries
    '
) ORDER BY "destination";

2. Select only edges (or relationships in Cypher terminology) from a city with a high priority to a valid city.

12345678
SELECT * FROM OPENCYPHER_TABLE( GRAPH WORKSPACE GX_DELIVERIES QUERY
    '
	MATCH (point1)-[conn]->(point2)
	WHERE point1.IN_SCOPE = 1 AND point2.priority = ''TRUE''
	RETURN point1.node_id as delivery_start, conn.length as distance, 
	conn.mode as delivery_type, point2.node_id as delivery_end
    '
) ORDER BY "delivery_start";

Well done! This completes the lesson on the SAP HANA Cloud Graph Engine. For further information on this topic, check out the following link.

• SAP HANA Cloud, SAP HANA Database Graph