Hi,

We are using Sqoop 1.3.0-cdh3u4 with Hadoop version 1.0.3.

We encountered the following error when we try to export HDFS file into
MSSQL 2005 (partially):

12/12/04 16:44:13 INFO mapred.JobClient: Task Id :
attempt_201212041541_0014_m_000000_2, Status : FAILED

java.io.IOException: com.microsoft.sqlserver.jdbc.SQLServerException:
Incorrect syntax near ','.

        at
com.cloudera.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:195)

        at
org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:651)

        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:766)

        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)

        at org.apache.hadoop.mapred.Child$4.run(Child.java:255)

        at java.security.AccessController.doPrivileged(Native Method)

        at javax.security.auth.Subject.doAs(Subject.java:416)

        at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1121)

        at org.apache.hadoop.mapred.Child.main(Child.java:249)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect
syntax near ','.

        at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)

        at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)

        at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)

        at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)

        at
com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)

        at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)

        at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)

        at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)

        at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)

        at
com.cloudera.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:234)

The HDFS file that we want to export was imported using sqoop from SQL 2005
before and uses ‘|’ as field delimiter, and there are commas (‘,’) in a
field of a line in the file.

The commands I submitted is (generalized with capital letters):

$ sqoop export -D sqoop.export.records.per.statement=75 -D
sqoop.export.statements.per.transaction=75 --connect
"jdbc:sqlserver://SERVER-NAME:1433;username=USER_NAME;password=PASSWD;database=DB_NAME"
--table TABLE_NAME -m 1 --input-fields-terminated-by '|' --export-dir
/EXPORT/FROM/DIRECTORY

I’ve adjusted values of sqoop.export.records.per.statement &
sqoop.export.statements.per.transaction, but that didn’t help.

It will be greatly appreciated if you can offer some help. Thanks.

Ivan

HiChun-fan,
would you mind sharing with us entire Sqoop log generated with parameter --verbose? Are you using build-in Microsoft SQL Connector or connector provided by Microsoft?

Jarcec

On Tue, Dec 04, 2012 at 05:51:31PM +0800, Chun-fan Ivan Liao wrote:
> Hi,
>
>
>
> We are using Sqoop 1.3.0-cdh3u4 with Hadoop version 1.0.3.
>
>
>
> We encountered the following error when we try to export HDFS file into
> MSSQL 2005 (partially):
>
>
>
> 12/12/04 16:44:13 INFO mapred.JobClient: Task Id :
> attempt_201212041541_0014_m_000000_2, Status : FAILED
>
> java.io.IOException: com.microsoft.sqlserver.jdbc.SQLServerException:
> Incorrect syntax near ','.
>
>         at
> com.cloudera.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:195)
>
>         at
> org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:651)
>
>         at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:766)
>
>         at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
>
>         at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
>
>         at java.security.AccessController.doPrivileged(Native Method)
>
>         at javax.security.auth.Subject.doAs(Subject.java:416)
>
>         at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1121)
>
>         at org.apache.hadoop.mapred.Child.main(Child.java:249)
>
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect
> syntax near ','.
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
>
>         at
> com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
>
>         at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)
>
>         at
> com.cloudera.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:234)
>
>
>
> The HDFS file that we want to export was imported using sqoop from SQL 2005
> before and uses ‘|’ as field delimiter, and there are commas (‘,’) in a
> field of a line in the file.
>
>
>
> The commands I submitted is (generalized with capital letters):
>
> $ sqoop export -D sqoop.export.records.per.statement=75 -D
> sqoop.export.statements.per.transaction=75 --connect
> "jdbc:sqlserver://SERVER-NAME:1433;username=USER_NAME;password=PASSWD;database=DB_NAME"
> --table TABLE_NAME -m 1 --input-fields-terminated-by '|' --export-dir
> /EXPORT/FROM/DIRECTORY
>
>
>
> I’ve adjusted values of sqoop.export.records.per.statement &
> sqoop.export.statements.per.transaction, but that didn’t help.
>
>
>
> It will be greatly appreciated if you can offer some help. Thanks.
>
>
>
> Ivan

Thank you, Jarcec. I'm not sure which connector we use. I've downloaded
"Microsoft SQL Server Connector for Apache Hadoop" from
http://www.microsoft.com/en-us/download/details.aspx?id=27584, but I didn't
remember if we really used that. How to make sure?

And here is the verbose log:

