Parameterized Views in Clickhouse

  

Parameterized views are templated views that accept query parameters. Unlike normal views, which are static, parameterized views behave like functions, allowing you to pass arguments (e.g., filters, limits) when querying them.

 

This is especially useful for:

 

·      Building dashboards with dynamic filters.

·      Reusing one view for multiple parameter sets.

·      Keeping logic DRY (Don’t Repeat Yourself).

 

Syntax

CREATE VIEW db_name.view_name AS
SELECT ...
FROM ...
WHERE column = {param_name:Type}

   

Follow below step by step procedure to define a parameterized view.

 

Step 1: Create a Database and Table

 

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE users (
    id UInt32,
    name String,
    age UInt8,
    country String
) ENGINE = MergeTree()
ORDER BY id;

krishna :) CREATE DATABASE IF NOT EXISTS demo_db;

CREATE DATABASE IF NOT EXISTS demo_db

Query id: 7304f773-e16a-4645-bed7-49d4a775cf53

Ok.

0 rows in set. Elapsed: 0.006 sec. 

krishna :) ;

Empty query

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

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

Query id: f71f3850-5d82-4ec3-8ff9-dee6a6737803

Ok.

0 rows in set. Elapsed: 0.008 sec. 

krishna :) 

Step 2: Insert Sample Data

INSERT INTO demo_db.users VALUES
(1, 'Ram', 31, 'India'),
(2, 'Krishna', 28, 'Canada'),
(3, 'Chamu', 31, 'India'),
(4, 'Sailu', 24, 'UK'),
(5, 'Gopi', 35, 'India'),
(6, 'Joel', 32, 'Canada'),
(7, 'Naveed', 41, 'India'),
(8, 'Jaideep', 43, 'USA');

krishna :) INSERT INTO demo_db.users VALUES
(1, 'Ram', 31, 'India'),
(2, 'Krishna', 28, 'Canada'),
(3, 'Chamu', 31, 'India'),
(4, 'Sailu', 24, 'UK'),
(5, 'Gopi', 35, 'India'),
(6, 'Joel', 32, 'Canada'),
(7, 'Naveed', 41, 'India'),
(8, 'Jaideep', 43, 'USA');

INSERT INTO demo_db.users FORMAT Values

Query id: ee9c37bd-c05d-433b-9a26-9ce79fc4bdf0

Ok.

8 rows in set. Elapsed: 0.012 sec.

   

Step 3: Print the data from users table.

 

krishna :) SELECT * FROM demo_db.users;

SELECT *
FROM demo_db.users

Query id: 965090e6-7b54-4571-b0ad-76d3d7a2fb0e

   ┌─id─┬─name────┬─age─┬─country─┐
1. │  1 │ Ram     │  31 │ India   │
2. │  2 │ Krishna │  28 │ Canada  │
3. │  3 │ Chamu   │  31 │ India   │
4. │  4 │ Sailu   │  24 │ UK      │
5. │  5 │ Gopi    │  35 │ India   │
6. │  6 │ Joel    │  32 │ Canada  │
7. │  7 │ Naveed  │  41 │ India   │
8. │  8 │ Jaideep │  43 │ USA     │
   └────┴─────────┴─────┴─────────┘

8 rows in set. Elapsed: 0.005 sec.

   

Step 4: Create a Parameterized View

 

CREATE VIEW demo_db.user_filter_view AS
SELECT id, name, age, country
FROM demo_db.users
WHERE age > {min_age:UInt8} AND country = {country_name:String};

krishna :) CREATE VIEW demo_db.user_filter_view AS
SELECT id, name, age, country
FROM demo_db.users
WHERE age > {min_age:UInt8} AND country = {country_name:String};

CREATE VIEW demo_db.user_filter_view
AS SELECT
    id,
    name,
    age,
    country
FROM demo_db.users
WHERE (age > {min_age:UInt8}) AND (country = {country_name:String})

Query id: eb041e16-0222-4c3a-85a1-3d670c537104

Ok.

0 rows in set. Elapsed: 0.005 sec.

   

Step 4: Query the View with Parameters

 

SELECT * FROM demo_db.user_filter_view(min_age = 27, country_name = 'India');

   

Above query return all the users who are from India and whose age is > 27.

 

krishna :) SELECT * FROM demo_db.user_filter_view(min_age = 27, country_name = 'India');

SELECT *
FROM demo_db.user_filter_view(min_age = 27, country_name = 'India')

Query id: ba8c4fa1-0c18-4e82-8d1a-5e141e12ac11

   ┌─id─┬─name───┬─age─┬─country─┐
1. │  1 │ Ram    │  31 │ India   │
2. │  3 │ Chamu  │  31 │ India   │
3. │  5 │ Gopi   │  35 │ India   │
4. │  7 │ Naveed │  41 │ India   │
   └────┴────────┴─────┴─────────┘

4 rows in set. Elapsed: 0.008 sec.

SELECT * FROM demo_db.user_filter_view(min_age = 35, country_name = 'India');

   

Above query return all the users who are from India and whose age is > 35.

 

krishna :) SELECT * FROM demo_db.user_filter_view(min_age = 35, country_name = 'India');

SELECT *
FROM demo_db.user_filter_view(min_age = 35, country_name = 'India')

Query id: 26245fe7-2bfe-48c7-8bc9-bb009d672d0d

   ┌─id─┬─name───┬─age─┬─country─┐
1. │  7 │ Naveed │  41 │ India   │
   └────┴────────┴─────┴─────────┘

1 row in set. Elapsed: 0.006 sec.

 

  

Previous                                                    Next                                                    Home