Writing into Azure SQL DB doesn't work for popular field types

[radekhag]

Hello,

The connector on supported Spark 2.4.5 clusters throws metadata-types-related errors on certain scenarios during bulk insert tasks:
scenarios.zip

1. On appending and overwriting data in existing SQL tables with popular field types like NVARCHAR(4000) and DATETIME2(0). They seem to successfully append tables NVARCHAR(MAX) and DATETIME fields because java types from StringType and TimestampType are probably by default converted into NVARCHAR(MAX) and DATETIME.

2. On saving combined with creating or overwriting whole SQL tables (by DROP and CREATE) by passing a schema of SQL table through an option ‘createTableColumnTypes’. A package throws errors when a passed schema contains fields of popular types like NVARCHAR(MAX), DATETIME, DATETIME2(0).

I’m sending an attachment containing snippets for both scenarios. The log4jerrors log points to a medata-types problem which happen despite using parameter ‘schemaCheckEnabled’ = false or true and referred to the scenario no. 1.
I would be grateful if you could help me. I think it is an issue of the package.

Regards,
Radoslaw

[hmayer1980]

Hello radekhag,

It looks like I do have the same issue. I wonder how you got the outputs information about the datatype issues. I only get the Connection Closed Error. My Azure Databricks does not include anything like
"INFO BulkCopyUtils: Spark Dataframe and SQL Server table have differing column names 'ExecProductId' and 'ExecProductId' at column index 4 (case insensitive)".

Not in the log4j output nor in the standard Error output.
Did you enable something specifically to get this Error output? Would help to know at least what columns are affected.

And yes, I totally agree that the provider needs to support datetime2(0) as this is a standard requirement.
With schemaCheckEnabled to false it should just write the values there and not fail.
With schemaCheckEnabled it should also not report it as error - but in my case I do not get any information about the Datatype missmatch.

I can confirm that datetime2(0) is the reason my write to Azure SQL is not working for some cases despite not having milliseconds in Azure Databricks at all the write still fail.

If I converted one of the tables to datetime2(7) on DEV things work as expected.

But I cannot to this so easy on production due to having billions of rows and this would most likely require a table rewrite.

Best Regards
Hannes

[hmayer1980]

I can also confirm that any datetime2(x) where x > 0 would work. Its just datetime2(0) that does not work.

[rajmera3]

Thanks @hmayer1980 and @radekhag for the information.
This issue is the same as #39. This is an issue with the timestamp conversion and a PR will be submitted to Spark for a fix.

Marking this as a duplicate for now but we will close it once the PR is created.

[shivsood]

Closing dupe of #39