---
title: "ClickHouse Examples"
---

# ClickHouse Examples

:::caution[Planned: not in the current release]
ClickHouse protocol support for HeliosDB Full is planned. The current release opens no ClickHouse listener. Reach
the same data over the PostgreSQL listener or `POST /api/v1/query` today. This page
describes the planned interface.
:::

Practical examples for HeliosDB's ClickHouse protocol support.

## Connection Examples

### Python Client

```python
from clickhouse_driver import Client

# Basic connection
client = Client(
    host='localhost',
    port=9000,
    user='default',
    password='',
    database='default'
)

# Execute query
result = client.execute('SELECT count() FROM events')
print(f"Total events: {result[0][0]}")
```

### Go Client

```go
import "github.com/ClickHouse/clickhouse-go/v2"

conn, err := clickhouse.Open(&clickhouse.Options{
    Addr: []string{"localhost:9000"},
    Auth: clickhouse.Auth{
        Database: "default",
        Username: "default",
        Password: "",
    },
    Compression: &clickhouse.Compression{
        Method: clickhouse.CompressionLZ4,
    },
    MaxOpenConns: 5,
})

ctx := context.Background()
rows, _ := conn.Query(ctx, "SELECT count() FROM events")
```

### Node.js Client

```javascript
const { createClient } = require('@clickhouse/client');

const client = createClient({
  host: 'localhost:9000',
  database: 'default',
  username: 'default',
  password: '',
  compression: { type: 'lz4' },
});

const result = await client.query({
  query: 'SELECT count() FROM events',
});
console.log(await result.json());
```

## Table Creation

### MergeTree Table

```sql
CREATE TABLE events (
  timestamp DateTime,
  event_type String,
  user_id UInt32,
  duration Float32
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id)
PRIMARY KEY (timestamp)
PARTITION BY toYYYYMM(timestamp);
```

### ReplacingMergeTree (Upsert)

```sql
CREATE TABLE user_profiles (
  user_id UInt32,
  version UInt32,
  name String,
  email String,
  updated_at DateTime
) ENGINE = ReplacingMergeTree(version)
ORDER BY user_id
PARTITION BY toYYYYMM(updated_at);

-- Query latest version
SELECT user_id, name, email
FROM user_profiles
FINAL
WHERE user_id = 123;
```

### AggregatingMergeTree

```sql
CREATE TABLE metrics_aggregated (
  timestamp DateTime,
  metric_name String,
  metric_value AggregateFunction(sum, Float64),
  metric_count AggregateFunction(count, UInt64)
) ENGINE = AggregatingMergeTree()
ORDER BY (timestamp, metric_name)
PARTITION BY toYYYYMMDD(timestamp);

-- Insert aggregated values
INSERT INTO metrics_aggregated
SELECT
    toStartOfMinute(timestamp) as timestamp,
    metric_name,
    sumState(value) as metric_value,
    countState(1) as metric_count
FROM raw_metrics
GROUP BY timestamp, metric_name;

-- Query merged values
SELECT
    timestamp,
    metric_name,
    sumMerge(metric_value) as total,
    countMerge(metric_count) as count
FROM metrics_aggregated
GROUP BY timestamp, metric_name;
```

## Query Optimization

### PREWHERE for Early Filtering

```sql
-- FAST: Filters before reading heavy columns
SELECT user_id, event_type, timestamp
FROM events
PREWHERE timestamp > '2025-01-01' AND event_type = 'purchase'
WHERE user_id > 0;
```

### Sampling for Large Datasets

```sql
-- Analyze 10% of data
SELECT event_type, count() as cnt
FROM events
SAMPLE 0.1
GROUP BY event_type;
```

### Efficient Aggregations

```sql
-- Basic aggregations
SELECT
  event_type,
  count() as events,
  count(DISTINCT user_id) as unique_users,
  sum(value) as total_value,
  avg(duration) as avg_duration,
  min(timestamp) as first_event,
  max(timestamp) as last_event
FROM events
GROUP BY event_type;
```

## Quantile Functions

### Percentile Calculations

