Projections in ClickHouse

Projections in ClickHouse are commonly used to speed up the queries on the MergeTree table at the cost of storage. The speedup achieved is due to how the data is stored (different from the original table). Projections will create a new hidden table with the data based on the CREATE statement. This will cause increased storage requirements compared to the original table. Let us look at managing projections in ClickHouse.

Let us begin by creating a MergeTree table for which we will add a projection.

CREATE TABLE projections_mergetree
(
   `ID` UInt64,
   `Name` String,
   `Value` Float64,
   `phone` String
)
ENGINE = MergeTree()
ORDER BY ID;

Let us create a projection with different sorting keys.

ALTER TABLE projections_mergetree ADD PROJECTION projections_mergetree_projection1 (
SELECT * ORDER BY phone);

This will add a new projection called projections_mergetree_projection1 to the table. Next, we will materialize the projection.

ALTER TABLE projections_mergetree MATERIALIZE PROJECTION projections_mergetree_projection1;

We can use the CLEAR PROJECTION statement to clear the files without deleting the projection files.

ALTER TABLE projections_mergetree CLEAR PROJECTION projections_mergetree_projection1;

Finally, we can use the DROP PROJECTION statement to delete this projection altogether.

ALTER TABLE projections_mergetree DROP PROJECTION projections_mergetree_projection1;

References

https://percona.community/events/percona-live/altinity-room/1/

https://clickhouse.com/docs/en/sql-reference/statements/alter/projection