This example is used as a demo in a session INT105 – Build Data Pipelines with SAP Data Intelligence at SAP TechEd 2020. You are more than welcome to join this session and to watch that demo. But it is not a prerequisite, if you want to continue with this post.
In the previous post we started building a data pipeline in SAP Data Intelligence to ingest data from an API end-point and to transform the data from the TLE encoded format to a readable JSON payload. Now it is time to load this data, and we will use SAP HANA, express edition, for that.
Ok, let’s continue our journey…
Add SAP HANA connection configuration
Let’s go to Connection Management application in SAP Data Intelligence.
default tenant of a trial instance you may a connection
HANA_LOCALHOST among others. It might be a good option to play with if you want to read/write data from SAP HANA from within Data Intelligence trial instance. But in our case I want to be able to have access to data in SAP HANA db from other external clients too.
Let’s create another connection. As I’ve mentioned it will be cloud-hosted instance of SAP HANA, express edition. I will call it
You may see I am turning
Use TLS option on. It is better be safe, than sorry.
Test Connection was successful, so let me Create this configuration.
Check objects in the Metadata Explorer
Now that the connection has been created, let’s check it in the Metadata Explorer application. If you are not familiar with the Metadata Explorer, than I would recommend to check tutorials first:
In the application go to Catalog > Browse Connections…
… and then to our connection (in my case it is
MyHXE_HXE_SYSTEM) and to the
SYSTEM schema. In my system it is empty for the moment.
Add SAP HANA operator to the data pipeline
Back to the Modeler application and our graph created in the previous post let’s add a SAP HANA Client operator to the data pipeline.
Connect the last Wiretap’s
out port to HANA Client’s
Let’s configure the operator by defining following parameters:
ISS locs to SAP HANA
MyHXE_HXE_SYSTEMfrom the Connection Manager
- Table name:
- Table columns:
- Input format: JSON
- Insert mode: INSERT
- Table initialization: Create
- Decimal output: Floating-point
- Terminate on error: False
The JSON configuration of table columns should allow us to see nice form view, when opened in Table Details preview.
Once this additional configuration is completed it is time to save and execute the graph.
And once it is running…
ISS_TRACK object in the Metadata Explorer
Go back to the Metadata Explorer. If needed refresh the screen to see changes in the
SYSTEM schema of
You should see
ISS_TRACK object of the type “Table” there.
Go to the Fact Sheet of this object and switch to Data Preview view. You should see data inserted by the running graph.
Let the graph run for at least 10 minutes to collect some data.
Exploration of data in SAP HANA
Once there is at least 10 minutes of data collected we can stop the graph’s execution and move to exploration of data in SAP HANA.
Let’s have a look what path the ISS made while I was running a graph collecting the data.
SELECT UTCTOLOCAL("TSTMP") AS "TSTMP", "LON", "LAT", "ALT", SECONDS_BETWEEN (UTCTOLOCAL("TSTMP"), NOW()) AS "Sec_Ago", NEW ST_POINT('Point Z('||"LON"||' '||"LAT"||' '||"ALT"||')',4326) AS "Loc3D" FROM "ISS_TRACK";
Let’s calculate the satellite’s “ground speed” — as if it moved at the surface of the Earth — at the last recorded timestamp.
SELECT TOP 1 UTCTOLOCAL ("TSTMP", 'CET') as TIMECET, ROUND(IFNULL(NEW ST_Point('POINT ('||"LON"||' '||"LAT"||')', 4326).ST_Distance (NEW ST_Point('POINT ('||LAG("LON", 1, "LON") OVER (ORDER BY "TSTMP")||' '||LAG("LAT", 1, "LAT") OVER (ORDER BY "TSTMP")||')', 4326), 'kilometer')/ SECONDS_BETWEEN (LAG("TSTMP", 1) OVER (ORDER BY "TSTMP"),"TSTMP"), 0), 2) AS "KMpS" FROM (SELECT TOP 2 UTCTOLOCAL("TSTMP") AS "TSTMP", "LON", "LAT", "ALT", SECONDS_BETWEEN (UTCTOLOCAL("TSTMP"), NOW()) AS "Sec_Ago", NEW ST_POINT('Point Z('||"LON"||' '||"LAT"||' '||"ALT"||')',4326) AS "Loc3D" FROM "SYSTEM"."ISS_TRACK" ORDER BY "TSTMP" DESC) ORDER BY "TSTMP" DESC
6.33 kilometers per second. Without getting a speeding ticket!
It is all for now covering what was included in my session’s demo. I am looking forward to virtually see you at SAP TechEd this week!
But I would like to build on this demo after the conference is over. I have some ideas, but please include yours in the comments. Much appreciated!
Stay healthy ❤️ and stay curious ? everyone,
-Vitaliy (aka @Sygyzmundovych)