ClickHouse is a column-oriented database management system developed by ClickHouse, Inc. and written in C++. It’s an Open Source OLAP database management system that is fast, fault-tolerant, easy to use, and highly reliable.
ClickHouse is a blazingly fast OLAP database system for online analytical processing. It allows you to generate analytical reports using SQL queries in real-time.
In this tutorial, you will learn how to install the ClickHouse OLAP Database System on an Ubuntu 22.04 server. You’ll also learn how to set up authentication on ClickHouse and cover its basic usage and queries.
Prerequisites
To complete this tutorial, ensure that your system meets the prerequisites below:
- An Ubuntu 22.04 server – This example uses an Ubuntu system with the hostname ‘clickhouse-server’ and the IP address ‘192.168.5.100’.
- A non-root user with sudo/root administrator privileges.
Preparing the System
Before starting the ClickHouse installation, ensure that the current CPU supports the SSE 4.2 instruction set and disable SWAP on your system.
The official pre-built binary package for ClickHouse is compiled on the x86 (64-bit) architecture and requires SSE 4.2 support. If your CPU doesn’t support SSE 4.2, you’ll need to build ClickHouse manually from the source.
Additionally, it’s recommended to disable SWAP, especially in production environments.
Run the command below to verify that your CPU supports the SSE 4.2 feature. You should receive the output ‘SSE 4.2 supported’ if your CPU is supported.
grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"
Next, run the command below to disable SWAP via the ‘/etc/fstab‘ configuration file. This command comments out the SWAP entry, effectively disabling it permanently.
sudo sed -i '/ swap / s/^\(.*\)$/#\1/g' /etc/fstab
Turn off SWAP for the current session and verify its status with the following commands:
sudo swapoff -a sudo free -m
If SWAP is disabled, the ‘total’ field for SWAP size will be 0.
With a CPU that supports SSE 4.2 and SWAP disabled, you’re ready to install the ClickHouse OLAP Database System.
Installing ClickHouse OLAP Database System
ClickHouse OLAP Database System offers multiple installation methods. For Linux distributions, ClickHouse provides repositories for both Debian-based and RPM-based systems. Additionally, you can install ClickHouse using Docker or a single binary package.
In this guide, you will install ClickHouse on an Ubuntu 22.04 system via the official repository. First, add the ClickHouse repository to your system and install the package using APT.
Start by installing essential dependencies with the command below. Confirm the installation by entering ‘y’ when prompted.
sudo apt install apt-transport-https ca-certificates dirmngr
Next, add the GPG key and ClickHouse repository with the following commands. This example uses the stable branch of the repository.
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \ /etc/apt/sources.list.d/clickhouse.list
Update your package index with the command below:
sudo apt update
You should see that the ClickHouse repository has been added.
Now, install the ‘clickhouse-server’ package as the database system and the ‘clickhouse-client’ package using the following command:
sudo apt install clickhouse-server clickhouse-client
Confirm the installation by entering ‘y’ when prompted and pressing ENTER to proceed.
During the ClickHouse installation, you’ll be prompted to set a password for the default user. Enter a new password, and you should see the following screen upon completion.
With ClickHouse installed, use the command below to start and enable the ClickHouse service. This ensures that the service starts automatically with each boot.
sudo systemctl start clickhouse-server sudo systemctl enable clickhouse-server
Verify the ClickHouse service status with:
sudo systemctl status clickhouse-server
The ClickHouse service should be running and enabled to start at boot, as shown in the output below.
You’ve completed the ClickHouse server installation. Next, you’ll learn how to access the ClickHouse server via the client application and set up password authentication for the ClickHouse administrator user.
Accessing ClickHouse via Command Line
With the ClickHouse server installed, you can access it using the ‘clickhouse-client,’ which you have also installed.
The ‘clickhouse-client’ is a command-line interface for interacting with and managing the ClickHouse server, similar to ‘mysql’ for MySQL/MariaDB, ‘psql’ for PostgreSQL, or ‘mongod’ for MongoDB.
To access the ClickHouse server, use the command below:
sudo clickhouse-client --password
When prompted, enter the password created during the installation.
Upon logging in, you’ll see the ClickHouse shell as shown below:
Here, you can see that you’re connected to the ClickHouse server, version 22.11, with the default user. The server runs on localhost, port 9000.
At this point, you’ve installed the ClickHouse server and client and connected to the server using the ‘clickhouse-client’ command. Next, you’ll set up an administrator user for the ClickHouse server.
Setting up an Administrator User on ClickHouse
You’ll now create an administrator user for the ClickHouse server. Before doing so, enable ‘Account Management’ for the ‘default’ user through the ClickHouse configuration.
Open the configuration file ‘/etc/clickhouse-server/users.d/default_password.xml‘ using the command below:
sudo nano /etc/clickhouse-server/users.d/default_password.xml
Add the following line inside the ‘<default>…</default>‘ section to enable ‘Access Management‘ for the ‘default’ user:
<clickhouse> <users> <default> ... <access_management>1</access_management> </default> </users> </clickhouse>
Save the file and exit the editor.
Restart the ClickHouse service to apply changes using:
sudo systemctl restart clickhouse-server
With ‘access_management’ enabled for the ‘default’ user, create a new administrator user for ClickHouse, who will have full access to all databases.
Connect to the ClickHouse server as the ‘default’ user:
sudo clickhouse-client --user default --password
After entering your password, you’ll access the ClickHouse server console:
Create a new ClickHouse user with the following query. Make sure to change the username, password, and host IP address according to your requirements. This example creates user ‘halsey‘ with a SHA256-hashed password ‘password’, accessible only from localhost (‘127.0.0.1’).
CREATE USER halsey HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'password';
Grant all privileges on the ClickHouse server to the user ‘halsey‘:
GRANT ALL ON *.* TO halsey WITH GRANT OPTION;
Verify the list of users on the ClickHouse server with the query below. You should see ‘halsey’ added to the list:
SHOW USERS;
Type ‘quit’ in the ClickHouse client console to exit.
With the new database admin user created, disable ‘access_management’ for the ‘default‘ user on your ClickHouse server.
Edit the configuration file ‘/etc/clickhouse-server/users.d/default_password.xml‘ again:
sudo nano /etc/clickhouse-server/users.d/default_password.xml
Change the ‘access_management‘ value to ‘0‘ to disable it for the ‘default’ user.
<clickhouse> <users> <default> ... <access_management>0</access_management> </default> </users> </clickhouse>
Save the changes and exit the editor.
Restart the ClickHouse service to apply the changes:
sudo systemctl restart clickhouse-server
Finally, log in to the ClickHouse server as user ‘halsey’. Enter the password when prompted, and press ENTER to log in:
sudo clickhouse-client --user halsey --password
After logging in, you should see a message confirming the successful login of the new admin user ‘halsey’:
With the new ClickHouse server admin user created, you will now learn to use ClickHouse for creating databases and tables, as well as inserting and retrieving data with SQL queries.
Basic Queries on ClickHouse Database System
In this section, you will explore the basic queries used in the ClickHouse Database System, similar to standard SQL commands. You’ll learn how to create databases and tables, insert and retrieve data, and explore the basic use of the ‘ALTER’ command to modify existing records on the ClickHouse server.
First, create a new database with the query below. Here, we’re creating a database named ‘testdb‘:
CREATE DATABASE testdb;
Switch to ‘testdb‘ using the ‘USE dbname’ command:
USE testdb;
Now, create a new table, ‘users,’ with the structure defined below:
id: UInt64 for storing integer values.name: String for storing text data.jobs: String for storing job titles.last_login: DateTime for storing date-time values.- The table engine is ‘MergeTree’, which is often used in ClickHouse.
CREATE TABLE users (id UInt64, name String, jobs String, last_login DateTime) ENGINE=MergeTree() PRIMARY KEY id ORDER BY id;
Next, insert new data into the ‘users’ table with the following queries. The examples below insert three different records into the ‘users’ table:
INSERT INTO users VALUES (1, 'alice', 'DevOps', '2022-10-10 00:10:10'); INSERT INTO users VALUES (2, 'alex', 'Manager', '2022-09-05 01:19:10'); INSERT INTO users VALUES (3, 'janet', 'Developer', '2022-05-05 04:29:10');
Insert the first record:
Insert the second record:
Insert the third record:
Retrieve records from the ‘users‘ table using the following query. You should see the three records you just added:
SELECT * FROM users;
You’ve learned how to create databases and tables, insert data, and retrieve that data in the ClickHouse OLAP Database System. Now, let’s learn to use the ‘ALTER’ command to modify existing data.
Execute the following ‘ALTER TABLE’ command to update data in the ‘users’ table. Here, we change the field ‘jobs’ for user ‘alex‘ to ‘Administrator‘:
ALTER TABLE users UPDATE jobs = 'Administrator' WHERE name = 'alex';
Verify the updated data in the ‘users’ table with the following query. The ‘jobs’ field for user ‘alex‘ should now display ‘Administrator‘:
SELECT * FROM users;
Now that you’ve learned the basic queries for creating and switching databases, creating tables, inserting data, and retrieving that data in ClickHouse, you also know how to use the ‘ALTER’ query to change existing data in a ClickHouse database. Next, you’ll clean up the ClickHouse setup.
Clean-up Environment
With the new database and tables created and sample data inserted, you’ll now clean up the ClickHouse Database System by deleting the database and tables created.
Remove the ‘users’ table from the ‘testdb’ database with the following ‘DROP TABLE’ command:
DROP TABLE users;
Drop the ‘testdb’ database using the ‘DROP DATABASE’ command:
DROP DATABASE testdb;
Verify that the ‘testdb’ database is deleted from the ClickHouse server with the command below:
SHOW DATABASES;
Your ClickHouse installation is now clean, with the admin user configured.
Conclusion
Congratulations! You’ve completed the installation of the ClickHouse OLAP Database System and learned how to set up an administrator user. You’ve also learned basic queries for creating a database and table, inserting and retrieving data, altering existing data, and cleaning up by removing databases and tables.
FAQs
- What is ClickHouse?
ClickHouse is a fast, open-source OLAP database management system. - Why do I need to disable SWAP when installing ClickHouse?
Disabling SWAP is recommended for performance reasons, especially in production environments. - Can I install ClickHouse on systems other than Ubuntu 22.04?
Yes, ClickHouse provides installation methods for various Linux distributions, Docker, and single binary packages. - Is ClickHouse suitable for real-time data analysis?
Yes, ClickHouse excels at real-time data analysis with its ability to generate analytical reports using SQL queries.