Sqoop & SQL Server #7 - Re usability with Options Files

Using an Options File for Reusability

An Options File is a text file containing code to be called as part of a Sqoop job. They are useful when using repeat code often. You can share them between developers and the interact easily with the rest of the Sqoop command you're running.

To create an Options File simply create a text file with the elements of Sqoop syntax you wish to repeatedly call. Functionality you need to be aware of includes;

• Each line identifies an option in the order that it appears otherwise on the command line
• You must specify a single option on multiple lines by using the back-slash character at the end of intermediate lines.
• Comments are supported when the line is prefixed with the hash character. Comments must be specified on a new line and may not be mixed with option text.
• All comments and empty lines are ignored.
• Unless options appear as quoted strings, any leading or trailing spaces are ignored.
• Quoted strings if used must not extend beyond the line on which they are specified.
• Option files can be specified anywhere in the command line as long as the options within them follow the otherwise prescribed rules of options ordering.

A simple example,

import

--connect

jdbc:sqlserver://10.88.12.0\\SQL2014

--database Utility

--username ClouderaAccess

--password GiveMeData

An example with comments,

#

# Options File for Sqoop Import

#

# Command

import

# Connection String

jdbc:sqlserver://10.88.12.0\\SQL2014

--database Utility

# Credentials

--username ClouderaAccess

--password GiveMeData

# End

Using an Options File

To call the file use the command --options-file plus any other arguments relevant to the file being called, in this case the table to be imported.  We'll use the Options File defined in the examples above.

sqoop --options-file /users/cloudera/sqlserver/optionsfiles/StandardImportConnection.txt --table Customers

And this is equivalent to,

sqoop import --connect "jdbc:sqlserver://10.88.12.0\\SQL2014;database=Utility;username=ClouderaAccess;password=GiveMeData" --table Customers