← 返回首页 ❓ 面试题库
📑 本篇章节导航 (共11章,点击直达)
一、ClickHouse基础与架构二、分布式与高可用三、Materialized View与数据管道四、性能优化五、Java集成六、高级特性七、与竞品对比八、实战场景题九、SQL实战十、面试高频综合题面试速记口诀

Q95 ClickHouse与OLAP分析引擎实战

20K Java+AI 岗位高频考点 — ClickHouse是实时OLAP分析的事实标准,大厂数据团队必问。覆盖列存储原理、MergeTree引擎家族、分布式集群、Materialized View、Java集成、性能优化、与StarRocks/Doris对比等。

一、ClickHouse基础与架构

1. ClickHouse为什么这么快?

列式存储:只读取查询涉及的列,减少IO。

向量化执行:利用SIMD指令,一次处理一批数据(Column-oriented batch processing)。

并行处理:多核并行扫描、分布式查询。

主键索引(稀疏索引):Skip Index加速过滤。

数据压缩:列式存储压缩比高(LZ4/ZSTD),减少磁盘IO。

C++实现:零拷贝、内存映射、精细的内存管理。

2. ClickHouse的表引擎家族有哪些?

MergeTree家族(最核心):

其他引擎:

3. MergeTree引擎的核心原理?


CREATE TABLE orders (
    order_id UInt64,
    user_id UInt64,
    amount Decimal(12,2),
    order_time DateTime,
    status String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_time)
ORDER BY (user_id, order_time)
TTL order_time + INTERVAL 6 MONTH
SETTINGS index_granularity = 8192;

核心概念:

4. 分区(Partition)和排序键(Order By)的区别?

维度PartitionOrder By
作用数据物理隔离分区内数据排序
查询优化分区裁剪稀疏索引+范围扫描
操作支持DROP/ATTACH/DETACH分区不支持直接操作
设计原则按时间/业务维度按查询的高频过滤条件

最佳实践:Partition按月(toYYYYMM),Order By按查询维度(如user_id, event_time)。

5. ClickHouse的索引机制?

主键索引(Primary Key / Sparse Index):

跳数索引(Skip Index):


CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    data String,
    INDEX idx_type event_type TYPE set(100) GRANULARITY 4,
    INDEX idx_bloom user_id TYPE bloom_filter(0.01) GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY event_time;

跳数索引类型:


二、分布式与高可用

6. ClickHouse的分布式架构是怎样的?


Client → Distributed表 → 本地分片(Shard 1)
                       → 本地分片(Shard 2)
                       → 远程分片(Shard 3)
每个Shard可以有多个副本(Replica)

配置文件:config.xml 中的 <remote_servers> 配置分片和副本。

查询流程:

1. 接收查询的节点成为Coordinator

2. 解析SQL,确定涉及的分片

3. 将查询分发到各分片

4. 各分片并行执行本地查询

5. Coordinator汇总结果返回

7. ClickHouse的副本机制?

基于ReplicatedMergeTree引擎 + ZooKeeper:


