1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
# MATERIALIZE
Materializes the specified source or expression on the current or a given cluster. In case of expression materialization, its type must be a list of structures. The cluster for materialization is taken from the `ON` expression or, if it is absent, from the [USE](use.md) operator.
A materialized source preserves all columns and the sort order. It also creates a barrier for optimizers with respect to possible merging of computations.
## Syntax
```yql
MATERIALIZE
<source> -- the source can be a table name, a named expression, or a nested SELECT
INTO $<bind_name> -- the parameter name by which the materialization results can be referenced elsewhere in the query
ON <cluster> -- the name of the cluster on which the source will be materialized (optional)
WITH <hints> -- additional modifiers (optional)
```
## Availability
`MATERIALIZE` is available since [2026.02](../changelog/2026.02.md) language version.
## Using modifiers
Materialization can be performed with one or several modifiers. A modifier is specified after the `WITH` keyword.
The following rules apply:
- If a modifier has a value, it is specified after the `=` sign: `MATERIALIZE ... INTO ... WITH SOME_HINT=value`.
- If several modifiers need to be specified, they are enclosed in parentheses: `MATERIALIZE ... INTO ... WITH (SOME_HINT1=value, SOME_HINT2, SOME_HINT3=value)`.
General list of supported modifiers:
* `prune_unused_columns` ‐ remove columns from the materialized source that are not used by consumers.
Systems on whose clusters materialization is performed may support an extended set of modifiers.
## Examples
```yql
USE cluster;
MATERIALIZE (SELECT 1 as a, 2 as b) INTO $materialized; -- Materializes a single-row expression on the cluster "cluster" and makes it available under the name $materialized
SELECT * FROM $materialized; -- Selecting from the materialized source
```
```yql
USE cluster;
$input = SELECT key, value FROM my_table ORDER BY key;
MATERIALIZE $input INTO $materialized ON another_cluster; -- Materializes the SELECT result on the cluster another_cluster and makes it available under the name $materialized.
-- The materialized source preserves the sort order by key.
SELECT * FROM another_table AS a
JOIN $materialized AS b USING key; -- JOIN with the materialized source. The choice of JOIN strategy takes into account the sorting of the materialized source
```
```yql
USE cluster;
$input = SELECT a, b, c, d FROM my_table;
MATERIALIZE $input INTO $materialized WITH prune_unused_columns; -- Materializes the SELECT result with the resulting set of columns [a, b, c] after optimizations.
SELECT a, b FROM $materialized; -- Selecting from the materialized source with the used set of columns [a, b]
SELECT c FROM $materialized; -- Selecting from the materialized source with the used set of columns [c]
```
|
