Administering Database Objects

Lesson Overview

In this lesson we will learn about some of the core database objects in Clickhouse including tables, views, functions, users and roles. This will be a quick overview over each of the main categories, many of which will be described in more detail in subsequent lessons.

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 );

The table can be dropped using the drop command:

drop table pizza_ingredients;

Views

Views provide a convenience function for executing common queries. 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 like so:

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

The view can be dropped using the drop command:

drop view high_priced_ingredients;

Functions

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

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

It is also possible to define external functions in a programming language which can be incorporated into your query.

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 ( )

Users

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

CREATE USER benjaminwootton IDENTIFIED WITH sha256_password BY 'qwerty';

Roles

Roles are part of the permission. A given role gets assigned a number of permissions, such as the ability to query a , and then users are granted the role.

CREATE ROLE accountant;
GRANT SELECT ON db.* TO 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.

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

Summary

In this lesson, we created basic database objects within Clickhouse and showed the consistent pattern for creating, altering and dropping them.

In the next lesson, we will look at the role based access control and permission model within Clickhouse which controls how users can access and interact with these objects.

This Post Requires A Membership

Sign Up

Already A Member? Log In

prevnext

© 2022 Timeflow Academy. Bought To You By Timeflow.