Support Questions
Find answers, ask questions, and share your expertise

Sqoop Export to Table with an Auto Increment Column

Highlighted

Sqoop Export to Table with an Auto Increment Column

Hi All

Requirement

1.Export data from hdfs to SQL Server table

2.One of the column in the target table is an auto increment column

I have tried the following ways

1.Say the target table have 10 business columns+1 auto increment column. HDFS will have 10 business column.- Didn't work .SQL server interpreted my first column as auto increment ID and had thrown a parsing exception

a. 
Can't parse input data: '<the first business column>' at
<SQLSERVER_TABLE>.__loadFromFields(<SQLSERVER_TABLE>.java:711) at
<SQLSERVER_TABLE>.parse(<SQLSERVER_TABLE>.java:609) at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89) ...
10 more Caused by: java.lang.NumberFormatException: For input string:
"col1" at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at 

2.Added null/1 as first column(Now I have total 11 column in hdfs 10 business col+null/1 column).

java.sql.BatchUpdateException: Cannot insert explicit value for
identity column in table '<SQLSERVER_TABLE>' when IDENTITY_INSERT is set
to OFF.

3.I have also tried the staging table option in sqoop export .To see if it helps in auto increment Id creation.

Thanks In Advance

Aparna

1 REPLY 1

Re: Sqoop Export to Table with an Auto Increment Column

Contributor

@aparna aravind From the error message it seems IDENTITY_INSERT is OFF for the target table. Can you set IDENTITY_INSERT to ON for the target table as shown below before executing sqoop job?

SET IDENTITY_INSERT <target table> ON