topic Re: Unable to write Data frame to Azure Synapse Table in Data Engineering

Unable to write Data frame to Azure Synapse Table

Akshith_Rajesh — Tue, 30 Aug 2022 16:36:29 GMT

When I am trying to insert records into the azure synapse Table using JDBC Its throwing below error

com.microsoft.sqlserver.jdbc.SQLServerException: The statement failed. Column 'COMPANY_ADDRESS_STATE' has a data type that cannot participate in a columnstore index.

In Database this table have column [COMPANY_ADDRESS_STATE] datatype as Varchar(50)

df.write.format("jdbc").mode("append") \
        .option('url', sqlURL)\
        .option('dbTable', tableName)\
        .option('user', synUser)\
        .option('password',jdbcPassword).save()

Can someone please guide me with the correct approach

Re: Unable to write Data frame to Azure Synapse Table

Hubert-Dudek — Tue, 30 Aug 2022 20:12:07 GMT

Columns that use any of the following data types cannot be included in a columnstore index:

nvarchar(max), varchar(max), and varbinary(max)

(Applies to SQL Server 2016 and prior versions, and nonclustered columnstore indexes)

so the issue is on the Azure Synapse's side.

Re: Unable to write Data frame to Azure Synapse Table

Akshith_Rajesh — Tue, 30 Aug 2022 20:23:43 GMT

Hi Hubert,

Interesting part is none of my columns have nvarchar(max), varchar(max), and varbinary(max) as the data types

Can you please let me know what ever the approach which I am trying to the table is the correct one or not

Or do we have any other approach in writing data to the synapse database using data bricks

Re: Unable to write Data frame to Azure Synapse Table

Hubert-Dudek — Tue, 30 Aug 2022 20:56:18 GMT

Yes, indeed, it is max only. I remember having some issues with varchar, too, but I can not remember exactly now. However, I solved it by creating a new table (like COMPANY_ADDRESS_STATE_2) in Azure SQL from databricks (omit append mode) and comparing datatypes.

But here is one more thing it is Synapse, so I think that you need to specify it by adding .format("com.databricks.spark.sqldw")

https://docs.microsoft.com/en-us/azure/databricks/data/data-sources/azure/synapse-analytics