==========12/12/05 12:08:57 DEBUG tool.BaseSqoopTool: Enabled debug logging.
12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Added factory
com.microsoft.sqoop.SqlServer.MSSQLServerManagerFactory specified by
/usr/local/sqoop/conf/managers.d/mssqoop-sqlserver
12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.microsoft.sqoop.SqlServer.MSSQLServerManagerFactory
12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.microsoft.sqoop.SqlServer.MSSQLServerManagerFactory
12/12/05 12:08:57 INFO SqlServer.MSSQLServerManagerFactory: Using
Microsoft's SQL Server - Hadoop Connector
12/12/05 12:08:57 INFO manager.SqlManager: Using default fetchSize of 1000
12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Instantiated ConnManager
com.microsoft.sqoop.SqlServer.MSSQLServerManager@736921fd
12/12/05 12:08:57 INFO tool.CodeGenTool: Beginning code generation
12/12/05 12:08:57 DEBUG manager.SqlManager: No connection paramenters
specified. Using regular API for making connection.
12/12/05 12:08:57 DEBUG manager.SqlManager: Using fetchSize for next query:
1000
12/12/05 12:08:57 INFO manager.SqlManager: Executing SQL statement: SELECT
TOP 1 * FROM [member_main]
12/12/05 12:08:57 DEBUG manager.SqlManager: Using fetchSize for next query:
1000
12/12/05 12:08:57 INFO manager.SqlManager: Executing SQL statement: SELECT
TOP 1 * FROM [member_main]
12/12/05 12:08:57 DEBUG orm.ClassWriter: selected columns:
12/12/05 12:08:57 DEBUG orm.ClassWriter:   MemberId
12/12/05 12:08:57 DEBUG orm.ClassWriter:   USERNAME
12/12/05 12:08:57 DEBUG orm.ClassWriter:   FirstName
12/12/05 12:08:57 DEBUG orm.ClassWriter:   LastName
12/12/05 12:08:57 DEBUG orm.ClassWriter:   EmailAddress
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Password
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Password_E5
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Birthday
12/12/05 12:08:57 DEBUG orm.ClassWriter:   CompanyName
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Gender
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Age
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Education
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Country
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Title
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Phone1
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Phone2
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Fax
12/12/05 12:08:57 DEBUG orm.ClassWriter:   State
12/12/05 12:08:57 DEBUG orm.ClassWriter:   City
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Address1
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Address2
12/12/05 12:08:57 DEBUG orm.ClassWriter:   ZipCode
12/12/05 12:08:57 DEBUG orm.ClassWriter:   VATID
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Language
12/12/05 12:08:57 DEBUG orm.ClassWriter:   rec_letter
12/12/05 12:08:57 DEBUG orm.ClassWriter:   rec_promotion
12/12/05 12:08:57 DEBUG orm.ClassWriter:   rec_type
12/12/05 12:08:57 DEBUG orm.ClassWriter:   JointSource
12/12/05 12:08:57 DEBUG orm.ClassWriter:   CustomerLevel
12/12/05 12:08:57 DEBUG orm.ClassWriter:   UpdateDate
12/12/05 12:08:57 DEBUG orm.ClassWriter:   CreateDate
12/12/05 12:08:57 DEBUG orm.ClassWriter:   FirstLoginDate
12/12/05 12:08:57 DEBUG orm.ClassWriter:   LastLoginDate
12/12/05 12:08:57 DEBUG orm.ClassWriter:   LastVisit
12/12/05 12:08:57 DEBUG orm.ClassWriter:   isValid
12/12/05 12:08:57 DEBUG orm.ClassWriter:   nJoint
12/12/05 12:08:57 DEBUG orm.ClassWriter:   Upd_SubDate
12/12/05 12:08:57 DEBUG orm.ClassWriter:   UnSub_Type
12/12/05 12:08:57 DEBUG orm.ClassWriter:   CreateDateFloat
12/12/05 12:08:57 DEBUG orm.ClassWriter: Writing source file:
/tmp/sqoop-hadoop/compile/14ce35e69f66546d9d0d41065fac0971/member_main.java
12/12/05 12:08:57 DEBUG orm.ClassWriter: Table name: member_main
12/12/05 12:08:57 DEBUG orm.ClassWriter: Columns: MemberId:4, USERNAME:12,
FirstName:-9, LastName:-9, EmailAddress:12, Password:12, Password_E5:12,
Birthday:93, CompanyName:-9, Gender:12, Age:5, Education:12, Country:5,
Title:-9, Phone1:12, Phone2:12, Fax:12, State:-9, City:-9, Address1:-9,
Address2:-9, ZipCode:12, VATID:12, Language:12, rec_letter:-7,
rec_promotion:-7, rec_type:5, JointSource:12, CustomerLevel:4,
UpdateDate:93, CreateDate:93, FirstLoginDate:93, LastLoginDate:93,
LastVisit:93, isValid:-7, nJoint:4, Upd_SubDate:93, UnSub_Type:4,
CreateDateFloat:8,
12/12/05 12:08:57 DEBUG orm.ClassWriter: sourceFilename is member_main.java
12/12/05 12:08:57 DEBUG orm.CompilationManager: Found existing
/tmp/sqoop-hadoop/compile/14ce35e69f66546d9d0d41065fac0971/
12/12/05 12:08:57 INFO orm.CompilationManager: HADOOP_HOME is
/usr/local/hadoop/libexec/..
12/12/05 12:08:57 DEBUG orm.CompilationManager: Adding source file:
/tmp/sqoop-hadoop/compile/14ce35e69f66546d9d0d41065fac0971/member_main.java
12/12/05 12:08:57 DEBUG orm.CompilationManager: Invoking javac with args:
12/12/05 12:08:57 DEBUG orm.CompilationManager:   -sourcepath
12/12/05 12:08:57 DEBUG orm.CompilationManager:
/tmp/sqoop-hadoop/compile/14ce35e69f66546d9d0d41065fac0971/
12/12/05 12:08:57 DEBUG orm.CompilationManager:   -d
12/12/05 12:08:57 DEBUG orm.CompilationManager:
/tmp/sqoop-hadoop/compile/14ce35e69f66546d9d0d41065fac0971/
12/12/05 12:08:57 DEBUG orm.CompilationManager:   -classpath
12/12/05 12:08:57 DEBUG orm.CompilationManager:
/usr/local/hadoop/libexec/../conf:/usr/lib/jvm/java-6-openjdk-amd64/lib/tools.jar:/usr/local/hadoop/libexec/..:/usr/local/hadoop/libexec/../hadoop-core-1.0.3.jar:/usr/local/hadoop/libexec/../lib/asm-3.2.jar:/usr/local/hadoop/libexec/../lib/aspectjrt-1.6.5.jar:/usr/local/hadoop/libexec/../lib/aspectjtools-1.6.5.jar:/usr/local/hadoop/libexec/../lib/commons-beanutils-1.7.0.jar:/usr/local/hadoop/libexec/../

