I have Hierarchy table have Organization level Parent Child relationships. and other table has account balance for the lowest level child in hierarchy table. I need to find all levels of Hierarchy starting from top child to lowest child. All top parent_node have top end parent as "****" . Please suggest hive query to solve this problem.
Input Table:
Hierarchy Table:
+---------------+----------------+
|parent_node_id | child_node_id |
+---------------+----------------+
| C1 | C11 |
+---------------+----------------+
| C11 | C12 |
+---------------+----------------+
| C12 | 123 |
+---------------+----------------+
| C12 | 456 |
+---------------+----------------+
| P1 | C1 |
+---------------+----------------+
| P1 | C2 |
+---------------+----------------+
| P2 | C3 |
+---------------+----------------+
Account Balance:
+-------+----------+
| node | balance |
+-------+----------+
| 123 | 100 |
+-------+----------+
| 456 | 150 |
+-------+----------+
| C2 | 400 |
+-------+----------+
| C3 | 200 |
+-------+----------+
Output Data:
Flattened Hierarchy:
+---------------+-------------+---------+----------+----------+
|Parent_node_id | Level 1 | Level 2 | Level 2 | Level3 |
+---------------+-------------+---------+----------+----------+
| P1 | C1 | C11 | C12 | 123 |
+---------------+-------------+---------+----------+----------+
| P1 | C1 | C11 | C12 | 456 |
+---------------+-------------+---------+----------+----------+
| P1 | C2 | NULL | NULL | NULL |
+---------------+-------------+---------+----------+----------+
| P2 | C3 | NULL | NULL | NULL |
+---------------+-------------+---------+----------+----------+
Balance from lowest child to top
+-------+----------+
| node | balance |
+-------+----------+
| 123 | 100 |
+-------+----------+
| 456 | 150 |
+-------+----------+
| C12 | 250 |
+-------+----------+
| C11 | 250 |
+-------+----------+
| C1 | 250 |
+-------+----------+
| P1 | 650 |
+-------+----------+
| C2 | 200 |
+-------+----------+
| P2 | 200 |
+-------+----------+
