Hive - Rolling up the amount balance from leaf nod

Question

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     |
+-------+----------+