read new column from .csv

Hi, my csv dataset files have two columns: column1, column2

I have created a serverless sqlpool view which reads the .csv file from the blobstorage container folder... If I want to pull another column, i.e. column3 into the existing .csv files, then the view will not be able to read the new column unless I separate the old and new .parquet files. What is the solution to keep the old and new .csv files in the same blobstorage directory and yet be able to read the new column too? Thank you

Smaran Thoomu 18,795 Reputation points Microsoft Vendor

2024-12-02T16:34:18.4966667+00:00
Hi @Rajan,Naveen,US-St. Louis,External

Welcome to Microsoft Q&A platform and thanks for posting your query here.

When working with serverless SQL pools in Synapse, changes to the structure of your .csv files (like adding a new column) can lead to challenges if you’re trying to query files with mixed schemas (old and new) in the same directory. Here’s how you can handle this scenario:

Use Wildcard Columns in Your SQL View

Instead of explicitly listing column names in your view definition, you can use SELECT * to include all columns in the file. While this approach works, it assumes all .csv files in the folder have the same schema.

Create Separate Views for Old and New Schema Files

Place the old .csv files in one directory and the new ones in another.

Create separate views for each folder, one for files with the old schema and another for files with the new schema.

You can then union the results if you need to work with all data together:
SELECT column1, column2, NULL AS column3 FROM OldSchemaView UNION ALL SELECT column1, column2, column3 FROM NewSchemaView

Modify Your Existing View with Conditional Logic

If separating files isn’t feasible, you can modify your view to handle optional columns. For example, use a TRY_CAST or COALESCE function to account for missing columns in older files:
SELECT column1, column2, TRY_CAST(JSON_VALUE(line, '$.column3') AS NVARCHAR) AS column3 FROM OPENROWSET(...)

Convert .csv Files to Parquet

If possible, consider converting your .csv files to Parquet format. Parquet files are schema-aware, and serverless SQL pools handle schema evolution better with Parquet. This approach avoids many of the issues with mixed schemas.

Use External Tables with Schema Discovery

You can define an external table that dynamically adapts to changes in the schema by using WITH options like FIELD_TERMINATOR and STRING_DELIMITER for flexible .csv reading. I hope this information helps. Let me know if you have any further questions
Smaran Thoomu 18,795 Reputation points Microsoft Vendor

2024-12-03T07:51:08.2066667+00:00

@Rajan,Naveen,US-St. Louis,External We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Smaran Thoomu 18,795 Reputation points Microsoft Vendor

2024-12-04T05:30:29.42+00:00

@Rajan,Naveen,US-St. Louis,External Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.

Share via

read new column from .csv

Your answer