altinity-expert-clickhouse-dictionaries

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Dictionary Diagnostics

字典诊断

Analyze external dictionaries: configuration, memory usage, reload status, and performance.

分析外部字典：配置、内存使用、重载状态和性能表现。

Diagnostics

诊断操作

Run all queries from the file checks.sql and analyze the results.

运行checks.sql文件中的所有查询并分析结果。

Dictionary Reload Operations

字典重载操作

Force Reload (syntax reference)

强制重载（语法参考）

sql

-- SYSTEM RELOAD DICTIONARY {database}.{name}
-- SYSTEM RELOAD DICTIONARIES

sql

-- SYSTEM RELOAD DICTIONARY {database}.{name}
-- SYSTEM RELOAD DICTIONARIES

Check Reload Result for Specific Dictionary

检查特定字典的重载结果

sql

-- Check reload result
select
    name,
    status,
    loading_start_time,
    loading_duration,
    last_exception
from system.dictionaries
where name = '{dictionary_name}'

sql

-- 检查重载结果
select
    name,
    status,
    loading_start_time,
    loading_duration,
    last_exception
from system.dictionaries
where name = '{dictionary_name}'

Best Practices

最佳实践

Dictionary Sizing Guidelines

字典规模选型指南

Elements	Recommended Type
< 100K	Flat (if sequential keys)
100K - 10M	Hashed
> 10M	Consider partitioning or cache
Complex keys	ComplexKeyHashed
Sparse access	Cache with SSD

元素数量	推荐类型
< 100K	Flat（如果是连续键）
100K - 10M	Hashed
> 10M	考虑分区或缓存
复杂键	ComplexKeyHashed
稀疏访问	搭配SSD使用缓存

Common Issues

常见问题

Symptom	Cause	Solution
High memory	Too many elements	Use cache type, filter data
Slow reload	Large source table	Add filters, use delta updates
Stale data	Source unreachable	Check connectivity, add retry
Failed status	Source query fails	Check source table/query

症状	原因	解决方案
内存占用过高	元素数量过多	使用缓存类型，过滤数据
重载速度慢	源表过大	添加过滤条件，使用增量更新
数据过时	源端不可达	检查连通性，添加重试机制
状态异常	源查询失败	检查源表/查询语句

Cross-Module Triggers

跨模块触发

Finding	Load Module	Reason
High memory usage	`altinity-expert-clickhouse-memory`	Overall memory analysis
Load failures	`altinity-expert-clickhouse-overview`	Error summary + routing
Source connectivity	`altinity-expert-clickhouse-logs`	Log investigation
Slow lookups	`altinity-expert-clickhouse-reporting`	Query optimization

发现问题	加载模块	原因
内存占用过高	`altinity-expert-clickhouse-memory`	整体内存分析
加载失败	`altinity-expert-clickhouse-overview`	错误汇总与路由
源端连通性问题	`altinity-expert-clickhouse-logs`	日志排查
查询缓慢	`altinity-expert-clickhouse-reporting`	查询优化

Settings Reference

设置参考

Setting	Notes
`dictionaries_lazy_load`	Load on first access vs startup
`dictionary_load_wait_timeout_ms`	Wait time for lazy load
`max_dictionary_num_to_warn`	Warning threshold

设置项	说明
`dictionaries_lazy_load`	首次访问时加载 vs 启动时加载
`dictionary_load_wait_timeout_ms`	懒加载等待时长
`max_dictionary_num_to_warn`	警告阈值