What are alternates data type for "sql server - xml and image" data types in synapse lake db?

Question

What are alternates data type for "sql server - xml and image" data types in synapse lake db?

K Tharangini 0

I am migrating data from sql server to lake database - delta tables, I am using string data type in delta table as an alternative for xml data type - sql table. I am getting below error. How this can be handled is there any other data type which can be used as an alternate for XML.Similarly, for image - sql data type I am using binary- lake db table and getting similar error.

Error Message: String or binary data would be truncated while reading column of type 'VARCHAR(8000)'

Any suggestions please?

1 answer

Your answer

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hello K Tharangini,

Welcome to the Microsoft Q&A forum.

In Serverless By default, the string type in delta table will be resolved to varchar(8000) by schema inference. This is documented here: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool#check-inferred-data-types

To resolve this, please define schema using WITH clause and setting ‘VARCHAR(MAX)’ for the problematic column.

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-08-14T16:08:41+00:00

Hello K Tharangini,

I am checking to see if you have any further questions here.
K Tharangini 0 Reputation points

2024-08-16T08:34:51.3633333+00:00

please provide an example for - schema using WITH clause and setting ‘VARCHAR(MAX)’
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-08-16T15:58:42.2533333+00:00
Here is an example from the Microsoft document: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool#check-inferred-data-types

This pattern can be applied to your scenario when migrating data from SQL Server to a Lake Database. Specifically, you would use the WITH clause to define columns like XMLColumn and ImageColumn with VARCHAR(MAX) and VARBINARY(MAX), respectively, similar to how you defined vendorID as varchar(4) in the provided example.

SELECT XMLColumn, ImageColumn FROM OPENROWSET( BULK 'https://yourstorageaccount.blob.core.windows.net/yourcontainer/yourfile.delta', FORMAT='DELTA' ) WITH ( XMLColumn VARCHAR(MAX), ImageColumn VARBINARY(MAX) ) AS yourTable;

I hope this helps.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.
K Tharangini 0 Reputation points

2024-08-21T08:14:10.79+00:00

I am using df to read from parquet and write to delta table, there Varchar(MAX) is not allowed
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-08-21T22:44:53.6733333+00:00

Thanks for the additional information.

For the XML Data:

Define the schema explicitly in your DataFrame, using StringType for the XML column. This ensures that the entire content is preserved without truncation.

For the Image Data:

Similarly, make sure the schema for the image column is set to BinaryType. This should match the binary data type in your Delta table.

I hope this help

Share via

What are alternates data type for "sql server - xml and image" data types in synapse lake db?

1 answer

Your answer