Views

ClickHouse Views

A view is a virtual or logical table created by a query that can be used to present data from one or more tables in a structured and easily accessible manner.

Views do not store data themselves. Instead, they are based on the data stored in the underlying tables.

In ClickHouse, we create a view with the CREATE VIEW statement:

CREATE VIEW airport_trips_with_high_tip AS
SELECT
    trip_id,
    pickup_datetime,
    pickup_ntaname,
    dropoff_ntaname
FROM trips
WHERE (dropoff_ntaname = 'Airport') AND (tip_amount > 100)

Query id: 288d5bd1-1172-4642-a9b4-99665f303a21

Ok.

0 rows in set. Elapsed: 0.085 sec.

We can then select from the view in the same way we would select from the table.

SELECT *
FROM airport_trips_with_high_tip

Query id: c04f1421-77f8-4404-9161-e6c16272a079

┌────trip_id─┬─────pickup_datetime─┬─pickup_ntaname────────────────┬─dropoff_ntaname─┐
│ 1215839820 │ 2015-09-18 12:33:40 │ Upper East Side-Carnegie Hill │ Airport         │
└────────────┴─────────────────────┴───────────────────────────────┴─────────────────┘

1 row in set. Elapsed: 0.036 sec. Processed 3.00 million rows, 15.08 MB (83.19 million rows/s., 418.01 MB/s.)
Peak memory usage: 40.57 MiB.

A standard view does not store any data, but instead queries the underlying table each time the view is accessed. This means they do not necessarily give any performance benefit. They are simply a convenience function.

We also have the option of materialising a view onto disk. In this instance, the results of the query are pre-computed and stored on disk ready for fast lookup. Materialized views are described in more detail in the next lesson.