Hi Chun-fan,
thank you very much for sharing the log with us. You are using Microsoft SQL Connector because you're downloaded manually from Microsoft web pages and you can also confirm that by following log lines:

> 12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Added factory
> com.microsoft.sqoop.SqlServer.MSSQLServerManagerFactory specified by
> /usr/local/sqoop/conf/managers.d/mssqoop-sqlserver
...
> 12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.microsoft.sqoop.SqlServer.MSSQLServerManager@736921fd

I'm not sure what is going wrong as it seems that the data were parsed correctly, but submitting the query to SQL server will fail. As a next step I would recommend turning Microsoft Connector off and using build-in one instead to see if the issue is specific to Sqoop or the Connector. You can do that by temporarily moving file /usr/local/sqoop/conf/managers.d/mssqoop-sqlserver somewhere else.

Jarcec

On Wed, Dec 05, 2012 at 12:25:24PM +0800, Chun-fan Ivan Liao wrote:
> Thank you, Jarcec. I'm not sure which connector we use. I've downloaded
> "Microsoft SQL Server Connector for Apache Hadoop" from
> http://www.microsoft.com/en-us/download/details.aspx?id=27584, but I didn't
> remember if we really used that. How to make sure?
>
> And here is the verbose log:
>
> ==========> 12/12/05 12:08:57 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Added factory
> com.microsoft.sqoop.SqlServer.MSSQLServerManagerFactory specified by
> /usr/local/sqoop/conf/managers.d/mssqoop-sqlserver
> 12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.microsoft.sqoop.SqlServer.MSSQLServerManagerFactory
> 12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.microsoft.sqoop.SqlServer.MSSQLServerManagerFactory
> 12/12/05 12:08:57 INFO SqlServer.MSSQLServerManagerFactory: Using
> Microsoft's SQL Server - Hadoop Connector
> 12/12/05 12:08:57 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/12/05 12:08:57 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.microsoft.sqoop.SqlServer.MSSQLServerManager@736921fd
> 12/12/05 12:08:57 INFO tool.CodeGenTool: Beginning code generation
> 12/12/05 12:08:57 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
> 12/12/05 12:08:57 DEBUG manager.SqlManager: Using fetchSize for next query:
> 1000
> 12/12/05 12:08:57 INFO manager.SqlManager: Executing SQL statement: SELECT
> TOP 1 * FROM [member_main]
> 12/12/05 12:08:57 DEBUG manager.SqlManager: Using fetchSize for next query:
> 1000
> 12/12/05 12:08:57 INFO manager.SqlManager: Executing SQL statement: SELECT
> TOP 1 * FROM [member_main]
> 12/12/05 12:08:57 DEBUG orm.ClassWriter: selected columns:
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   MemberId
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   USERNAME
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   FirstName
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   LastName
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   EmailAddress
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Password
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Password_E5
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Birthday
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   CompanyName
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Gender
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Age
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Education
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Country
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Title
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Phone1
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Phone2
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Fax
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   State
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   City
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Address1
> 12/12/05 12:08:57 DEBUG orm.ClassWriter:   Address2

Hi Jarek,

It actually worked! Thank you so much~! :D

However now we faced another problem. The former data we tried to export is
only test data, which row count is only 10. When we tried to export
production data back into SQL Server from HDFS file which was previously
imported using Sqoop from SQL server, different errors occurred. The row
count is about 400k, and only about 120k rows were exported. This time we
used "-m 5", and if using "-m 1", nothing will be exported. Verbose log is
in the bottom of this mail.

Is this has to do with that we used MS SQL connector to do previous import,
not the default one?

Also, should we specify any character encoding, e.g. utf-8 during
import/export process? There are characters of many different languages in
our original data in SQL Server, and I'm not sure what the encoding is
after imported into HDFS.

Thanks again, Jarek.

