Skip to content

incremental_materialized_views

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

incremental_materialized_views

README.md

Incremental Materialized Views

Progressive tutorial: basic MVs to full Medallion Architecture.

Setup

1. Configure Environment

cp .env.example .env
# Edit .env with your connection details

2. Source Environment and Create Alias

source .env

Choose ONE alias based on your setup:

# Local ClickHouse (no password)
alias ch="clickhouse-client --host=$CLICKHOUSE_HOST --user=$CLICKHOUSE_USER"

# Local ClickHouse (with password)
alias ch="clickhouse-client --host=$CLICKHOUSE_HOST --user=$CLICKHOUSE_USER --password=$CLICKHOUSE_PASSWORD"

# ClickHouse Cloud
alias ch="clickhouse-client --host=$CLICKHOUSE_HOST --port=9440 --user=$CLICKHOUSE_USER --password=$CLICKHOUSE_PASSWORD --secure"

3. Verify Connection

ch --query "SELECT version()"

Quick Start

Run all examples with validation:

./run_tests.sh

Clean up everything:

ch --queries-file MASTER_CLEANUP.sql

Example 1: Basic Incremental MVs

Database: mv_demo_basic | Scenario: Page view tracking

Key Concept: A Materialized View in ClickHouse is an INSERT trigger, not a cached query.

%%{init: {'themeVariables': {'primaryTextColor': '#000000', 'lineColor': '#000000'}}}%%
flowchart LR
    A[INSERT] --> B[page_views_raw]
    B --> C{MV Trigger}
    C --> D[page_views_count]

    style A fill:#e1f5fe,color:#000000
    style B fill:#fff3e0,color:#000000
    style C fill:#f3e5f5,color:#000000
    style D fill:#e8f5e9,color:#000000
Loading
Step-by-Step Instructions

Step 1: Create Database and Source Table

ch --queries-file 01_basic_incremental_mvs/00_setup.sql

This creates:

  • Database mv_demo_basic
  • Source table page_views_raw (MergeTree)

Step 2: Create Materialized View

ch --queries-file 01_basic_incremental_mvs/01_basic_mv.sql

This creates:

  • Target table page_views_count (MergeTree)
  • Materialized View that counts page views by URL

Step 3: Run Demo Queries

ch --queries-file 01_basic_incremental_mvs/02_queries.sql

Demonstrates:

  • Inserting raw page view events
  • Querying aggregated counts (no computation at query time)

Step 4: Performance Comparison

ch --queries-file 01_basic_incremental_mvs/03_performance.sql

Compares query performance:

  • MV Target (pre-aggregated): ~3ms, reads only aggregated rows
  • Raw Table (on-the-fly): ~5ms, must scan 100K+ rows

Step 5: Cleanup (Optional)

ch --queries-file 01_basic_incremental_mvs/99_cleanup.sql

What You Learn

  • MVs trigger automatically on every INSERT
  • Results stored in target table - no query-time computation
  • Zero orchestration - no Airflow, no cron, no batch jobs

Limitation: MV creates new rows for each INSERT batch. Need manual GROUP BY for totals.

Example 2: SummingMergeTree

Database: mv_demo_summing | Scenario: Website metrics

Key Concept: SummingMergeTree automatically sums numeric columns when rows with the same ORDER BY key are merged.

%%{init: {'themeVariables': {'primaryTextColor': '#000000', 'lineColor': '#000000'}}}%%
flowchart LR
    A[INSERT] --> B[events_raw]
    B --> C{MV Trigger}
    C --> D[hourly_metrics<br/>SummingMergeTree]
    D --> E{Background<br/>Merge}
    E --> F[Auto-Summed<br/>Rows]

    style A fill:#e1f5fe,color:#000000
    style B fill:#fff3e0,color:#000000
    style C fill:#f3e5f5,color:#000000
    style D fill:#e8f5e9,color:#000000
    style E fill:#fce4ec,color:#000000
    style F fill:#e8f5e9,color:#000000
Loading
Step-by-Step Instructions

Step 1: Create Database and Source Table

