ClickHouse¶

In this article

• ClickHouse. Core Features
• Deployment Features
• Getting Started After Deploying ClickHouse
• Connecting to ClickHouse
• Core Operations in ClickHouse
• Ordering a server with ClickHouse

ClickHouse. Core Features¶

• Columnar storage: data is stored by columns rather than rows, which significantly speeds up analytical query execution and reduces the volume of processed data.
• High performance: optimized for handling massive datasets and capable of executing queries on billions of rows thanks to vectorized processing and efficient CPU utilization.
• Data compression: employs specialized compression algorithms that can dramatically reduce the amount of stored data (often several times smaller than row-based DBMS), especially under analytical workloads.
• SQL compatibility: supports extended SQL syntax with additional analytical functions, simplifying migration of existing applications.
• Distributed processing: enables cluster creation for horizontal scaling and data processing across multiple servers.
• Data replication: built-in replication support ensures fault tolerance and high data availability.
• Support for multiple data formats: can import and export data in CSV, JSON, Parquet, Avro, and other formats.
• Materialized views: automatically aggregates data in the background to accelerate recurring queries.
• Integration with external sources: supports direct reading of data from MySQL, PostgreSQL, MongoDB, Kafka, S3, and other systems.

Deployment Features¶

• Installed software:

  • ClickHouse Server >= 26.1.1
  • ClickHouse Client >= 26.1.1

• Installation specifics:

  • Software installed via the official developer script;
  • Installation time: 5-10 minutes;
  • Database is available for network connections via various SQL clients;
  • By default the user default with the password from the root account is used.

Configuration and data file locations:

• Main server configuration: /etc/clickhouse-server/config.xml
• User settings: /etc/clickhouse-server/users.xml
• Server logs:
  • /var/log/clickhouse-server/clickhouse-server.log
  • /var/log/clickhouse-server/clickhouse-server.err.log
• Data: /var/lib/clickhouse/
• PID file: /run/clickhouse-server/clickhouse-server.pid
• System limits: /etc/security/limits.d/clickhouse.conf

Available ports for connections:

Getting Started After Deploying ClickHouse¶

A notification will be sent to the e-mail specified during registration that the server is ready for work after paying for the order. It will contain the IP address of the VPS, as well as the login and password for the connection. Clients of our company manage the hardware in the server`s and API control panel - Invapi.

Authorization details:

• Server IP address: provided in the email and in the control panel;
• Login: default;
• Password: matches the password of the root account and is sent to the email address.

Connecting to ClickHouse¶

Connecting via clickhouse-client¶

ClickHouse includes a native command-line client for database operations.

1. Connecting from the server (locally):

clickhouse-client

2. Connecting from a remote machine:

clickhouse-client --host <IP-address> --port 9000 --user default --password <password>

3. Executing a simple query:

SELECT version();

4. Viewing the list of databases:

SHOW DATABASES;

Connecting via DBeaver¶

DBeaver is a universal GUI client for working with various databases, including ClickHouse.

1. Installing DBeaver

Download and install DBeaver from the official website.

2. Creating a new connection

• Launch DBeaver
• Select Database >> New Database Connection (or press Ctrl+Shift+N)

• In the database list find and select ClickHouse

• Click Next

3. Configuring connection parameters

In the settings window specify the following parameters:

Host: <IP-address of your server>
Port: 8123
Database/Schema: (leave empty or specify default)
Username: default
Password: <root_password>

4. Downloading the JDBC driver

When connecting for the first time, DBeaver will prompt you to download the JDBC driver for ClickHouse:

• Click Download in the popup window:

• Wait for the driver to finish downloading:

• Click OK

5. Testing the connection

• Click Test Connection;
• If everything is configured correctly, a message will appear: Connected (XXX ms):

• The message will include:
• Server: ClickHouse X.X.X.XXX
• Driver: ClickHouse JDBC Driver X.X.X

6. Finalizing setup

• Click Finish;
• The connection will appear in the Database Navigator;
• Expand the connection tree to view databases, tables, and other objects.

Common issues:

Core Operations in ClickHouse¶

Creating a database¶

CREATE DATABASE IF NOT EXISTS my_database;

Creating a table¶

CREATE TABLE my_database.events
(
    event_date Date,
    event_time DateTime,
    user_id UInt32,
    event_type String,
    value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

Inserting data¶

INSERT INTO my_database.events VALUES
    ('2024-12-25', '2024-12-25 10:00:00', 1, 'click', 1.5),
    ('2024-12-25', '2024-12-25 10:05:00', 2, 'view', 2.3),
    ('2024-12-25', '2024-12-25 10:10:00', 1, 'purchase', 99.99);

Selecting data¶

Simple query:

SELECT * FROM my_database.events LIMIT 10;

Aggregating data:

SELECT 
    event_type,
    count() as events_count,
    avg(value) as avg_value
FROM my_database.events
WHERE event_date = '2024-12-25'
GROUP BY event_type;

Time series:

SELECT 
    toStartOfHour(event_time) as hour,
    count() as events_per_hour
FROM my_database.events
WHERE event_date >= '2024-12-01'
GROUP BY hour
ORDER BY hour;

Importing data from CSV¶

Creating a table:

CREATE TABLE my_database.logs
(
    timestamp DateTime,
    level String,
    message String
)
ENGINE = MergeTree()
ORDER BY timestamp;

Importing data:

INSERT INTO my_database.logs
FROM INFILE '/path/to/file.csv'
FORMAT CSV;

Managing users and privileges¶

Creating a user:

CREATE USER analyst IDENTIFIED BY 'secure_password';

Granting permissions:

GRANT SELECT ON my_database.* TO analyst;

Viewing users:

SHOW USERS;

Viewing privileges:

SHOW GRANTS FOR analyst;

Managing the service¶

Starting the service:

clickhouse start

Stopping the service:

clickhouse stop

Restarting the service:

clickhouse restart

Service status:

clickhouse status

Viewing logs:

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

Checking active ports¶

To see which ports ClickHouse is listening on:

ss -tlnp | grep clickhouse

Or more detailed:

ss -tlnp | grep $(pidof clickhouse-server)

To check configured ports in the configuration:

cat /etc/clickhouse-server/config.xml | grep -E "port>" | head -20

To check a specific port:

ss -tlnp | grep :8123

Expected port check output:

LISTEN  *:8123  clickhouse-serv  (HTTP)
LISTEN  *:9000  clickhouse-serv  (Native TCP)
LISTEN  *:9004  clickhouse-serv  (MySQL)
LISTEN  *:9005  clickhouse-serv  (PostgreSQL)
LISTEN  *:9009  clickhouse-serv  (Interserver)

Ordering a server with ClickHouse¶

To install this software using the API, follow these instructions.

Some of the content on this page was created or translated using AI.