> For the complete documentation index, see [llms.txt](https://experience-digital-transform.gitbook.io/experience-digital-transformation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://experience-digital-transform.gitbook.io/experience-digital-transformation/analytics/reference-implementation/analytical-data-store-clickhouse-architecture-and-configuration.md). # Analytical data store: ClickHouse architecture and configuration ClickHouse acts as the core of this reference implementation — an open-source, column-oriented DBMS optimized for real-time online analytical processing (OLAP). Unlike traditional relational databases (OLTP) like PostgreSQL, which store data row-by-row, ClickHouse stores data column-by-column, enabling extremely high-speed execution of aggregate `SELECT` queries with `GROUP BY` operations. ## Native initialization and system packages To eliminate virtualization overhead that degrades disk I/O performance (which is critical for the MergeTree engine family), ClickHouse is installed natively. On Ubuntu/Debian, the process involves importing GPG keys, registering the official repository, and installing the server and client binary packages. The initialization sequence on Ubuntu is executed via the following system calls: 1. Install `apt-transport-https`, `ca-certificates`, and `dirmngr` to support secure TLS downloads. 2. Add the official Ubuntu keyserver public key `E0C56BD4`. 3. Add the repository `https://packages.clickhouse.com/deb` and run `apt-get install -y clickhouse-server clickhouse-client`. 4. Integrate with systemd to enable and start the service: `sudo systemctl enable --now clickhouse-server`. For CentOS/RHEL-based infrastructures, the process relies on `yum-utils` and Altinity LTS repositories, which provide stable, enterprise-tested versions. Successful installation is validated by executing `clickhouse-client -q "SELECT version();"`, which confirms the server is ready to accept TCP connections. ## Modular configuration directory architecture In terms of Site Reliability Engineering (SRE) and configuration management, editing the main `/etc/clickhouse-server/config.xml` file directly is an anti-pattern because it complicates minor and major server upgrades. This reference implementation mandates using drop-in directories: `config.d/` for server settings and `users.d/` for user configurations and quotas. ClickHouse automatically parses all XML and YAML files within these folders in alphabetical order and merges them into a single configuration tree at startup. For example, modular configs include `/etc/clickhouse-server/config.d/macros.xml` for cluster macros (cluster name, shard, and replica IDs) needed for `ReplicatedMergeTree` tables. ZooKeeper or ClickHouse Keeper connection details are placed in `zoo.xml`. If using a standalone ClickHouse Keeper, its configuration resides in `/etc/clickhouse-keeper/keeper_config.xml` with drop-in files under `keeper_config.d/`. ## Network security, routing, and firewall configurations By default, to ensure security, ClickHouse only binds to localhost (`127.0.0.1` or `::1`), which blocks external integration attempts from Apache Airflow and Apache Superset. To allow communication, a dedicated configuration file `/etc/clickhouse-server/config.d/network.xml` must be created to define the `` directive. Setting `` to `0.0.0.0` configures ClickHouse to listen on all IPv4 interfaces, while `::` enables dual-stack IPv6 support. In highly secure environments, the directive can target specific internal IP addresses or DNS hostnames (e.g. `clickhouse.internal.example.com`). {% hint style="warning" %} 🌐 ClickHouse resolves hostnames only at startup; if the DNS record changes later, the listening socket does not automatically rebind, requiring a restart of the `clickhouse-server` service. {% endhint %} The network layout utilizes several critical ports that must be managed by firewalls (e.g. UFW): * **HTTP Interface (Port 8123):** Used for REST API interactions, health checks, and connection via the `clickhouse-connect` driver (the primary connector for Superset). * **Native TCP Interface (Port 9000):** A binary protocol offering maximum throughput, used by `clickhouse-client` and the `airflow-clickhouse-plugin` for high-volume `INSERT` tasks. * **Interserver HTTP Interface (Port 9009):** Used strictly for node-to-node replication and distributed queries within a cluster. This port inherits the host configuration from `interserver_listen_host`. To block unauthorized access, firewall rules must restrict access to ports 8123 and 9000 to trusted subnets only (e.g. `10.0.0.0/8`), rejecting other traffic: ```bash ufw allow from 10.0.0.0/8 to any port 8123 proto tcp ufw allow from 10.0.0.0/8 to any port 9000 proto tcp ufw deny 8123 ufw deny 9000 ``` If data transit occurs across untrusted networks, TLS-encrypted ports 8443 (HTTPS) and 9440 (Native Secure) must be used with OpenSSL certificates configured in both ClickHouse and Keeper configurations. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://experience-digital-transform.gitbook.io/experience-digital-transformation/analytics/reference-implementation/analytical-data-store-clickhouse-architecture-and-configuration.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.