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 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 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;
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 oddor_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:
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 ( )
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 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;
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;
In this lesson, we created basic database objects within Clickhouse, including tables, views, functions and dictionaries. We demonstrated the consistent pattern for creating, altering and dropping these objects.
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.