title: Using Sqoop to Migrate MS SQL Server tables into Hive
body: <p>I need to create a Hive that needs to join several tables who are located in SQL Server. </p>
  
  <p>What I have done:</p>
  
  <p>I have no problem using Sqoop to move the tables/queryresults into HDFS. And then, I am creating an external table in Hive to point the table to the HDFS folder. Also, to keep the table info up-to-date, I need to put something on crontab that will delete the HDFS content and call the same sqoop command again. </p>
  
  <p>The solution above works but it is really a PITA to maintain. </p>
  
  <p>I have to write a Hive create external table query which is like a one time work, I know, but you need to write down every columns </p>
  
  <p>I saw a post <a href="http://grokbase.com/t/sqoop/user/127hqcgc0k/import-sqlserver-to-hive" rel="nofollow">here</a>, where it seems like SQOOP can move data from SQL server into Hive directly without caring about the details of the columns. </p>
  
  <p>I tried this:</p>
  
  <pre><code>sqoop import --driver 'net.sourceforge.jtds.jdbc.Driver' 
  --connect 'jdbc:jtds:sqlserver:/ip:1433/db;user=user;password=password' 
  --direct 
  --table "[db\$Shipment Cross Reference]" 
  --hive-import 
  --direct 
  --hive-overwrite 
  --create-hive-table 
  --hive-table shipment_cross_reference 
  --fields-terminated-by '\001' 
  --lines-terminated-by '\n' -m 1 
  </code></pre>
  
  <p>However, it errored out:</p>
  
  <pre><code>14/03/12 19:19:31 INFO mapred.JobClient: Job complete: job_201403051725_0059
  14/03/12 19:19:31 INFO mapred.JobClient: Counters: 6
  14/03/12 19:19:31 INFO mapred.JobClient:   Job Counters 
  14/03/12 19:19:31 INFO mapred.JobClient:     Failed map tasks=1
  14/03/12 19:19:31 INFO mapred.JobClient:     Launched map tasks=4
  14/03/12 19:19:31 INFO mapred.JobClient:     Total time spent by all maps in occupied slots (ms)=33406
  14/03/12 19:19:31 INFO mapred.JobClient:     Total time spent by all reduces in occupied slots (ms)=0
  14/03/12 19:19:31 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
  14/03/12 19:19:31 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
  14/03/12 19:19:31 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
  14/03/12 19:19:31 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 355.1399 seconds (0 bytes/sec)
  14/03/12 19:19:31 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead
  14/03/12 19:19:31 INFO mapreduce.ImportJobBase: Retrieved 0 records.
  14/03/12 19:19:31 ERROR tool.ImportTool: Error during import: Import job failed!
  </code></pre>
  
  <p>Can anyone tell me will it be possible to use one SQOOP to move from SQL server to Hive in one go? of write will be awesome, or partition by date...etc. </p>
  
  <p>Or what does the error mean?
  Thanks!</p>