====================================12/12/05 19:55:27 DEBUG tool.BaseSqoopTool: Enabled debug logging.
12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/12/05 19:55:27 DEBUG manager.DefaultManagerFactory: Trying with scheme:
jdbc:sqlserver:
12/12/05 19:55:27 INFO manager.SqlManager: Using default fetchSize of 1000
12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Instantiated ConnManager
com.cloudera.sqoop.manager.SQLServerManager@6766afb3
12/12/05 19:55:27 INFO tool.CodeGenTool: Beginning code generation
12/12/05 19:55:27 DEBUG manager.SqlManager: No connection paramenters
specified. Using regular API for making connection.
12/12/05 19:55:27 DEBUG manager.SqlManager: Using fetchSize for next query:
1000
12/12/05 19:55:27 INFO manager.SqlManager: Executing SQL statement: SELECT
t.* FROM member_main AS t WHERE 1=0
12/12/05 19:55:27 DEBUG orm.ClassWriter: selected columns:
12/12/05 19:55:27 DEBUG orm.ClassWriter:   MemberId
12/12/05 19:55:27 DEBUG orm.ClassWriter:   USERNAME
12/12/05 19:55:27 DEBUG orm.ClassWriter:   FirstName
12/12/05 19:55:27 DEBUG orm.ClassWriter:   LastName
12/12/05 19:55:27 DEBUG orm.ClassWriter:   EmailAddress
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Password
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Password_E5
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Birthday
12/12/05 19:55:27 DEBUG orm.ClassWriter:   CompanyName
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Gender
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Age
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Education
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Country
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Title
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Phone1
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Phone2
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Fax
12/12/05 19:55:27 DEBUG orm.ClassWriter:   State
12/12/05 19:55:27 DEBUG orm.ClassWriter:   City
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Address1
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Address2
12/12/05 19:55:27 DEBUG orm.ClassWriter:   ZipCode
12/12/05 19:55:27 DEBUG orm.ClassWriter:   VATID
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Language
12/12/05 19:55:27 DEBUG orm.ClassWriter:   rec_letter
12/12/05 19:55:27 DEBUG orm.ClassWriter:   rec_promotion
12/12/05 19:55:27 DEBUG orm.ClassWriter:   rec_type
12/12/05 19:55:27 DEBUG orm.ClassWriter:   JointSource
12/12/05 19:55:27 DEBUG orm.ClassWriter:   CustomerLevel
12/12/05 19:55:27 DEBUG orm.ClassWriter:   UpdateDate
12/12/05 19:55:27 DEBUG orm.ClassWriter:   CreateDate
12/12/05 19:55:27 DEBUG orm.ClassWriter:   FirstLoginDate
12/12/05 19:55:27 DEBUG orm.ClassWriter:   LastLoginDate
12/12/05 19:55:27 DEBUG orm.ClassWriter:   LastVisit
12/12/05 19:55:27 DEBUG orm.ClassWriter:   isValid
12/12/05 19:55:27 DEBUG orm.ClassWriter:   nJoint
12/12/05 19:55:27 DEBUG orm.ClassWriter:   Upd_SubDate
12/12/05 19:55:27 DEBUG orm.ClassWriter:   UnSub_Type
12/12/05 19:55:27 DEBUG orm.ClassWriter:   CreateDateFloat
12/12/05 19:55:27 DEBUG orm.ClassWriter: Writing source file:
/tmp/sqoop-hadoop/compile/7131fa8fb957892b4af354982da9e57d/member_main.java
12/12/05 19:55:27 DEBUG orm.ClassWriter: Table name: member_main
12/12/05 19:55:27 DEBUG orm.ClassWriter: Columns: MemberId:4, USERNAME:12,
FirstName:-9, LastName:-9, EmailAddress:12, Password:12, Password_E5:12,
Birthday:93, CompanyName:-9, Gender:12, Age:5, Education:12, Country:5,
Title:-9, Phone1:12, Phone2:12, Fax:12, State:-9, City:-9, Address1:-9,
Address2:-9, ZipCode:12, VATID:12, Language:12, rec_letter:-7,
rec_promotion:-7, rec_type:5, JointSource:12, CustomerLevel:4,
UpdateDate:93, CreateDate:93, FirstLoginDate:93, LastLoginDate:93,
LastVisit:93, isValid:-7, nJoint:4, Upd_SubDate:93, UnSub_Type:4,
CreateDateFloat:8,
12/12/05 19:55:27 DEBUG orm.ClassWriter: sourceFilename is member_main.java
12/12/05 19:55:27 DEBUG orm.CompilationManager: Found existing
/tmp/sqoop-hadoop/compile/7131fa8fb957892b4af354982da9e57d/
12/12/05 19:55:27 INFO orm.CompilationManager: HADOOP_HOME is
/usr/local/hadoop/libexec/..
12/12/05 19:55:27 DEBUG orm.CompilationManager: Adding source file:
/tmp/sqoop-hadoop/compile/7131fa8fb957892b4af354982da9e57d/member_main.java
12/12/05 19:55:27 DEBUG orm.CompilationManager: Invoking javac with args:
12/12/05 19:55:27 DEBUG orm.CompilationManager:   -sourcepath
12/12/05 19:55:27 DEBUG orm.CompilationManager:
/tmp/sqoop-hadoop/compile/7131fa8fb957892b4af354982da9e57d/
12/12/05 19:55:27 DEBUG orm.CompilationManager:   -d
12/12/05 19:55:27 DEBUG orm.CompilationManager:
/tmp/sqoop-hadoop/compile/7131fa8fb957892b4af354982da9e57d/
12/12/05 19:55:27 DEBUG orm.CompilationManager:   -classpath
12/12/05 19:55:27 DEBUG orm.CompilationManager:
/usr/local/hadoop/libexec/../conf:/usr/lib/jvm/java-6-openjdk-amd64/lib/tools.jar:/usr/local/hadoop/libexec/..:/usr/local/hadoop/libexec/../hadoop-core-1.0.3.jar:/usr/local/hadoop/libexec/../lib/asm-3.2.jar:/usr/local/hadoop/libexec/../lib/aspectjrt-1.6.5.jar:/usr/local/hadoop/libexec/.

Hi Ivangelion,
I'm glad that you were able to move on with your issue. It seems to me that you're running on OpenJDK - unfortunately Sqoop is tested and supported only Oracle JDK.

Based on the exceptions you're hitting:

  java.lang.NumberFormatException: For input string: "Male"

  java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff

It seems to me your input files got somehow corrupted and for example for the first exception Sqoop is looking for column that should be number but found string "Male" instead. You've mentioned that your data can contain a lot of wild characters, can it happen that your data also contains new line characters? Would you mind re-trying import with parameter --hive-drop-import-delims [1] to see if it helps? (this parameter do not depend on hive in any way regardless of it's name).

