Unable to change the index, when writing to a Azure SQL Data Warehouse

[Rajesh_M]

Hi,

I have some data in a spark data frame and I am trying to write it to a table in Azure SQL Data Warehouse. If I use df.write.mode(saveMode="overwrite") I get this error:

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

I guess its trying to create the end table using with a columnstore index. I need a way to specify that the create statement not create the end table with a column store index.

The other issue is if I change that to df.write.option("truncate",True).mode(saveMode="append") and run it a few times (after manually creating the table the first time), I end up with duplicate records in the end table. That basically means that the "truncate" option isn't working

Best,

Rajesh

[Hubert-Dudek · Accepted solution]

For avoiding duplicates during append you can set index in sql with options: "is Unique"=Yes and "Ignore Duplicate Keys"=Yes.

Spark/databricks don't know what is in database that's why you have duplicates.

Truncate option is for overwrite mode only as than instead of DROP TABLE in SQL it makes TRUNCATE TABLE so schema survive.

Regarding error probably in SQL you have separated CLUSTERED index but I am not sure.

[Rajesh_M]

Thanks @Hubert Dudek​ .

Do you know if there is a way to run a create table statement on Azure Synapse/Azure SQL Datawarehouse from Databricks?

[Hubert-Dudek]

Yes, it is possible, as they both use blob storage. As architecture like this is more complex, you need to go through the manual; there are a lot of examples: https://docs.microsoft.com/en-us/azure/databricks/data/data-sources/azure/synapse-analytics