ch --queries-file 02_summing_merge_tree/00_setup.sql

This creates:

  • Database mv_demo_summing
  • Source table events_raw (MergeTree)

Step 2: Create SummingMergeTree Target Table

ch --queries-file 02_summing_merge_tree/01_summing_tables.sql

This creates:

  • Target table hourly_metrics (SummingMergeTree)
  • ORDER BY (hour, page) defines the merge key

Step 3: Create Materialized View

ch --queries-file 02_summing_merge_tree/02_mvs.sql

This creates:

  • MV that aggregates events into hourly metrics

Step 4: Run Demo Queries

ch --queries-file 02_summing_merge_tree/03_queries.sql

Demonstrates:

  • Multiple INSERT batches creating duplicate keys
  • Using FINAL to see merged results before background merge
  • Using OPTIMIZE TABLE to force immediate merge

Step 5: Performance Comparison

ch --queries-file 02_summing_merge_tree/04_performance.sql

Compares query performance (500K events):

  • SummingMergeTree: ~4ms, reads 54 pre-merged rows
  • Regular MergeTree: ~3ms, reads 50 rows (no auto-sum)
  • Raw Events Table: ~10ms, must scan 500K rows

Step 6: Cleanup (Optional)

ch --queries-file 02_summing_merge_tree/99_cleanup.sql

What You Learn

  • Rows with same ORDER BY key automatically combine
  • Use FINAL or GROUP BY for accurate results before merge completes
  • Great for counters and simple summations

Limitation: Only handles SUM. Cannot do COUNT DISTINCT or AVG.

Example 3: AggregatingMergeTree

Database: mv_demo_aggregating | Scenario: E-commerce metrics with unique customers

Key Concept: AggregateFunction columns store intermediate STATE, enabling COUNT DISTINCT and AVG across INSERT batches.

%%{init: {'themeVariables': {'primaryTextColor': '#000000', 'lineColor': '#000000'}}}%%
flowchart LR
    A[INSERT] --> B[orders_raw]
    B --> C{MV with<br/>*State functions}
    C --> D[hourly_sales<br/>AggregatingMergeTree]
    D --> E[Query with<br/>*Merge functions]
    E --> F[Final<br/>Results]

    style A fill:#e1f5fe,color:#000000
    style B fill:#fff3e0,color:#000000
    style C fill:#f3e5f5,color:#000000
    style D fill:#e8f5e9,color:#000000
    style E fill:#fff9c4,color:#000000
    style F fill:#e8f5e9,color:#000000
Loading

State/Merge Pattern

INSERT Time QUERY Time
countState() countMerge()
sumState() sumMerge()
avgState() avgMerge()
uniqState() uniqMerge()
Step-by-Step Instructions

Step 1: Create Database and Source Table

ch --queries-file 03_aggregating_merge_tree/00_setup.sql

This creates:

  • Database mv_demo_aggregating
  • Source table orders_raw (MergeTree)

Step 2: Create AggregatingMergeTree Target Table

ch --queries-file 03_aggregating_merge_tree/01_aggregating_tables.sql

This creates:

  • Target table hourly_sales with AggregateFunction columns:
    • total_orders AggregateFunction(count, UInt64)
    • total_revenue AggregateFunction(sum, Decimal(10,2))
    • avg_order_value AggregateFunction(avg, Decimal(10,2))
    • unique_customers AggregateFunction(uniq, String)

Step 3: Create Materialized View with State Functions

ch --queries-file 03_aggregating_merge_tree/02_state_merge_mvs.sql

This creates:

  • MV using countState(), sumState(), avgState(), uniqState()

Step 4: Run Demo Queries

ch --queries-file 03_aggregating_merge_tree/03_queries.sql

Demonstrates:

  • Inserting orders with same customer across multiple batches
  • Querying with countMerge(), sumMerge(), avgMerge(), uniqMerge()
  • Correct COUNT DISTINCT even across separate INSERT batches

Step 5: Performance Comparison

ch --queries-file 03_aggregating_merge_tree/04_performance.sql

