ClickHouse 介绍

ClickHouse详解 一 clickhouse-简介 ​ ClickHouse是俄罗斯的Yandex于2016年开源的一个用于联机分析(OLAP:Online Analytical Processing)的列式数据库管理系统(DBMS:Database Management System) , 主要用于在线分析处理查询(OLAP),能够使用SQL查询实时生成分析数据报告。 ClickHouse的全称是Click Stream,Data WareHouse,简称ClickHouse 实时数据 ,事件,快 [外链图片转存失 阅读详情

ClickHouse 介绍

目录

  1. 简介
  2. 核心特性
  3. 架构设计
  4. 数据模型
  5. 表引擎
  6. 查询语言
  7. 集群与复制
  8. 性能优化
  9. 应用场景
  10. 最佳实践

简介

ClickHouse 是一个用于 联机分析处理(OLAP) 的列式数据库管理系统(DBMS)。它由 Yandex 公司于 2016 年开源,主要用于处理海量数据的快速分析查询。

发展历史

  • 2009年:Yandex 开始开发 ClickHouse,用于解决 Metrica 分析系统中的性能瓶颈
  • 2016年:ClickHouse 正式开源,采用 Apache 2.0 许可证
  • 2021年:ClickHouse, Inc. 成立,成为独立公司运营
  • 至今:已成为业界领先的开源 OLAP 数据库之一

核心定位

ClickHouse 专为以下场景设计:

  • 宽表分析:处理包含数百列的宽表
  • 实时分析:支持亚秒级查询响应
  • 高吞吐量:每秒可处理数十亿行数据
  • 压缩存储:高效的数据压缩算法

核心特性

1. 列式存储

ClickHouse 采用列式存储架构,数据按列而非按行存储:

列式存储

行式存储

转换

Row 1: col1,col2,col3

Row 2: col1,col2,col3

Row 3: col1,col2,col3

Column 1: val1,val2,val3

Column 2: val1,val2,val3

Column 3: val1,val2,val3

优势:

  • 减少 I/O:查询时只读取需要的列
  • 高效压缩:同类型数据压缩率更高
  • 向量化执行:利用 SIMD 指令加速计算

2. 数据压缩

ClickHouse 支持多种压缩算法:

压缩算法压缩率解压速度适用场景
LZ4低最快实时性要求高
ZSTD中快通用场景
ZSTD(1-22)高中等存储敏感场景
NONE无最快已压缩数据

3. 索引机制

ClickHouse 使用稀疏索引和二级索引:

命中

未命中

可用

不可用

查询请求

主键过滤

稀疏索引定位

全表扫描

数据块扫描

二级索引

跳过无关数据块

读取所有数据

返回结果

4. 向量化执行

ClickHouse 使用向量化执行引擎,利用 CPU 的 SIMD(单指令多数据)指令集:

SQL查询

查询解析

查询优化

生成执行计划

向量化执行引擎

SIMD指令集

批量数据处理

结果聚合

返回结果


架构设计

整体架构

存储介质

ClickHouse Server

客户端层

分布式层

存储层

执行层

查询层

ClickHouse Client

HTTP Interface

JDBC/ODBC Driver

第三方工具

Parser

Interpreter

Optimizer

I/O线程池

数据处理线程

聚合线程

表引擎

数据分区

索引管理

压缩模块

ZooKeeper

副本协调

分布式DDL

本地磁盘/SSD

S3对象存储

HDFS

查询处理流程

Storage Executor Optimizer Interpreter Parser 客户端 Storage Executor Optimizer Interpreter Parser 客户端 发送SQL查询 词法分析 语法分析 生成AST 查询优化 重写查询 选择索引 生成执行计划 读取数据 返回数据块 向量化处理 聚合计算 返回结果

表引擎架构

表引擎

Log系列

MergeTree系列

集成系列

特殊系列

TinyLog

StripeLog

Log

MergeTree

ReplacingMergeTree

SummingMergeTree

AggregatingMergeTree

CollapsingMergeTree

VersionedCollapsingMergeTree

GraphiteMergeTree

HDFS

Kafka

S3

MySQL

PostgreSQL

ODBC

JDBC

Dictionary

View

MaterializedView

File

Null

Set

Join

URL

Memory

Buffer

数据分区架构

表 Table

分区 Partition

分区1: 2024-01

分区2: 2024-02

分区3: 2024-03

数据块 Part

Part 1

Part 2

Part 3

列数据 Column

Column_1.mr

Column_2.mr

Column_3.mr

标记文件 Marks

primary.idx

*.mrk

*.mrk2


数据模型

基本数据类型

数值类型
类型大小范围说明
UInt81字节0-255无符号8位整数
UInt162字节0-65535无符号16位整数
UInt324字节0-4294967295无符号32位整数
UInt648字节0-18446744073709551615无符号64位整数
Int81字节-128-127有符号8位整数
Int162字节-32768-32767有符号16位整数
Int324字节-2147483648-2147483647有符号32位整数
Int648字节-9223372036854775808-9223372036854775807有符号64位整数
Float324字节-单精度浮点数
Float648字节-双精度浮点数
Decimal可变-高精度小数
字符串类型
类型说明
String可变长度字符串
FixedString(N)固定长度N的字符串
UUIDUUID类型
时间类型
类型说明
Date日期,精度到天
DateTime日期时间,精度到秒
DateTime64日期时间,支持亚秒精度
特殊类型
类型说明
Enum88位枚举
Enum1616位枚举
Array(T)数组类型
Tuple(T1, T2, …)元组类型
Map(K, V)键值对类型
Nested嵌套结构
Nullable(T)可空类型
LowCardinality(T)低基数类型优化

表结构设计示例

-- 创建用户行为分析表
CREATE TABLE user_events
(
    -- 主键和分区键
    event_date Date,
    event_time DateTime,
    
    -- 用户信息
    user_id UInt64,
    user_type Enum8('guest' = 0, 'registered' = 1, 'vip' = 2),
    
    -- 事件信息
    event_type String,
    event_category LowCardinality(String),
    page_url String,
    referrer_url String,
    
    -- 设备信息
    device_type Enum8('desktop' = 0, 'mobile' = 1, 'tablet' = 2),
    os_name LowCardinality(String),
    browser_name LowCardinality(String),
    
    -- 位置信息
    country LowCardinality(String),
    city LowCardinality(String),
    
    -- 业务指标
    session_id String,
    duration_seconds UInt32,
    page_views UInt16,
    
    -- 数组类型
    tags Array(String),
    product_ids Array(UInt64)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_time)
SETTINGS index_granularity = 8192;

表引擎

MergeTree 系列

MergeTree 是 ClickHouse 中最核心的表引擎系列,支持数据分区、索引、副本等功能。

1. MergeTree

基础表引擎,支持数据分区和索引。

CREATE TABLE orders
(
    order_id UInt64,
    user_id UInt64,
    order_date Date,
    order_time DateTime,
    product_id UInt64,
    quantity UInt32,
    price Decimal(18, 2),
    status LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, user_id, order_id)
SETTINGS index_granularity = 8192;

参数说明:

  • PARTITION BY:分区键,按月分区
  • ORDER BY:排序键,决定数据在分区内的排序方式
  • PRIMARY KEY:主键,默认与 ORDER BY 相同
  • SETTINGS index_granularity:索引粒度,默认8192行
2. ReplacingMergeTree

支持去重的 MergeTree,根据 ORDER BY 键进行去重。

CREATE TABLE orders_unique
(
    order_id UInt64,
    user_id UInt64,
    order_date Date,
    order_time DateTime,
    product_id UInt64,
    quantity UInt32,
    price Decimal(18, 2),
    status LowCardinality(String),
    version UInt64
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_id)
SETTINGS index_granularity = 8192;

特点:

  • 根据 ORDER BY 键去重
  • 保留 version 值最大的记录
  • 去重在合并时执行,不是实时的
3. SummingMergeTree

预聚合表引擎,自动对数值列进行求和。

CREATE TABLE daily_sales
(
    date Date,
    product_id UInt64,
    category LowCardinality(String),
    region LowCardinality(String),
    sales_amount Decimal(18, 2),
    sales_count UInt32,
    order_count UInt32
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, product_id, category, region)
SETTINGS index_granularity = 8192;

特点:

  • 自动对非主键的数值列求和
  • 适合预聚合场景
4. AggregatingMergeTree

支持自定义聚合函数的表引擎。

CREATE TABLE user_daily_stats
(
    date Date,
    user_id UInt64,
    -- 聚合状态列
    page_views AggregateFunction(sum, UInt64),
    unique_pages AggregateFunction(uniq, String),
    session_duration AggregateFunction(avg, UInt64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, user_id)
SETTINGS index_granularity = 8192;

-- 插入聚合数据
INSERT INTO user_daily_stats
SELECT
    toDate(event_time) as date,
    user_id,
    sumState(page_views) as page_views,
    uniqState(page_url) as unique_pages,
    avgState(duration_seconds) as session_duration
FROM user_events
GROUP BY date, user_id;

-- 查询聚合结果
SELECT
    date,
    user_id,
    sumMerge(page_views) as page_views,
    uniqMerge(unique_pages) as unique_pages,
    avgMerge(session_duration) as avg_session_duration
FROM user_daily_stats
GROUP BY date, user_id;
5. CollapsingMergeTree

通过 sign 字段实现增量更新和删除。

CREATE TABLE orders_collapsing
(
    order_id UInt64,
    user_id UInt64,
    order_date Date,
    product_id UInt64,
    quantity UInt32,
    price Decimal(18, 2),
    sign Int8  -- 1表示插入,-1表示删除
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_id)
SETTINGS index_granularity = 8192;

使用方式:

  • 插入:sign = 1
  • 删除:插入相同主键的记录,sign = -1
  • 更新:先删除(sign = -1),再插入(sign = 1)
6. VersionedCollapsingMergeTree

CollapsingMergeTree 的改进版,支持并发写入。

CREATE TABLE orders_versioned
(
    order_id UInt64,
    user_id UInt64,
    order_date Date,
    product_id UInt64,
    quantity UInt32,
    price Decimal(18, 2),
    sign Int8,
    version UInt64
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_id)
SETTINGS index_granularity = 8192;

Log 系列

Log 系列引擎适用于临时数据和小批量数据写入。

-- TinyLog:最简单的引擎,无索引
CREATE TABLE events_tinylog
(
    event_time DateTime,
    event_type String,
    user_id UInt64
)
ENGINE = TinyLog();

-- StripeLog:支持并发读取
CREATE TABLE events_stripelog
(
    event_time DateTime,
    event_type String,
    user_id UInt64
)
ENGINE = StripeLog();

-- Log:支持并发写入
CREATE TABLE events_log
(
    event_time DateTime,
    event_type String,
    user_id UInt64
)
ENGINE = Log();

集成系列

Kafka 引擎
CREATE TABLE kafka_events
(
    event_time DateTime,
    event_type String,
    user_id UInt64,
    event_data String
)
ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka1:9092,kafka2:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 3;

-- 创建物化视图实时消费Kafka数据
CREATE TABLE events
(
    event_time DateTime,
    event_type String,
    user_id UInt64,
    event_data String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

CREATE MATERIALIZED VIEW events_consumer TO events
AS SELECT * FROM kafka_events;
MySQL 引擎
CREATE TABLE mysql_users
(
    id UInt32,
    name String,
    email String,
    created_at DateTime
)
ENGINE = MySQL('mysql-host:3306', 'database', 'users', 'user', 'password');

-- 查询MySQL数据
SELECT * FROM mysql_users WHERE id = 123;
S3 引擎
CREATE TABLE s3_logs
(
    timestamp DateTime,
    level String,
    message String
)
ENGINE = S3('https://bucket.s3.amazonaws.com/logs/*.gz', 'CSV', 'timestamp DateTime, level String, message String');

特殊系列

物化视图
-- 创建物化视图
CREATE MATERIALIZED VIEW daily_sales_mv
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, product_id)
AS SELECT
    order_date,
    product_id,
    sumState(quantity) as total_quantity,
    sumState(price * quantity) as total_amount
FROM orders
GROUP BY order_date, product_id;

-- 查询物化视图
SELECT
    order_date,
    product_id,
    sumMerge(total_quantity) as total_quantity,
    sumMerge(total_amount) as total_amount
FROM daily_sales_mv
GROUP BY order_date, product_id;
字典表
-- 创建字典
CREATE DICTIONARY user_dict
(
    user_id UInt64,
    user_name String,
    user_level UInt8,
    register_date Date
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' PASSWORD '' DB 'default' TABLE 'users'))
LIFETIME(MIN 300 MAX 600)
LAYOUT(HASHED());

-- 使用字典
SELECT
    event_time,
    user_id,
    dictGet('user_dict', 'user_name', user_id) as user_name,
    dictGet('user_dict', 'user_level', user_id) as user_level
FROM user_events
LIMIT 10;

查询语言

ClickHouse 使用类 SQL 查询语言,支持大多数标准 SQL 语法。

基本查询

-- 简单查询
SELECT * FROM orders WHERE order_date >= '2024-01-01' LIMIT 10;

-- 聚合查询
SELECT
    product_id,
    count() as order_count,
    sum(quantity) as total_quantity,
    sum(price * quantity) as total_amount,
    avg(price) as avg_price
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY product_id
ORDER BY total_amount DESC
LIMIT 100;

-- HAVING 子句
SELECT
    user_id,
    count() as order_count,
    sum(price * quantity) as total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY user_id
HAVING total_spent > 10000
ORDER BY total_spent DESC;

JOIN 操作

-- INNER JOIN
SELECT
    o.order_id,
    o.user_id,
    u.user_name,
    o.product_id,
    o.quantity,
    o.price
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2024-01-01';

-- LEFT JOIN
SELECT
    u.user_id,
    u.user_name,
    count(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
    AND o.order_date >= '2024-01-01'
GROUP BY u.user_id, u.user_name;

-- RIGHT JOIN
SELECT
    p.product_id,
    p.product_name,
    count(o.order_id) as order_count
FROM orders o
RIGHT JOIN products p ON o.product_id = p.product_id
    AND o.order_date >= '2024-01-01'
GROUP BY p.product_id, p.product_name;

窗口函数

-- 计算用户累计消费
SELECT
    user_id,
    order_date,
    order_id,
    amount,
    sum(amount) OVER (
        PARTITION BY user_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_amount
FROM orders
WHERE order_date >= '2024-01-01';

-- 计算排名
SELECT
    user_id,
    total_spent,
    rank() OVER (ORDER BY total_spent DESC) as rank_by_spent,
    dense_rank() OVER (ORDER BY total_spent DESC) as dense_rank_by_spent
FROM (
    SELECT
        user_id,
        sum(price * quantity) as total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY user_id
);

数组操作

-- 数组创建和操作
SELECT
    [1, 2, 3, 4, 5] as arr1,
    arrayJoin([1, 2, 3]) as value,
    arraySum([1, 2, 3, 4, 5]) as sum,
    arrayAvg([1, 2, 3, 4, 5]) as avg,
    arrayFilter(x -> x > 2, [1, 2, 3, 4, 5]) as filtered,
    arrayMap(x -> x * 2, [1, 2, 3, 4, 5]) as mapped,
    has([1, 2, 3, 4, 5], 3) as contains,
    indexOf([1, 2, 3, 4, 5], 3) as index;

-- 数组聚合
SELECT
    user_id,
    groupArray(event_type) as event_types,
    groupUniqArray(event_type) as unique_event_types,
    groupArray(order_date) as order_dates
FROM user_events
GROUP BY user_id;

条件表达式

-- CASE WHEN
SELECT
    order_id,
    quantity,
    CASE
        WHEN quantity < 10 THEN 'small'
        WHEN quantity < 100 THEN 'medium'
        ELSE 'large'
    END as order_size
FROM orders;

-- if 函数
SELECT
    order_id,
    if(quantity > 100, 'bulk', 'regular') as order_type
FROM orders;

-- multiIf 函数
SELECT
    order_id,
    multiIf(
        quantity > 1000, 'enterprise',
        quantity > 100, 'business',
        'personal'
    ) as customer_type
FROM orders;

时间函数

-- 时间转换和计算
SELECT
    event_time,
    toDate(event_time) as date,
    toYear(event_time) as year,
    toMonth(event_time) as month,
    toDayOfMonth(event_time) as day,
    toHour(event_time) as hour,
    toMinute(event_time) as minute,
    toStartOfDay(event_time) as day_start,
    toStartOfWeek(event_time) as week_start,
    toStartOfMonth(event_time) as month_start,
    toStartOfQuarter(event_time) as quarter_start,
    toStartOfYear(event_time) as year_start,
    dateDiff('day', toDateTime('2024-01-01'), event_time) as days_since,
    addDays(event_time, 7) as next_week,
    subtractDays(event_time, 7) as last_week
FROM user_events
LIMIT 10;

集群与复制

集群架构

ZooKeeper 集群

ClickHouse 集群

副本组 3 (Shard 3)

副本组 2 (Shard 2)

副本组 1 (Shard 1)

Node 1
Replica 1

Node 2
Replica 2

Node 3
Replica 1

Node 4
Replica 2

Node 5
Replica 1

Node 6
Replica 2

ZooKeeper 1

ZooKeeper 2

ZooKeeper 3

副本配置

<!-- config.xml -->
<clickhouse>
    <zookeeper>
        <node>
            <host>zk1</host>
            <port>2181</port>
        </node>
        <node>
            <host>zk2</host>
            <port>2181</port>
        </node>
        <node>
            <host>zk3</host>
            <port>2181</port>
        </node>
    </zookeeper>

    <remote_servers>
        <my_cluster>
            <shard>
                <replica>
                    <host>node1</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>node2</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>node3</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>node4</host>
                    <port>9000</port>
                </replica>
            </shard>
        </my_cluster>
    </remote_servers>
</clickhouse>

分布式表

-- 创建本地表
CREATE TABLE orders_local ON CLUSTER my_cluster
(
    order_id UInt64,
    user_id UInt64,
    order_date Date,
    product_id UInt64,
    quantity UInt32,
    price Decimal(18, 2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, order_id)
SETTINGS index_granularity = 8192;

-- 创建分布式表
CREATE TABLE orders_distributed ON CLUSTER my_cluster
(
    order_id UInt64,
    user_id UInt64,
    order_date Date,
    product_id UInt64,
    quantity UInt32,
    price Decimal(18, 2)
)
ENGINE = Distributed(my_cluster, default, orders_local, rand());

-- 查询分布式表
SELECT
    product_id,
    count() as order_count,
    sum(quantity) as total_quantity,
    sum(price * quantity) as total_amount
FROM orders_distributed
WHERE order_date >= '2024-01-01'
GROUP BY product_id
ORDER BY total_amount DESC
LIMIT 100;

分片策略

ClickHouse 支持多种分片策略:

-- 1. 随机分片
ENGINE = Distributed(cluster, database, table, rand());

-- 2. 哈希分片
ENGINE = Distributed(cluster, database, table, intHash32(user_id));

-- 3. 跳数一致性哈希分片
ENGINE = Distributed(cluster, database, table, jumpConsistentHash(user_id, 2));

-- 4. 自定义分片
ENGINE = Distributed(cluster, database, table, sharding_key);

数据复制流程

Disk Replica Node ZooKeeper Primary Node Client Disk Replica Node ZooKeeper Primary Node Client 写入数据 写入本地磁盘 注册日志条目 通知新数据 获取日志位置 拉取数据 返回数据块 写入本地磁盘 确认复制完成 复制完成确认 返回写入成功

性能优化

查询优化

1. 使用合适的索引
-- 好的索引设计
CREATE TABLE events
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type String,
    page_url String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)  -- 按月分区
ORDER BY (event_date, user_id, event_time)  -- 合理的排序键
PRIMARY KEY (event_date, user_id);  -- 主键

-- 利用索引的查询
SELECT * FROM events
WHERE event_date = '2024-01-15'
  AND user_id = 12345
  AND event_time BETWEEN '2024-01-15 10:00:00' AND '2024-01-15 11:00:00';
2. 避免全表扫描
-- 避免:全表扫描
SELECT * FROM events WHERE event_type = 'page_view';

-- 推荐:先过滤再聚合
SELECT
    event_type,
    count() as cnt
FROM events
WHERE event_date >= '2024-01-01'
GROUP BY event_type;
3. 使用物化视图
-- 预聚合物化视图
CREATE MATERIALIZED VIEW hourly_stats_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_hour)
ORDER BY (event_hour, event_type)
AS SELECT
    toStartOfHour(event_time) as event_hour,
    event_type,
    count() as event_count
FROM events
GROUP BY event_hour, event_type;

-- 查询物化视图
SELECT
    event_hour,
    event_type,
    sum(event_count) as total_count
FROM hourly_stats_mv
WHERE event_hour >= toDateTime('2024-01-01 00:00:00')
GROUP BY event_hour, event_type;
4. 使用 PREWHERE
-- PREWHERE 先过滤,再读取其他列
SELECT
    user_id,
    event_type,
    page_url
FROM events
PREWHERE event_date = '2024-01-15'
WHERE event_type = 'page_view';

存储优化

1. 选择合适的压缩算法
-- 高压缩率场景
CREATE TABLE compressed_data
(
    date Date,
    data String
)
ENGINE = MergeTree()
ORDER BY date
SETTINGS
    compression_codec = 'ZSTD(19)';  -- 高压缩率

-- 实时性要求高的场景
CREATE TABLE realtime_data
(
    timestamp DateTime,
    metric_name String,
    metric_value Float64
)
ENGINE = MergeTree()
ORDER BY (timestamp, metric_name)
SETTINGS
    compression_codec = 'LZ4';  -- 快速压缩
2. 使用 LowCardinality 类型
-- 优化低基数列
CREATE TABLE events_optimized
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),  -- 低基数优化
    country LowCardinality(String),     -- 低基数优化
    device_type LowCardinality(String)  -- 低基数优化
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_time);
3. 合理设置分区
-- 按月分区
PARTITION BY toYYYYMM(event_date)

-- 按天分区(数据量大时)
PARTITION BY toDate(event_date)

-- 按业务维度分区
PARTITION BY (toYYYYMM(event_date), event_type)

写入优化

1. 批量写入
-- 推荐:批量插入
INSERT INTO events VALUES
    ('2024-01-15 10:00:00', 'page_view', 12345, '/home'),
    ('2024-01-15 10:00:01', 'click', 12345, '/product/123'),
    ('2024-01-15 10:00:02', 'page_view', 67890, '/about');

-- 推荐:从文件批量导入
INSERT INTO events FORMAT CSVWithNames
FROM 'events.csv';
2. 使用 Buffer 表
-- 创建 Buffer 表
CREATE TABLE events_buffer
(
    event_time DateTime,
    event_type String,
    user_id UInt64,
    page_url String
)
ENGINE = Buffer(default, events, 16, 10, 100, 10000, 1000000, 10000000, 100000000);

-- 数据会自动刷新到目标表
3. 异步插入
-- 启用异步插入
SET async_insert = 1;
SET wait_for_async_insert = 0;

INSERT INTO events VALUES ('2024-01-15 10:00:00', 'page_view', 12345, '/home');

配置优化

<!-- config.xml -->
<clickhouse>
    <!-- 内存配置 -->
    <max_memory_usage>10000000000</max_memory_usage>
    <max_memory_usage_for_user>8000000000</max_memory_usage_for_user>
    
    <!-- 并发配置 -->
    <max_concurrent_queries>100</max_concurrent_queries>
    <max_concurrent_queries_for_user>50</max_concurrent_queries_for_user>
    
    <!-- 线程配置 -->
    <background_pool_size>16</background_pool_size>
    <background_schedule_pool_size>16</background_schedule_pool_size>
    
    <!-- 合并配置 -->
    <max_bytes_to_merge_at_max_space_in_pool>1048576000</max_bytes_to_merge_at_max_space_in_pool>
    <number_of_free_entries_in_pool_to_lower_max_size_of_merge>8</number_of_free_entries_in_pool_to_lower_max_size_of_merge>
    
    <!-- 缓存配置 -->
    <mark_cache_size>5368709120</mark_cache_size>
    <uncompressed_cache_size>8589934592</uncompressed_cache_size>
</clickhouse>

应用场景

1. 用户行为分析

-- 用户漏斗分析
WITH funnel_steps AS (
    SELECT
        user_id,
        groupArray(event_type) as events,
        groupArray(event_time) as times
    FROM user_events
    WHERE event_date >= '2024-01-01'
    GROUP BY user_id
    HAVING length(events) >= 4
)
SELECT
    countIf(has(events, 'view_home')) as step1_home,
    countIf(has(events, 'view_product')) as step2_product,
    countIf(has(events, 'add_to_cart')) as step3_cart,
    countIf(has(events, 'purchase')) as step4_purchase
FROM funnel_steps;

2. 实时监控告警

-- 异常检测
SELECT
    toStartOfMinute(event_time) as minute,
    count() as event_count,
    avg(event_count) OVER (
        ORDER BY minute
        ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
    ) as avg_count,
    event_count / avg(event_count) OVER (
        ORDER BY minute
        ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
    ) as ratio
FROM user_events
WHERE event_date >= today()
GROUP BY minute
HAVING ratio > 2.0
ORDER BY minute;

3. A/B 测试分析

-- A/B 测试效果对比
SELECT
    experiment_group,
    count(DISTINCT user_id) as user_count,
    countIf(event_type = 'purchase') as purchase_count,
    purchase_count / user_count as conversion_rate,
    avg(purchase_amount) as avg_purchase_amount
FROM (
    SELECT
        u.user_id,
        u.experiment_group,
        e.event_type,
        o.amount as purchase_amount
    FROM users u
    LEFT JOIN user_events e ON u.user_id = e.user_id
    LEFT JOIN orders o ON u.user_id = o.user_id
    WHERE u.experiment_date >= '2024-01-01'
)
GROUP BY experiment_group;

4. 留存分析

-- 用户留存分析
WITH user_cohorts AS (
    SELECT
        user_id,
        toDate(min(event_time)) as cohort_date
    FROM user_events
    WHERE event_date >= '2024-01-01'
    GROUP BY user_id
),
user_activities AS (
    SELECT
        c.user_id,
        c.cohort_date,
        toDate(e.event_time) as activity_date,
        dateDiff('day', c.cohort_date, toDate(e.event_time)) as day_number
    FROM user_cohorts c
    INNER JOIN user_events e ON c.user_id = e.user_id
)
SELECT
    cohort_date,
    count(DISTINCT user_id) as cohort_size,
    countIf(day_number = 0) as day0,
    countIf(day_number = 1) as day1,
    countIf(day_number = 7) as day7,
    countIf(day_number = 30) as day30,
    round(day1 / cohort_size * 100, 2) as day1_retention,
    round(day7 / cohort_size * 100, 2) as day7_retention,
    round(day30 / cohort_size * 100, 2) as day30_retention
FROM user_activities
GROUP BY cohort_date
ORDER BY cohort_date;

5. 时间序列分析

-- 时间序列预测(移动平均)
SELECT
    date,
    revenue,
    avg(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as ma7,
    avg(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as ma30
FROM (
    SELECT
        toDate(order_date) as date,
        sum(price * quantity) as revenue
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY date
)
ORDER BY date;

最佳实践

1. 表设计原则

主键选择
  • 选择高基数列:避免使用低基数列作为主键
  • 查询友好:主键应该包含常用查询条件
  • 数据分布均匀:避免数据倾斜
-- 好的设计
ORDER BY (event_date, user_id, event_time)

-- 不好的设计
ORDER BY (event_type)  -- 低基数
ORDER BY (user_id)     -- 没有包含时间维度
分区策略
  • 按时间分区:最常用的分区方式
  • 分区大小适中:每个分区 10-100GB
  • 避免过多分区:分区过多影响性能
-- 推荐:按月分区
PARTITION BY toYYYYMM(event_date)

-- 不推荐:按天分区(数据量不大时)
PARTITION BY toDate(event_date)

2. 查询编写规范

使用 LIMIT
-- 好的做法
SELECT * FROM large_table WHERE condition LIMIT 1000;

-- 避免:无限制查询
SELECT * FROM large_table WHERE condition;
避免 SELECT *
-- 好的做法
SELECT user_id, event_type, event_time FROM events;

-- 避免:查询所有列
SELECT * FROM events;
使用合适的聚合函数
-- 好的做法:使用 uniqExact 精确去重
SELECT uniqExact(user_id) FROM events;

-- 好的做法:使用 uniq 近似去重(大数据量)
SELECT uniq(user_id) FROM events;

3. 数据导入最佳实践

使用批量导入
# 使用 clickhouse-client 批量导入
clickhouse-client --query="INSERT INTO events FORMAT CSVWithNames" < events.csv

# 使用 HTTP 接口批量导入
cat events.csv | curl 'http://localhost:8123/?query=INSERT%20INTO%20events%20FORMAT%20CSVWithNames' --data-binary @-
使用 INSERT SELECT
-- 从其他表导入
INSERT INTO events_archive
SELECT * FROM events
WHERE event_date < '2024-01-01';

-- 从外部表导入
INSERT INTO events
SELECT * FROM s3('s3://bucket/events/*.csv', 'CSV', 'event_time DateTime, event_type String, user_id UInt64');

4. 监控与维护

系统表监控
-- 查询当前执行的查询
SELECT * FROM system.processes WHERE query NOT LIKE '%system%';

-- 查看表大小
SELECT
    database,
    table,
    formatReadableSize(sum(bytes)) as size,
    sum(rows) as rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY size DESC;

-- 查看分区信息
SELECT
    partition,
    sum(rows) as rows,
    formatReadableSize(sum(bytes_on_disk)) as size
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition
ORDER BY partition DESC;
定期维护
-- 手动触发合并
OPTIMIZE TABLE events PARTITION '202401' FINAL;

-- 删除旧分区
ALTER TABLE events DROP PARTITION '202301';

-- 修改表设置
ALTER TABLE events MODIFY SETTING max_bytes_to_merge_at_once = 1073741824;

5. 安全配置

<!-- users.xml -->
<clickhouse>
    <users>
        <!-- 只读用户 -->
        <readonly>
            <password>readonly_password</password>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
            <databases>
                <database_name>analytics</database_name>
            </databases>
        </readonly>

        <!-- 管理员用户 -->
        <admin>
            <password_sha256_hex>...</password_sha256_hex>
            <access_management>1</access_management>
            <networks>
                <ip>10.0.0.0/8</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </admin>
    </users>

    <profiles>
        <readonly>
            <readonly>1</readonly>
            <allow_ddl>0</allow_ddl>
        </readonly>
    </profiles>
</clickhouse>

总结

ClickHouse 是一个高性能的列式 OLAP 数据库,具有以下核心优势:

  1. 极致性能:列式存储、向量化执行、高效压缩
  2. 简单易用:类 SQL 查询语言、丰富的表引擎
  3. 高可用性:支持副本、分片、分布式查询
  4. 灵活扩展:支持水平扩展、多种存储后端
  5. 丰富生态:支持多种数据源集成

ClickHouse 适用于以下场景:

  • 用户行为分析
  • 实时监控告警
  • A/B 测试分析
  • 留存分析
  • 时间序列分析
  • 日志分析
  • BI 报表

通过合理的设计和优化,ClickHouse 可以在处理海量数据时提供亚秒级的查询响应,是现代数据分析平台的重要组件。

ClickHouse学习笔记(一):ClickHouse架构概述(为什么ClickHouse这么快呢?) ClickHouse是一款MPP架构的数据库,它没有采用Hadoop生态中的主从架构,而是使用了多主对等网络结果,同时它也是基于关系模型的ROLAP方案。 阅读详情

相关推荐

【用户画像】ClickHouse简介、特点、安装和部署

之前数仓和画像的处理都是批处理,一般在夜间进行,花费时间很长,按照脚本和调度去完成,后来的处理称为即时处理,即时处理要求计算的时间非常之短,存放在hive中,肯定是不行的,这种需求既要求从一定的数据量中提取(如果是小数量可以从MySQL提取),同时需要速度快。这种数据库就需要在hive中和OLAP中同时存储一份,OLAP总体的设计目标就是即席查询,但是不同的数据库之间又有细微的差别,OLAP数据库有kylinprestodruidclickhouse:多用于用户画像和实时计算,速度快。

weixin_43923463的博客 3366

ClickHouse的介绍(基本sql操作,以及数据库引擎、表引擎、分片、副本、explain、优化、物化视图等)

ClickHouse是俄罗斯的Yandex于2016年开源的列式存储数据库(DBMS),使用C++语言编写,主要用于在线分析处理查询(OLAP),能够使用SQL查询实时生成分析数据报告。列式存储以下面的表为例:1)采用行式存储时,数据在磁盘上的组织结构为:好处是想查某个人所有的属性时,可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时,需要不停的查找,或者全表扫描才行,遍历的很多数据都是不需要的。2)采用列式存储时,数据在磁盘上的组织结构为:这时想查所有人的年龄只需把年龄那一列拿出来就可以了。3

迷雾总会解 4万+

ClickHouse使用介绍和部署指南

ClickHouse 是一种用于在线分析处理 (OLAP) 的高性能、面向列的 SQL 数据库管理系统 (DBMS)。它以其快速的查询性能和高效的数据压缩而闻名,并且能够处理 PB 级别的数据。1.

m0_58000616的博客 4847

ClickHouse 快速入门

文章目录一、ClickHouse入门1. ClickHouse的特点1.1 列式存储1.2 DBMS的功能1.3 多样化引擎1.4 高吞吐写入能力1.5 数据分区与线程级并行二、ClickHouse的安装1. 准备工作1. 1 确定防火墙处于关闭状态1.2 CentOS取消打开文件数限制1.3 安装依赖1.4 取消SELINUX2. 单机安装(在线)三、数据类型1. 整型2. 浮点型3. 布尔型4. Decimal 型5. 字符串6. 枚举类型7. 时间类型8. 数组四、表引擎1. 表引擎的使用2. Tin

mengxianglong123的博客 1万+

MOSFET导通电阻Rdson全解析:物理组成、公式推导与工程选型要点

在功率电子设计中,MOSFET作为核心开关器件,其导通电阻Rdson直接决定导通损耗与系统效率。从半导体物理角度看,Rdson并非单一固定值,而是由源极电阻、沟道电阻、累积层电阻、JFET区电阻、漂移区电阻及衬底电阻等多个分量串联而成,并随栅极驱动电压、结温以及击穿电压等级发生显著变化。理解Rdson的物理来源与数学表达,有助于工程师在低压大电流与高压小电流等不同场景中合理选型、优化驱动电压并准确估算热损耗。文章从沟道电阻公式出发,深入剖析耐压与导通电阻的物理矛盾、温度系数的影响规律,并给出数据手册参数的正

weixin_29163857的博客 199

别再踩坑了!Win10下用阿狸狗V3.2.6安装Cadence SPB 17.4的保姆级避坑指南

本文提供了Win10系统下使用阿狸狗V3.2.6安装Cadence SPB 17.4的详细避坑指南,涵盖环境准备、安装失败清理、分步安装与问题诊断、授权配置及典型故障排除等内容,帮助用户顺利完成安装并避免常见错误。

weixin_32576389的博客 361

ClickHouse介绍

ClickHouseClickHouse是一个用于联机分析处理(OLAP)的高性能、面向列的 SQL 数据库管理系统(DBMS)。它可以作为开源软件或云服务提供。绝大多数是读请求数据以相当大的批次(> 1000行)更新,而不是单行更新;或者根本没有更新。已添加到数据库的数据不能修改。对于读取,从数据库中提取相当多的行,但只提取列的一小部分。宽表,即每个表包含着大量的列查询相对较少(通常每台服务器每秒查询数百次或更少)对于简单查询,允许延迟大约50毫秒。

终将成为大牛 306

手把手教你用Cadence Virtuoso完成LNA全套仿真:基于SpectreRF手册的实战补充

本文详细指导如何使用Cadence Virtuoso和SpectreRF完成LNA的全套仿真,涵盖噪声仿真、增益压缩、THD分析及双音IP3测量等关键步骤。针对官方手册中的常见问题,提供了12个实战解决方案,包括hbnoise函数选择、双音信号源配置等,帮助工程师高效完成射频IC设计。

weixin_29102619的博客 528

ClickHouse引擎介绍

ClickHouse引擎介绍 1. 数据库引擎 1.1 Mysql MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中,并允许您对表进行INSERT和SELECT查询,以方便您在ClickHouse与MySQL之间进行数据交换。 MySQL数据库引擎会将其查询语句转换为MySQL语法并发送到MySQL服务器中,因此可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。 但无法对其执行以下操作: RENAME CREATE TABLE ALTER 用法

努力努力再努力!的博客 4310

Calibre提参实战:如何用hcell和xcell文件精准屏蔽模块(附避坑指南)

本文详细解析了Calibre提参过程中hcell和xcell文件的应用技巧,帮助工程师精准屏蔽模块以优化寄生参数提取。通过对比hcell与xcell的核心差异、图形界面与命令行操作指南,以及常见问题排查方法,提升芯片后端设计效率。附避坑指南,解决EETOP论坛热议的模块丢失问题。

weixin_26795617的博客 441

告别臃肿!保姆级教程:用NTLite深度精简Win10,打造你的专属纯净系统(附完整组件移除清单)

本文提供了一份详细的NTLite教程,帮助用户深度精简Windows10系统,移除冗余组件并优化性能。从准备工作到组件移除清单,再到系统配置优化和无人值守安装,逐步指导用户打造专属纯净系统。附完整组件移除清单和实战经验,确保系统稳定高效运行。

weixin_30945039的博客 869

clickhouse system表介绍

aggregate_function_combinators build_options 存储了clickhouse的编译信息,包括 clickhouse 版本号,编译用的cxx版本等 clusters 本机地址 localhost 是一个域名,在过去它指向 127.0.0.1 这个IP地址。在操作系统支持 ipv6 后,它同时还指向ipv6 的地址 ::1 127.0.0.1是代表自己机器的回路地址,无论是否联网,自己机器都是127.0.0.1 127网段是保留区段,任何ping向保留区段127的都会

司马懿的西山居 3957

技术解析:ChangeFormer——基于Transformer的孪生网络在遥感变化检测中的创新应用

本文深入解析了ChangeFormer模型在遥感变化检测中的创新应用,该模型基于Transformer架构和孪生网络(Siamese Network),显著提升了变化检测的精度和效率。通过详细的技术实现和实战经验分享,展示了其在建筑物变化、森林监测等场景中的卓越表现,为相关领域的研究和应用提供了有力工具。

weixin_29102619的博客 498

clickHouse基本介绍

ClickHouse是一个面向联机分析处理(OLAP)的开源的面向列式存储的DBMS,简称CK, 与Hadoop, Spark相比,ClickHouse很轻量级,由俄罗斯第一大搜索引擎Yandex于2016年6月发布, 开发语言为C++ ClickHouse的特点: 开源的列存储数据库管理系统,支持线性扩展,简单方便,高可靠性, 容错跑分快:比Vertica快5倍,比Hive快279倍,比...

黑马程序员官方博客 3万+

rk3576之imx415 4k@60fps

1;0;1;0;1;0;1;0;1;0;1;0;1;0;

darennet的专栏 705

第4章 部署负载均衡 Keepalived + HAProxy

本章在所有 Master 节点部署 Keepalived + HAProxy,为 kube-apiserver 提供高可用入口。Keepalived 实现 VIP(192.168.3.59)漂移,HAProxy 轮询转发请求至三个 Master 的 6443 端口(监听 6444 避免冲突)。健康检查脚本连续 5 次失败则停止 Keepalived,触发 VIP 自动切换。本章还包含配置详解、日常运维和问题排查。

幸福街--linux技术分享 123

ClickHouse介绍与应用

ClickHouse由俄罗斯最大的搜索引擎Yandex于2016年6月发布,开发语言为c。ClickHouse是面向在线分析处理(OLAP )的开源列存储的DBMS。与简称、Hadoop、Spark等巨无霸的组件相比,ClickHouse更轻什么是列式存储IdNameAge1张三182李四223王五34采用行式存储时,数据在磁盘上的组织结构为:好处是想查某个人所有的属性时,可以通过一次磁盘查找加顺序读取就可以。

super_mochi1 1158

C# WinForm 基于DirectShow.dll编写的程序,可边预览边保存视频,音频和图片

基于微软的DirectShow.dll的二次开发1.自动识别连接的摄像头跟麦克风且可以对其进行选择(默认选中第一个)2.可录制wmv和avi两种格式的视频并且都带有音频,avi可以录制的分辨率和帧数进行选择,wmv默认进行了淸晰度优化如不喜欢可去除代码中有相关提示3.可在预览点开之后进行拍照,也可边录制视频边拍照4.添加了视频和音频的压缩功能(酌情使用)这次二次开发发现C#对DirectShow的相关信息太少,这次做完也不敢藏私共享出来,还望大家指正

ClickHouse介绍和使用

文章目录概述什么是clickhouse安装前的准备Centos取消打开文件数限制取消SELINUX取消防火墙安装依赖安装 概述 什么是clickhouse ClickHouse是开元的列式存储数据库(DBMS),主要用于在线处理查询(OLAP),能够使用SQL查询实时生成数据分析报告。 安装前的准备 Centos取消打开文件数限制 在/etc/security/limits.conf、/etc/security/limits.d/20-nproc.conf这两个文件的末尾加入一下内容: * soft nof

weixin_43790613的博客 7836

ssm框架搭建

这个是一个在idea上成功跑起来的ssm框架项目,保存在这里方便以后使用直接下载

老版QQ原生表情gif动态

这是老版的QQ表情,不过全是动态的的,Windows照片查看器是不会动的,可以右击用浏览器打开查看,再说一遍是老版的,只是分享给宁愿用老版也要动态的人。

Clickhouse的基本介绍

Clickhouse的基本介绍

Xlucas的博客 1万+

pscad IEEE标准系统搭建说明

pscad IEEE标准系统搭建说明,包含典型的IEEE 9节点、14节点、30节点、39节点和118节点系统。内含标准测试系统的原始数据和潮流结果。

ClickHouse基础介绍

clickhouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS),由俄罗斯最大的搜索公司Yandex开发,于2016年开源,采用c++开发

小徐的博客 1587
上一篇: HBase之LSM树(Log-Structured Merge-Tree)存储引擎
下一篇: ClickHouse之MergeTree存储引擎
d3y1
博客等级 码龄12年 1438粉丝 2807原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值