UPDATE: August 2019

Finally HANA Express Edition 2.0 SPS04 is out and we can use the Graph Database Cypher language directly within SELECT statements. See end of updated chapter “Graph Database”.

On May 25th this year I held a session at the first SAP Inside Track “Ruhrgebiet” #sitPOTT

I’ve promissed, that I will write a blog post and publish the data I’ve used. So here we go…

My session was inspired (among others) by my interest for Graph Databases. See also my blog post “Neo4a: The Neo4j ABAP Connector“.

For a session I held for my department a couple of weeks ago I’ve searched for a data model (other than the SFLIGHT) and hey, I’ve found the Open Beer Database. The model looked very promissing.

Unfortunatelly the data was quite old (2011) and a bit crappy, there where line feeds in the middle of texts and many not converted German Umlauts (you may know, that many of German breweries and beers have the ending “bräu”). I’ve cleaned up the data by hand (and some scripts) and uploaded the CSVs to my HANA instance. For some Polish cities I got some help on Twitter (thank you Witalij Rudnicki  et al.)

Graph Database

For a Graph workspace we only can combine two tables: the objects (called verteces) and the connections between these objects (called edges). Because we have many types of objects in our data model (beers, breweries,…) and will have many types of connections (brewed at, has style,…) I had to combine the existing data.

Fortunatelly I have already installed the newest ABAP Developer Edition on my Laptop and I was able to use ABAP using the Sidecar aproach. See also my blog post “SAP HANA Express and ABAP Developer Edition: new BFFs“.

The data you can find on Github in my repository “HANAandBEER“. I’ve uploaded the (mostly) cleaned CSVs and a catalog object named CatalogHanaBeerDB.tar.gz.

If you have installed SAP HANA Express Edition or any other HANA 2.0 you can import the catalog object directly in the WebIDE. There’s no need to upload the CSVs separately. Also the Graph Workspace should already be created automatically:

(I’m using HANA 2.0 SPS3, so the screenshot may vary a bit in your system)

Context menu of the tenant of your choice (HXE in HANA Express) -> Import Catalog Objects

Choose the Catalog file, change the schema name if needed and import the data.

In the schema selection choose the (new) schema and select “tables” in the project explorer

If the import went well, the tables and content should be loaded into the system.

For my demo in the session I’ve used some Cypher examples. Cypher is the open sourced language for Graph databases created by Neo4j.

First we have to open our new Graph workspace:

A sample of 200 objects are selected and shown. To better identify the objects and connections we have to adjust the settings a bit

Select NAME as Vertex label and TYPE as Edge label

Sometimes the settings are lost while working with the graph. In this case you have to repeat these steps (Hello WebIDE dev team, are you listening?). Also, if you go to the settings, the entered Cypher code is lost. So, if you are working on a larger Cypher code, copy the code to an external editor from time to time.

Some Cypher examples:

Select all beers of breweries which brew Altbier (obviously my favorite kind of beer, because I was born in Düsseldorf, the home of Altbier)

match (beer1)-[e1]->(brewery)
match (beer2)-[e2]->(brewery)
where ( beer1.NAME = 'Alt' or beer1.NAME = 'Altbier' )
and e1.TYPE = 'BREWED_AT'and e2.TYPE = 'BREWED_AT'
return beer1.NAME as b1name

You will get a nice Graph with the result (cutout):

Other examples:

Select all beers which have the same style like “Bolten Alt” (ID 4621) and are brewed in Germany

match (beer1)-[e1]->(style)
match (beer2)-[e2]->(style)
match (beer2)-[e3]->(brewery)
where beer1.ID = 4621
and e1.TYPE = 'HAS_STYLE'
and e2.TYPE = 'HAS_STYLE'
and e3.TYPE = 'BREWED_AT'
and brewery.COUNTRY = 'Germany'
return beer1.NAME as b1name

Show me all beers which have the same alcohol by volume (ABV) like the beers brewed at the Bolten brewery

match (Beer)-[e1]->(Brewery)
match (Beer)-[e2]->(abv)
match (Beer2)-[e3]->(abv)
where Brewery.NAME = 'Privatbrauerei Bolten'
and e1.TYPE = 'BREWED_AT'
and e2.TYPE = 'HAS_ABV'
and e3.TYPE = 'HAS_ABV'
return Brewery.NAME as brname