Compares query performance (1M orders):

  • AggregatingMergeTree + uniqMerge(): ~6ms, reads 14 rows
  • Raw Table + COUNT DISTINCT: ~26ms, must scan 1M rows

Step 6: Cleanup (Optional)

ch --queries-file 03_aggregating_merge_tree/99_cleanup.sql

What You Learn

  • *State() functions create mergeable intermediate state
  • *Merge() functions finalize results at query time
  • Enables accurate COUNT DISTINCT across multiple INSERT batches
  • Foundation for cascading aggregations

Example 4: Dictionaries

Database: mv_demo_dictionaries | Scenario: E-commerce order enrichment

Key Concept: Dictionaries provide O(1) in-memory lookups, replacing expensive JOINs for dimension data.

%%{init: {'themeVariables': {'primaryTextColor': '#000000', 'lineColor': '#000000'}}}%%
flowchart LR
    subgraph Source Tables
        P[dim_products]
        C[dim_customers]
    end

    subgraph Dictionaries
        PD[products_dict<br/>HASHED layout]
        CD[customers_dict<br/>HASHED layout]
    end

    subgraph Usage
        Q[Query with<br/>dictGet]
        MV[MV with<br/>dictGet]
    end

    P -->|SYSTEM RELOAD| PD
    C -->|SYSTEM RELOAD| CD
    PD -->|O 1 lookup| Q
    CD -->|O 1 lookup| Q
    PD -->|O 1 lookup| MV
    CD -->|O 1 lookup| MV

    style P fill:#fff3e0,color:#000000
    style C fill:#fff3e0,color:#000000
    style PD fill:#e8f5e9,color:#000000
    style CD fill:#e8f5e9,color:#000000
    style Q fill:#e1f5fe,color:#000000
    style MV fill:#f3e5f5,color:#000000
Loading

dictGet() Functions

Function Description
dictGet(dict, attr, key) Get attribute value by key
dictGetOrDefault(dict, attr, key, default) Get with fallback for missing keys
dictHas(dict, key) Check if key exists
Step-by-Step Instructions

Step 1: Create Database and Dimension Tables

ch --queries-file 04_dictionaries/00_setup.sql

This creates:

  • Database mv_demo_dictionaries
  • dim_products - Product catalog with price/cost
  • dim_customers - Customer profiles with tier
  • orders_raw - Fact table for orders
  • Sample dimension data (10 products, 10 customers)

Step 2: Create Dictionaries

ch --queries-file 04_dictionaries/01_dictionaries.sql

This creates:

  • products_dict - HASHED layout for product lookups
  • customers_dict - HASHED layout for customer lookups
  • Dictionaries are loaded into memory for O(1) access

Step 3: Learn dictGet() Usage

ch --queries-file 04_dictionaries/02_dictget_examples.sql

Demonstrates:

  • Basic dictGet() for single attribute lookup
  • Multiple attribute lookups in one query
  • dictGetOrDefault() for handling missing keys
  • Calculated fields using dictionary values

Step 4: Compare JOIN vs dictGet()

ch --queries-file 04_dictionaries/03_join_vs_dictget.sql

Demonstrates:

  • Traditional JOIN approach (slower)
  • dictGet() approach (faster, preferred)
  • Why dictionaries are better for dimension lookups

Step 5: Materialized View with Dictionaries

ch --queries-file 04_dictionaries/04_mv_with_dictionaries.sql

This creates:

  • orders_enriched - Target table with enriched data
  • MV that auto-enriches orders using dictGet()
  • Analytics queries on enriched data

Step 6: Performance Comparison

ch --queries-file 04_dictionaries/05_performance.sql

Compares query performance (500K orders):

  • dictGet() - O(1) lookup: ~15ms, constant time regardless of dimension size
  • JOIN - table scan: ~25ms, must scan dimension tables for every query

Shows why dictionaries are essential for dimension lookups at scale.

Step 7: Cleanup (Optional)

ch --queries-file 04_dictionaries/99_cleanup.sql

