[SPARK-10856] SQL Server dialect needs to map java.sql.Timestamp to DATETIME instead of TIMESTAMP

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.4.0, 1.4.1, 1.5.0
• Fix Version/s: 1.6.0
• Component/s: SQL
• Labels: patch
• Flags: Patch, Important
• Assignee: L. C. Hsieh
• Reporter: Henrik Behrens
• Created: 28/Sep/15 17:24
• Updated: 21/Feb/19 20:05
• Resolved: 07/Oct/15 21:49

Description

When saving a DataFrame to MS SQL Server, en error is thrown if there is more than one TIMESTAMP column:

df.printSchema

root

– Id: string (nullable = false)

– TypeInformation_CreatedBy: string (nullable = false)

– TypeInformation_ModifiedBy: string (nullable = true)

– TypeInformation_TypeStatus: integer (nullable = false)

– TypeInformation_CreatedAtDatabase: timestamp (nullable = false)

– TypeInformation_ModifiedAtDatabase: timestamp (nullable = true)

df.write.mode("overwrite").jdbc(url, tablename, props)

com.microsoft.sqlserver.jdbc.SQLServerException: A table can only have one timestamp column. Because table 'DebtorTypeSet1' already has one, the column 'TypeInformation_ModifiedAtDatabase' cannot be added.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError
(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServ
erStatement.java:1635)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep
aredStatement(SQLServerPreparedStatement.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecC
md.doExecute(SQLServerPreparedStatement.java:372)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLSe
rverConnection.java:1793)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLSer
verStatement.java:184)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLS
erverStatement.java:159)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate
(SQLServerPreparedStatement.java:315)

I tested this on Windows and SQL Server 12 using Spark 1.4.1.

I think this can be fixed in a similar way to Spark-10419.

As a refererence, here is the type mapping according to the SQL Server JDBC driver (basicDT.java, extracted from sqljdbc_4.2.6420.100_enu.exe):

private static void displayRow(String title, ResultSet rs) {
try {
System.out.println(title);
System.out.println(rs.getInt(1) + " , " + // SQL integer type.
rs.getString(2) + " , " + // SQL char type.
rs.getString(3) + " , " + // SQL varchar type.
rs.getBoolean(4) + " , " + // SQL bit type.
rs.getDouble(5) + " , " + // SQL decimal type.
rs.getDouble(6) + " , " + // SQL money type.
rs.getTimestamp(7) + " , " + // SQL datetime type.
rs.getDate(8) + " , " + // SQL date type.
rs.getTime(9) + " , " + // SQL time type.
rs.getTimestamp(10) + " , " + // SQL datetime2 type.
((SQLServerResultSet)rs).getDateTimeOffset(11)); // SQL datetimeoffset type.

Issue Links

• links to: [Github] Pull Request #8978 (viirya)