EDUCAÇÃO E TECNOLOGIA

Using multi model capabilities of SAP HANA in the new ABAP 1909 Developer Edition – Part III – Graph functions

This is part three of a three part series:

In this part three I will demonstrate, how to define a Graph workspace and call a Graph function directly from ABAP. I will not explain, what HANA Graph is and what functions you can use. There a plenty of good (and great!) blog posts, tutorials, help pages and even an openSAP course out there, which can explain the topic much better than me.

Unfortunatelly (again) I haven’t found any documentation for ABAP Graph, but I could remember a slidedeck from the DSAG Developers Days 2019, where this function was mentioned. With try and error I was able to create the ABAP Graph workspace and could call Graph functions.

To simplify the model we will use the well known Flight Data Model, where we want to connect the airports. The sample data for this example is already generated on the ABAP 1909 Developer Edition.

Because a Graph workspace needs CDS views instead of database tables and the key of the edge table must be one field only, we need to define two new CDS views:

The vertex view

We only need the airport Ids here

define view zabap_graph_airport_vertex as select from sairport
{ key id as AirportId, name as Name
}

The edge view

To eliminate the client in this view, we have to insert it in the WHERE clause, else we would have double entries (and a dump) when we read the view within the AMDP procedure. Haven’t found out yet how else to could solve this problem.

define view zabap_graph_spfli_edge as select from spfli
{ key concat(carrid, connid) as ConnectionKey, carrid as Carrid, connid as Connid, countryfr as Countryfr, cityfrom as Cityfrom, airpfrom as AirportFrom, countryto as Countryto, cityto as Cityto, airpto as AirportTo, fltime as Fltime, deptime as Deptime, arrtime as Arrtime, distance as Distance, distid as Distid, fltype as Fltype, period as Period
}
where mandt = $session.client

On SAP HANA you would define a graph workspace with the SQL command CREATE GRAPH WORKSPACE.

In the ABAP environment the definition of the workspace takes place in a special AMDP method.

Definition

CLASS-METHODS connection_graph FOR DDL OBJECT OPTIONS CDS SESSION CLIENT REQUIRED.

Implementation

METHOD connection_graph BY DATABASE GRAPH WORKSPACE FOR HDB LANGUAGE SQL USING zabap_graph_spfli_edge zabap_graph_airport_vertex. edge table zabap_graph_spfli_edge source column AirportFrom target column AirportTo key column ConnectionKey vertex table zabap_graph_airport_vertex key column AirportId; endmethod. 

Would be intersting to know, whether a virtual workspace will be created on the database everytime the class is called or only the first time. Maybe someone from the kernel team can answer this question?

The definition of the Graph functions can (must? Haven’t tested yet) be defined in the same class.

Definition

TYPES: BEGIN OF ty_route, ConnectionKey TYPE zabap_graph_spfli_edge-ConnectionKey, AirportFrom TYPE zabap_graph_spfli_edge-AirportFrom, Airportto TYPE zabap_graph_spfli_edge-Airportto, END OF ty_route. TYPES: tt_routes TYPE STANDARD TABLE OF ty_route WITH EMPTY KEY. CLASS-METHODS get_shortest_path AMDP OPTIONS CDS SESSION CLIENT current IMPORTING VALUE(i_airport_from) TYPE zabap_graph_spfli_edge-AirportFrom VALUE(i_airport_to) TYPE zabap_graph_spfli_edge-AirportTo EXPORTING VALUE(e_routes) TYPE tt_routes RAISING cx_amdp_execution_error.

Implementation

 METHOD get_shortest_path BY DATABASE PROCEDURE FOR HDB LANGUAGE GRAPH OPTIONS READ-ONLY USING zcl_abap_graph=>connection_graph. Graph g = Graph ( "ZCL_ABAP_GRAPH=>CONNECTION_GRAPH" ); Vertex v_from = Vertex (:g, :i_airport_from); Vertex v_to = Vertex (:g, :i_airport_to); WeightedPath<BigInt> p_path = Shortest_Path ( :g, :v_from, :v_to ); e_routes = select :part.ConnectionKey, :part.AirportFrom, :part.AirportTo foreach part in edges( :p_path ); ENDMETHOD.

How does it work? We are using the defined Graph workspace “ZCL_ABAP_GRAPH=>CONNECTION_GRAPH”. With the function “shortest_path” we are creating a path object with shortest connection between the two vertices v_from (Airport from) and v_to (Airport to).

The SELECT reads this path object and returns the edges of the found path.

This is the easy part and somehow self explaining 😉

TRY. zcl_abap_graph=>get_shortest_path( EXPORTING i_airport_from = 'FRA' i_airport_to = 'SIN' IMPORTING e_routes = DATA(routes) ). cl_demo_output=>display( routes ). CATCH cx_amdp_execution_error INTO DATA(lcx). cl_demo_output=>display( lcx->get_text( ) ).
ENDTRY.

As a result you should get

CONNECTIONKEY AIRPORTFROM AIRPORTTO UA0941 FRA SFO SQ0015 SFO SIN 

-> the shortest path (number of hops*) between Frankfurt and Singapur is via San Francisco

* for other examples (WEIGHT distance or flight time) we don’t have enough test data in the system. Feel free to try it on your own. Leave a comment, if it worked out. Here are some Graph function, you can try: SAP HANA Graph Reference

Direct on the HANA database we can also use “Open Cypher” as query language

Unfortunatelly I still was not able to implement such a query in ABAP (not on this ABAP 1909 Developer Edition, nor on the most current Steampunk, nor on ABAP 7.55 SP1).

See also my question on SCN

 METHOD call_cypher BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY. e_routes = select * from opencypher_table( GRAPH WORKSPACE "ZCL_ABAP_GRAPH=>CONNECTION_GRAPH" QUERY 'match (from)-[e]->(to) return e.CONNECTIONKEY as ConnectionKey, from.AIRPORTID as AitportFrom, to.AIRPORTID as AirportTo' ); ENDMETHOD. 

Now you have all the tools (Document Store, Spatial functions, Graph functions) at hand to build new interesting stuff. Feel free to leave a comment. What ideas do you have already in mind? What do you want to build first?

Cheers, Uwe

Code on Github