```sql
-- Quick approximate quantiles
SELECT
  event_type,
  quantile(0.5)(duration) as p50,
  quantile(0.95)(duration) as p95,
  quantile(0.99)(duration) as p99
FROM events
GROUP BY event_type;

-- Multiple quantiles at once
SELECT
  event_type,
  quantiles(0.25, 0.5, 0.75, 0.95, 0.99)(duration) as percentiles
FROM events
GROUP BY event_type;
```

## Top-K Queries

```sql
-- Get top 10 event types
SELECT event_type, count() as cnt
FROM events
GROUP BY event_type
ORDER BY cnt DESC
LIMIT 10;

-- TopK function (more efficient)
SELECT
  user_id,
  topK(10)(event_type) as top_events
FROM events
GROUP BY user_id;
```

## Unique Counting

```sql
-- Approximate distinct count (very fast!)
SELECT
  event_type,
  uniq(user_id) as unique_users,
  uniqHLL12(user_id) as uniq_hll
FROM events
GROUP BY event_type;

-- Exact distinct count
SELECT
  event_type,
  uniqExact(user_id) as unique_users
FROM events
GROUP BY event_type;
```

## Window Functions

### Running Totals

```sql
SELECT
  timestamp,
  event_type,
  value,
  sum(value) OVER (
    PARTITION BY event_type
    ORDER BY timestamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM events
ORDER BY event_type, timestamp;
```

### Row Number and Ranking

```sql
SELECT
  timestamp,
  user_id,
  value,
  row_number() OVER (
    PARTITION BY user_id
    ORDER BY timestamp
  ) as event_seq,
  rank() OVER (
    PARTITION BY user_id
    ORDER BY value DESC
  ) as value_rank
FROM events
ORDER BY user_id, timestamp;
```

### Lead/Lag for Time-Series

```sql
SELECT
  timestamp,
  value,
  lag(value, 1) OVER (ORDER BY timestamp) as prev_value,
  lead(value, 1) OVER (ORDER BY timestamp) as next_value,
  value - lag(value, 1) OVER (ORDER BY timestamp) as value_change
FROM metrics
ORDER BY timestamp;
```

## Materialized Views

```sql
-- Base table
CREATE TABLE events (
  timestamp DateTime,
  event_type String,
  user_id UInt32,
  value Float32
) ENGINE = MergeTree()
ORDER BY timestamp;

-- Aggregated view
CREATE MATERIALIZED VIEW events_per_minute
ENGINE = AggregatingMergeTree()
ORDER BY (timestamp, event_type)
AS
SELECT
  toStartOfMinute(timestamp) as timestamp,
  event_type,
  count() as event_count,
  sum(value) as total_value,
  avg(value) as avg_value
FROM events
GROUP BY toStartOfMinute(timestamp), event_type;

-- Query view directly
SELECT * FROM events_per_minute
WHERE timestamp > '2025-01-01'
ORDER BY timestamp DESC;
```

## External Data Integration

### S3 Integration

```sql
-- Direct query from S3
SELECT * FROM s3(
    'https://my-bucket.s3.amazonaws.com/data/events/*.parquet',
    'PARQUET'
)
LIMIT 100;

-- Insert from S3
INSERT INTO events
SELECT *
FROM s3(
    'https://my-bucket.s3.amazonaws.com/data/events/*.csv',
    'CSV',
    'timestamp DateTime, event_type String, user_id UInt32, value Float32'
);
```

### Kafka Integration

```sql
-- Create Kafka source table
CREATE TABLE events_kafka (
  timestamp DateTime,
  event_type String,
  user_id UInt32,
  value Float32
) ENGINE = Kafka()
SETTINGS
  kafka_broker_list = 'kafka-1:9092,kafka-2:9092',
  kafka_topic_list = 'events',
  kafka_group_id = 'clickhouse_consumer',
  kafka_format = 'JSONEachRow';

-- Create materialized view to consume
CREATE MATERIALIZED VIEW events_mv
TO events
AS SELECT * FROM events_kafka;
```

## Bulk Insert

