Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

Explorer

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

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

Expert Contributor

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

5 REPLIES 5

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

Expert Contributor

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

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

Explorer

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 ?

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

Expert Contributor

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

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

Explorer

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.

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

Expert Contributor

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

Don't have an account?
Coming from Hortonworks? Activate your account here