Azure Synaps serverless scan file that should be filtered

Shlomi Lanton 56

Hey,

We have a large amount of files stored in Storage Account in Parquet format with the following hierarchy.
/m_id=%M_ID%/quarter=%QUARTER%/month=%MONTH%/partition_version=last/part-0.parquet'.
examples for quarter values: 2024Q1, 2024Q2, 2024Q3 etc.

We have a view we use the query the data, the view is defined using OPENROWSET , when running a query a WHERE filter and [quarter] in ('2023Q2', '2023Q3', '2023Q4', '2024Q1') we get an error

Error handling external file: 'IO request completed with an error. ERROR = 0x0000000C'. Underlying data description: file '/machine_id=aaaa/quarter=2024Q2/month=6/partition_version=last/part-0.parquet'.

but note that the quarter of 2024Q2 is not in the query, so we expect this file to not be scanned at all.

How can we dig down to this issue?
can we get a list of all files scanned in a query?
We apply multiple filters in the query (on partitions and values), does the order of elements in the query matter?

Thanks

BhargavaGunnam-MSFT 28,606 Reputation points Microsoft Employee

2024-07-01T17:21:49.3+00:00

Hello Shlomi Lanton,

I have seen this error when the path doesn't exist.

Can you please check the path and see if it exists in the container?
Shlomi Lanton 56 Reputation points

2024-07-02T08:58:38.64+00:00

I believe it was there, we are seeing this error if the file is modified while the query is running, but note that this quarter wasn't in the query WHERE part, so I expect Synapse will not try to read it at all. @BhargavaGunnam-MSFT
BhargavaGunnam-MSFT 28,606 Reputation points Microsoft Employee

2024-07-02T19:12:47.57+00:00

After further checking the cause of the error is that the files which are trying to read are being modified while a query is executing.

The default behavior is to error out in that case.

if you wants to allow appendable files to be queried while updated by allowing inconsistent reads in queries.

ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-single-csv-file#querying-appendable-files

Inconsistent reads issues can happen for external tables, too. In that case, the syntax is a bit different:

TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables#external-table-on-appendable-files

I hope this answers your question.
Shlomi Lanton 56 Reputation points

2024-07-02T20:08:35.5666667+00:00

Thanks for the reply.
First, the solution you offered is only for CSV files and I have parquet, if this can applied to parquet as well it will be great.
Second, it still doesn't explain why files that should be filtered in the query are scanned
BhargavaGunnam-MSFT 28,606 Reputation points Microsoft Employee

2024-07-03T18:27:48.5966667+00:00

Hi Shlomi Lanton,

Sorry, this solution won't work for Parquet format(This is only for CSV)

Can you please try with DBCC FREEPROCCACHE ?

Regrading your second question- this needs further troubleshooting as this is not an expected behavior. I would suggest to file a support case so that support engineer can live troubleshoot the issue.

1 answer

Shlomi Lanton 56 Reputation points

2024-07-04T05:14:39.9233333+00:00

For anyone that come across this in the feature, it seem our issue was with the way we defined the column type for the partitions while building our view. Moving from VARCHAR(MAX) to VARCHAR(%VALUE%) (according to the length of the column) fixed the issue and allowed us to run queries with proper partition pruning.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Azure Synaps serverless scan file that should be filtered

1 answer