Sqoop Errors

Sqoop Errors

You may face various errors while using Sqoop to import & export data. Some of the errors are listed below with their solution.

Error 1: Import/ Export may fail due to older jackson related jar files.

Exception in thread "main" java.lang.NoSuchMethodError: com.fasterxml.jackson.databind.ObjectMapper.readerFor(Ljava/lang/Class;)Lcom/fasterxml/jackson/databind/ObjectReader;

Solution: Change the Jackson jar file from sqoop lib to higher version (2.9 or above)


Error 2: Import/Export may fail due to older commons-io related jar file.

java.lang.NoSuchMethodError: org.apache.commons.io.FileUtils.isSymlink(Ljava/io/File;)Z
2018-07-25 23:51:27,740 ERROR [main] tool.ImportTool (ImportTool.java:run(634)) - Import failed: java.io.IOException: java.util.concurrent.ExecutionException: java.lang.NoSuchMethodError: org.apache.commons.io.FileUtils.isSymlink(Ljava/io/File;)Z

Solution: Replace commons-io jar file to 2.5 or higer version


Error 3: Import/Export may fail with class not found exception.

java.lang.Exception: java.lang.RuntimeException: java.lang.ClassNotFoundException: Class table_name not found

Solution: Run command from Sqoop home directory and with --bindir as described in Sqoop Import Tutorial & Sqoop Export Tutorial


Error 4: Import/Export may fail "No primary key could be found" if there is no primary key on table and --split-by or -m 1 is not specified.

2018-07-26 22:52:47,736 ERROR [main] tool.ImportTool (ImportTool.java:run(638)) - Import failed: No primary key could be found for table order_items_wp. Please specify one with --split-by or perform a sequential import with '-m 1'.

Solution: create primary key on the table or use --split-by or -m 1


Error 5: Import/Export may fail with "Mixed update/insert is not supported against" error. This is weird as this will work when you will remove new MySql driver from the command

2018-07-30 23:15:14,188 ERROR [main] tool.ExportTool (ExportTool.java:run(105)) - Error during export: 
Mixed update/insert is not supported against the target database yet

Solution: Remove new driver from command --driver com.mysql.cj.jdbc.Driver


Error 6: Sqoop Import with query will fail if $CONDITIONS' is not specified in Where condition

2018-07-30 23:46:51,544 ERROR [main] tool.ImportTool (ImportTool.java:run(634)) - Import failed: java.io.IOException: Query [select order_status,order_id from orders] must contain '$CONDITIONS' in WHERE clause.

Solution: Specify $CONDITIONS in the where clause, for example

 --query 'select order_status,order_id from orders WHERE $CONDITIONS'

Error 7: Sqoop Hive import will fail if column name is mentioned different in --map-column-hive than RDBMS table name then it will fail with below error.

ERROR [main] tool.ImportTool (ImportTool.java:run(630)) - Import failed: No column by the name order_ts_string found while importing data

Solution: Change the hive table column name in "--map-column-hive" attribute to match with RDBMS table column name.


Error 8: Sqoop import may fail with below even if there is no timestamp column

2018-08-29 12:57:42,987 ERROR [main] sqoop.Sqoop (Sqoop.java:runSqoop(185)) - Got exception running Sqoop: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'. The value 'convertToNull' is not acceptable.

Solution: Change connect URL to add "zeroDateTimeBehavior=CONVERT_TO_NULL" or "zeroDateTimeBehavior=ROUND" like below

--connect jdbc:mysql://localhost:3306/retail_db?zeroDateTimeBehavior=CONVERT_TO_NULL

Error 9: Sqoop import will fail if numerical column is not specified as the first column in the custom column list.

java.lang.Exception: java.lang.NumberFormatException: For input string: "CLOSED"

Solution: Solution for it is to specify split-by clause with the numerical column.

 --split-by order_id_numeric_column