In this lesson we will:

  • Learn about some of the core database objects in Clickhouse including tables, views, functions, users and roles.

Database Objects

In Clickhouse and other database, our work as administrators often involves managing objects such as tables, views, functions, users and roles.

Each of these objects are managed with a consistent lifecycle and set of commands such as CREATE, ALTER and DELETE, giving us some consistency in the mental model of how we manage the database.


Tables are of course the central concept of any relational database. They can be created using the CREATE table command with a series of typed column names:

create table pizza_ingredients( 
  id integer, description varchar,  price integer );

Tables of course have data inserted and manipulated with insert, update and delete statements.

The table can be dropped on demand using the drop command:

drop table pizza_ingredients;


Views provide a convenience function for encapsulating common queries as a database object. They do not store data, but instead execute the defined query against the underlying table each time the view is accessed.

Views can be created by specifying the underlying query in the as clause:

create view high_priced_ingredients as 
( select * from pizza_ingredients where price > 90 )

Similar to tables, the view can be dropped using the drop command:

drop view high_priced_ingredients;


Clickhouse users are the real world people or processes which are interacting with your Clickhouse instance. It is of course best practice to issue users with their own usernames and passwords for auditing and control purposes:

Users can created with the following SQL statement:

CREATE USER benjaminwootton 
IDENTIFIED WITH sha256_password BY 'qwerty';

And as per the other database objects, can be dropped:



Roles are used to control access to database objects and data through a Role Based Access Control system. A given role gets assigned a number of permissions, such as the ability to query a table, and then users are granted the role.

CREATE ROLE accountant;
GRANT SELECT ON db.* TO accountant;

And as per the other database objects, can be dropped in the following way:

DROP ROLE accountant;


Quotas allow us to apply limits to what individual users or roles can do with the database. For instance, we may wish to limit the number of queries that certain users or groups of users can do in a given time window, to ensure they do not overload the database.


And dropped in the same way:

drop quota Q1


Functions allow you to create simple pieces of business logic in SQL which can be executed inline within queries.

For instance, we can create a function called odd_or_even which takes an integer and returns odd or even in the following way.

CREATE FUNCTION odd_or_even AS (n) -> if(n % 2, 'odd', 'even');

This can then be used inline in a SQL statement:

SELECT odd_or_even(3)



It is also possible to define external functions in a programming language such as Java which can be incorporated into your query. This will be defined in a subsequent lesson.


Dictionaries are sets of key and value pairs which are used as part of your queries, such as:

M --> Male
F --> Female
AMER --> America
EMEA --> Europe, Middle East, Asia

Clickhouse provides a special object type for Dictionaries in order to provide some more efficient optimisations rather than simply creating and joining to lookup tables:

create dictionary genders ( )

Information Schema

Clickhouse includes a database called the Information Schema, which provides metadata about our database objects.

select * from information_schema.tables

Informations on views can be queried from:

select * from information_schema.views
Next Lesson:

Administering Clickhouse Users and Roles

In this lesson we will look into the Clickhouse users, role and permission and security model which is used to control access to data within.

0h 10m

Continuous Delivery For Data Engineers

This site has been developed by the team behind Timeflow, an Open Source CI/CD platform designed for Data Engineers who use dbt as part of the Modern Data Stack. Our platform helps Data Engineers improve the quality, reliability and speed of their data transformation pipelines.

Join our mailing list for our latest insights on Data Engineering:

Timeflow Academy is the leading online, hands-on platform for learning about Data Engineering using the Modern Data Stack. Bought to you by Timeflow CI

© 2023 Timeflow Academy. All rights reserved