How to materialize part of the query?

1

Hi, I’d like to materialize a part of the query to execute it only once and all next references to it should work with already processed data.

like

explain plan for
with t as (
    select distinct cId from myTable limit 10
)
select * from t join t as t1 using (cid)

More references to t I have more parquet scans appear in the plan. Would be nice to read the thing only once

2

@comphead

You can create a reflection for the dataset you want to materialize

3

Thanks @balaji.ramaswamy, reflection will work for static dataset
But the real use case it is

explain plan for
with t as (
    select distinct cId from myTable where someDate between :dt0 and :dt1
) select * from t join t as t1 using (cid)
4

@comphead

What happens if you create a reflection without the :dt0 and :dt1? Is it too big?

5

Yep, its pretty big.

The entire question is a bit wider. The calculated dataset in t can have dozens of rows, but it gets calculated using tons of resources and time based on runtime params. And I need to reuse that portion of data in my query as many times as needed.

Otherwise I will recalculate the thing over and over again, wasting cluster resources.