Integrating Spark with Azure Synapse Dedicated sql instance

Question

Hi,

I am having a Synapse dedicate sql instance to which I am trying to read and write using Apache spark from Java application. But issue arise when trying to create a Table due to default Clustered index creation which does not allow MAX for Synapse but same works for other sql server or Azure sql server instances. What are best options for Spark when writing along with creation of table for Synapse tables.

Note : Do not want to create a Heap type of table and want to go with default CCI table.

Thanks

Answer 1

Hi @Nitish Sharma
Welcome to Microsoft Q&A platform and thanks for posting your query here.

I understand that you're facing issues when writing from Spark to a Synapse dedicated SQL pool due to the default Clustered Columnstore Index (CCI) restrictions on MAX data types. Since Synapse does not support MAX in a CCI table, here are some alternative approaches you can consider:

1. Use VarChar with a Fixed Length – Instead of VARCHAR(MAX), define a reasonable fixed-length VARCHAR(n) that fits your data requirements.
2. Create the Table in Advance – Instead of letting Spark create the table dynamically, predefine it in Synapse with a compatible schema.
3. Use Delta Lake or External Tables – If flexibility is needed, you can store data in Delta Lake on ADLS and query it using Synapse.
4. Manually Define Indexes – Use DISTRIBUTION and indexing strategies explicitly when creating the table, ensuring compatibility with Synapse's CCI requirements.

Let me know if you need further clarification or additional recommendations!

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.