Synapse Analytics - Unable to truncate column from csv in view creation

Kalin Ramsumer 6 Reputation points
2022-10-06T07:45:36.277+00:00

I'm attempting to create a view in Synapse reading a csv file in blob storage.
When reading the file, a single column has a length that exceeds 8000.
I'm okay with this column being truncated.

When setting ANSI WARNINGS OFF, this allows me to view the truncated column without warning and subsequently create the view, but would require ANSI WARNINGS to be explicitly set to OFF when querying the view again.

Is there a way to SUBSTRING/accept column truncation on view creation so that when the view is queried, there isnt a need to set ANSI WARNINGS OFF?
Alternatively, what is the best approach to handle these columns that exceed 8000 characters?

The message returned when ANSI WARNINGS is on is as follows:
"String or binary data would be truncated while reading column of type 'VARCHAR'. Check ANSI_WARNINGS option."

I would appreciate any insight!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,451 questions
{count} vote