Or just browse thru the graph. Let’s start in Amsterdam.

match (city)
where city.NAME = 'Amsterdam'
return city.NAME as brname

After double click on Amsterdam, we see, that Amsterdam is a city in The Netherlands and has got two breweries:

Hopping from one object to the next…

UPDATE August 2019

With HANA Express Edition 2.0 SPS04 we can user the Graph Database Cypher language directly within SELECT statements. You can enter the following SELECT statement in the SQL console (or in your ABAP program, see below):

Show me all beers of breweries, which also brew Altbier

SELECT * FROM OPENCYPHER_TABLE( GRAPH WORKSPACE "CBA_BEER"."beer_graph" QUERY '
	match (beer1)-[e1]->(brewery)
	match (beer2)-[e2]->(brewery)
	where ( beer1.NAME = ''Alt'' or beer1.NAME = ''Altbier'' )
	and e1.TYPE = ''BREWED_AT''and e2.TYPE = ''BREWED_AT''
	return brewery.NAME as brewery, beer2.NAME as b2name
	order by brewery.NAME, beer2.NAME
')

Result:

Same as ABAP Code (with HANA as secondary database / Sidecar scenario)

TYPES: BEGIN OF ty_beer,
		 brewery  TYPE string,
		 beer     TYPE string,
	   END OF ty_beer.

DATA beers TYPE STANDARD TABLE OF ty_beer.

TRY.
	DATA(sql) = NEW cl_sql_statement( con_ref = cl_sql_connection=>get_connection( 'HANACBA' ) ).

	DATA(statement) = |SELECT * FROM OPENCYPHER_TABLE( GRAPH WORKSPACE "CBA_BEER"."beer_graph" QUERY '| &&
					  |  match (beer1)-[e1]->(brewery) | &&
					  |  match (beer2)-[e2]->(brewery) | &&
					  |  where ( beer1.NAME = ''Alt'' or beer1.NAME = ''Altbier'' ) | &&
					  |  and e1.TYPE = ''BREWED_AT''and e2.TYPE = ''BREWED_AT'' | &&
					  |  return brewery.NAME as brewery, beer2.NAME as b2name | &&
					  |  order by brewery.NAME, beer2.NAME | &&
					  |')|.

	DATA(result) = sql->execute_query( statement ).
	result->set_param_table( REF #( beers ) ).
	result->next_package( ).

	cl_demo_output=>display( beers ).

  CATCH cx_sql_exception INTO DATA(lcx2).
	cl_demo_output=>display( lcx2->get_text( ) ).

  CATCH cx_parameter_invalid INTO DATA(lcx_parameter).    "
	cl_demo_output=>display( lcx_parameter->get_text( ) ).

ENDTRY.

Spatial

If you take a deeper look at the created tables, you may have seen, that in the table “breweries_geocode” I’ve created a column named “point” with the SQL type “ST_POINT”. In this column I’ve entered, with the help of an ABAP programm of course, the spatial coded coordinates of the breweries.

If we look at the raw data, we cannot see much…

But after applying some spatial function we can get the coordinates back from the point

GeoJSON

Using the function .ST_AsGeoJson() will return the point as GeoJSON. You can copy the created JSON into the clipboard via context menu.

To check the created GeoJSON you can go to http://geojson.io and paste it into the input field. As a result you’ll see Bolten brewery on the map.

GeoJSON is a well known data format to describe simple and also more complex Geo data. You can, for example, download all the county bounderies of Germany from the “Open Data Lab“.

Combining this with another of my open source projects called “ZGeoJSON“, which includes already “LeafletJS” to visualize the data, you can create surprising apps in ABAP.

Display the boundery of Düsseldorf (one of the examples contained in the ZGeoJSON package)

And yes, this is really created in ABAP, with just a couple of lines of code…

Back to beer

Show me all breweries in a city

Show me the distance to the next Altbier brewery

(… now I’m thirsty)

Cheers, Uwe

Replay of my session

All other recordings of sitPOTT you can find here (thank you Gregor Wolf  and Oliver Kohl )