CODEWITHSUNDEEP
mysqlhadoopsqoop
Rishi Deshmukh
Rishi Deshmukh

Reputation: 3

Sqoop import error

I am trying to import a MySQL table using sqoop import. Following is the error that i am getting.

Code:

[root@sandbox ~]# sqoop import \
   --connect "jdbc:mysql://localhost:3306/retail_db" \
   --username=root \
   --password=hadoop \
   --table departments \
   --as-avrodatafile \
  --target-dir=/user/root/departments

Error:

ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'. 
The value 'convertToNull' is not acceptable.

java.sql.SQLException: The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'.
The value 'convertToNull' is not acceptable.

Please help me out if anyone knows the reason/solution for this.

Thanks, Rishi

Upvotes: 0

Views: 6417

Answers (4)

Ishan Kumar
Ishan Kumar

Reputation: 1982

I got into the same issue after devops team upgraded the mysql to version 8. After that all the sqoop jobs started failing. I used below command and it worked for me.

sqoop import -Dmapreduce.job.queuename=queue --connect jdbc:mysql:/hostname:3306/db_name?zeroDateTimeBehavior=round -m 1 --driver com.mysql.jdbc.Driver --username user --password pwd --table dim_store  --fields-terminated-by "\t" --hive-import --hive-overwrite  --hive-table hivedb.hivetable;

Upvotes: 0

Ling M
Ling M

Reputation: 21

Works fine with "zeroDateTimeBehavior=CONVERT_TO_NULL"

Upvotes: 0

Gagandeep Singh
Gagandeep Singh

Reputation: 21

This how i tried and it worked for windows

sqoop import 
--connect "jdbc:mysql://localhost/employees?zeroDateTimeBehavior=CONVERT_TO_NULL" 
--table tablename
--username username
--password password
--m 1 
--target-dir /user/sqoop/tablename
--outdir java_files

zeroDateTimeBehavior=CONVERT_TO_NULL , the CONVERT_TO_NULL is driven from the error i got on run time . It works

Upvotes: 2

Peter Saul
Peter Saul

Reputation: 64

can you try using the following code below:

jdbc:mysql://localhost:3306/retail_db?zeroDateTimeBehavior=convertToNull

Upvotes: 0

Related Questions