Jarcec

On Thu, Dec 06, 2012 at 12:03:06PM +0800, Ivangelion wrote:
> Hi Jarek,
>
> It actually worked! Thank you so much~! :D
>
> However now we faced another problem. The former data we tried to export is
> only test data, which row count is only 10. When we tried to export
> production data back into SQL Server from HDFS file which was previously
> imported using Sqoop from SQL server, different errors occurred. The row
> count is about 400k, and only about 120k rows were exported. This time we
> used "-m 5", and if using "-m 1", nothing will be exported. Verbose log is
> in the bottom of this mail.
>
> Is this has to do with that we used MS SQL connector to do previous import,
> not the default one?
>
> Also, should we specify any character encoding, e.g. utf-8 during
> import/export process? There are characters of many different languages in
> our original data in SQL Server, and I'm not sure what the encoding is
> after imported into HDFS.
>
> Thanks again, Jarek.
>
> ====================================> 12/12/05 19:55:27 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/12/05 19:55:27 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:sqlserver:
> 12/12/05 19:55:27 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.cloudera.sqoop.manager.SQLServerManager@6766afb3
> 12/12/05 19:55:27 INFO tool.CodeGenTool: Beginning code generation
> 12/12/05 19:55:27 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
> 12/12/05 19:55:27 DEBUG manager.SqlManager: Using fetchSize for next query:
> 1000
> 12/12/05 19:55:27 INFO manager.SqlManager: Executing SQL statement: SELECT
> t.* FROM member_main AS t WHERE 1=0
> 12/12/05 19:55:27 DEBUG orm.ClassWriter: selected columns:
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   MemberId
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   USERNAME
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   FirstName
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   LastName
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   EmailAddress
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Password
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Password_E5
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Birthday
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   CompanyName
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Gender
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Age
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Education
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Country
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Title
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Phone1
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Phone2
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Fax
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   State
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   City
> 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Address1

Hi Jarek,

I've tried to use "--hive-drop-import-delims", but sqoop showed it has
syntax error:

  ERROR tool.BaseSqoopTool: Unrecognized argument: --hive-drop-import-delims

Also, should I change Java OpenJDK to Oracle JDK in order to make Sqoop
export work?

Thanks!
Ivan

On Fri, Dec 7, 2012 at 12:28 AM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:

