Projections

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/projection.md)

This page discusses what projections are, how you can use them and various options for manipulating projections.

## Overview of projections[​](#overview "Direct link to Overview of projections")

Projections store data in a format that optimizes query execution, this feature is useful for:

- Running queries on a column that is not a part of the primary key
- Pre-aggregating columns, it will reduce both computation and IO

You can define one or more projections for a table, and during the query analysis the projection with the least data to scan will be selected by ClickHouse without modifying the query provided by the user.

Disk usage

Projections will create internally a new hidden table, this means that more IO and space on disk will be required.
For example, if the projection has defined a different primary key, all the data from the original table will be duplicated.

You can see more technical details about how projections work internally on this [page](/docs/guides/best-practices/sparse-primary-indexes#option-3-projections).

## Using projections[​](#examples "Direct link to Using projections")

### Example filtering without using primary keys[​](#example-filtering-without-using-primary-keys "Direct link to Example filtering without using primary keys")

Creating the table:

```
CREATE TABLE visits_order
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent
```

Using `ALTER TABLE`, we could add the Projection to an existing table:

```
ALTER TABLE visits_order ADD PROJECTION user_name_projection (
    SELECT *
    ORDER BY user_name
)

ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection
```

Inserting the data:

```
INSERT INTO visits_order SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);
```

The Projection will allow us to filter by `user_name` fast even if in the original Table `user_name` was not defined as a `PRIMARY_KEY`.
At query time, ClickHouse determines that less data will be processed if the projection is used, as the data is ordered by `user_name`.

```
SELECT
    *
FROM visits_order
WHERE user_name='test'
LIMIT 2
```

To verify that a query is using the projection, we could review the `system.query_log` table. On the `projections` field we have the name of the projection used or empty if none has been used:

```
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
```

### Example pre-aggregation query[​](#example-pre-aggregation-query "Direct link to Example pre-aggregation query")

Create the table with projection `projection_visits_by_user`:

```
CREATE TABLE visits
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String,
   PROJECTION projection_visits_by_user
   (
       SELECT
           user_agent,
           sum(pages_visited)
       GROUP BY user_id, user_agent
   )
)
ENGINE = MergeTree()
ORDER BY user_agent
```

Insert the data:

```
INSERT INTO visits SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);
```

```
INSERT INTO visits SELECT
    number,
    'test',
    1. * (number / 2),
   'IOS'
FROM numbers(100, 500);
```

Execute a first query with `GROUP BY` using the field `user_agent`.
This query will not use the projection defined as the pre-aggregation does not match.

```
SELECT
    user_agent,
    count(DISTINCT user_id)
FROM visits
GROUP BY user_agent
```

To make use of the projection you can execute queries that select part of, or all of the pre-aggregation and `GROUP BY` fields:

```
SELECT
    user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent
```

```
SELECT
    user_agent,
    sum(pages_visited)
FROM visits
GROUP BY user_agent
```

As previously mentioned, you can review the `system.query_log` table to understand if a projection was used.
The `projections` field shows the name of the projection used.
It will be empty if no projection has been used:

```
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
```

### Creating and using projection indexes[​](#projection-indexes "Direct link to Creating and using projection indexes")

Creating a [projection index](/docs/engines/table-engines/mergetree-family/mergetree#projection-index):

```
CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id INDEX user_id TYPE basic
)
ENGINE = MergeTree()
ORDER BY (event_id);
```

Creating a projection with explicit `_part_offset` field

Projection indexes can alternatively be created using the following syntax (not recommended):

```
CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id
    (
        SELECT
            _part_offset
        ORDER BY user_id
    )
)
ENGINE = MergeTree()
ORDER BY (event_id);
```

Inserting some sample data:

```
INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;
```

The `_part_offset` field preserves its value through merges and mutations, making it valuable for secondary indexing. We can leverage this in queries:

```
SELECT
    count()
FROM events
WHERE _part_starting_offset + _part_offset IN (
    SELECT _part_starting_offset + _part_offset
    FROM events
    WHERE user_id = 42
)
SETTINGS enable_shared_storage_snapshot_in_query = 1
```

## Manipulating projections[​](#manipulating-projections "Direct link to Manipulating projections")

The following operations with [projections](/docs/engines/table-engines/mergetree-family/mergetree#projections) are available:

### ADD PROJECTION[​](#add-projection "Direct link to ADD PROJECTION")

Use the statement below to add a projection description to a tables metadata:

```
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]
```

#### `WITH SETTINGS` Clause[​](#with-settings "Direct link to with-settings")

`WITH SETTINGS` defines **projection-level settings**, which customize how the projection stores data (for example, `index_granularity` or `index_granularity_bytes`).
These correspond directly to **MergeTree table settings**, but apply **only to this projection**.

Example:

```
ALTER TABLE t
ADD PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 4096,
    index_granularity_bytes = 1048576
);
```

Projection settings override the effective table settings for the projection, subject to validation rules (e.g., invalid or incompatible overrides will be rejected).

### DROP PROJECTION[​](#drop-projection "Direct link to DROP PROJECTION")

Use the statement below to remove a projection description from a tables metadata and delete projection files from disk.
This is implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

```
ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name
```

### MATERIALIZE PROJECTION[​](#materialize-projection "Direct link to MATERIALIZE PROJECTION")

Use the statement below to rebuild the projection `name` in partition `partition_name`.
This is implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

```
ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
```

### CLEAR PROJECTION[​](#clear-projection "Direct link to CLEAR PROJECTION")

Use the statement below to delete projection files from disk without removing description.
This is implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

```
ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
```