Azure synapse serverless query partitioned parquet using openrowset

Question

My parquet files are partitioned in Azure blob storage like in

path/year=/month=/

so I am able to do

select * from openrowset (BULK '/path/year=/month=/' ,DATA_SOURCE ='mysource',FORMAT = 'PARQUET') as files

I can do

select * from openrowset (BULK '/path/year=2021/month=1/' ,DATA_SOURCE ='mysource',FORMAT = 'PARQUET') as files

but I can't do

select * from openrowset (BULK '/path/year=2021/month={1,2}/' ,DATA_SOURCE ='mysource',FORMAT = 'PARQUET') as files

what's the correct syntax to do that to select multiple partitions in the open rowset ?

Answer 1

Hello @sakuraime ,

Thanks for the question and using MS Q&A platform.

For the demo purpose, I had created sample folder structure as per your requirement:

Here is the query to read subset of files in folder using multiple file paths option:

For more details, refer to Read subset of files in folder using multiple file paths.

Azure Synapse Serverless SQL pool supports reading multiple files/folders by using wildcards, which are similar to the wildcards used in Windows OS. However, greater flexibility is present since multiple wildcards are allowed.

Note: All files accessed with the single OPENROWSET must have the same structure (i.e., number of columns and their data types).

In this article - Query folders and multiple files, you'll learn how to write a query using serverless SQL pool in Azure Synapse Analytics.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators