Skip to Content
Technical Articles
Author's profile photo Witalij Rudnicki

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

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Chris Hallenbeck
      Chris Hallenbeck

      Very cool!

      Author's profile photo Shivam Shukla
      Shivam Shukla

      Ohh great to see the graphic view of data ,  thanks a lot for write 🙂

       

      Thanks,

      Shivam

      Author's profile photo Bartosz Jarkowski
      Bartosz Jarkowski

      Great blog Witalij Rudnicki !! 🙂

       

      Author's profile photo Lucia Subatin
      Lucia Subatin

      Glad I bookmarked this one for later reading, already trying it 🙂

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Hi Witalij,

      really nice. I know you are currently on the plane to LAS, hope you have a nice flight (and good WiFi to anwer my question 😉 ).

      I'm trying your example with my HANA Express 2.0 and I'm getting an error at the place where I want to create the graph workspace:

      Do I have to install additional functionality to the HANA instance or is the Graph DB not part of the Express edition?

      Cheers and have fun at TechEd, Uwe

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Ha, it’s really just a glitch in the matrix WebIDE -> you can execute the statement without errors ?