Przejdź do treści
Biblioteka skilli
Dane i analityka · Budowanie i kod

realtime-analytics

/realtime-analytics

Buduje potoki analityki w czasie rzeczywistym od zera: projekt schematu zdarzeń, usługi ingestii z batchowaniem, optymalizacja tabel ClickHouse, zapytania agregujące i podłączenie dashboardu.

Repozytorium
skills ↗
Licencja
Apache-2.0
Ostatnia zmiana repozytorium
25.10.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
2
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
2
Przestarzałość

Brak sygnałów. Repozytorium zmienione 25.10.2026, bez odwołań do wycofanych modeli i API.

Licencja

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

Co robi i kiedy

Buduje potoki analityki w czasie rzeczywistym od zera: projekt schematu zdarzeń, usługi ingestii z batchowaniem, optymalizacja tabel ClickHouse, zapytania agregujące i podłączenie dashboardu.

Wyzwalacze z opisu

set up analyticsbuild a dashboardtrack events in real timeClickHouse analyticsevent ingestion pipelinelive metrics
Oryginalny opis (EN)

Build real-time analytics pipelines from scratch. Use when someone asks to "set up analytics", "build a dashboard", "track events in real time", "ClickHouse analytics", "event ingestion pipeline", or "live metrics". Covers event schema design, ingestion services with batching, ClickHouse table optimization, aggregation queries, and dashboard wiring.

Jak działa

  1. Overview
  2. Instructions
  3. Examples
  4. Guidelines
Sekcje z nagłówków SKILL.md, w oryginale · 183 linii, 882 słów
Podgląd SKILL.md
---
name: realtime-analytics
description: >-
  Build real-time analytics pipelines from scratch. Use when someone asks to
  "set up analytics", "build a dashboard", "track events in real time",
  "ClickHouse analytics", "event ingestion pipeline", or "live metrics".
  Covers event schema design, ingestion services with batching, ClickHouse
  table optimization, aggregation queries, and dashboard wiring.
license: Apache-2.0
compatibility: "Node.js 20+ (@clickhouse/client), ClickHouse 23.8+, Docker recommended"
metadata:
  author: terminal-skills
  version: "1.1.0"
  repository: https://github.com/ClickHouse/ClickHouse
  category: data-ai
  tags: "analytics, real-time, clickhouse, event-tracking"
---

# Real-Time Analytics

## Overview

This skill enables AI agents to build self-hosted, real-time analytics systems. It covers the full pipeline from event ingestion through storage to query and visualization, using ClickHouse as the analytical database for sub-second query performance at scale.

## Instructions

### Event Schema Design

1. Every event must have these base fields:
   - `event_name` — LowCardinality(String) for efficient storage
   - `timestamp` — DateTime64(3) for millisecond precision
   - `session_id` — String, client-generated UUID
   - `user_id` — Nullable(String) for anonymous tracking
   - `device_type` — LowCardinality(String): desktop, mobile, tablet
   - `country_code` — LowCardinality(FixedString(2))
   - `properties` — String containing JSON for event-specific data

2. ClickHouse table optimization rules:
   - Use `MergeTree()` engine, partition by `toYYYYMM(date)`
   - ORDER BY should start with the most filtered column (usually `event_name`)
   - Add TTL for automatic data expiration (default 90 days)
   - Use `LowCardinality()` for any string column with fewer than 10,000 distinct values

### Ingestion Service

1. Build as a stateless HTTP service accepting `POST /events` with JSON array body.
2. Validate incoming events: reject if `event_name` or `timestamp` is missing.
3. Buffer events in memory. Flush when either condition is met:
   - Buffer reaches 1,000 events
   - 2 seconds have elapsed since last flush
4. Use ClickHouse's `INSERT ... FORMAT JSONEachRow` for batch inserts (`npm install @clickhouse/client`). ISO-8601 timestamps such as `2026-10-13T09:15:02.481Z` are rejected by default; pass `clickhouse_settings: { date_time_input_format: 'best_effort' }` or convert to `YYYY-MM-DD hh:mm:ss.mmm` first. ClickHouse recommends 10,000-100,000 rows per insert; 1,000 is the floor, so raise `FLUSH_SIZE` at high volume or enable `async_insert=1` (keep `wait_for_async_insert=1`) and let the server batch.
5. On flush failure, retry 3 times with exponential backoff, then write to a dead-letter file.
6. Expose `GET /health` returning: `{ "buffer_size": N, "last_flush": "ISO timestamp", "status": "ok" }`.

### Aggregation Queries

Write queries as named `.sql` files. Common dashboard panels:

**Active users (last N minutes):**
```sql
SELECT uniq(session_id) AS active_users  -- approximate and fast; uniqExact() for exact counts
FROM events
WHERE timestamp > now() - INTERVAL 5 MINUTE;
```

**Funnel analysis (two-step):**
```sql
SELECT
  toStartOfHour(timestamp) AS hour,
  countIf(event_name = 'signup_started') AS started,
  countIf(event_name = 'signup_completed') AS completed,
  round(completed / started * 100, 1) AS conversion_pct
FROM events
WHERE date = today()
GROUP BY hour
ORDER BY hour;
```

**Top features by unique users:**
```sql
SELECT
  JSONExtractString(properties, 'feature') AS feature,
  count(DISTINCT user_id) AS unique_users
FROM events
WHERE event_name = 'feature_used' AND date = today()
GROUP BY feature
ORDER BY unique_users DESC
LIMIT 10;
```