CREATE TABLE orders_local ON CLUSTER '{cluster}' (
    -- 列定义
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/orders', '{replica}')
PARTITION BY toYYYYMM(order_time)
ORDER BY (user_id, order_time);

ZooKeeper存储:

注意:ZooKeeper是性能瓶颈,大规模集群考虑用ClickHouse Keeper(内置替代方案)。

8. ClickHouse Keeper vs ZooKeeper?

维度ZooKeeperClickHouse Keeper
语言JavaC++
性能中高
运维独立部署可内嵌
内存需要JVM调优原生内存管理
一致性协议ZABRaft

建议:新集群直接用ClickHouse Keeper。

9. Distributed表的写入策略?

方式一:写Distributed表(简单但有风险)

方式二:写本地表(推荐)

方式三:通过负载均衡器

10. ClickHouse的集群怎么扩容?

水平扩容(加Shard):

1. 修改集群配置,新增Shard

2. 新数据写入新Shard

3. 历史数据需要重新分布(ALTER TABLE ... RESHARD)

垂直扩容(加副本):

1. 新增Replica节点

2. 自动从其他副本同步数据

3. 不影响在线服务

注意:ClickHouse的Reshard目前还不太成熟,建议提前规划好分片数量。


三、Materialized View与数据管道

11. Materialized View(物化视图)的原理?

ClickHouse的MV本质是一个INSERT Trigger:

1. 当源表有新数据INSERT时

2. 触发MV定义的SELECT查询

3. 将查询结果写入MV的目标表


-- 目标表
CREATE TABLE order_stats_daily (
    stat_date Date,
    total_amount AggregateFunction(sum, Decimal(12,2)),
    order_count AggregateFunction(count)
) ENGINE = AggregatingMergeTree()
ORDER BY stat_date;

-- 物化视图
CREATE MATERIALIZED VIEW mv_order_stats TO order_stats_daily AS
SELECT
    toDate(order_time) AS stat_date,
    sumState(amount) AS total_amount,
    countState() AS order_count
FROM orders
GROUP BY stat_date;

12. AggregatingMergeTree的聚合函数后缀?

完整用法:


-- 写入(通过MV自动触发)
INSERT INTO orders VALUES (1, 100.00, '2024-01-01 10:00:00');

-- 查询(手动Merge)
SELECT
    stat_date,
    sumMerge(total_amount) AS total_amount,
    countMerge(order_count) AS order_count
FROM order_stats_daily
GROUP BY stat_date;

13. 如何用ClickHouse实现实时数仓?


Kafka → ClickHouse Kafka Engine表 → Materialized View → 本地表
                                                     ↓
                                              聚合MV → 预聚合表

-- Kafka引擎表(消费Kafka数据)
CREATE TABLE kafka_orders (
    order_id UInt64,
    user_id UInt64,
    amount Decimal(12,2),
    order_time DateTime
) ENGINE = Kafka()
SETTINGS kafka_broker_list = 'broker:9092',
         kafka_topic_list = 'orders',
         kafka_group_name = 'ch_consumer',
         kafka_format = 'JSONEachRow';

-- MV:Kafka表 → 本地表
CREATE MATERIALIZED VIEW mv_kafka_to_local TO orders AS
SELECT * FROM kafka_orders;

14. ClickHouse如何处理迟到数据?

挑战:MV只处理新INSERT的数据,历史数据更新时MV不会重新计算。

解决方案:

1. Window视图(实验性):支持延迟触发

2. 定期重算:定时清空聚合表,从源表全量重算

3. VersionedCollapsingMergeTree:通过版本号处理修正


四、性能优化

15. ClickHouse查询优化技巧?

1. 分区裁剪:查询条件包含分区键

2. 主键索引:ORDER BY的前缀列用于过滤

3. 避免SELECT *:只查需要的列

4. PREWHERE:先过滤再读列数据

5. 避免JOIN:大表JOIN性能差,用字典或宽表替代

6. IN查询优化:用IN (subquery)代替多表JOIN


-- PREWHERE优化
SELECT user_id, count()
FROM events
PREWHERE event_type = 'click'  -- 先用稀疏索引过滤
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY user_id;

-- 替代JOIN:用字典
CREATE DICTIONARY user_dict (
    user_id UInt64,
    user_name String,
    level UInt32
) SOURCE(HTTP(url 'http://api/users' format 'JSONEachRow'))
LIFETIME(MIN 300 MAX 3600);

SELECT dictGet('user_dict', 'user_name', user_id) AS name
FROM events;

16. ClickHouse的写入优化?

1. 批量写入:每次INSERT至少1000行,最好10000-100000行

2. 控制INSERT频率:每秒不超过1-2次INSERT(每次INSERT生成一个Part)

3. 分区合理:不要过度分区(每月/每天一个分区即可)

4. 异步INSERT:async_insert=1 + wait_for_async_insert=0

5. 避免小批量:小批量导致大量小Part,触发频繁Merge


-- 异步INSERT配置
SET async_insert = 1;
SET wait_for_async_insert = 0;
SET async_insert_max_data_size = '10M';
SET async_insert_busy_timeout_ms = 10000;

17. ClickHouse的内存管理?

查询内存:

服务器内存:

OOM排查:查询system.query_log,找出内存消耗最大的查询。

18. ClickHouse与大表JOIN的优化方案?

方案一:字典替代JOIN


-- 维表小(<千万行)→ 用字典
SELECT dictGet('product_dict', 'name', product_id)
FROM orders;

方案二:宽表冗余

方案三:Colocate JOIN

方案四:Subquery代替JOIN


-- 用 IN 代替 JOIN
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE level > 5);

