How to support column names with spaces using sqoop import?

Question

We have an MSSQL DB set-up with column names of "Column 0" and "Column 1": note the space.

If I run the following command, it errors:

sqoop import --driver net.sourceforge.jtds.jdbc.Driver --connect jdbc:jtds:sqlserver://somemssqldb.com/OurDB --table dbo.OurTableName --username username --password ourPassword --columns "Column 0" --target-dir s3://our-s3-bucket/9/data/1262/141893327230246 -m 1

Stack trace reports:

Error: java.io.IOException: SQLException in nextKeyValue Caused by: java.sql.SQLException: Incorrect syntax near '0'.

If we remove the column name spaces in the DB as well as in the command, it works.

How can we get it to support using spaces in the --columns argument? The only way we are successfully able to do so if by using --query specifically and escape.

For example we have to write a query like this:

SELECT t.[Column 1]

Answer 1

From the name "sqoop import" it sounds like the tool is only used for reading?

Perhaps you can try confusing it by renaming your table, and then creating a view with the same name as the table. In the view you can alias your columns without spaces.

EXEC SP_RENAME SourceTable, SourceTable_

CREATE VIEW SourceTable
AS

SELECT [Column 0] as Column0
FROM SourceTable_

The you can write your query like this...

SELECT t.Column0
FROM SourceTable t

At best this is a last resort workaround, and it might not work if the application tries to update the view.

Worth a shot.

Answer 2

I have just faced the same issue as you yesterday (input table on a SQLserver, Sqoop version 1.4.5) and the only way I found also to solve it was using the '--query' statement.

However, I had no need to "escape" the column name and I used the '*' wildcard to map all the columns (which is the behaviour that I want: I just need to replicate the whole table from SQLserver). Advantage of using the wildcard: it is generic for every table so I can easily do a script to replicate lot of tables.

As an example, here is the full command I use:

sqoop import --connect "jdbc:sqlserver://$sqlServer:1433; database=$origDatabase; username=$myUser; password=$myPassword" 
--hive-import --driver com.microsoft.sqlserver.jdbc.SQLServerDriver
 --hive-database $hiveDatabase --fields-terminated-by '\t' 
--null-string '' --null-non-string '' -m 1 --outdir $dirJavaGeneratedCode
 --query "select a.* from $origTable a where \$CONDITIONS"
 --target-dir /tmp/$myTable --hive-table $myTable >> $logFile 2>> $logFile

Answer 3

Use --query option if the column names are having white space

example sqoop import query sqoop import --driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" --connect "jdbc:sqlserver://aaaaa;databaseName=bbbb" --username "userName" --password "PassWord" --target-dir "/target directory" --query 'Select * from dbo.PANHPE_PROD_LN_ID_BU_GRP WHERE $CONDITIONS' --fields-terminated-by \034 -m 1

in my case column Name : Manufacturing_Product_Global Business_Unit_Identifier