title: Creation of pipe-delimited hive table - duplicate ids
body: <p>I'm trying to create a pipe-delimited hive table using these commands:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS tableA (
  id                      string,
  col1                     double,
  col2                     double,
  col3                     double,
  col4                     double,
  col5                     double,
  col6                     double,
  col7                     double
  
  )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
  tblproperties ("skip.header.line.count"="1");
  
  
  INSERT INTO TABLE TABLEA
  select a.id
         b.col1,
         b.col2,
         b.col3,
         b.col4,
         b.col5,
         b.col6,
         b.col7
  FROM   customerTable as a  left join factTable as b  on a.id   = b.id; 
  </code></pre>
  
  <p>I get duplicate records in the new table, tableA. I checked using </p>
  
  <pre><code>select count(distinct id) as cnt from tableA ;
  </code></pre>
  
  <p>Whereas if I create a normal hive table like this, I don't get any duplicate ids:</p>
  
  <pre><code>Create table if not exists tableA as 
  select a.id
             b.col1,
             b.col2,
             b.col3,
             b.col4,
             b.col5,
             b.col6,
             b.col7
      FROM   customerTable as a  left join factTable as b  on a.id   = b.id; 
  </code></pre>
  
  <p>The table created is in order of 80 Million rows but the difference in the number of records ( duplicate records) is only 58 records.</p>
  
  <p>Not sure whats going on. I guess the problem is with how I'm creating the pipe-delimited hive table. Any help would be appreciated.</p>