ClickHouse installation¶

Installation¶

Consult the official ClickHouse production installation documentation for Ubuntu / Debian Linux here. ClickHouse features a client-server architecture which are installed as separate packages.

ClickHouse provides a default user named default with a password set during the installation process. This guide uses admin as the password.
ClickHouse provides a default database named default.
The main directory for ClickHouse server is /etc/clickhouse-server.

Set up the Debian package repository.

sudo apt-get install -y apt-transport-https \
ca-certificates curl gnupg

curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | \
sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] \
https://packages.clickhouse.com/deb stable main" | \
sudo tee /etc/apt/sources.list.d/clickhouse.list

sudo apt-get update

Install the deb packages for ClickHouse server and ClickHouse client. Provide a password for the default user during installation.

sudo apt install -y clickhouse-server clickhouse-client

Enable ClickHouse server on startup.

sudo systemctl enable clickhouse-server

Start ClickHouse server using systemd.

sudo systemctl start clickhouse-server

Verify the status of the ClickHouse process.

sudo systemctl status clickhouse-server

Access control¶

By default, SQL-driven access control and account management is disabled in ClickHouse. Access control can be configured by adding new configuration files to directory /etc/clickhouse-server/users.d. To enable SQL-driven access control for the default user, and to add the default and baoanalytics users to the default profile, add the following file.

sudo nano /etc/clickhouse-server/users.d/users.xml

Specify the following content in XML format.

<clickhouse>
  <users>
    <default>
      <access_management>1</access_management>
      <named_collection_control>1</named_collection_control>
      <show_named_collections>1</show_named_collections>
      <show_named_collections_secrets>1</show_named_collections_secrets>
      <min_os_cpu_wait_time_ratio_to_throw>3</min_os_cpu_wait_time_ratio_to_throw>
      <profile>default</profile>
    </default>
  </users>
</clickhouse>

Set file ownership.

sudo chown clickhouse:clickhouse /etc/clickhouse-server/users.d/users.xml

Restart the service to have the changes take effect.

sudo systemctl restart clickhouse-server

Password type¶

Verify that the password type is set to sha256_password

sudo cat /etc/clickhouse-server/config.xml | grep "<default_password_type>"

Database and admin user¶

To create a new database called baoanalytics, enter the ClickHouse client.

clickhouse-client

Execute the following SQL statement to create the database.

create database baoanalytics;

Create a new admin user called baoanalytics with password mypassword, associated with the default profile, with the following SQL statement. Replace mypassword with a strong password, and take note securely.

create user baoanalytics identified by 'mypassword' profile default;

grant all on *.* to baoanalytics with grant option;

Note the importance of the association with the default profile, which ensures that the configuration settings in a following section take effect.

After an admin user is created, SQL-driven access control can be removed from the default user for security purposes.

Performance tuning¶

Memory allocations, caching and oncurrency should be adjusted to the available server resources. Set server-wide global settings by adding the following file and content.

sudo nano /etc/clickhouse-server/config.d/performance.xml

Specify the following content in XML format. The size unit is bytes. Audit logging is disabled as it typically generates very large amounts of data.

<clickhouse>
  <!-- Memory -->
  <max_server_memory_usage>4294967296</max_server_memory_usage> <!-- 4GB -->
  <mark_cache_size>104857600</mark_cache_size> <!-- 100MB -->
  <!-- Connection -->
  <max_connections>100</max_connections>
  <max_concurrent_queries>100</max_concurrent_queries>
  <max_table_size_to_drop>0</max_table_size_to_drop>
  <!-- Logging -->
  <asynchronous_metric_log remove="1"/>
  <metric_log remove="1"/>
  <query_thread_log remove="1" />  
  <query_log remove="1" />
  <query_views_log remove="1" />
  <part_log remove="1"/>
  <session_log remove="1"/>
  <text_log remove="1" />
  <trace_log remove="1"/>
  <crash_log remove="1"/>
  <opentelemetry_span_log remove="1"/>
  <zookeeper_log remove="1"/>
  <!-- Merge -->
  <merge_tree>
    <max_bytes_to_merge_at_max_space_in_pool>1073741824</max_bytes_to_merge_at_max_space_in_pool>
  </merge_tree>
</clickhouse>

Set file ownership.

sudo chown clickhouse:clickhouse /etc/clickhouse-server/config.d/performance.xml

Set user-specific settings by editing the existing users.xml file previously created and adding the following content.

sudo nano /etc/clickhouse-server/users.d/users.xml

<clickhouse>
  <profiles>
    <default>
      <max_memory_usage>4294967296</max_memory_usage> <!-- 4GB -->
      <max_bytes_before_external_group_by>2147483648</max_bytes_before_external_group_by> <!-- 2GB -->
      <max_bytes_before_external_sort>2147483648</max_bytes_before_external_sort> <!-- 2GB -->
      <max_insert_block_size>10485760</max_insert_block_size> <!-- 10 MB -->
      <max_threads>1</max_threads>
      <max_bytes_before_remerge_sort>134217728</max_bytes_before_remerge_sort> <!-- 128 MB -->
      <enable_json_type>1</enable_json_type>
    </default>
  </profiles>

  <users>
    <!-- Users omitted for brevity -->
  </users>
</clickhouse>

A convenient GB-to-byte mapping table is found below.

GB	Bytes
1	1073741824
2	2147483648
3	3221225472
4	4294967296
5	5368709120
6	6442450944

Restart service for changes to take effect.

sudo systemctl restart clickhouse-server

Logging¶

View ClickHouse log files.

tail -f /var/log/clickhouse-server/clickhouse-server.log

tail -f /var/log/clickhouse-server/clickhouse-server.err.log

View ClickHouse journal log.

sudo journalctl -f -u clickhouse-server