title: Hive: How to do a SELECT query to output a unique primary key using HiveQL?
body: <p>I have the following schema dataset which i want to transform into a table that can be exported to SQL.  I am using <code>HIVE</code>. Input as follows</p>
  
  <pre><code>call_id,stat1,stat2,stat3
  1,a,b,c,
  2,x,y,z,
  3,d,e,f,
  1,j,k,l,
  </code></pre>
  
  <p>The output table needs to have <code>call_id</code> as its primary key so it needs to be unique. The output schema should be</p>
  
  <pre><code>call_id,stat2,stat3,
  1,b,c, or (1,k,l)
  2,y,z,
  3,e,f,
  </code></pre>
  
  <p>The problem is that when i use the keyword <code>DISTINCT</code> in the <code>HIVE</code> query, the <code>DISTINCT</code> applies to the all the colums combined. I want to apply the DISTINCT operation only to the <code>call_id</code>. Something on the lines of</p>
  
  <pre><code>SELECT DISTINCT(call_id), stat2,stat3 from intable;
  </code></pre>
  
  <p>However this is not valid in <code>HIVE</code>(I am not well-versed in SQL either).</p>
  
  <p>The only legal query seems to be </p>
  
  <pre><code>SELECT DISTINCT call_id, stat2,stat3 from intable; 
  </code></pre>
  
  <p>But this returns multiple rows with same <code>call_id</code> as the other columns are different and the row on the whole is distinct.</p>
  
  <p>NOTE: There is no arithmetic relation between a,b,c,x,y,z, etc. So any trick of averaging or summing is not viable.</p>
  
  <p>Any ideas how i can do this?</p>