Course Overview
ClickHouse Database Objects

Views

Lesson #3

In this lesson we will:

  • Learn about ClickHouse views and how they are defined and managed in a ClickHouse database.

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.

Next Lesson:
03

Materialised Views

In this lesson we will introduce materialised views, and demonstrate how they are more powerful than materialsied views as implemented by other databases.

0h 15m




Work With The Experts In Real-Time Analytics & AI

we help enterprise organisations deploy powerful real-time Data, Analytics and AI solutions based on ClickHouse, the worlds fastest open-source database.

Join our mailing list for regular insights:

We help enterprise organisations deploy advanced data, analytics and AI enabled systems based on modern cloud-native technology.

© 2024 Ensemble. All Rights Reserved.