```python
def bulk_insert_events(client, events, batch_size=10000):
    """Insert events efficiently in batches."""
    for i in range(0, len(events), batch_size):
        batch = events[i:i+batch_size]
        client.execute(
            'INSERT INTO events (timestamp, event_type, user_id, value) VALUES',
            batch,
            types_check=True
        )
        print(f"Inserted {i+len(batch)} events")

# Generate sample data
import datetime
import random

events = [
    (
        datetime.datetime.now() - datetime.timedelta(hours=random.randint(0, 24)),
        random.choice(['click', 'view', 'purchase']),
        random.randint(1, 10000),
        random.uniform(0, 100)
    )
    for _ in range(1000000)
]

bulk_insert_events(client, events, batch_size=50000)
```

## Real-World Examples

### Web Analytics Dashboard

```python
# Create tables
client.execute('''
CREATE TABLE IF NOT EXISTS page_views (
  timestamp DateTime,
  session_id UUID,
  user_id UInt32,
  page_path String,
  duration_ms UInt32,
  status_code UInt16,
  referrer String
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id)
PARTITION BY toYYYYMMDD(timestamp)
''')

# Query for dashboard
dashboard_data = client.execute('''
SELECT
  page_path,
  count() as views,
  count(DISTINCT user_id) as unique_users,
  round(avg(duration_ms), 0) as avg_duration_ms,
  round(quantile(0.95)(duration_ms), 0) as p95_duration_ms
FROM page_views
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY page_path
ORDER BY views DESC
LIMIT 20
''')
```

### Log Analysis

```sql
CREATE TABLE application_logs (
  timestamp DateTime,
  level Enum8('DEBUG' = 1, 'INFO' = 2, 'WARN' = 3, 'ERROR' = 4),
  service String,
  message String,
  trace_id String,
  duration_ms UInt32
) ENGINE = MergeTree()
ORDER BY (timestamp, service, level)
PARTITION BY toYYYYMMDD(timestamp)
TTL timestamp + INTERVAL 30 DAY;

-- Error pattern analysis
SELECT
  service,
  level,
  substring(message, 1, 100) as error_pattern,
  count() as occurrences,
  max(timestamp) as last_seen,
  avg(duration_ms) as avg_duration
FROM application_logs
WHERE timestamp > now() - INTERVAL 24 HOUR
  AND level >= 3
GROUP BY service, level, error_pattern
ORDER BY occurrences DESC
LIMIT 20;
```

### Time-Series Metrics

```python
# High-cardinality time-series storage
client.execute('''
CREATE TABLE metrics (
  timestamp DateTime,
  metric_name String,
  metric_value Float64,
  tags Array(String)
) ENGINE = MergeTree()
ORDER BY (timestamp, metric_name)
PARTITION BY toYYYYMMDD(timestamp)
''')

# Pre-aggregated hourly table
client.execute('''
CREATE MATERIALIZED VIEW metrics_1h
ENGINE = AggregatingMergeTree()
ORDER BY (hour, metric_name)
AS
SELECT
  toStartOfHour(timestamp) as hour,
  metric_name,
  sumState(metric_value) as total,
  countState(1) as count,
  minState(metric_value) as min_value,
  maxState(metric_value) as max_value
FROM metrics
GROUP BY hour, metric_name
''')

# Query with finalization
result = client.execute('''
SELECT
  metric_name,
  sumMerge(total) as total_value,
  countMerge(count) as data_points,
  minMerge(min_value) as min_value,
  maxMerge(max_value) as max_value
FROM metrics_1h
WHERE hour >= now() - INTERVAL 7 DAY
GROUP BY metric_name
ORDER BY metric_name
''')
```

## Troubleshooting Queries

### Check Slow Queries

```sql
SELECT
  query_duration_ms,
  query,
  read_rows,
  result_rows
FROM system.query_log
WHERE query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 10;
```

### Analyze Index Usage

```sql
SELECT
  table,
  name,
  type,
  data_compressed_bytes,
  data_uncompressed_bytes
FROM system.data_skipping_indices;
```

### Check Table Size

```sql
SELECT
  database,
  table,
  formatReadableSize(sum(bytes)) as size,
  sum(rows) as rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes) DESC;
```

---

**Related**: [README.md](https://www.heliosdb.com/docs/full/reference/protocols/clickhouse/readme/) | [CONFIGURATION.md](https://www.heliosdb.com/docs/full/reference/protocols/clickhouse/configuration/) | [COMPATIBILITY.md](https://www.heliosdb.com/docs/full/reference/protocols/clickhouse/compatibility/)