What You Learn

  • Dictionaries are in-memory key-value stores
  • O(1) lookup performance vs O(n) for JOINs
  • dictGet() works seamlessly in Materialized Views
  • Auto-refresh keeps dictionaries in sync with source tables
  • Foundation for real-time data enrichment

Key Insight: Use dictionaries for dimension tables, use JOINs only when you need to join fact tables.

Example 5: Medallion Architecture

Database: fastmart_demo | Scenario: FastMart e-commerce

Key Concept: Full Bronze -> Silver -> Gold pipeline combining all patterns from Examples 1-4.

%%{init: {'themeVariables': {'primaryTextColor': '#000000', 'lineColor': '#000000'}}}%%
flowchart TB
    subgraph Bronze ["Bronze Layer (7d TTL)"]
        A[events_raw<br/>JSON blobs]
    end

    subgraph Silver ["Silver Layer (30d TTL)"]
        B[orders_silver<br/>Parsed JSON]
        C[orders_enriched<br/>+ dictGet lookups]
    end

    subgraph Gold ["Gold Layer (90-365d TTL)"]
        D[sales_by_minute]
        E[sales_by_hour]
        F[sales_by_day]
    end

    A -->|MV: JSON parsing| B
    B -->|MV: Dictionary enrichment| C
    C -->|MV: State aggregation| D
    D -->|MV: Cascading rollup| E
    E -->|MV: Cascading rollup| F

    style A fill:#cd7f32,color:#000000
    style B fill:#c0c0c0,color:#000000
    style C fill:#c0c0c0,color:#000000
    style D fill:#ffd700,color:#000000
    style E fill:#ffd700,color:#000000
    style F fill:#ffd700,color:#000000
Loading
Step-by-Step Instructions

Prerequisites

pip install -r 05_medallion_architecture/scripts/requirements.txt

Environment Configuration

Configure data generation volume in .env:

# Recommended configurations:
#   Quick demo:     EVENT_COUNT=500000   EVENT_DAYS=1  (~2 min generation)
#   Standard demo:  EVENT_COUNT=1000000  EVENT_DAYS=1  (~4 min generation)
#   Large scale:    EVENT_COUNT=5000000  EVENT_DAYS=1  (~15 min generation)

export EVENT_COUNT=500000  # Number of events (minimum 500K recommended)
export EVENT_DAYS=1        # Time span (1 day recommended for dense aggregations)

Higher event counts produce more dramatic performance differences between layers.

Step 1: Create Database and Configuration

ch --queries-file 05_medallion_architecture/01_setup/00_config.sql

This creates:

  • Database fastmart_demo
  • Common settings and configurations

Step 2: Create Dimension Tables

ch --queries-file 05_medallion_architecture/01_setup/01_dimensions.sql

This creates:

  • dim_products - Product catalog
  • dim_stores - Store locations
  • dim_customers - Customer profiles

Step 3: Generate Dimension Data

python3 05_medallion_architecture/scripts/generate_dimensions.py

Populates dimension tables with sample data.

Step 4: Create Bronze Layer

ch --queries-file 05_medallion_architecture/02_bronze/10_bronze_tables.sql

This creates:

  • events_raw - Raw JSON event storage with 7-day TTL

Step 5: Create Silver Layer Tables

ch --queries-file 05_medallion_architecture/03_silver/20_silver_tables.sql

This creates:

  • orders_silver - Parsed order data
  • orders_enriched - Orders with dimension lookups

Step 6: Create Silver Layer MVs

ch --queries-file 05_medallion_architecture/03_silver/21_incremental_mvs.sql

This creates:

  • MV for JSON parsing (Bronze -> Silver)
  • MV for enrichment with dictGet()

Step 7: Create Dictionaries

ch --queries-file 05_medallion_architecture/03_silver/22_dictionaries.sql

This creates:

  • Dictionaries for O(1) dimension lookups

Step 8: Create Gold Layer - Minute Aggregations

ch --queries-file 05_medallion_architecture/04_gold/30_gold_minute.sql

This creates:

  • sales_by_minute with State functions
  • MV from Silver -> Gold minute

Step 9: Create Gold Layer - Hourly/Daily Rollups

ch --queries-file 05_medallion_architecture/04_gold/31_gold_hourly.sql

This creates:

  • sales_by_hour - Cascading from minute
  • sales_by_day - Cascading from hour

Step 10: Generate Test Events

# Uses EVENT_COUNT and EVENT_DAYS from .env (default: 500K events, 1 day)
python3 05_medallion_architecture/scripts/generate_events.py

Generates test events using configuration from .env. Default 500K events over 1 day provides sufficient data density for meaningful aggregation comparisons.

Step 11: (Optional) Stream Real-Time Events

python3 05_medallion_architecture/scripts/stream_simulator.py --rate 500

Continuously streams events at 500/second.

Step 12: Performance Comparison

ch --queries-file 05_medallion_architecture/05_queries/43_performance.sql

Compares query performance across all layers:

  • Gold Layer (sales_by_day): ~2ms, reads pre-aggregated rows - FASTEST
  • Gold Layer (sales_by_hour): ~3ms, slightly more rows
  • Silver Layer (orders_enriched): ~15ms, must scan enriched rows
  • Bronze Layer (events_raw): ~50ms, JSON parsing + dictionary lookups - SLOWEST

Demonstrates:

  • Data volume reduction at each layer
  • Query time improvement from layered aggregation
  • Scalability analysis (10x, 100x data projections)

Step 13: Cleanup (Optional)

ch --queries-file 05_medallion_architecture/06_cleanup/99_cleanup.sql

Data Flow Diagram

%%{init: {'themeVariables': {'primaryTextColor': '#000000', 'lineColor': '#000000'}}}%%
flowchart LR
    subgraph Input
        API[API Events]
        Kafka[Kafka Stream]
    end

    subgraph Processing
        B[Bronze<br/>events_raw]
        S1[Silver<br/>orders_silver]
        S2[Silver<br/>orders_enriched]
        G1[Gold<br/>sales_by_minute]
        G2[Gold<br/>sales_by_hour]
        G3[Gold<br/>sales_by_day]
    end

    subgraph Lookups
        D1[dict: products]
        D2[dict: stores]
        D3[dict: customers]
    end

    API --> B
    Kafka --> B
    B -->|JSON parse| S1
    S1 --> S2
    D1 -.->|dictGet| S2
    D2 -.->|dictGet| S2
    D3 -.->|dictGet| S2
    S2 -->|*State| G1
    G1 -->|cascade| G2
    G2 -->|cascade| G3
Loading

Key Patterns

Pattern File Description
JSON parsing 03_silver/21_incremental_mvs.sql Extract fields from raw JSON
Dictionary enrichment 03_silver/22_dictionaries.sql O(1) lookups via dictGet()
State functions 04_gold/30_gold_minute.sql Aggregate with *State()
Cascading aggregation 04_gold/31_gold_hourly.sql Rollup minute->hour->day

ClickHouse Cloud

Update .env for cloud connections:

CLICKHOUSE_HOST=abc123.us-east-1.aws.clickhouse.cloud
CLICKHOUSE_PORT=8443
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=your-password
CLICKHOUSE_SECURE=true

Then source and create alias:

source .env
alias ch="clickhouse-client --host=$CLICKHOUSE_HOST --port=9440 --user=$CLICKHOUSE_USER --password=$CLICKHOUSE_PASSWORD --secure"

Project Structure

incremental_materialized_views/
|-- 01_basic_incremental_mvs/
|-- 02_summing_merge_tree/
|-- 03_aggregating_merge_tree/
|-- 04_dictionaries/
|-- 05_medallion_architecture/
|   |-- 01_setup/
|   |-- 02_bronze/
|   |-- 03_silver/
|   |-- 04_gold/
|   |-- 05_queries/
|   |-- 06_cleanup/
|   |-- scripts/
|-- demo/                        # Single-file demo combining all concepts
|   |-- README.md
|   |-- webinar_demo.sql
|-- .env.example
|-- .env
|-- MASTER_CLEANUP.sql
|-- run_tests.sh
|-- README.md