Error in Sqoop - with SQL table names having '-' (hyphens)

[#1]

Hello

I am trying to import tables from MSSQL server and tables names can have special character in them (e.g. -)

e.g. table-name 

Sqoop version: sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.15.0

Here is the command.

            sqoop import \
            --connect 'jdbc:sqlserver://TSTSQL; databaseName=dbname' \
            --username sqoop --password **** \
            --driver com.microsoft.sqlserver.jdbc.SQLServerDriver \
            --table test-sql_table_name \
            --hive-import --hive-overwrite \
            --delete-target-dir \
            --hive-table temp.test_sql_table_name \
            --hive-drop-import-delims --fields-terminated-by '|' \
            --null-string '\\N' \
            --num-mappers 1

Error:

18/11/30 14:50:37 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:297)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:777)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:786)
        at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:289)
        at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:260)
        at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:246)
        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:327)
        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1858)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1657)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:494)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:252)
18/11/30 14:50:37 ERROR tool.ImportTool: Import failed: java.io.IOException: No columns to generate for ClassWriter
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1663)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:494)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:621)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

There is a patch available for this - https://issues.apache.org/jira/browse/SQOOP-521

But, it looks like Cloudera's Sqoop1 doesn't have this patch applied? Can someone help? Thanks.

BTW - escaping the table name within [] works, but data is not imported because of below error.

FAILED: SemanticException Line 2:17 Invalid path ''hdfs://nameservice1/user/<userid>/[test-table_name]'': No files matching path hdfs://nameservice1/user/<userid>/%5Btest-table_name%5D

[#2 · Accepted solution]

Hi,

You can use double quotes or brackets for the table name that you want to import.

Coming to the HDFS error, you can change the directory name by using --target-dir "<any other name>"

Eventually all the data is gonna go into the Hive table and the location of this table would be "/user/hive/warehouse/database/tablename"

This target dir is just a temporary path and finally it will be moved to hive path, so to overcome this error you can give any path in target-dir which should be in correct format.

Regards

Nitish

[#3]

Tried Double quotes - same error.

--target-dir should work as a workaround.

Does that mean the patch mentioned is not part of Sqoop1 in CDH 5.15.x ?

[#4]

Hi,

I think that after apply the patch there is no need to give the tablename in []. As per the comment what you can do is to put the table name in quotes or []. But after getting this patch applied you don't have to put the tablename in quotes or brackets.

The reason why i am saying this is because you are on 1.4.6 version and if this patch was already in the Sqoop then you wouldn't have faced this error.

So looks like you need to apply patch on CDH version.

Hope this helps.

Regards

Nitish

[#5]

Hi

I understand that I can apply the patch and it could solve the issue.

My question is, doesn't Cloudera apply these patches for the tool that comes with CDH? My understanding was that even though version number is old, Cloudera backports the security and other features/patches that do not break backward compatibility.

[#6]

It does though but if it has mentioned that in your CDH version you can apply this patch on top of it to make the job run fine then you need to apply the patch on it owns.

It is resolved means there is a patch that has been created and on these CDH versions it has to be applied.

Hope this info helps.

Regards

Nitish