Database Objects

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

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

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;

Users

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:

DROP USER BENJAMINWOOTTON;

Roles

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

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.

CREATE QUOTA Q1 FOR INTERVAL 15 month MAX queries = 100 TO CURRENT_USER;

And dropped in the same way:

drop quota Q1

Functions

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)

Outputs:

odd

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

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