**Error rate per minute:**
```sql
SELECT
  toStartOfMinute(timestamp) AS minute,
  count() AS error_count
FROM events
WHERE event_name = 'error_occurred'
  AND timestamp > now() - INTERVAL 1 HOUR
GROUP BY minute
ORDER BY minute;
```

### Dashboard API Layer

1. Create one REST endpoint per dashboard panel (e.g., `GET /api/dashboard/active-users`).
2. Each endpoint runs its SQL query and returns JSON.
3. Add a `time_range` query param: `1h`, `6h`, `24h`, `7d`. Adjust the WHERE clause accordingly.
4. Cache results for 3-5 seconds to avoid hammering ClickHouse on concurrent dashboard loads.

### Docker Compose Setup

Provide a `docker-compose.yml` with:
- ClickHouse service (pinned version, volume for data)
- Ingestion service (builds from Dockerfile)
- Dashboard frontend (builds from Dockerfile or served via nginx)
- An init container or entrypoint script that creates the events table

## Examples

### Example 1 — Schema for an e-commerce app

**Input:** "Design analytics events for an e-commerce checkout flow."

**Output:**
```sql
-- Core events: product_viewed, add_to_cart, checkout_started,
-- payment_submitted, order_completed, order_failed

CREATE TABLE events (
    event_name   LowCardinality(String),
    timestamp    DateTime64(3),
    session_id   String,
    user_id      Nullable(String),
    device_type  LowCardinality(String),
    country_code LowCardinality(FixedString(2)),
    properties   String,
    date         Date DEFAULT toDate(timestamp)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (event_name, timestamp)
TTL date + INTERVAL 180 DAY;
```

Properties JSON per event:
- `product_viewed`: `{"product_id": "p-42", "category": "electronics", "price_cents": 4999}`
- `add_to_cart`: `{"product_id": "p-42", "quantity": 1}`
- `payment_submitted`: `{"method": "card", "amount_cents": 9998}`

### Example 2 — Ingestion buffer logic

**Input:** "Show the buffer flush logic in Node.js."

**Output:**
```js
import { createClient } from '@clickhouse/client';

const clickhouse = createClient({
  url: process.env.CLICKHOUSE_URL ?? 'http://localhost:8123',
  password: process.env.CLICKHOUSE_PASSWORD ?? '',
});
const FLUSH_INTERVAL_MS = 2000;
const FLUSH_SIZE = 1000;
let buffer = [];

async function addEvents(events) {
  buffer.push(...events);
  if (buffer.length >= FLUSH_SIZE) await flush();
}

setInterval(async () => {
  if (buffer.length > 0) await flush();
}, FLUSH_INTERVAL_MS);

async function flush(attempt = 1) {
  const batch = buffer.splice(0, buffer.length);
  try {
    await clickhouse.insert({
      table: 'events',
      values: batch,
      format: 'JSONEachRow',
      clickhouse_settings: { date_time_input_format: 'best_effort' },
    });
  } catch (err) {
    if (attempt >= 3) throw err; // caller writes the batch to the dead-letter file
    await new Promise(r => setTimeout(r, 2 ** attempt * 250));
    buffer.unshift(...batch);
    return flush(attempt + 1);
  }
}
```

## Guidelines

- **Partition wisely.** Monthly partitions work for most apps. Daily only if you ingest > 100M events/day.
- **Do not use Kafka unless asked.** For under 50K events/second, direct HTTP ingestion with in-memory buffering is simpler and sufficient.
- **Always add TTL.** Unbounded analytical tables grow fast. Default to 90 days; let the user override.
- **Test with realistic volume.** Generate synthetic events to validate the pipeline handles expected throughput before going live.
- **Cardinality matters in ORDER BY.** Put low-cardinality columns first and `timestamp` last; the table's sort key decides how much data each query reads.
- **Avoid SELECT *.** Always specify columns in aggregation queries to minimize I/O.
Pliki w paczce (2)
  • SKILL.md 7 KB
  • _scores.json 3 KB

Wersje

W bazie nie ma innych skilli o nazwie realtime-analytics.

Migawka bazy z 13.11.2026 · wersja paczki 83ebb2d23919 (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100clickhouse-query
    Uruchamia zapytania ClickHouse do analityki, analizy metryk i eksploracji danych zdarzeń, domyślnie tylko do odczytu.
  • 100tinybird
    Buduje API analityki w czasie rzeczywistym z Tinybird: pobieranie milionów zdarzeń, zapytania SQL przez HTTP, pipes, endpointy API i dashboardy.
  • 90clickhouse-real-time-analytics
    Prowadzi agentów przez projekt analityki czasu rzeczywistego w ClickHouse: szybkie warstwy serwujące, agregacje zdarzeń, widoki zmaterializowane i dostęp do metryk z niskim opóźnieniem.
  • 100clickhouse-webhooks-events
    Wczytuje dane do ClickHouse z webhooków, Kafki i źródeł strumieniowych z batchowaniem, deduplikacją i wzorcami exactly-once.
  • 90clickhouse-analytics
    Odpowiada na pytania o dane w ClickHouse przez serwer ch-ui MCP, w tym o tabele, metryki i wydajność zapytań.
  • 100clickhouse
    Obsługuje ClickHouse, kolumnową bazę OLAP: uruchamianie w Dockerze, projektowanie tabel MergeTree, ładowanie zdarzeń i logów, zapytania agregujące i retencyjne, rollupy i połączenie z Node.js.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.