alibaba / alibaba/DataX

Hive同步到SqlServer 数值较大的Double类型 转化错误

Open
#802 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
17.4k
Forks
5.7k
PR merge metrics
No merged PRs in 30d

Description

错误内容:

```
2020-08-19 12:48:33.774 [0-0-0-writer] ERROR StdoutPluginCollector - 脏数据:
{"exception":"Error converting data type nvarchar to numeric.","record":[{"byteSize":4,"index":0,"rawData":"oral","type":"STRING"},{"byteSize":3,"index":1,"rawData":"Adv","type":"STRING"},{"byteSize":17,"index":2,"rawData":"Total Advertising","type":"STRING"},{"byteSize":19,"index":3,"rawData":"Advertising working","type":"STRING"},{"byteSize":26,"index":4,"rawData":"xxxxx","type":"STRING"},{"byteSize":9,"index":5,"rawData":"Marketing","type":"STRING"},{"byteSize":9,"index":6,"rawData":"Marketing","type":"STRING"},{"byteSize":9,"index":7,"rawData":"SENSODYNE","type":"STRING"},{"byteSize":27,"index":8,"rawData":"SENSODYNE CORE - CORE RANGE","type":"STRING"},{"byteSize":10,"index":9,"rawData":"1.303677E7","type":"DOUBLE"},{"byteSize":6,"index":10,"rawData":202005,"type":"LONG"}],"type":"writer"}

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to numeric.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) ~[sqljdbc4-4.0.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332) ~[sqljdbc4-4.0.jar:na]
at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.doOneInsert(CommonRdbmsWriter.java:382) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.doBatchInsert(CommonRdbmsWriter.java:362) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.startWriteWithConnection(CommonRdbmsWriter.java:297) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Task.startWrite(CommonRdbmsWriter.java:319) [plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
at com.alibaba.datax.plugin.writer.sqlserverwriter.SqlServerWriter$Task.startWrite(SqlServerWriter.java:81) [sqlserverwriter-0.0.1-SNAPSHOT.jar:na]
at com.alibaba.datax.core.taskgroup.runner.WriterRunner.run(WriterRunner.java:56) [datax-core-0.0.1-SNAPSHOT.jar:na]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_222]
```

看了CommonRdbmsWriter代码 怀疑是Double类型的字段在preparedStatement变成了科学技术法表达, 导致错误
CommonRdbmsWriter.java 426行
```
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.NUMERIC:
case Types.DECIMAL:
case Types.FLOAT:
case Types.REAL:
case Types.DOUBLE:
String strValue = column.asString();
if (emptyAsNull && "".equals(strValue)) {
preparedStatement.setString(columnIndex + 1, null);
} else {
preparedStatement.setString(columnIndex + 1, strValue);
}
break;
```
问题:
1. 为何要把数字类型转化成String?
2. 确定是否这个问题导致同步失败?

Contributor guide

No contributing guide indexed for this repository

Research direction

Start in CommonRdbmsWriter.java around line 426 and trace the numeric handling used by CommonRdbmsWriter$Task.doOneInsert, then inspect the SQL Server writer path shown in the stack trace. Reproduce the Hive-to-SQL Server case with the DOUBLE value 1.303677E7 and determine whether string binding causes the numeric conversion error. Done means the cause is confirmed or ruled out and the required behavior is documented with coverage for the failing case.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.