title: Efficient HIVE query for multi-level join
body: <p>I am converting a T-SQL UDF to HIVE HQL.  The UDF uses a recursive CTE to flatten a hierarchy in the source table by self-joining on a "parent" column.  To get rid of the recursion, I did multiple left joins to create the flattened hierarchy, i.e.:</p>
  
  <pre><code>select a.description, b.description, c.description, etc
  from sourcetable a
  left join sourcetable b on a.parent=b.id
  left join sourcetable c on b.parent=c.id
  etc.
  </code></pre>
  
  <p>The end result is that you have all of the levels of the hierarchy laid out by column, for example with familiar data:</p>
  
  <pre><code>Phoenix    Arizona       UnitedStates
  WashDC     UnitedStates  NULL
  Trenton    NewJersey     UnitedStates
  </code></pre>
  
  <p>Notice how the hierarchy can be "ragged."</p>
  
  <p>There's no filtering in this use case, we're processing all records.</p>
  
  <p>I have <strong>TEN LEVELS</strong> of hierarchy to deal with, and it runs fairly slow.  The original CTE ran in less than a minute, whereas the new query in hive takes about 9 minutes.  10-node cluster, 1.5M records, about 1.5GB data.  We only have mapreduce configured.</p>
  
  <p>What is the best way to optimize for this use case?  I don't think partitioning would help because we're not filtering the data.  Would bucketing help?  Should I bucket on the record ID, or on the parent ID column?</p>
  
  <p>Many thanks,
  Richard</p>