topic Re: Unable to change the index, when writing to a Azure SQL Data Warehouse in Data Engineering

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

Rajesh_M — Thu, 10 Mar 2022 16:38:21 GMT

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

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

Hubert-Dudek — Thu, 10 Mar 2022 17:33:52 GMT

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.

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

Rajesh_M — Thu, 10 Mar 2022 18:28:26 GMT

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?

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

Hubert-Dudek — Mon, 18 Apr 2022 09:38:05 GMT

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