SAP HANA Graph visualizes SFLIGHT data

The idea for this post came from a joke during a conversation with Bartosz Jarkowski on Twitter: https://twitter.com/Sygyzmundovych/status/1016289331714121730

Hmm… Why should not try testing knowledge from recent openSAP course “Analyzing Connected Data with SAP HANA Graph” using that SFLIGHT data? Frankly, my biggest issue was that I am not ABAP developer. Even though I heard about this classic data model, I’ve never worked with it…

I have SAP HANA, express edition, running as a Docker container on my Intel NUC, so no ABAP models in there. But what is the community for? It happened that Riccardo Brogi paved a way to this data in SAP HANA with HOWTO – Import SFLIGHT sample data into HANA from a local computer already 5 years ago!

But now in 2018 we have SAP HANA 2.0 SPS3 which comes with support for graph workspaces and the new graph visualization capability in the Database Explorer. So, let’s give it a try.

1. Download and prepare SFLIGHT data

In my case I am not using the desktop, but downloading data from Riccardo’s repo directly to my SAP HANA’s server.

At OS level:

sudo su - hxeadm
cd $DIR_INSTANCE/work/
wget https://github.com/mrbrogi/SAP_SFLIGHT_4_HANA/blob/f8773d1d6c38457f16d59e1476244a68ae5ff7cf/sflights%20Database%20for%20HANA.zip?raw=true -O sflight.zip
unzip sflight.zip

Now data and catalogs should be uncompressed in /usr/sap/HXE/HDB90/work/

2. Upload data into SFLIGHT schema

From SAP HANA Database Explorer I connected to my HXE tenant (but you can pick whatever place you want in your database).

IMPORT "SFLIGHT"."SMACOURSE",
	"SFLIGHT"."STICKET",
	"SFLIGHT"."SDESSERT",
	"SFLIGHT"."SMEALT",
	"SFLIGHT"."SCURX",
	"SFLIGHT"."STRAVELAG",
	"SFLIGHT"."SFLIMEAL",
	"SFLIGHT"."SFLIGHT",
	"SFLIGHT"."SCPLANE",
	"SFLIGHT"."SNVOICE",
	"SFLIGHT"."SPPLANE",
	"SFLIGHT"."SMEAL",
	"SFLIGHT"."SCITAIRP",
	"SFLIGHT"."SCARPLAN",
	"SFLIGHT"."SCARR",
	"SFLIGHT"."SCOUNTER",
	"SFLIGHT"."SPFLI",
	"SFLIGHT"."SAIRPORT",
	"SFLIGHT"."SBUSPART",
	"SFLIGHT"."SAPLANE",
	"SFLIGHT"."SMENU",
	"SFLIGHT"."SSTARTER",
	"SFLIGHT"."SCURR",
	"SFLIGHT"."SGEOCITY",
	"SFLIGHT"."SCUSTOM",
	"SFLIGHT"."SBOOK" 
FROM '/usr/sap/HXE/HDB90/work' WITH REPLACE THREADS 8;

This will create all tables and load all data. In my case I needed only airports as vertexes and connections as edges of a graph. But if you know all other tables and data in it, then here you go!

3. Prepare the data model for a graph

Currently SAP HANA expects a single column key for both vertexes and edges. And this column should be both Unique and Not Null.

But coming from ABAP these tables contain MANDT field as part of the key. And I checked that clean data seems to be in the client 300.

So, quick and dirt, I am just creating another table from this data to be used in the graph workspace.

CREATE COLUMN TABLE "SFLIGHT"."SAIRPORT_C" 
AS (select
	"ID",
	"NAME",
	"TIME_ZONE"
from "SFLIGHT"."SAIRPORT"
where "MANDT" = '300');

ALTER TABLE "SFLIGHT"."SAIRPORT_C" ADD PRIMARY KEY ("ID");

And in case of the connections table, the key need concatenation of airline and flight number, so I combine them as _ID key column.

CREATE COLUMN TABLE "SFLIGHT"."SPFLI_C" 
AS (select
	 concat("CARRID", "CONNID") as "_ID",
	 "CARRID", "CONNID",
	 "COUNTRYFR", "CITYFROM", "AIRPFROM",
	 "COUNTRYTO", "CITYTO", "AIRPTO",
	 "FLTIME", "DEPTIME", "ARRTIME",
	 "DISTANCE", "DISTID",
	 "FLTYPE", "PERIOD" 
from "SFLIGHT"."SPFLI" 
where "MANDT"=300);

ALTER TABLE "SFLIGHT"."SPFLI_C" ADD PRIMARY KEY ("_ID");

4. Create and visualize the graph

Now it is time to create the graph workspace based on the tables above…

--DROP GRAPH WORKSPACE "SFLIGHT"."CONNECTIONS_C";
CREATE GRAPH WORKSPACE "SFLIGHT"."CONNECTIONS_C"
	EDGE TABLE "SFLIGHT"."SPFLI_C"
		SOURCE COLUMN "AIRPFROM"
		TARGET COLUMN "AIRPTO"
		KEY COLUMN "_ID"
	VERTEX TABLE "SFLIGHT"."SAIRPORT_C"
		KEY COLUMN "ID";

…and visualize the graph in SAP HANA Database Explorer:

Too bad Birmingham, that is the home airport for Bartosz, is not there 😀

This was my first, but surely not the last, post on graph processing with SAP HANA. Stay tuned for more, but share your ideas and suggestions in the meantime!

‘Till next time,
-Vitaliy, aka @Sygyzmundovych