question

hannaS-3589 avatar image
0 Votes"
hannaS-3589 asked SamaraSoucy-MSFT answered

Truncate column on load from API To Synapse

I am copying data from an API into Parquet file and then to the data warehouse. One of the columns in a table ( I cannot figure out which one) is causing "Unexpected error encountered filling record reader buffer: HadoopSqlException: String or binary data would be truncated" error . I have done varchar(max) for all destination columns just for testing, yet it still fails. I would happily cut the data to fit the max allowed column width if I could figure out how to do that on copy from API and if I could find which column is actually causing the problem. How can I make it work? What are my options here? Thank you, Hanna

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SamaraSoucy-MSFT avatar image
0 Votes"
SamaraSoucy-MSFT answered

Polybase has a hard 4000 character limit regardless of what the column is set to in the SQL pool. The two most common solutions are to either just truncate the data or split it during load and then recombine it once it is on the SQL Pool side. Unfortunately there is no easy way to figure out which column is the problem from the error message.

Please let me know if you are using a different method and I will help you find a solution for that method, but assuming you are using Pipelines with two copy activities (API -> parquet and parquet -> DW) here is one option- Switch your second copy activity to a Data Flow. Set the same source and destination, but add a derived column activity between them. Add a new column pattern type == 'string' and for the expression enter $$ and left($$, 4000)

83015-2021-03-30-18-05-50-analyticssynapsews-azure-synap.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.