I have a scenario find the lowest level child nodes from hierarchy table having parent_node_id and child_node_id as below. Source table is in Hive and Impala database. Please suggest hive/impala query to find out the lowest level child nodes for each parent node in source table.
I tried using CTE recursive query in Impala but I guess it is not supported.
Thank you in Advance!
Source Table:
+-------------+--------------+
|child_node_id|parent_node_id|
+-------------+--------------+
| C1 | P1 |
+-------------+--------------+
| C2 | P2 |
+-------------+--------------+
| C11 | C1 |
+-------------+--------------+
| C12 | C11 |
+-------------+--------------+
| 123 | C12 |
+-------------+--------------+
Expected Output:
+-------------+--------------+
|parent_node |lowest_l_child|
+-------------+--------------+
| P1 | 123 |
+-------------+--------------+
| P2 | C2 |
+-------------+--------------+
| C1 | 123 |
+-------------+--------------+
| C11 | 123 |
+-------------+--------------+
| C12 | 123 |
+-------------+--------------+