How to CREATE VIEW in ClickHouse

Galaxy Glossary

How do I use CREATE VIEW in ClickHouse to build reusable queries?

CREATE VIEW stores a reusable SELECT query as a virtual table, optimizing reusability and security without duplicating data.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What problem does CREATE VIEW solve?

CREATE VIEW lets you package complex SELECT logic behind a simple table-like object. Analysts query the view as if it were a table, keeping business logic in one place and preventing code duplication.

How do I write the CREATE VIEW statement?

Use CREATE VIEW view_name AS SELECT ... . Supply column aliases when expressions are unclear. Add OR REPLACE to update an existing view without dropping it first.

Can I parameterize views for ecommerce reporting?

Yes. For example, you can join Orders, OrderItems and Products to expose revenue metrics. Users can then add WHERE filters when querying the view.

When should I choose MATERIALIZED VIEW instead?

Regular views run the underlying SELECT at query time. If performance is critical and data latency can be tolerated, switch to MATERIALIZED VIEW to store pre-computed results.

Best practices for CREATE VIEW

Prefix view names with business context, e.g., sales_ or customer_.
Document column meanings in comments.
Keep views small; chain views rather than creating monoliths.
Grant SELECT only on views to protect raw tables.

Example: daily revenue view

The example below shows how to expose daily revenue per product using familiar ecommerce tables.

Why How to CREATE VIEW in ClickHouse is important

How to CREATE VIEW in ClickHouse Example Usage


-- Querying the view for the last 7 days
SELECT *
FROM   daily_product_revenue
WHERE  order_day >= today() - 7
ORDER  BY daily_revenue DESC;

How to CREATE VIEW in ClickHouse Syntax


CREATE [OR REPLACE] VIEW view_name [ (column1, column2, ...) ] AS
SELECT column_list
FROM source_table
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY expression];

-- Example in ecommerce context
CREATE OR REPLACE VIEW daily_product_revenue AS
SELECT   p.id   AS product_id,
         p.name AS product_name,
         toDate(o.order_date) AS order_day,
         sum(oi.quantity * p.price) AS daily_revenue
FROM     Orders      o
JOIN     OrderItems  oi ON o.id = oi.order_id
JOIN     Products    p  ON p.id = oi.product_id
GROUP BY product_id, product_name, order_day;

Common Mistakes

Skipping OR REPLACE and getting an error when rerunning scripts. Fix: add OR REPLACE to make your deployment idempotent.
Using SELECT * inside views, causing unexpected column drift when tables evolve. Fix: list columns explicitly with aliases.

Frequently Asked Questions (FAQs)

Is a ClickHouse view stored physically?

No. Regular views are virtual. The underlying SELECT re-executes on each query, so storage is minimal.

Can I index a view?

You cannot add indexes directly. Optimize the underlying tables or switch to a MATERIALIZED VIEW for faster reads.

How do I drop a view?

Run DROP VIEW [IF EXISTS] view_name;. Add IF EXISTS to avoid errors if the view is already gone.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie