ClickHouse is a robust open-source column-oriented database management system developed by Yandex. It’s cross-platform, allowing installation on Linux, FreeBSD, or Mac OS X operating systems. ClickHouse is known for its flexible query complexity, resource usage settings, and a rich feature set, including data compression, IPv6 support, high availability, real-time data ingestion, parallel and distributed query execution, on-disk locality of reference, and true column-oriented storage.
This tutorial guides you through installing ClickHouse on a Debian 10 server. Additionally, it will explain how to create a database and table using the ClickHouse client and configure the ClickHouse server to accept remote connections.
Requirements
- A server running Debian 10.
- A root password for your server.
Getting Started
Start by updating your system to the latest version. Execute the following commands:
apt-get update -y apt-get upgrade -y
After updating your system, restart the server to apply the changes.
Install ClickHouse
Since ClickHouse is not available in the default Debian 10 repository, you need to add its repository to APT first.
Download and import the GPG key with:
apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4
Output:
Executing: /tmp/apt-key-gpghome.CJf2zS1pCH/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4 gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <milovidov@yandex-team.ru>" imported gpg: Total number processed: 1 gpg: imported: 1
Add the ClickHouse repository to APT:
echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | tee /etc/apt/sources.list.d/clickhouse.list
Update the repository and install the ClickHouse server and client packages:
apt-get update -y apt-get install dirmngr clickhouse-server clickhouse-client -y
During installation, provide the ClickHouse password when prompted:
Once the installation completes, start the ClickHouse server and enable it to start at boot:
systemctl start clickhouse-server systemctl enable clickhouse-server
Check the ClickHouse service status with:
systemctl status clickhouse-server
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)
If the server fails to start, edit the /etc/ssl/openssl.cnf file:
nano /etc/ssl/openssl.cnf
Comment out this line:
#ssl_conf = ssl_sect
Save the file and restart the server:
systemctl start clickhouse-server
Successful start 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
Create a Database and Table with ClickHouse
You can create and manage databases or tables using SQL commands in the ClickHouse client.
Connect to the ClickHouse server using the password set during installation:
clickhouse-client --password admin@123
Client connection 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 :)
Create a sample database named clickdb:
debian :) CREATE DATABASE clickdb;
Database creation output:
CREATE DATABASE clickdb Ok. 0 rows in set. Elapsed: 0.014 sec.
Switch to the newly created database clickdb:
debian :) USE clickdb;
Database switch output:
USE clickdb Ok. 0 rows in set. Elapsed: 0.002 sec.
Create a table named ontime:
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);
Table creation 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.
View the table structure:
debian :) describe ontime;
Table structure 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.
Delete Database and Table
You can delete the ontime table:
debian :) DROP TABLE ontime;
Table deletion output:
DROP TABLE ontime Ok. 0 rows in set. Elapsed: 0.003 sec.
Delete the clickdb database:
debian :) DROP DATABASE clickdb;
Database deletion output:
DROP DATABASE clickdb Ok. 0 rows in set. Elapsed: 0.002 sec.
Configure ClickHouse to Connect from a Remote Host
To connect to the ClickHouse server from a remote host, modify the config.xml file:
nano /etc/clickhouse-server/config.xml
Locate this line:
<!-- <listen_host>0.0.0.0</listen_host> -->
Replace with the following:
<listen_host>0.0.0.0</listen_host>
Save the file and restart the ClickHouse service:
systemctl restart clickhouse-server
Now, from a remote host, connect to the ClickHouse server:
clickhouse-client --host your_server_ip --password admin@123
Successful connection 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 and configured the ClickHouse server on Debian 10. You can now efficiently create databases, tables, and execute queries using the ClickHouse client. For further information, refer to the official ClickHouse documentation at ClickHouse Doc. Feel free to reach out with any questions.
FAQ
What is ClickHouse?
ClickHouse is an open-source columnar database management system developed by Yandex for analytical data queries. It excels in running large-scale, real-time, analytical queries while maintaining high performance.
Why is my ClickHouse server failing to start?
If the ClickHouse server fails to start, check for issues in the /etc/ssl/openssl.cnf configuration file. Ensure #ssl_conf = ssl_sect is commented out if you’re experiencing issues.
How can I enable remote connections to my ClickHouse server?
Edit the config.xml file and uncomment <listen_host>0.0.0.0</listen_host> to allow connections from remote hosts, then restart the ClickHouse service.
What should I do if I forget my ClickHouse password?
If you forget your ClickHouse password, you’ll need to reset it through the server’s configuration files or reconfiguration of the user settings within ClickHouse.