How to Install ClickHouse OLAP Database on Debian 10
ClickHouse is an open-source column-oriented database management system developed by Yandex. It is cross-platform and can be installed on any Linux, FreeBSD or Mac OS X operating system. ClickHouse offers flexible limits on query complexity and resource usage, which can be fine-tuned with settings. ClickHouse provides a rich set of features, such as, Data compression, Support of IPv6, High availability, Real-time data ingestion, Parallel and distributed query execution, On-disk locality of reference, True column-oriented storage and many more.
This tutorial will explain how to install ClickHouse on Debian 10 server. We will also explain how to create database and table with ClickHouse client and configure ClickHouse server to allow connection from the remote host.
Requirements
- A server running Debian 10.
- A root password is setup to your server.
Getting Started
Before starting, you will need to update your system with the latest version. You can do this by running the following command:
apt-get update -y
apt-get upgrade -y
Once your server is updated, restart your server to apply the changes.
Install ClickHouse
By default, ClickHouse is not available in the Debian 10 default repository. So, you will need to add the ClickHouse repository to the APT.
First, download and import the GPG key with the following command:
apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4
You should see the following output:
Executing: /tmp/apt-key-gpghome.CJf2zS1pCH/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4 gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <[email protected]>" imported gpg: Total number processed: 1 gpg: imported: 1
Next, add the ClickHouse repository to APT with the following command:
echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | tee /etc/apt/sources.list.d/clickhouse.list
Next, update the repository and install ClickHouse server and client package with the following command:
apt-get update -y
apt-get install dirmngr clickhouse-server clickhouse-client -y
During the installation, you will be asked to provide ClickHouse password as shown below:
Provide your password and hit Enter to proceed with the installation. Once the installation is completed, start the ClickHouse server and enable it to start after system reboot with the following command:
systemctl start clickhouse-server
systemctl enable clickhouse-server
You can verify the status of ClickHouse service with the following command:
systemctl status clickhouse-server
You should see the following output:
systemctl status clickhouse-server ? clickhouse-server.service - ClickHouse Server (analytic DBMS for big data) Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled) Active: activating (auto-restart) (Result: exit-code) since Sat 2019-09-07 07:48:00 EDT; 8s ago Process: 642 ExecStart=/usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-ser Main PID: 642 (code=exited, status=1/FAILURE)
In the above output, you should see that ClickHouse server is failed to start. To resolve this error, open /etc/ssl/openssl.cnf file with the following command:
nano /etc/ssl/openssl.cnf
Comment the following line:
#ssl_conf = ssl_sect
Save and close the file when you are finished. Then, start the Clickhouse server again with the following command:
systemctl start clickhouse-server
Once the ClickHouse server started successfully, you should get the following output:
? clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2019-09-06 05:28:51 EDT; 1min 24s ago
Main PID: 1822 (clickhouse-serv)
Tasks: 38 (limit: 1138)
Memory: 42.6M
CGroup: /system.slice/clickhouse-server.service
??1822 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
Sep 06 05:28:51 debian systemd[1]: Started ClickHouse Server (analytic DBMS for big data).
Sep 06 05:28:51 debian clickhouse-server[1822]: Include not found: clickhouse_remote_servers
Sep 06 05:28:51 debian clickhouse-server[1822]: Include not found: clickhouse_compression
Sep 06 05:28:51 debian clickhouse-server[1822]: Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Sep 06 05:28:51 debian clickhouse-server[1822]: Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
Sep 06 05:28:51 debian clickhouse-server[1822]: Include not found: networks
Sep 06 05:28:53 debian clickhouse-server[1822]: Include not found: clickhouse_remote_servers
Sep 06 05:28:53 debian clickhouse-server[1822]: Include not found: clickhouse_compression
When you are finished, you can proceed to the next step.
Create a Database and Table with ClickHouse
You can create and delete databases and tables by running SQL commands directly in the ClickHouse client prompt.
First, connect the ClickHouse server by specifying the password which you have created during the ClickHouse installation with the following command:
clickhouse-client --password admin@123
You should see the following output:
ClickHouse client version 19.13.3.26 (official build). Connecting to localhost:9000 as user default. Connected to ClickHouse server version 19.13.3 revision 54425. debian :)
Now, create a sample database named clickdb with the following command:
debian :) CREATE DATABASE clickdb;
You should see the following output:
CREATE DATABASE clickdb Ok. 0 rows in set. Elapsed: 0.014 sec.
Next, change the database to clickdb with the following command:
debian :) USE clickdb;
You should see the following output:
USE clickdb Ok. 0 rows in set. Elapsed: 0.002 sec.
Next, create a table named ontime with the following command:
debian :) CREATE TABLE ontime ( Year UInt16, Quarter UInt8, Month UInt8, DayofMonth UInt8, DayOfWeek UInt8, FlightDate Date, AirlineID Int32, FlightNum String ) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192);
You should see the following output:
CREATE TABLE ontime
(
`Year` UInt16,
`Quarter` UInt8,
`Month` UInt8,
`DayofMonth` UInt8,
`DayOfWeek` UInt8,
`FlightDate` Date,
`AirlineID` Int32,
`FlightNum` String
)
ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192)
Ok.
0 rows in set. Elapsed: 0.127 sec.
You can also see the content of your table with the following command:
debian :) describe ontime;
You should see the following output:
DESCRIBE TABLE ontime ??name?????????type?????default_type???default_expression???comment???codec_expression???ttl_expression?? ? Year ? UInt16 ? ? ? ? ? ? ? Quarter ? UInt8 ? ? ? ? ? ? ? Month ? UInt8 ? ? ? ? ? ? ? DayofMonth ? UInt8 ? ? ? ? ? ? ? DayOfWeek ? UInt8 ? ? ? ? ? ? ? FlightDate ? Date ? ? ? ? ? ? ? AirlineID ? Int32 ? ? ? ? ? ? ? FlightNum ? String ? ? ? ? ? ? ????????????????????????????????????????????????????????????????????????????????????????????????????????? 8 rows in set. Elapsed: 0.007 sec.
You can also insert, update, and delete data from the ontime table.
Delete Database and Table
You can also delete your database and table with ClickHouse client.
If you want to delete the table, run the following command:
debian :) DROP TABLE ontime;
You should see the following output:
DROP TABLE ontime Ok. 0 rows in set. Elapsed: 0.003 sec.
You can also delete the database with the following command:
debian :) DROP DATABASE clickdb;
You should see the following output:
DROP DATABASE clickdb Ok. 0 rows in set. Elapsed: 0.002 sec.
When you are finished, you can proceed to the next step.
Configure ClickHouse to Connect from Remote Host
You can also connect the ClickHouse database server from the remote host. To do so, you will need to edit the ClickHouse configuration file config.xml:
nano /etc/clickhouse-server/config.xml
Find the following line:
<!-- <listen_host>0.0.0.0</listen_host> -->
And change it with the following:
<listen_host>0.0.0.0</listen_host>
Save and close the file when you are finished. Then, restart the ClickHouse service to apply the new configuration:
systemctl restart clickhouse-server
ClickHouse is now configured to connect from the remote host.
Now, log into the remote host and connect the ClickHouse server with the following command:
clickhouse-client --host your_server_ip --password admin@123
Once you have connected successfully, you should see the following output:
ClickHouse client version 19.13.3.26 (official build). Connecting to localhost:9000 as user default. Connected to ClickHouse server version 19.13.3 revision 54425. debian :)
Conclusion
Congratulations! you have successfully installed ClickHouse server on Debian 10 server. You can now easily create new databases, tables, insert and update data and perform queries through ClickHouse client. For more information, you can visit the ClickHouse official documentation at ClickHouse Doc. Feel free to ask me if you have any questions.
