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,526 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,526 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://video2.skills-academy.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://video2.skills-academy.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

Share via

Azure Synaps serverless scan file that should be filtered