Use projections

A projection stores another layout for a table. It uses extra storage and increases insert and merge work.

Before you begin

• Use the analytics database. Create it with the database API if it does not exist.
• Use an events table with tenant and event_time paths.

1. Add a projection

Add a layout that stores counts by tenant.

ALTER TABLE events ADD PROJECTION by_tenant (SELECT `__raw_data.tenant`, count() GROUP BY `__raw_data.tenant`)

2. Materialize the projection

Populate the projection for existing table parts.

ALTER TABLE events MATERIALIZE PROJECTION by_tenant

3. Inspect projection parts

Check whether table parts contain the projection.

SELECT count() FROM system.projection_parts WHERE database = currentDatabase() AND table = 'events' AND name = 'by_tenant'

4. Check projection mutations

Repeat this query until it returns 0.

SELECT count() FROM system.mutations WHERE database = currentDatabase() AND table = 'events' AND command LIKE '%PROJECTION by_tenant%' AND is_done = 0

5. Clear projection data

Clear stored projection data and keep its definition.

ALTER TABLE events CLEAR PROJECTION by_tenant

Repeat the mutation query until it returns 0.

6. Drop the projection

Drop the projection when you no longer need its layout.

ALTER TABLE events DROP PROJECTION by_tenant

Next steps

• Projection concepts
• ALTER TABLE statements