DOCSEARCHbyAlgolia
Submit Docs

ClickHouse Examples and Recipes

repository·main·Indexed 42 days ago

https://github.com/clickhouse/examples

A collection of examples, recipes, and tutorials for building with ClickHouse. Covers real-time analytics, data pipelines, observability, and AI agents, including agentic SLA-breach detection using clickhousectl and Model Context Protocol (MCP) integrations with Agno, AnythingLLM, and Chainlit.

Tokens
257.8K
Snippets
895
Records
1.1K
Agent score
51%

What's inside ClickHouse Examples

  1. Overview of the Stock Reservation API example

    main

    The Stock Reservation API is a demonstration application built with Hono, Bun 1.4.2, and Bun.SQL, using ClickHouse Managed Postgres as the backend. It demonstrates how to handle limited inventory with transactional integrity and idempotency.

    Key Features

    • Transactional Integrity: All stock changes occur within Postgres transactions. Conditional inventory updates prevent over-reservation.
    • Idempotency: Retrying a successful request with the same Idempotency-Key returns the original reservation without consuming additional stock.
    • Concurrency Control: Uses unique constraints to coordinate requests using the same client identity and idempotency key across different processes.
    • Release Safety: Releasing a reservation restores its quantity exactly once, even if multiple release requests are received simultaneously.

  2. Overview of the Feature Request Board example

    main

    The Feature Request Board is a demonstration application built using Next.js, Prisma ORM, node-postgres, Clerk, and ClickHouse Managed Postgres.

    It allows users to:

    • Submit feature ideas.
    • Vote once per request (enforced at the database level).
    • Filter requests by status.
    • Authors can edit or delete their own requests.
    • Designated maintainers can change request statuses.

    Note: This specific example uses ClickHouse Managed Postgres as a standard transactional database for all application records, relationships, and vote counts. It does not require a ClickHouse analytical service or ClickPipes setup. For an example that uses ClickHouse for analytics, see the Shortwave example in this repository.

  3. Overview of UpClick GCP Demo

    main

    The UpClick GCP Demo is a demonstration of a GCP 2nd gen Pub/Sub NodeJS cloud function. The function is triggered by a Cloud Scheduler via Pub/Sub messages containing a target website URL.

    Workflow:

    1. A target website is received via Pub/Sub.
    2. The function tests the website for latency and captures metrics.
    3. Data is enriched with geographic information using MaxMind geo-ip lite.
    4. The enriched metrics are sent to ClickHouse Cloud.
    WARNING

    Unmaintained example. This example was last updated in December 2024 and is no longer actively maintained. Dependencies and instructions may be out of date.

  4. Overview of Local Analytics with ClickHouse

    main

    Local analytics allows you to analyze files and data sources directly on your laptop without setting up a server or performing an import step. This is achieved using two primary surfaces:

    1. clickhouse-local: A command-line interface (CLI) for running SQL on local files.
    2. chDB: An in-process ClickHouse implementation for Python, allowing you to query data using SQL within your Python scripts.

    Each example in this repository is a self-contained folder designed to accompany a specific how-to article on clickhouse.com.

  5. Overview of the Shortwave Link Shortener Example

    main

    Shortwave is a SaaS link-shortener example built using the ClickHouse Cloud unified data stack. It demonstrates how to combine application data storage, real-time data synchronization, and high-performance analytics.

    Core Components:

    • ClickHouse Managed Postgres: Stores application data (links, tags, folders, UTM templates).
    • ClickPipes: Synchronizes link metadata from Postgres to ClickHouse via Change Data Capture (CDC).
    • ClickHouse Cloud: Powers click analytics and stores recorded traffic.
    • TanStack Start App: A React-based frontend running on Cloudflare Workers, using Click UI and Clerk for authentication.

    Key Features:

    • Create, edit, and organize short links with tags and folders.
    • Save UTM templates and download QR codes.
    • Explore recorded traffic and analytics.
    • Automatic handling of bots and previews in reports.

  6. Overview of ClickHouse Language Clients Tour

    main
    This repository provides a unified tour of eight official ClickHouse client libraries. The goal is to demonstrate how the same program—connecting via TLS, creating tables, performing batched inserts, running parameterized queries, streaming rows, and handling errors—is expressed across different programming languages. Each implementation follows a strict contract defined in SPEC.md and produces identical output as seen in expected-output.txt.

  7. Overview of ClickHouse products and services

    main

    ClickHouse offers several ways to interact with analytical and transactional data:

    • ClickHouse (Analytical Database): An open-source, column-oriented SQL database for fast analytics. Available as a managed service in ClickHouse Cloud (AWS, Google Cloud, Azure).
    • ClickHouse Managed Postgres: A PostgreSQL service within ClickHouse Cloud designed for transactional applications. It integrates with the analytical service via ClickPipes for managed ingestion (CDC, streaming, object storage).
    • chDB: An embedded ClickHouse engine for Python. Allows querying files and DataFrames with SQL without a running database server.
    • clickhouse-local: A command-line tool that runs the ClickHouse engine to query local or remote files (CSV, Parquet, etc.) directly from your terminal.
    • ClickStack: An observability stack combining ClickHouse, OpenTelemetry, and HyperDX for logs, metrics, and traces.
    • LibreChat & Agentic Data Stack: Tools for building AI agents that can query ClickHouse via the ClickHouse MCP server.

  8. Overview of the Workshop Booking API

    main

    The Workshop Booking API is a demonstration application built with FastAPI, SQLAlchemy, psycopg, and Alembic running on ClickHouse Managed Postgres.

    It solves the concurrency problem of ensuring two users cannot book the same last seat in a workshop. The application uses a locking protocol where the API locks the workshop row, checks availability, and inserts the booking within a single transaction.

    API Routes

    RoutePurposeSuccess
    GET /workshopsList upcoming workshops and available seats200
    POST /bookingsBook one seat using { "id": "UUID", "workshop_id": "UUID" }201, or 200 on retry
    GET /bookings/{id}Read your booking200
    DELETE /bookings/{id}Cancel your booking200

    Note: Booking routes require a bearer token. The server maps tokens to seeded attendees; callers cannot choose a different owner in the request.

  9. Use the clickhouse-connect Python driver

    main

    clickhouse-connect is the official ClickHouse Python driver. It uses the HTTP(S) protocol and exchanges data in ClickHouse's native columnar format for efficient typed inserts and queries.

    Key features include:

    • Support for pandas, Polars, and Arrow integrations.
    • An AsyncClient for asynchronous operations.
    • A synchronous Client for standard use cases.
    • Native handling of ClickHouse error codes via DatabaseError.code.

    This driver is compatible with Python 3.10 through 3.14.

    import clickhouse_connect

# Basic client initialization
client = clickhouse_connect.get_client(host='...', secure=True)

# Execute a command (DDL)
client.command('CREATE TABLE IF NOT EXISTS my_table (...)')

# Insert data using Python lists
client.insert('my_table', rows=[[1, 'data'], [2, 'more']], column_names=['id', 'name'])

# Execute a parameterized query
result = client.query('SELECT * FROM my_table WHERE id = {id:UInt64}', parameters={'id': 1})

# Stream rows to avoid buffering large results
with client.query_rows_stream('SELECT * FROM my_table') as stream:
    for row in stream:
        print(row)

  10. Deployment and architecture documentation for Shortwave

    main

    For detailed information regarding the deployment lifecycle and system design, refer to the following resources:

    • Deployment inputs and order: See docs/setup-plan.md.
    • Architecture and decisions: See docs/architecture.md and docs/decisions.md.
    • Deployment and release checklist: See docs/v1-readiness.md for requirements before a production release.

    Current Hosting Support:

    • V1 currently supports Cloudflare Workers hosting.
    • Future extensions planned include Vercel, automatic customer-domain provisioning, ClickStack, and pg_clickhouse.

  11. Explore ClickHouse examples and resources

    main

    The clickhouse/clickhouse/examples repository contains a variety of practical implementations, client tours, and integration patterns. You can explore the following categories of examples:

    • Applications: Real-world use cases like link shorteners, feature request boards, workshop bookings, and report history management using ClickHouse Cloud.
    • AI & Agents: AI agent workflows, MCP (Model Context Protocol) integrations, and clickhousectl usage.
    • Language Clients: Step-by-step tours for official clients in C#, Java, Rust, Go, C++, Python, and Node.js.
    • Local Analytics: Using clickhouse-local and chDB to query files directly from your terminal or within Python.
    • Data Ingestion & Modeling: Blockchain (Ethereum) schemas and ingestion, and PostgreSQL replication/modeling using PeerDB.
    • Observability: ClickStack examples for LLM applications and MCP servers.
    • Deployment: Docker Compose recipes for local deployments and cluster configurations.

  12. Use Ethereum batch schemas for ClickHouse

    main
    The ethereum/batch/schemas directory provides the SQL table definitions required to store Ethereum dataset batches in ClickHouse. This directory includes schemas for three primary entities: blocks, transactions, and traces. Use these .sql files to initialize your ClickHouse database environment before ingesting Ethereum batch data.