五、Java集成

19. ClickHouse的Java客户端有哪些?

客户端特点推荐
clickhouse-jdbc官方JDBC驱动✅ 通用
clickhouse-client官方新版客户端✅ 高性能
MyBatis + JDBCORM集成✅ 常用

// JDBC连接
String url = "jdbc:clickhouse://localhost:8123/default";
try (Connection conn = DriverManager.getConnection(url, "default", "");
     Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery("SELECT count() FROM events");
    if (rs.next()) {
        System.out.println("Total: " + rs.getLong(1));
    }
}

// 批量写入
try (PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO events (event_time, user_id, event_type) VALUES (?, ?, ?)")) {
    for (Event e : events) {
        ps.setTimestamp(1, Timestamp.valueOf(e.getTime()));
        ps.setLong(2, e.getUserId());
        ps.setString(3, e.getType());
        ps.addBatch();
    }
    ps.executeBatch();
}

20. MyBatis集成ClickHouse?


<!-- pom.xml -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.6.x</version>
</dependency>

<!-- application.yml -->
spring:
  datasource:
    url: jdbc:clickhouse://localhost:8123/default
    driver-class-name: com.clickhouse.jdbc.ClickHouseDriver

@Mapper
public interface EventMapper {
    @Select("SELECT * FROM events WHERE user_id = #{userId} LIMIT 100")
    List<Event> getByUserId(@Param("userId") Long userId);

    @Insert("INSERT INTO events (event_time, user_id, event_type, data) " +
            "VALUES (#{eventTime}, #{userId}, #{eventType}, #{data})")
    void insert(Event event);
}

21. ClickHouse与Spring Boot的实时报表方案?


@Service
public class ReportService {
    @Autowired JdbcTemplate clickhouseJdbc;

    // 实时PV/UV统计
    public DailyStats getDailyStats(LocalDate date) {
        return clickhouseJdbc.queryForObject(
            "SELECT " +
            "  count() AS pv, " +
            "  uniq(user_id) AS uv, " +
            "  sum(amount) AS total_amount " +
            "FROM orders " +
            "WHERE toDate(order_time) = ?",
            (rs, rowNum) -> new DailyStats(
                rs.getLong("pv"),
                rs.getLong("uv"),
                rs.getBigDecimal("total_amount")),
            date);
    }

    // 漏斗分析
    public List<FunnelStep> getFunnel(LocalDate date) {
        return clickhouseJdbc.query(
            "SELECT " +
            "  windowFunnel(86400)( " +
            "    event_time, " +
            "    event_type = 'view', " +
            "    event_type = 'cart', " +
            "    event_type = 'pay' " +
            "  ) AS step " +
            "FROM events " +
            "WHERE toDate(event_time) = ? " +
            "GROUP BY user_id",
            // ...
        );
    }
}

六、高级特性

22. ClickHouse的字典(Dictionary)怎么用?


-- HTTP字典(实时拉取)
CREATE DICTIONARY product_dict (
    id UInt64,
    name String,
    category String,
    price Decimal(10,2)
) SOURCE(HTTP(
    url 'http://api-service:8080/api/products'
    format 'JSONEachRow'
))
LIFETIME(MIN 300 MAX 3600)
LAYOUT(COMPLEX_KEY_HASHED());

-- 使用
SELECT
    dictGet('product_dict', 'name', product_id) AS product_name,
    dictGet('product_dict', 'category', product_id) AS category
FROM orders;

字典类型:

23. ClickHouse的窗口函数支持?

ClickHouse支持标准窗口函数:


-- 用户留存分析
SELECT
    first_day,
    count(DISTINCT user_id) AS day0,
    count(DISTINCT IF(datediff = 1, user_id, NULL)) AS day1,
    count(DISTINCT IF(datediff = 7, user_id, NULL)) AS day7
FROM (
    SELECT
        user_id,
        min(toDate(event_time)) AS first_day,
        dateDiff('day', first_day, toDate(event_time)) AS datediff
    FROM events
    GROUP BY user_id
)
GROUP BY first_day
ORDER BY first_day;

