title: Hive: Repeat SELECT for each Row
body: <p>I'm not entirely sure if this is a good question title, but I'll explain as best I can in the body.</p>
  
  <p>I'm working with the Million Songs Dataset, from <a href="http://labrosa.ee.columbia.edu/millionsong/" rel="nofollow">http://labrosa.ee.columbia.edu/millionsong/</a></p>
  
  <p>My ultimate goal is to create something along the lines of "similar song", in which I take a song and get similar songs based on year, duration, etc.</p>
  
  <p>I have the data in a hive table set up as </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS songs(genre String, artist String, danceability Double, duration Double, loudness Double, similarArtists String, hotness Double, title String) PARTITIONED BY(year String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
  </code></pre>
  
  <p>My problem comes because Hive does not support inequalities in JOIN.</p>
  
  <p>Ideally I'd like to have a query like</p>
  
  <pre><code>SELECT songs.artist, songs.title, t2.title FROM songs JOIN songs as t2 ON songs.year &gt; t2.year -5 AND songs.year &lt; t2.year +5;
  </code></pre>
  
  <p>However this is not possible currently. I'm currently stumped as to the best way to attempt my similar song query. It's possible to do it in a SELECT statment</p>
  
  <pre><code>SELECT title FROM songs WHERE year &lt; 2000 + 5 AND year &gt; 2000 - 5;
  </code></pre>
  
  <p>But I'm unsure how to run this on every row, while taking the apropriate values instead of hard coding 2005 I.E.:</p>
  
  <pre><code>SELECT title FROM songs WHERE year &lt; song.year + 5 AND year &gt; song.year-5;
  </code></pre>
  
  <p>Has anyone run into a situation like this or have any overall ideas I could try?</p>