Clickhouse For Ubuntu/Debian/RHEL
JDK
Using JDK 11 as well as enabling G1 garbage collector is recommended. Comparing to JDK 8, the performance of handling jobs under multi-threads will be improved more than 30%.
Clickhouse
1. Download & Install
sudo apt-get install apt-transport-https ca-certificates dirmngr
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
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo chown -R clickhouse /var/lib/clickhouse /var/log/clickhouse-server /etc/clickhouse-server /etc/clickhouse-client
Please set Clickhouse password to sqlflow@gudu
Generate clickhouse-server key & crts
Check your config.xml
vim /etc/clickhouse-server/config.xml
Find the instruction of openSSL

Run the above command, in my case it is:
sudo openssl req -subj "/CN=localhost" -new -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/clickhouse-server/server.key -out /etc/clickhouse-server/server.crt
You should have:

If you got
Can't load /home/<user>/.rnd into RNG
Then you can try removing or commenting RANDFILE = $ENV::HOME/.rnd
line in /etc/ssl/openssl.cnf
Replace IPv6 config
If your server is not enabled with IPV6, you will have following errors when trying to start the clickhouse server:
{} <Error> Application: DB::Exception: Listen [::]:8123 failed: Poco::Exception. Code: 1000, e.code() = 0, DNS error: EAI: Address family for hostname not supported (version 22.2.3.1)
This is because the clickhouse server uses IPV6 syntax by default. We need to replace the IPV6 syntax by IPV4 compatible syntax.
Edit the listen.xml
:
/etc/clickhouse-server/config.d/listen.xml
Replace the line
<listen_host>::</listen_host>
to
<listen_host>0.0.0.0</listen_host>
Hint: Do not change the same field value in /etc/clickhouse-server/config.xml
, if the field is changed in config.xml, please restore the field back to the original value.
Re-generate clickhouse-server key & crts if you meet the following error after properly configuring the listen.xml
:

2. Memory Settings
We will need to limit the clickhouse service If the client server has a memory less than 32GB.
clickhouse memory settings
In /etc/clickhouse-server/config.xml
set max_server_memory_usage_to_ram_ratio
to 0.125 so that 32G*0.125 will be 4G

Update /etc/clickhouse-server/users.xml
and set max_memory_usage
to 4000000000, max_bytes_before_external_group_by
to 2000000000. You may need to create max_bytes_before_external_group_by
if it isn't present.

Restart the Clickhouse service: sudo /etc/init.d/clickhouse-server restart
Sqlservice memory settings
With this config, the Simple mode will use 22G memory and the Rugular mode will be in 18G memory.
# Update sqlservice.sh, set the heapsize as 18G
elif (( $memory < 32*1024*1024 ));
then
heapsize="18g"
3. Set Clickhouse default password
Set Clickhouse password to sqlflow@gudu
If mistakenly set to another password, you will need:
delete
/etc/clickhouse-server/users.d/default-password.xml
update
/etc/clickhouse-server/users.xml
, set password to sqlflow@gudurestart Clickhouse,
sudo /etc/init.d/clickhouse-server restart
4. Init Clickhouse
Run following commands:
cd /wings/sqlflow/backend
sh bin/init_regular.sh
If you got any errors related to Clickhouse, you can check /var/log/clickhouse-sever
for the error logs and please ensure that you have finished the steps of key generation and IPv6 configuration.
5. Start SQLFlow
cd /wings/sqlflow/backend
sh bin/backend.sh
Last updated