Hierarchy in Hive

@JediBrooker Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive experts @ggangadharan @Shmoo @james_jones who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Hi @JediBrooker ,

The query that you could use should be something like this:

   WITH org_hierarchy_anchor AS (
     SELECT 
       sup.organisation_id AS sup_id,
       sup.organisation_name AS sup_name,
       sub.organisation_id AS sub_id,
       sub.organisation_name AS sub_name,
       CAST(sub.organisation_name AS STRING) AS hierarchy_path
     FROM organisation sup
     JOIN relationship r ON sup.organisation_id = r.relationship_id
     JOIN organisation sub ON r.relationship_orgid = sub.organisation_id
   ),
   org_hierarchy_recursive AS (
     SELECT 
       sup_id,
       sup_name,
       sub_id,
       sub_name,
       hierarchy_path
     FROM org_hierarchy_anchor
     UNION ALL
     SELECT 
       oh.sup_id,
       oh.sup_name,
       s.organisation_id AS sub_id,
       s.organisation_name AS sub_name,
       CONCAT(oh.hierarchy_path, '->', s.organisation_name) AS hierarchy_path
     FROM org_hierarchy_recursive oh
     JOIN relationship r ON oh.sub_id = r.relationship_id
     JOIN organisation s ON r.relationship_orgid = s.organisation_id
     WHERE INSTR(oh.hierarchy_path, CAST(s.organisation_name AS STRING)) = 0
   )
   SELECT sup_id, sup_name, sub_id, sub_name, hierarchy_path
   FROM org_hierarchy_recursive
   ORDER BY sup_id, sub_id;

Please, check the column names and table names, I just used what you have provided here.

Let me know if works.

Best,

Cristian Barrueco 

To provide the exact HQL query, Please share the following :

• DDL for both the tables
• Sample records from each table 
• The expected output based on the sample data. 

The above information will help to understand the problem statement better and validate the solution.