Introduction

ClickHouse Database Objects

A ClickHouse database is made up of a number of objects such as tables, views, users, roles, functions and dictionaries.

It is the responsibility of the ClickHouse developer or administrator to create and configure these objects in order to together define your database, data structures and associated business logic.

Object Lifecycle

Most objects in ClickHouse go through a similar lifecycle where they are created, altered and potentially dropped when no longer used. This gives a certain degree of consistency in how we define and manage our database and makes it easier to navigate.

First we can create objects using a CREATE statement. For instance, a table would be created like this:

CREATE TABLE orders
(
    `order_id` integer,
    `value` float
)
ENGINE = MergeTree
ORDER BY order_id

Whilst a database user would be created like this:

CREATE USER benjaminwootton IDENTIFIED BY 'mypassword'

Next, we can alter the object using the ALTER SQL statement:

ALTER TABLE orders
    ADD COLUMN `category` String

With the same pattern for users:

ALTER USER benjaminwootton IDENTIFIED BY 'goldfish'

Finally, we can DROP the objects if and when they are no longer needed.

DROP TABLE orders

DROP USER benjaminwootton

These operations are referred to as "data definition language" or DDL statements, because they are defining the structure or definition of our data and associated objects.

Access Rights

Only certain users in your ClickHouse instance will have access rights to manage these object lifecycles, and this can be managed in a finely controlled way:

GRANT CREATE TABLE ON default TO benjaminwootton;
GRANT ALTER TABLE ON default TO benjaminwootton;
GRANT DROP TABLE ON default TO benjaminwootton;

The ability to create users is slightly differently, but the same pattern can be applied to other objects such as dictionaries:

GRANT CREATE DICTIONARY ON default TO benjaminwootton;
GRANT ALTER DICTIONARY ON default TO benjaminwootton;
GRANT DROP DICTIONARY ON default TO benjaminwootton;

Once permissions have been granted, they can also be revoked from the user:

REVOKE CREATE TABLE ON default FROM benjaminwootton;
REVOKE ALTER TABLE ON default FROM benjaminwootton;
REVOKE DROP TABLE ON default FROM benjaminwootton;

Again, this mental model of granting and revoking to and from objects gives us a consistent mental model which makes it easier to work with ClickHouse.