Database object lineage using SAP HANA Graph

[mfath]

In SAP HANA Cloud, you find database objects like tables, views, procedure, functions etc. These objects do not "stand alone", but rather depend on each other. SQL views are built on tables, and are used in procedures. When migrating or changing database objects, it is often required to understand the "dependency tree".

In this blog post, I will briefly describe how you can use the SAP HANA Graph engine to explore these dependencies. Note that this is just an example to showcase the graph engine's capabilities - it is not an official database operation. The code runs on SAP HANA Cloud QRC2 (including SAP HANA Cloud Trial) and can be found on github.com.

SAP HANA exposes its database objects and dependencies via two system views: SYS.OBJECTS and SYS.OBJECT_DEPENDENCIES. From a graph perspective, these two datasets represent a graph's vertices and edges. So, the only thing we need to do to explore database objects graph is to create a GRAPH WORKSPACE, pointing to the OBJECTS and OBJECT_DEPENDENCIES.

The GRAPH WORKSPACE can be visualized using the SAP HANA Database Explorer. In the above image, the green vertices are database views, the light blue vertex is the graph workspace, and the red ones are procedures. We see that the procedure GS_SPOA depends on the V_OBJECT_GRAPH workspace, which in turn depends on the V_VERTICES view and so on.

Now, to retrieve depending and dependent objects programmatically, we can run a piece of GraphScript code that calls the built-in algorithm SHORTEST_PATHS_ONE_TO_ALL. The code block returns a set of "upstream" or "downstream" vertices, including their hop-distance to the start object. In the example below, we started at the view V_EDGES and found 10 object depending on the view.

You can find the simple script on github.com.