Technical Articles
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
Very cool!
Ohh great to see the graphic view of data , thanks a lot for write 🙂
Thanks,
Shivam
Great blog Witalij Rudnicki !! 🙂
Glad I bookmarked this one for later reading, already trying it 🙂
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
Ha, it’s really just a glitch in the
matrixWebIDE -> you can execute the statement without errors