title: Hive: How to create multiple tables from multiple files or count entries per file
body: <p>My goal is to combine entries from multiple files into 1 table but am having some trouble getting there.</p>
  
  <p>So I understand that you can add all entries into a table by doing:</p>
  
  <pre><code>CREATE EXTERNAL TABLE tablename
  (
      teams STRING
  )
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  LOCATION 'hdfs:///hive-data';
  </code></pre>
  
  <p>Where each text file's data looks something like:</p>
  
  <pre><code>student#A18645
  student#V86541
  student#X78543
  </code></pre>
  
  <p>However, with the code above, this combines all the data from N number of files together in my directory which makes it hard to combine the data. What I want is to have the first entries from all files be concatenated together into a single string and entered into a new table and so forth.
  I have tried to number each entry using ROW_NUMBER() but that does not give the number of their place in the file but rather in the table itself. </p>
  
  <p>Therefore, is there a way I can create a table per file, number the entries, and join all the tables together so that in the end, I can get a table that looks like:</p>
  
  <pre><code>number           students
  1                student#A18645,student#D94655,...student#S45892
  2                student#V86541,student#D45645,...student#F46444
  3                student#X78543,student#T78722,...student#M99846
  </code></pre>
  
  <p>Or rather, a way to number each entry as the line number of the file it came from so I can do an inner join on my table.</p>
  
  <p>Note: the number of files can vary so I do not have a set number of files to loop through</p>