24. ClickHouse的数组和高阶函数?


-- 数组操作
SELECT
    arrayJoin([1, 2, 3]) AS x;  -- 展开数组为多行

-- JSON提取
SELECT
    JSONExtractString(data, 'name') AS name,
    JSONExtractFloat(data, 'amount') AS amount
FROM events;

-- 正则提取
SELECT extractAll(text, '\\d+') AS numbers FROM logs;

-- 条件聚合
SELECT
    countIf(status = 'success') AS success_count,
    countIf(status = 'fail') AS fail_count,
    ratioToReport(amount) OVER () AS amount_ratio
FROM orders;

25. ClickHouse的数据类型最佳实践?

类型使用建议
UInt8/16/32/64无符号整数,比有符号快
String文本,UTF-8
FixedString(N)定长字符串(如UUID)
DateTime秒精度时间戳
DateTime64(3)毫秒精度
Decimal(P,S)精确小数(金额)
LowCardinality(String)低基数字符串(枚举类)
Nullable(T)允许NULL(性能差,尽量避免)
Array(T)数组
Map(K,V)映射

最佳实践:


七、与竞品对比

26. ClickHouse vs StarRocks vs Apache Doris?

维度ClickHouseStarRocksDoris
核心优势单表查询极快多表JOIN强均衡
JOIN能力弱强(Colocate/Cost-based)强
实时更新支持但弱强(MVCC)强
运维复杂度高(ZK依赖)中中
生态丰富快速追赶成熟
典型用户字节/Yandex美团/京东百度/京东
语言C++C++C++/Java

选型建议:

27. ClickHouse vs Elasticsearch?

维度ClickHouseElasticsearch
核心用途OLAP分析全文搜索 + 分析
写入性能极高高
查询类型SQL聚合分析全文检索 + 聚合
实时性秒级近实时(1秒)
分词不支持强(IK等分词器)
典型场景报表/BI/数据仓库搜索/日志/指标

结论:分析报表用ClickHouse,全文搜索用ES,两者可以配合使用。

28. ClickHouse vs Apache Druid?

维度ClickHouseDruid
部署复杂度中高(多个组件)
实时摄入Kafka Engine原生支持
查询延迟低极低
SQL支持完善一般
社区活跃一般

结论:大多数场景ClickHouse更简单高效。


八、实战场景题

29. 设计一个实时大屏系统?


MySQL(Binlog) → Flink CDC → Kafka → ClickHouse(Kafka Engine + MV)
                                                ↓
                                         API服务查询
                                                ↓
                                          WebSocket推送大屏

ClickHouse表设计:


