# How to Use ClickHouse for High-Performance Querying on Large Datasets on Ubuntu GPU Server

> URL: https://www.atlantic.net/gpu-server-hosting/how-to-use-clickhouse-for-high-performance-querying-on-large-datasets-on-ubuntu-gpu-server/ | Published: 2025-03-27 | Updated: 2026-05-04 | Author: Hitesh Jethva

ClickHouse is an open-source column-oriented database management system that excels at real-time analytical processing (OLAP). When deployed on a [GPU-enabled Ubuntu server](https://www.atlantic.net/gpu-server-hosting/), ClickHouse can achieve even greater performance for querying large datasets.

This in-depth guide will walk you through setting up ClickHouse with GPU support, generating test data, loading it into ClickHouse, and verifying the installation.

## Prerequisites

- An Ubuntu 24.04 server with an NVIDIA GPU.
- A non-root user with sudo privileges.
- NVIDIA drivers installed.

## Step 1: Setting Up the Python Environment

First, we’ll create a clean Python environment for our data generation and verification scripts.

1. Install Python and the required packages.

```bash
apt install -y python3 python3-pip python3-venv
```

2. Create and activate the virtual environment.

```bash
python3 -m venv venv
source venv/bin/activate
```

3. Install ClickHouse and additional packages.

```bash
pip install numpy pandas clickhouse-driver
```

**Explanation:**

- **numpy** for numerical operations
- **pandas** for data manipulation
- **clickhouse-driver** for Python connectivity to ClickHouse

## Step 2: Generating Synthetic Test Data

Create a Python script to generate a large dataset for testing.

```bash
nano generate_data.py
```

Add the following code:

```bash
# generate_data.py
import pandas as pd
import numpy as np

# Generate 1 million rows of synthetic data
data = {
    'id': np.arange(1, 1_000_001),
    'feature1': np.random.rand(1_000_000),
    'feature2': np.random.rand(1_000_000),
    'feature3': np.random.rand(1_000_000)
}

df = pd.DataFrame(data)
df.to_csv('data.csv', index=False)
print("Generated data.csv with 1,000,000 rows")
```

Run the script.

```bash
python3 generate_data.py
```

This creates a CSV file with 1 million rows of random floating-point numbers across three features, plus an ID column.

```bash
Generated data.csv with 1,000,000 rows
```

## Step 3: Setting Up ClickHouse with GPU Support

ClickHouse has experimental GPU support that can accelerate certain operations. We’ll use Docker to run ClickHouse with GPU access.

```bash
docker run -d \
  --name clickhouse-server \
  --gpus all \
  -p 9000:9000 \
  -p 8123:8123 \
  -v $PWD/data:/var/lib/clickhouse \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  clickhouse/clickhouse-server:latest
```

**Key parameters:**

- **–gpus all:** Enables GPU access for the container
- Ports 9000 (native protocol) and 8123 (HTTP interface) exposed
- Data directory mounted for persistence
- Access management enabled for security

## Step 4: Preparing the ClickHouse Database

1. Copy the data file into the container and access the ClickHouse shell:

```bash
docker cp data.csv clickhouse-server:/tmp/data.csv
```

2. Connect to the ClickHouse container.

```bash
docker exec -it clickhouse-server /bin/bash
```

3. Inside the container, connect to the ClickHouse server.

```bash
clickhouse-client
```

4. Create a table to hold our data.

```bash
CREATE TABLE large_dataset (
    id UInt32,
    feature1 Float64,
    feature2 Float64,
    feature3 Float64
) ENGINE = MergeTree()
ORDER BY id;
```

5. Exit from the ClickHouse shell.

```bash
exit
```

## Step 5: Loading Data into ClickHouse

1. Use the ClickHouse client to import the CSV data.

```bash
clickhouse-client --query "INSERT INTO large_dataset FORMAT CSV" < /tmp/data.csv --progress
```

2. Exit from the Docker container.

```bash
exit
```

## Step 6: Verifying the Data

1. Create a verification script to check the data was loaded correctly:

```bash
nano verify_data.py
```

Add the code below.

```bash
# verify_data.py
from clickhouse_driver import Client

client = Client(
    host='localhost',
    user='default',
    password=''
)

count = client.execute('SELECT count() FROM large_dataset')[0][0]
print(f"Table contains {count} rows")

sample = client.execute('SELECT * FROM large_dataset LIMIT 5')
print("Sample rows:")
for row in sample:
    print(row)
```

2. Run the verification.

```bash
python3 verify_data.py
```

Output.

```bash
Table contains 2000000 rows
Sample rows:
(1, 0.38233336651112515, 0.29973790958931235, 0.936007728842977)
(2, 0.13126390736004656, 0.249047252224595, 0.6534321474304323)
(3, 0.7285561857877713, 0.38113395640456516, 0.6836105833319909)
(4, 0.5117479220423862, 0.7073417787216559, 0.8446349501745961)
(5, 0.6415940064857016, 0.7574774031938657, 0.7175210065963386)
```

## Step 7: Running Performance Queries

Now that the data is loaded, let’s test some queries. ClickHouse’s columnar storage and GPU acceleration shine with analytical queries:

1. First, install the ClickHouse client.

```bash
apt install clickhouse-client
```

2. Connect to the ClickHouse server.

```bash
clickhouse-client
```

Output.

```bash
ClickHouse client version 18.16.1.
Connecting to localhost:9000.
Connected to ClickHouse server version 25.3.2 revision 54476.

e7cf71653d41 :)
```

2. Run the below query for basic aggregation.

```bash
SELECT 
    avg(feature1) as avg_feature1,
    stddevPop(feature2) as std_feature2,
    quantile(0.99)(feature3) as p99_feature3
FROM large_dataset
```

Output:

```bash
SELECT 
    avg(feature1) AS avg_feature1, 
    stddevPop(feature2) AS std_feature2, 
    quantile(0.99)(feature3) AS p99_feature3
FROM large_dataset 

┌───────avg_feature1─┬────────std_feature2─┬───────p99_feature3─┐
│ 0.5002608413633995 │ 0.28872380769587086 │ 0.9908446093587597 │
└────────────────────┴─────────────────────┴────────────────────┘
↘ Progress: 1.00 million rows, 24.00 MB (108.22 million rows/s., 2.60 GB/s.)  99%
1 rows in set. Elapsed: 0.010 sec. Processed 1.00 million rows, 24.00 MB (103.62 million rows/s., 2.49 GB/s.)
```

3. Run the below query for filtering and grouping.

```bash
SELECT 
    intDiv(id, 100000) as bucket,
    count() as count,
    avg(feature1 + feature2) as avg_combined
FROM large_dataset
WHERE feature3 > 0.5
GROUP BY bucket
ORDER BY bucket
```

Output:

```bash
SELECT 
    intDiv(id, 100000) AS bucket, 
    count() AS count, 
    avg(feature1 + feature2) AS avg_combined
FROM large_dataset 
WHERE feature3 > 0.5
GROUP BY bucket
ORDER BY bucket ASC

┌─bucket─┬─count─┬───────avg_combined─┐
│      0 │ 49802 │ 1.0000974894524948 │
│      1 │ 50131 │ 1.0002304278511396 │
│      2 │ 49990 │ 1.0011286054623088 │
│      3 │ 50031 │ 0.9968373869613624 │
│      4 │ 50022 │ 1.0022925786397234 │
│      5 │ 50123 │ 0.9986748463314014 │
│      6 │ 50218 │ 1.0004042381656353 │
│      7 │ 50041 │ 0.9998849136710541 │
│      8 │ 50138 │  1.000476200468667 │
│      9 │ 49908 │ 1.0030392683409466 │
└────────┴───────┴────────────────────┘
↙ Progress: 1.00 million rows, 28.00 MB (87.21 million rows/s., 2.44 GB/s.)  99%
10 rows in set. Elapsed: 0.012 sec. Processed 1.00 million rows, 28.00 MB (83.71 million rows/s., 2.34 GB/s.)
```

4. Exit from the ClickHouse shell.

```bash
exit
```

## Conclusion

By combining ClickHouse’s columnar storage and vectorized query execution with GPU acceleration on an Ubuntu server, you can achieve exceptional performance when querying large datasets. The setup process is straightforward with Docker, and the performance benefits for analytical workloads are substantial.