Views in Clickhouse

  

In ClickHouse, a view is a virtual table that’s defined by a query. It doesn’t store data itself (except for materialized views, which do store data). It’s useful for abstraction, reuse, or simplifying complex queries.

 

ClickHouse supports three types of views:

 

·      View: Just a saved query, doesn't store data.

·      Parameterized View: A templated view that accepts parameters.

·      Materialized View: Stores query result physically, can be used for performance optimization.

 

Syntax to create a view

CREATE VIEW [IF NOT EXISTS] db_name.view_name AS
SELECT column1, column2, ...
FROM db_name.table_name
WHERE condition;

Following step-by-step procedure helps you to create a view and experiment with it.

 

Step 1: Create a database demo_db.

CREATE DATABASE IF NOT EXISTS demo_db;

krishna :) CREATE DATABASE IF NOT EXISTS demo_db;

CREATE DATABASE IF NOT EXISTS demo_db

Query id: e166928f-8c32-42c8-82da-41bfbc34a6b5

Ok.

0 rows in set. Elapsed: 0.006 sec.

   

Step 2: Create users table.

 

CREATE TABLE IF NOT EXISTS demo_db.users (
    id UInt32,
    name String,
    age UInt8,
    country String
) ENGINE = MergeTree()
ORDER BY id;

krishna :) CREATE TABLE IF NOT EXISTS demo_db.users (
    id UInt32,
    name String,
    age UInt8,
    country String
) ENGINE = MergeTree()
ORDER BY id;

CREATE TABLE IF NOT EXISTS demo_db.users
(
    `id` UInt32,
    `name` String,
    `age` UInt8,
    `country` String
)
ENGINE = MergeTree
ORDER BY id

Query id: 3096a9c0-86ce-497b-911e-c3f47d5f9ddb

Ok.

0 rows in set. Elapsed: 0.007 sec.

   

Step 3: Insert sample data.

 

INSERT INTO demo_db.users VALUES
(1, 'Ram', 30, 'India'),
(2, 'Krishna', 25, 'India'),
(3, 'Gopi', 35, 'Canada'),
(4, 'Sai', 28, 'USA'),
(5, 'Kalpana', 32, 'USA');

krishna :) INSERT INTO demo_db.users VALUES
(1, 'Ram', 30, 'India'),
(2, 'Krishna', 25, 'India'),
(3, 'Gopi', 35, 'Canada'),
(4, 'Sai', 28, 'USA'),
(5, 'Kalpana', 32, 'USA');

INSERT INTO demo_db.users FORMAT Values

Query id: b4931a61-ae1c-4136-8cc6-d2c6abab9588

Ok.

5 rows in set. Elapsed: 0.014 sec. 

   

Step 4: Print the records of users table.

 

krishna :) SELECT * FROM demo_db.users;

SELECT *
FROM demo_db.users

Query id: 730b07f7-33e0-4bc6-8e7e-9139a41bd06e

   ┌─id─┬─name────┬─age─┬─country─┐
1. │  1 │ Ram     │  30 │ India   │
2. │  2 │ Krishna │  25 │ India   │
3. │  3 │ Gopi    │  35 │ Canada  │
4. │  4 │ Sai     │  28 │ USA     │
5. │  5 │ Kalpana │  32 │ USA     │
   └────┴─────────┴─────┴─────────┘

5 rows in set. Elapsed: 0.003 sec.

   

Step 6: Create a View

This view shows only users from India.

 

CREATE VIEW demo_db.india_users AS
SELECT id, name, age
FROM demo_db.users
WHERE country = 'India';

krishna :) CREATE VIEW demo_db.india_users AS
SELECT id, name, age
FROM demo_db.users
WHERE country = 'India';

CREATE VIEW demo_db.india_users
AS SELECT
    id,
    name,
    age
FROM demo_db.users
WHERE country = 'India'

Query id: bcb38ae9-45eb-4116-bcb5-d5d0c6614843

Ok.

0 rows in set. Elapsed: 0.007 sec. 

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.india_users;

SELECT *
FROM demo_db.india_users

Query id: be3a7b90-117b-4200-8745-0c871e3d41cc

   ┌─id─┬─name────┬─age─┐
1. │  1 │ Ram     │  30 │
2. │  2 │ Krishna │  25 │
   └────┴─────────┴─────┘

2 rows in set. Elapsed: 0.004 sec.

 

  

Previous                                                    Next                                                    Home