-- 实时订单统计
CREATE TABLE order_realtime (
    event_time DateTime,
    user_id UInt64,
    amount Decimal(12,2),
    product_id UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(event_time)
ORDER BY event_time;

-- MV: 每分钟聚合
CREATE MATERIALIZED VIEW mv_minute_stats TO minute_stats AS
SELECT
    toStartOfMinute(event_time) AS minute,
    count() AS order_count,
    sum(amount) AS total_amount,
    uniq(user_id) AS uv
FROM order_realtime
GROUP BY minute;

30. 如何用ClickHouse实现用户行为分析?


-- 用户路径分析(使用sequenceMatch)
SELECT
    count() AS total,
    sequenceMatch('(?1)(?2)')(event_time, event_type = 'view', event_type = 'pay') AS view_to_pay,
    sequenceMatch('(?1)(?2)')(event_time, event_type = 'view', event_type = 'cart') AS view_to_cart
FROM events
WHERE event_time > now() - INTERVAL 7 DAY
GROUP BY user_id;

-- 漏斗分析(使用windowFunnel)
SELECT
    level,
    count() AS count
FROM (
    SELECT
        user_id,
        windowFunnel(86400)(
            event_time,
            event_type = 'view',
            event_type = 'click',
            event_type = 'cart',
            event_type = 'pay'
        ) AS level
    FROM events
    WHERE event_time > today() - 7
    GROUP BY user_id
)
GROUP BY level
ORDER BY level;

31. 如何实现ClickHouse的数据同步?

MySQL → ClickHouse:

ClickHouse → ClickHouse:

ClickHouse → 外部:

32. ClickHouse的监控和运维?

关键指标:


-- 慢查询
SELECT query_duration_ms, query
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Part数量监控
SELECT table, count() AS part_count
FROM system.parts
WHERE active
GROUP BY table
HAVING part_count > 100;  -- Part过多影响性能

告警项:

33. ClickHouse的备份与恢复?

方案一:FREEZE PARTITION


ALTER TABLE orders FREEZE PARTITION '202401';
-- 生成硬链接到 /var/lib/clickhouse/shadow/

方案二:clickhouse-backup工具


clickhouse-backup create backup_20240101
clickhouse-backup upload backup_20240101  # 上传到S3
clickhouse-backup restore backup_20240101

方案三:远程副本恢复

34. ClickHouse在实时数仓中的角色?


ODS → Kafka → ClickHouse(本地表)           — 明细数据
              ↓ (MV聚合)
DWS → ClickHouse(聚合表)                   — 聚合数据
              ↓ (API)
ADS → Spring Boot API → 前端大屏/报表       — 应用数据

ClickHouse优势:

35. ClickHouse的常见踩坑?

1. 频繁小INSERT:导致Part爆炸 → 批量写入

2. ORDER BY设计不当:查询慢 → 按查询条件设计

3. JOIN性能差:避免大表JOIN → 用字典/宽表

4. Nullable滥用:性能差 → 用默认值替代

5. 分区过多:Part数量爆炸 → 按月分区

6. ZooKeeper压力:大规模集群瓶颈 → 用ClickHouse Keeper

7. 内存不足:大查询OOM → 设置溢出磁盘


九、SQL实战

36. 常用分析函数?


-- 同比环比
SELECT
    month,
    amount,
    amount / lagInFrame(amount) OVER (ORDER BY month) - 1 AS month_over_month,
    amount / lagInFrame(amount, 12) OVER (ORDER BY month) - 1 AS year_over_year
FROM monthly_stats;

-- Top N
SELECT user_id, sum(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 100;

-- 分位数
SELECT
    quantile(0.5)(response_time) AS p50,
    quantile(0.95)(response_time) AS p95,
    quantile(0.99)(response_time) AS p99
FROM api_logs;

37. 如何实现留存分析?


SELECT
    first_day,
    count(DISTINCT user_id) AS cohort_size,
    count(DISTINCT IF(day_diff = 1, user_id, NULL)) AS d1,
    count(DISTINCT IF(day_diff = 7, user_id, NULL)) AS d7,
    count(DISTINCT IF(day_diff = 30, user_id, NULL)) AS d30
FROM (
    SELECT
        user_id,
        min(toDate(event_time)) AS first_day,
        dateDiff('day', first_day, toDate(event_time)) AS day_diff
    FROM events
    WHERE event_time >= today() - 60
    GROUP BY user_id
)
GROUP BY first_day
ORDER BY first_day;

38. 如何实现实时TopN排行?


-- 使用AggregatingMergeTree
CREATE TABLE user_rank (
    rank_date Date,
    user_id UInt64,
    total_amount AggregateFunction(sum, Decimal(12,2))
) ENGINE = AggregatingMergeTree()
ORDER BY (rank_date, user_id);

-- MV自动聚合
CREATE MATERIALIZED VIEW mv_user_rank TO user_rank AS
SELECT
    toDate(order_time) AS rank_date,
    user_id,
    sumState(amount) AS total_amount
FROM orders
GROUP BY rank_date, user_id;

-- 查询Top 100
SELECT
    user_id,
    sumMerge(total_amount) AS amount
FROM user_rank
WHERE rank_date = today()
GROUP BY user_id
ORDER BY amount DESC
LIMIT 100;

39. 如何处理ClickHouse中的数据更新?

ClickHouse不擅长数据更新(Mutation代价大):


-- ALTER UPDATE(重量级操作,异步执行)
ALTER TABLE orders UPDATE status = 'cancelled' WHERE order_id = 123;

-- 查看Mutation进度
SELECT * FROM system.mutations WHERE is_done = 0;

更好的方案:

1. ReplacingMergeTree:后台合并时自动去重

2. CollapsingMergeTree:通过sign字段标记删除

3. VersionedCollapsingMergeTree:带版本号

4. 设计为Append-Only,不更新

40. ClickHouse的TTL(数据生命周期)?


-- 表级TTL
CREATE TABLE logs (
    event_time DateTime,
    data String
) ENGINE = MergeTree()
ORDER BY event_time
TTL event_time + INTERVAL 30 DAY;  -- 30天后自动删除

-- 列级TTL
ALTER TABLE logs MODIFY COLUMN data String TTL event_time + INTERVAL 7 DAY;

-- 移动到冷存储
ALTER TABLE logs MODIFY TTL event_time + INTERVAL 7 DAY TO DISK 'cold',
                        event_time + INTERVAL 30 DAY TO DELETE;

十、面试高频综合题

41. ClickHouse适合什么样的业务场景?

42. ClickHouse的写入瓶颈在哪里?

1. Part数量:每秒INSERT次数过多导致Part爆炸

2. ZooKeeper:副本同步时ZK成为瓶颈

3. 磁盘IO:Merge和INSERT争抢IO

4. 网络:分布式写入时的网络带宽

优化:批量写入 + 异步INSERT + 控制INSERT频率 + SSD。

43. 如何评估ClickHouse的存储容量?


-- 当前存储
SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS rows,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table;

-- 压缩比
SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    sum(data_uncompressed_bytes) / sum(data_compressed_bytes) AS ratio
FROM system.columns
GROUP BY table;

44. ClickHouse的查询并发能力如何?


-- 限制查询资源
CREATE SETTINGS PROFILE reporting
SETTINGS max_memory_usage = '4GB', max_execution_time = 60;

45. 如何实现ClickHouse的多租户?

方案一:按数据库隔离


CREATE DATABASE tenant_a;
CREATE DATABASE tenant_b;

方案二:行级隔离(RBAC)


CREATE ROW POLICY policy_a ON orders USING tenant_id = 'A' FOR SELECT;

方案三:视图隔离


CREATE VIEW tenant_a_orders AS SELECT * FROM orders WHERE tenant_id = 'A';

46. ClickHouse与Flink如何配合?


数据源 → Flink CDC → Kafka → ClickHouse
                        ↓
              Flink聚合 → ClickHouse聚合表
                        ↓
              Flink Window → ClickHouse时序表

Flink JDBC Sink:


JdbcSink.sink(
    "INSERT INTO events (event_time, user_id, event_type) VALUES (?, ?, ?)",
    (ps, event) -> {
        ps.setTimestamp(1, Timestamp.valueOf(event.getTime()));
        ps.setLong(2, event.getUserId());
        ps.setString(3, event.getType());
    },
    JdbcExecutionOptions.builder()
        .withBatchSize(10000)
        .withBatchIntervalMs(5000)
        .build(),
    new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()
        .withUrl("jdbc:clickhouse://ch-host:8123/default")
        .build()
);

47. ClickHouse 23.x/24.x有哪些重要新特性?

48. ClickHouse的数据导入导出格式?

导入:


# CSV
clickhouse-client --query="INSERT INTO events FORMAT CSV" < data.csv

# Parquet
clickhouse-client --query="INSERT INTO events FORMAT Parquet" < data.parquet

# JSON
clickhouse-client --query="INSERT INTO events FORMAT JSONEachRow" < data.json

导出:


SELECT * FROM events INTO OUTFILE 'output.parquet' FORMAT Parquet;

49. ClickHouse的安全配置?


<!-- config.xml -->
<users>
    <readonly_user>
        <password_sha256_hex>...</password_sha256_hex>
        <profile>readonly</profile>
        <networks>
            <ip>10.0.0.0/8</ip>
        </networks>
    </readonly_user>
</users>

<profiles>
    <readonly>
        <readonly>1</readonly>
        <max_memory_usage>2000000000</max_memory_usage>
    </readonly>
</profiles>

安全最佳实践:

50. 如何设计一个TB级数据量的ClickHouse集群?

集群规划:

硬件:

表设计:


面试速记口诀

ClickHouse三板斧:列存+向量化→快、MergeTree→核心引擎、MV→预聚合管道

>

写入三原则:大批量、低频率、异步INSERT

>

查询三优化:分区裁剪、主键过滤、避免JOIN用字典

>

选型三对比:分析用CH、搜索用ES、均衡用StarRocks/Doris