Azure synapse serverless openrowset parquet

Question

Suppose I have a parquet folder

table/year/month/*.parquet

and my query is

SELECT count()
FROM OPENROWSET(BULK '/path/table/2021/10/.parquet',DATA_SOURCE='source',FORMAT = 'PARQUET')
AS a

the above is for one year and
what if I would like to do for two years 2021 and 2020 ? what's the syntax

SELECT count()
FROM OPENROWSET(BULK '/path/table/2021,2020/10/.parquet',DATA_SOURCE='source',FORMAT = 'PARQUET')
AS a

Answer 1

You can do this by using a combination of wildcard (*) and filepath() function:

SELECT count()  
FROM OPENROWSET(BULK '/path/table/202*/10/.parquet',DATA_SOURCE='source',FORMAT = 'PARQUET')  
AS a  
WHERE  
    a.filepath(3) IN (2020,2021)