Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Prevent Azure Synapse Serverless views from returning invalid data after source file schema change
I'm using Azure Synapse Serverless SQL to create views over Parquet files in a data lake. I have encountered an issue where views return invalid results if the source table schema changes.
For example, suppose I have the following parquet file:
| A | B | C |
|---|---|---|
| A | B | C |
| A | B | C |
Then suppose I create the following view in Synapse Serverless:
create view test as
SELECT * FROM
OPENROWSET(
BULK 'https://example.dfs.core.windows.net/test.parquet',
FORMAT = 'PARQUET'
) AS [result]
If I query this view, all is well, and I get the expected output, matching the table above.
Now, suppose I update the source parquet file with a new column as follows:
| A | Z | B | C |
|---|---|---|---|
| A | Z | B | C |
| A | Z | B | C |
If I query this with select * from test, I expect to either get columns A, B and C, or perhaps columns A, Z, B and C.
What I actually get is this:
| A | B | C |
|---|---|---|
| A | Z | B |
| A | Z | B |
Note that the values in 'column B' are actually the values from the new column Z. And the values in 'column C' are the values from column B. Synapse is silently returning invalid data for columns B and C.
Recreating the view fixes this (until next time the source file schema changes). This appears to be a known issue.
How can I ensure that Synapse Serverless views are robust to source file schema changes, and don't silently return wrong data for columns?
1 answer
Explicitly specify the schema to ensure that the view continues to return valid data, even if new columns are added to the source file.
Adapt the example above as follows to prevent the issue:
create view test as
SELECT * FROM
OPENROWSET(
BULK 'https://example.dfs.core.windows.net/test.parquet',
FORMAT = 'PARQUET'
)
WITH (
A varchar(100),
B varchar(100),
C varchar(100)
)
AS [result]
0 comment threads
1 comment thread