Database object lineage using SAP HANA Graph
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.
You can find the simple script on github.com.
I have tried your script in on on-premise HANA system (rev55) and I get the following error:
Could not execute 'DO(
IN i_start BIGINT => 201917,--201935, -- the key of the start vertex
IN i_dir NVARCHAR(10) ...'
Error: (dberror) : feature not supported: Anonymous blocks with GraphScript are currently not supported
Any idea what should be changed into the script to make it working ?
yes, anonymous blocks using the GRAPH language are only supported in HANA Cloud. To run the logic in a HANA on-prem system, you need to put the code into a procedure. I've added such a procedure to the script on github.
Thanks Markus, the procedure is working fine.
Would it be possible to get a screenshot of the setting you applied to get different colors depending on the OBJECT_TYPE. I have tried but it is not working.
you need to click on the settings icon
there is no dropdown list to select color in my case, It is a free text. I have tried to type GREEN but it is not working.
I'm in revision 55. Is it a known issue ?
sorry, I don't know. Maybe you can try on a different browser? The drop-down ultimately select HEX values. You might try with #F0F0F0.
Thanks, working fine this way