trying to write data from adlsgen2 to synapse sql dedicated pool but getting data type error

Question

I am trying to write data into a table from adls gen2 using a copy activity. when creating the table, i have to use varchar(max). varchar(8000) is not an option as my data is too big for that. when creating the table using dedicated sql pool, i am able to create table with the varchar(max) datatype by using: with (heap). Getting the following error when trying to write to the table:

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=Microsoft.Data.SqlClient.SqlException,Message=The statement failed. Column 'custom_fields' has a data type that cannot participate in a columnstore index.,Source=Framework Microsoft SqlClient Data Provider,'

Is there any way to keep the varchar(max) data type and write from adlsgen2 to table in synapse using dedicated sql pool?

Answer 1

Hi Syed Aziz,

Thanks for reaching out to Microsoft Q&A.

he error you're encountering is due to a restriction in Azure Synapse SQL Dedicated Pool: VARCHAR(MAX) columns are incompatible with columnstore indexes because columnstore indexing does not support variable-length large object types like VARCHAR(MAX). Since Synapse Dedicated SQL Pools use columnstore indexing by default for performance, this conflict arises.

Here are some potential solutions:

1. Switch to a HEAP table (No Columnstore Index):
   • As you've mentioned, you can use HEAP when creating the table to avoid a columnstore index, allowing VARCHAR(MAX) without conflicts. However, this means you will not benefit from the performance boost of a columnstore index.

          CREATE TABLE my_table
          (
              id INT,
              custom_fields VARCHAR(MAX)
          )
          WITH (HEAP);
          
          
     

2. Use VARCHAR(8000) or a Fixed-Length Column:
   • If possible, limit the VARCHAR(MAX) column to VARCHAR(8000) (or another feasible length) if your data can be truncated or processed in chunks to fit within 8000 characters. This will allow columnstore indexing.
3. Split Large Text Columns into Multiple Columns:
   • Consider splitting the custom_fields column into smaller segments (VARCHAR(8000) columns like custom_fields_part1, custom_fields_part2, etc.). This workaround allows for a similar large capacity while keeping columnstore indexing.
   Use a Separate Table for the VARCHAR(MAX) Column:
   • Create a separate table for columns with large text data and join it with the main table. This approach keeps your primary table optimized with columnstore indexing, while the additional table stores larger text data.
   Alternative Storage Solution:

     - Store large text or JSON fields in Azure Data Lake Storage and retrieve them as needed, keeping only essential data fields in Synapse SQL Dedicated Pool.
   

Each of these approaches has trade-offs. The most efficient choice will depend on how you need to query the data and whether columnstore indexing benefits are essential for your workload. These suggestiosn will help you deciding with some perspective even if you could not arrive at a fix.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.