Create view which will do partition filtering in Synapse openrowset

Question

I have a view that is defined as

create view [dbo].[darts] as
    SELECT pricedate, [hour], node_id, dalmp, cast(result.filepath(1) as int) as [Year], cast(result.filepath(2) as int) as [Month]

    FROM
        OPENROWSET(
            BULK 'year=*/month=*/*.parquet',
            DATA_SOURCE='mysource',
            FORMAT = 'parquet'
        ) 
        with (
        pricedate date,
        node_id bigint, 
        [hour] int,
        dalmp float
)
        as [result] 
        where cast(result.filepath(1) as int)=datepart(year, pricedate) and cast(result.filepath(2) as int)=datepart(month, pricedate)

What I want to be able to do is do a query on this view like:

select * from darts where pricedate='2022-11-01'

and have the where clause of the view definition force it to only look in year=2022/month=11 but it doesn't work unless I do it explicitly such as:

select * from darts where pricedate='2022-11-01' and Year=2022 and Month=11

For clarity, when I say the first query doesn't work, what I mean is that it isn't doing any partition pruning, that query searches all files/data. Whereas, my second query only scans the fraction that I expect.

Are there any extra modifiers/syntax/functional form I could use in my view definition that would force partition pruning in the case of my first query?

Create view which will do partition filtering in Synapse openrowset

0 Answers0