Query Hierarchy Data in SAP HANA Database using SQLScript
With SAP HANA 2 SP02, SQLScript enhancements on HANA database enables SQL developers to query hierarchy data stored as parent-child relation in database tables easily using SQLScript Hierarchy function. In this SQLScript tutorial, I want to show SQL programmers how they can query and filter hierarchical data with sample cases using SQL Hierarchy function.
SQL Server provided a similar hierarchy function and enabled the use of recursive CTE queries for hierarchy data for long time ago.
It is very good that SQLScript developers have the same option and easiness for querying hierarchical data on SAP HANA database tables.
Hierarchy Data as Parent-Child Rows in SAP HANA Database
Let's create our sample hierarchy table in our database and populate it with parent-child records simulating a company organization.
Create Column Table Organization (
Id int generated by default as identity(start with 1 increment by 1) not null,
Unit varchar(100) Not Null,
ParentID int
);
Insert Into Organization (Unit, ParentId) values ('CEO', NULL);
Insert Into Organization (Unit, ParentId) values ('Sales Department', 1);
Insert Into Organization (Unit, ParentId) values ('Administration Department', 1);
Insert Into Organization (Unit, ParentId) values ('Customer Service Department', 1);
Insert Into Organization (Unit, ParentId) values ('Marketing Department', 1);
Insert Into Organization (Unit, ParentId) values ('Planning Department', 2);
Insert Into Organization (Unit, ParentId) values ('Research Department', 2);
Insert Into Organization (Unit, ParentId) values ('Engineering Department', 2);
Insert Into Organization (Unit, ParentId) values ('Human Resources Department', 3);
Insert Into Organization (Unit, ParentId) values ('Operation Department', 3);
Insert Into Organization (Unit, ParentId) values ('Planning Department', 4);
Insert Into Organization (Unit, ParentId) values ('Research Department', 4);
Insert Into Organization (Unit, ParentId) values ('Products Division A', 5);
Insert Into Organization (Unit, ParentId) values ('Products Division B', 5);
Insert Into Organization (Unit, ParentId) values ('Training Group', 9);
Insert Into Organization (Unit, ParentId) values ('Recruiting Team', 9);
Insert Into Organization (Unit, ParentId) values ('Salary Group', 9);
Insert Into Organization (Unit, ParentId) values ('Planning Department', 10);
Insert Into Organization (Unit, ParentId) values ('Research Department', 10);
Insert Into Organization (Unit, ParentId) values ('Overseas Business Department', 13);
Insert Into Organization (Unit, ParentId) values ('Domestic Business Department', 13);
Insert Into Organization (Unit, ParentId) values ('Direct Sales Department', 14);
Insert Into Organization (Unit, ParentId) values ('Channel Department', 14);
The hierarchical data that I wanted to create in SAP HANA database using above SQL DML commands on parent-child table Organization is following organizational structure of a fictional company.
I used identity value for the ID column, SQL developers can refer to SQLScript Identity Column for details.
Query Hierarchy Data using SQLScript Hierarchy Function on HANA Database
On a parent - child table, SQL programmers can easily use SQLScript function Hierarchy to resolve the hierarchical ranks, relations, levels of a node easily.
Here is the simplest syntax for calling Hierarchy function using SQLScript on SAP HANA database
It is must to use HIERARCHY function name in the FROM clause just like a table (as it is a table function) and define the hierarchy in a predefined format in the SOURCE() of the function argument.
The source query, should have a node_id and a parent_id column to identify the relation in the hierarchical data structure.
select *
from HIERARCHY (
source (
select
id as node_id,
parentid as parent_id,
unit
from Organization
)
)
And the output is as follows on given parent-child sample hierarchical data
As I marked on the above output screenshot, the most important columns of the Hierarchy function output table is; Hierarchy_Level and Hierarchy_Parent_Rank
Hierarchy_Level is showing the position of levels from top to down of the current node in the hierarchical structure.
I tried to show the levels or hierarchy ranks of nodes in the organizational structure to make it visually visible in the following diagram.
List Parent Nodes or Ancestors of a Hierarchy Node on SAP HANA Database
So if you are searching for the upper-level managers or upper organizational units in an organizational structure, which SQL query HANA database developers should use?
SQLScript enhancements introduced SAP HANA database programmers the hierarchy function HIERARCHY_ANCESTORS to query nodes in a hierarchical structure for parent nodes
Here is a sample SQL query which lists all the parents of a node in given hierarchy data in this tutorial.
select *
from HIERARCHY_ANCESTORS (
SOURCE HIERARCHY (
source (
select
id as node_id,
parentid as parent_id,
unit
from Organization
)
)
START WHERE node_id = 23
)
As SQL developers can see easily, I have listed all parents of organizational unit "Channel Department" which has Id as 23 in the database table.
Please note that the hierarchy_distance column shows the levels between two nodes. For parent nodes, the distance is expressed in negative values.
Let's combine our knowledge and list only the parent node names (unit descriptions) in order starting from top to bottom seperated by ">" character.
select
string_agg(unit, ' > ' order by hierarchy_rank) as bottom2up
from HIERARCHY_ANCESTORS (
SOURCE HIERARCHY (
source (
select
id as node_id,
parentid as parent_id,
unit
from Organization
)
)
START WHERE node_id = 23
)
Here is the output: "CEO > Marketing Department > Products Division B > Channel Department"
Display Child Nodes of a Given Hierarchy Node using SQL
Let's this time, display the child nodes of a given node in the hierarchy using SQLScript hierarchy functions on our sample HANA database table.
Assume business requirement needs to see the child nodes of "Marketing Department" which has the node ID value as 5
This time we will use SQL HIERARCHY_DESCENDANTS functions.
I want to create a string output variable with indents according to the node level compared to top most parent node "Marketing Department"
Simple SQL query is as follows
select *
from HIERARCHY_DESCENDANTS (
SOURCE HIERARCHY (
source (
select
id as node_id,
parentid as parent_id,
unit
from Organization
)
)
START WHERE node_id = 5
)
But for inserting indents showing visually the hierarchy between node, SQLScript programmers can modify the above SELECT statement as follows:
select
string_agg(
LPAD('', HIERARCHY_DISTANCE, CHAR(9)) || unit || BINTOSTR(HEXTOBIN('0D0A')),
'' order by hierarchy_rank
) as topdown
from HIERARCHY_DESCENDANTS (
SOURCE HIERARCHY (
source (
select
id as node_id,
parentid as parent_id,
unit
from Organization
)
)
START WHERE node_id = 5
)
I added TAB character which can be managed by using CHAR(9)
The carriage return character can be managed by using BINTOSTR(HEXTOBIN('0D0A'))
The output of the above SQLScript code execution on tutorial sample hierarchy data will be as follows
Display Sibling Nodes on HANA Hierarchy Table using SQLScript
SQL programmers can use another Hierarchy function named HIERARCHY_SIBLINGS function to query a given hierarchical structure for the nodes sharing the same level or for siblings of a given parent-child node.
For example, if we want to get the same level nodes with "Marketing Department" as marked in below diagram, HANA database developers can use HIERARCHY_SIBLINGS function and get the desired results easily.
The SQLScript query that will be used by HANA database programmer is as follows
select
node_id, parent_id, unit
from HIERARCHY_SIBLINGS (
SOURCE HIERARCHY (
source (
select
id as node_id,
parentid as parent_id,
unit
from Organization
)
)
START WHERE node_id = 5
)
And the output will be just as we wanted to have:
For more on Hierarchy function SQLScript database developers can refer to official documentation
In the reference documentation, SQL programmers developing on SAP HANA database will see two more hierarchy functions namely HIERARCHY_LEVELED function and HIERARCHY_COMPOSITE_ID function.
