SQOOP - Import data from SQL Server

Preamble

This article describe how import data from Microsoft SQL Server database.

Be sure to have enought rights to access database.

Download data from table

Sqoop import

#Driver
driver=sqlserver					
#IP or DNS
ip=127.0.0.1
#Port
port=1433
#User
username=myuser
#Password
password=mypwd
#Database
database=mydb
#Table
table=mytable
#Folder in HDFS
hdfsdest=/user/hdfs/$table

#Importation of tables
sqoop import --connect "jdbc:$driver://$ip:$port;DatabaseName=$database"	 --username $username --password $password \
--target-dir $hdfsdest \
--num-mappers 1 \
--table $table

Options

Option	Operation
--verbose	Show more logs
--delete-target-dir	Delete HDFS directory before import
--query "SELECT * FROM $table WHERE \$CONDITIONS"	Import data from a request instead of a table. The option --table must be removed from command line
--num-mappers n (replace n by a number)	Cut the data flow in n data flow, n is the number of datanodes. This option must be used with option --split-by 'column' witch specify the column to split.
--as-textfile	Import data in text mode
--null-non-string '\\N'	Change null values of non-string type in '\\N'. Used for Impala and Hive
--null-string '\\N'	Change null values of string type in '\\N'. Used for Impala and Hive
--hive-import	Create a Hive/Impala table with imported data
--hive-table $table	Specify the name of the Hive/Impala table
--hive-overwrite	Overwrite the data of the table. If this option is not present data are concatenate
--hive-database db (change db by your database)	Specify the name of the Hive/Impala database
--hive-drop-import-delims	Drops \n, \r, and \01 from string fields when importing to Hive