Hello, my question is that how does reflection cache data in case my VDS contain grouping code and i set reflection on it?
Imagine VDS:
// return 100 rows
select x, y, max(z1) m1, min(z2) m2
from
(
//returns 10k rows
select x, y, z1, z2 from data where …
)
group by x, y
when i set reflection on it i expected that only 100 rows produced by group by will be cached, but in reality from reflection jobs i VDS queries i suspect that 10k rows are cached. Am i right?
so each time i query VDS it pulls 10k rows (x, y, z1, z2 columns) aggregates them and returns?
Is it possible to somehow reflect top level data in reflection store? my grouping vds reduces from millions of rows to only thousands so that footprint of the reflection becomes too big.
Note: i use incremental reflection refresh if it matter(maybe it is) based on new data source files arrived.
One workaround i found is using FAKE aggregate reflection instead and specify all columns as dimensions there(x, y, m1, m2) with no metrics.
than i see that reflection footprint contains only aggregated data after GROUP BY step.
The drawback is that in order to have aggregation picked up i have to write:
select x, y from VDS group by x, y Though group by here is redundant and does not change output, without it query is not accelerated.
This is not accelerated: select max(x) from VDS
This is accelarated: select max(x) from (select x from VDS group by x)
so i have to create another VDS2 based on original as
select x, y, m1, m2 from VDS group by x, y, m1, m2
and then query new VDS2 as i wish if it was original VDS
UPDATE: also seems i found another solution for this:
i have to create Fake VDS2 above original VDS BUT this new VDS2 has to have any predicate(even non filtering). For example:
create VDS2 as select * from VDS where x=x than create RAW reflection on VDS2 and observe its footprint, now it contains only data grouped by VDS. Withou redundant where x=x raw reflection will fallback to cache all rows from the data source before group by applied…