> Hi Ivangelion,
> I'm glad that you were able to move on with your issue. It seems to me
> that you're running on OpenJDK - unfortunately Sqoop is tested and
> supported only Oracle JDK.
>
> Based on the exceptions you're hitting:
>
>   java.lang.NumberFormatException: For input string: "Male"
>
>   java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd
> hh:mm:ss[.fffffffff
>
> It seems to me your input files got somehow corrupted and for example for
> the first exception Sqoop is looking for column that should be number but
> found string "Male" instead. You've mentioned that your data can contain a
> lot of wild characters, can it happen that your data also contains new line
> characters? Would you mind re-trying import with parameter
> --hive-drop-import-delims [1] to see if it helps? (this parameter do not
> depend on hive in any way regardless of it's name).
>
> Jarcec
>
> On Thu, Dec 06, 2012 at 12:03:06PM +0800, Ivangelion wrote:
> > Hi Jarek,
> >
> > It actually worked! Thank you so much~! :D
> >
> > However now we faced another problem. The former data we tried to export
> is
> > only test data, which row count is only 10. When we tried to export
> > production data back into SQL Server from HDFS file which was previously
> > imported using Sqoop from SQL server, different errors occurred. The row
> > count is about 400k, and only about 120k rows were exported. This time we
> > used "-m 5", and if using "-m 1", nothing will be exported. Verbose log
> is
> > in the bottom of this mail.
> >
> > Is this has to do with that we used MS SQL connector to do previous
> import,
> > not the default one?
> >
> > Also, should we specify any character encoding, e.g. utf-8 during
> > import/export process? There are characters of many different languages
> in
> > our original data in SQL Server, and I'm not sure what the encoding is
> > after imported into HDFS.
> >
> > Thanks again, Jarek.
> >
> > ====================================> > 12/12/05 19:55:27 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Loaded manager factory:
> > com.cloudera.sqoop.manager.DefaultManagerFactory
> > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> > com.cloudera.sqoop.manager.DefaultManagerFactory
> > 12/12/05 19:55:27 DEBUG manager.DefaultManagerFactory: Trying with
> scheme:
> > jdbc:sqlserver:
> > 12/12/05 19:55:27 INFO manager.SqlManager: Using default fetchSize of
> 1000
> > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> > com.cloudera.sqoop.manager.SQLServerManager@6766afb3
> > 12/12/05 19:55:27 INFO tool.CodeGenTool: Beginning code generation
> > 12/12/05 19:55:27 DEBUG manager.SqlManager: No connection paramenters
> > specified. Using regular API for making connection.
> > 12/12/05 19:55:27 DEBUG manager.SqlManager: Using fetchSize for next
> query:
> > 1000
> > 12/12/05 19:55:27 INFO manager.SqlManager: Executing SQL statement:
> SELECT
> > t.* FROM member_main AS t WHERE 1=0
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter: selected columns:
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   MemberId
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   USERNAME
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   FirstName
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   LastName
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   EmailAddress
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Password
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Password_E5
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Birthday
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   CompanyName
> > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   Gender

I see, Would you mind upgrading your Sqoop to most recent version 1.4.2?

Jarcec

On Fri, Dec 07, 2012 at 11:19:31AM +0800, Chun-fan Ivan Liao wrote:
> Hi Jarek,
>
> I've tried to use "--hive-drop-import-delims", but sqoop showed it has
> syntax error:
>
>   ERROR tool.BaseSqoopTool: Unrecognized argument: --hive-drop-import-delims
>
> Also, should I change Java OpenJDK to Oracle JDK in order to make Sqoop
> export work?
>
> Thanks!
> Ivan
>
> On Fri, Dec 7, 2012 at 12:28 AM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:
>
> > Hi Ivangelion,
> > I'm glad that you were able to move on with your issue. It seems to me
> > that you're running on OpenJDK - unfortunately Sqoop is tested and
> > supported only Oracle JDK.
> >
> > Based on the exceptions you're hitting:
> >
> >   java.lang.NumberFormatException: For input string: "Male"
> >
> >   java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd
> > hh:mm:ss[.fffffffff
> >
> > It seems to me your input files got somehow corrupted and for example for
> > the first exception Sqoop is looking for column that should be number but
> > found string "Male" instead. You've mentioned that your data can contain a
> > lot of wild characters, can it happen that your data also contains new line
> > characters? Would you mind re-trying import with parameter
> > --hive-drop-import-delims [1] to see if it helps? (this parameter do not
> > depend on hive in any way regardless of it's name).
> >
> > Jarcec
> >
> > On Thu, Dec 06, 2012 at 12:03:06PM +0800, Ivangelion wrote:
> > > Hi Jarek,
> > >
> > > It actually worked! Thank you so much~! :D
> > >
> > > However now we faced another problem. The former data we tried to export
> > is
> > > only test data, which row count is only 10. When we tried to export
> > > production data back into SQL Server from HDFS file which was previously
> > > imported using Sqoop from SQL server, different errors occurred. The row
> > > count is about 400k, and only about 120k rows were exported. This time we
> > > used "-m 5", and if using "-m 1", nothing will be exported. Verbose log
> > is
> > > in the bottom of this mail.
> > >
> > > Is this has to do with that we used MS SQL connector to do previous
> > import,
> > > not the default one?
> > >
> > > Also, should we specify any character encoding, e.g. utf-8 during
> > > import/export process? There are characters of many different languages
> > in
> > > our original data in SQL Server, and I'm not sure what the encoding is
> > > after imported into HDFS.
> > >
> > > Thanks again, Jarek.
> > >
> > > ====================================> > > 12/12/05 19:55:27 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> > > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Loaded manager factory:
> > > com.cloudera.sqoop.manager.DefaultManagerFactory
> > > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> > > com.cloudera.sqoop.manager.DefaultManagerFactory
> > > 12/12/05 19:55:27 DEBUG manager.DefaultManagerFactory: Trying with
> > scheme:
> > > jdbc:sqlserver:
> > > 12/12/05 19:55:27 INFO manager.SqlManager: Using default fetchSize of
> > 1000
> > > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> > > com.cloudera.sqoop.manager.SQLServerManager@6766afb3
> > > 12/12/05 19:55:27 INFO tool.CodeGenTool: Beginning code generation
> > > 12/12/05 19:55:27 DEBUG manager.SqlManager: No connection paramenters
> > > specified. Using regular API for making connection.
> > > 12/12/05 19:55:27 DEBUG manager.SqlManager: Using fetchSize for next
> > query:
> > > 1000
> > > 12/12/05 19:55:27 INFO manager.SqlManager: Executing SQL statement:
> > SELECT
> > > t.* FROM member_main AS t WHERE 1=0
> > > 12/12/05 19:55:27 DEBUG orm.ClassWriter: selected columns:
> > > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   MemberId
> > > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   USERNAME
> > > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   FirstName
> > > 12/12/05 19:55:27 DEBUG orm.ClassWriter:   LastName
>

I've upgrade sqoop to 1.4.2 and copied hadoop-core-1.0.3.jar
and sqljdbc4.jar to /usr/local/sqoop/lib. I've also specified the parameter
"--hive-drop-import-delims" in the command, but the same error remained.
Parameters specified behind --hive-drop-import-delims could not be parsed:

=============12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Error parsing arguments for
export:
12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Unrecognized argument:
--hive-drop-import-delims
12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Unrecognized argument: --verbose

Try --help for usage instructions.
....
=============
Is there anything I can do now, e.g. re-importing data using default
connector and see if the imported data can be exported back to SQL Server?
On Fri, Dec 7, 2012 at 12:01 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:

> I see, Would you mind upgrading your Sqoop to most recent version 1.4.2?
>
> Jarcec
>
> On Fri, Dec 07, 2012 at 11:19:31AM +0800, Chun-fan Ivan Liao wrote:
> > Hi Jarek,
> >
> > I've tried to use "--hive-drop-import-delims", but sqoop showed it has
> > syntax error:
> >
> >   ERROR tool.BaseSqoopTool: Unrecognized argument:
> --hive-drop-import-delims
> >
> > Also, should I change Java OpenJDK to Oracle JDK in order to make Sqoop
> > export work?
> >
> > Thanks!
> > Ivan
> >
> > On Fri, Dec 7, 2012 at 12:28 AM, Jarek Jarcec Cecho <[EMAIL PROTECTED]
> >wrote:
> >
> > > Hi Ivangelion,
> > > I'm glad that you were able to move on with your issue. It seems to me
> > > that you're running on OpenJDK - unfortunately Sqoop is tested and
> > > supported only Oracle JDK.
> > >
> > > Based on the exceptions you're hitting:
> > >
> > >   java.lang.NumberFormatException: For input string: "Male"
> > >
> > >   java.lang.IllegalArgumentException: Timestamp format must be
> yyyy-mm-dd
> > > hh:mm:ss[.fffffffff
> > >
> > > It seems to me your input files got somehow corrupted and for example
> for
> > > the first exception Sqoop is looking for column that should be number
> but
> > > found string "Male" instead. You've mentioned that your data can
> contain a
> > > lot of wild characters, can it happen that your data also contains new
> line
> > > characters? Would you mind re-trying import with parameter
> > > --hive-drop-import-delims [1] to see if it helps? (this parameter do
> not
> > > depend on hive in any way regardless of it's name).
> > >
> > > Jarcec
> > >
> > > On Thu, Dec 06, 2012 at 12:03:06PM +0800, Ivangelion wrote:
> > > > Hi Jarek,
> > > >
> > > > It actually worked! Thank you so much~! :D
> > > >
> > > > However now we faced another problem. The former data we tried to
> export
> > > is
> > > > only test data, which row count is only 10. When we tried to export
> > > > production data back into SQL Server from HDFS file which was
> previously
> > > > imported using Sqoop from SQL server, different errors occurred. The
> row
> > > > count is about 400k, and only about 120k rows were exported. This
> time we
> > > > used "-m 5", and if using "-m 1", nothing will be exported. Verbose
> log
> > > is
> > > > in the bottom of this mail.
> > > >
> > > > Is this has to do with that we used MS SQL connector to do previous
> > > import,
> > > > not the default one?
> > > >
> > > > Also, should we specify any character encoding, e.g. utf-8 during
> > > > import/export process? There are characters of many different
> languages
> > > in
> > > > our original data in SQL Server, and I'm not sure what the encoding
> is
> > > > after imported into HDFS.
> > > >
> > > > Thanks again, Jarek.
> > > >
> > > > ====================================> > > > 12/12/05 19:55:27 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> > > > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Loaded manager factory:
> > > > com.cloudera.sqoop.manager.DefaultManagerFactory
> > > > 12/12/05 19:55:27 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> > > > com.cloudera.sqoop.manager.DefaultManagerFactory
> > > > 12/12/05 19:55:27 DEBUG manager.DefaultManagerFactory: Trying with

I've just realized what is wrong, parameter --hive-drop-import-delims is import specific and it seems that you're trying to use it for export. Would you mind to re-import your data with this parameter and try the export?

Jarcec

On Fri, Dec 07, 2012 at 03:45:55PM +0800, Chun-fan Ivan Liao wrote:
> I've upgrade sqoop to 1.4.2 and copied hadoop-core-1.0.3.jar
> and sqljdbc4.jar to /usr/local/sqoop/lib. I've also specified the parameter
> "--hive-drop-import-delims" in the command, but the same error remained.
> Parameters specified behind --hive-drop-import-delims could not be parsed:
>
> =============> 12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Error parsing arguments for
> export:
> 12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Unrecognized argument:
> --hive-drop-import-delims
> 12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Unrecognized argument: --verbose
>
> Try --help for usage instructions.
> ....
> =============>
> Is there anything I can do now, e.g. re-importing data using default
> connector and see if the imported data can be exported back to SQL Server?
>
>
> On Fri, Dec 7, 2012 at 12:01 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:
>
> > I see, Would you mind upgrading your Sqoop to most recent version 1.4.2?
> >
> > Jarcec
> >
> > On Fri, Dec 07, 2012 at 11:19:31AM +0800, Chun-fan Ivan Liao wrote:
> > > Hi Jarek,
> > >
> > > I've tried to use "--hive-drop-import-delims", but sqoop showed it has
> > > syntax error:
> > >
> > >   ERROR tool.BaseSqoopTool: Unrecognized argument:
> > --hive-drop-import-delims
> > >
> > > Also, should I change Java OpenJDK to Oracle JDK in order to make Sqoop
> > > export work?
> > >
> > > Thanks!
> > > Ivan
> > >
> > > On Fri, Dec 7, 2012 at 12:28 AM, Jarek Jarcec Cecho <[EMAIL PROTECTED]
> > >wrote:
> > >
> > > > Hi Ivangelion,
> > > > I'm glad that you were able to move on with your issue. It seems to me
> > > > that you're running on OpenJDK - unfortunately Sqoop is tested and
> > > > supported only Oracle JDK.
> > > >
> > > > Based on the exceptions you're hitting:
> > > >
> > > >   java.lang.NumberFormatException: For input string: "Male"
> > > >
> > > >   java.lang.IllegalArgumentException: Timestamp format must be
> > yyyy-mm-dd
> > > > hh:mm:ss[.fffffffff
> > > >
> > > > It seems to me your input files got somehow corrupted and for example
> > for
> > > > the first exception Sqoop is looking for column that should be number
> > but
> > > > found string "Male" instead. You've mentioned that your data can
> > contain a
> > > > lot of wild characters, can it happen that your data also contains new
> > line
> > > > characters? Would you mind re-trying import with parameter
> > > > --hive-drop-import-delims [1] to see if it helps? (this parameter do
> > not
> > > > depend on hive in any way regardless of it's name).
> > > >
> > > > Jarcec
> > > >
> > > > On Thu, Dec 06, 2012 at 12:03:06PM +0800, Ivangelion wrote:
> > > > > Hi Jarek,
> > > > >
> > > > > It actually worked! Thank you so much~! :D
> > > > >
> > > > > However now we faced another problem. The former data we tried to
> > export
> > > > is
> > > > > only test data, which row count is only 10. When we tried to export
> > > > > production data back into SQL Server from HDFS file which was
> > previously
> > > > > imported using Sqoop from SQL server, different errors occurred. The
> > row
> > > > > count is about 400k, and only about 120k rows were exported. This
> > time we
> > > > > used "-m 5", and if using "-m 1", nothing will be exported. Verbose
> > log
> > > > is
> > > > > in the bottom of this mail.
> > > > >
> > > > > Is this has to do with that we used MS SQL connector to do previous
> > > > import,
> > > > > not the default one?
> > > > >
> > > > > Also, should we specify any character encoding, e.g. utf-8 during
> > > > > import/export process? There are characters of many different
> > languages
> > > > in
> > > > > our original data in SQL Server, and I'm not sure what the encoding
> > is
> > > > > after imported into HDFS.

Hi Jarcec,

Sorry for the late reply.

I've tried to re-import the data then exported it, but still in vain. Error
messages were the same.

However I think I've found the root cause. I found there are pipes '|' in
the data of some of the rows, and since the pipe is the delimiter, this
made Sqoop split data incorrectly.

We will try to change those pipes in data to other characters, skip those
rows with pipes in them, or just delete those pipes.

Thank you so much for your kind and generous help!! :)

Kind regards,
Ivan

On Sat, Dec 8, 2012 at 12:02 AM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:

> I've just realized what is wrong, parameter --hive-drop-import-delims is
> import specific and it seems that you're trying to use it for export. Would
> you mind to re-import your data with this parameter and try the export?
>
> Jarcec
>
> On Fri, Dec 07, 2012 at 03:45:55PM +0800, Chun-fan Ivan Liao wrote:
> > I've upgrade sqoop to 1.4.2 and copied hadoop-core-1.0.3.jar
> > and sqljdbc4.jar to /usr/local/sqoop/lib. I've also specified the
> parameter
> > "--hive-drop-import-delims" in the command, but the same error remained.
> > Parameters specified behind --hive-drop-import-delims could not be
> parsed:
> >
> > =============> > 12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Error parsing arguments for
> > export:
> > 12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Unrecognized argument:
> > --hive-drop-import-delims
> > 12/12/06 23:39:07 ERROR tool.BaseSqoopTool: Unrecognized argument:
> --verbose
> >
> > Try --help for usage instructions.
> > ....
> > =============> >
> > Is there anything I can do now, e.g. re-importing data using default
> > connector and see if the imported data can be exported back to SQL
> Server?
> >
> >
> > On Fri, Dec 7, 2012 at 12:01 PM, Jarek Jarcec Cecho <[EMAIL PROTECTED]
> >wrote:
> >
> > > I see, Would you mind upgrading your Sqoop to most recent version
> 1.4.2?
> > >
> > > Jarcec
> > >
> > > On Fri, Dec 07, 2012 at 11:19:31AM +0800, Chun-fan Ivan Liao wrote:
> > > > Hi Jarek,
> > > >
> > > > I've tried to use "--hive-drop-import-delims", but sqoop showed it
> has
> > > > syntax error:
> > > >
> > > >   ERROR tool.BaseSqoopTool: Unrecognized argument:
> > > --hive-drop-import-delims
> > > >
> > > > Also, should I change Java OpenJDK to Oracle JDK in order to make
> Sqoop
> > > > export work?
> > > >
> > > > Thanks!
> > > > Ivan
> > > >
> > > > On Fri, Dec 7, 2012 at 12:28 AM, Jarek Jarcec Cecho <
> [EMAIL PROTECTED]
> > > >wrote:
> > > >
> > > > > Hi Ivangelion,
> > > > > I'm glad that you were able to move on with your issue. It seems
> to me
> > > > > that you're running on OpenJDK - unfortunately Sqoop is tested and
> > > > > supported only Oracle JDK.
> > > > >
> > > > > Based on the exceptions you're hitting:
> > > > >
> > > > >   java.lang.NumberFormatException: For input string: "Male"
> > > > >
> > > > >   java.lang.IllegalArgumentException: Timestamp format must be
> > > yyyy-mm-dd
> > > > > hh:mm:ss[.fffffffff
> > > > >
> > > > > It seems to me your input files got somehow corrupted and for
> example
> > > for
> > > > > the first exception Sqoop is looking for column that should be
> number
> > > but
> > > > > found string "Male" instead. You've mentioned that your data can
> > > contain a
> > > > > lot of wild characters, can it happen that your data also contains
> new
> > > line
> > > > > characters? Would you mind re-trying import with parameter
> > > > > --hive-drop-import-delims [1] to see if it helps? (this parameter
> do
> > > not
> > > > > depend on hive in any way regardless of it's name).
> > > > >
> > > > > Jarcec
> > > > >
> > > > > On Thu, Dec 06, 2012 at 12:03:06PM +0800, Ivangelion wrote:
> > > > > > Hi Jarek,
> > > > > >
> > > > > > It actually worked! Thank you so much~! :D
> > > > > >
> > > > > > However now we faced another problem. The former data we tried to
> > > export
> > > > > is
> > > > > > only test data, which row count is only 10. When we tried to
> export
> > > >