本文主要介绍 clickhouse 的基础用法
安装
下面以 Debain/Ubuntu系统为例,安装方式
1 | sudo apt-get install -y apt-transport-https ca-certificates dirmngr |
创建表
- MergeTree类型
以下是一个记录用户访问日志的表
1
2
3
4
5
6
7
8
9
10CREATE TABLE IF NOT EXISTS visit_log (
user_id UInt32,
url String,
count UInt8,
created_at DateTime
) Engine = MergeTree
PARTITION BY toYYYYMMDD(created_at) -- 按照日期分区
ORDER BY created_at -- 按照时间排序
TTL created_at + toIntervalDay(180) -- 指定180天自动删除过期数据
SETTINGS index_granularity = 8192 -- 设置索引颗粒度 - 创建视图
以下视图是为了按小时统计url的访问次数
more >>1
2
3
4
5
6
7
8
9
10
11
12
13CREATE MATERIALIZED VIEW url_stat
ENGINE = AggregatingMergeTree()
ORDER BY (hour_time, url)
TTL hour_time + toIntervalDay(1)
AS SELECT
url,
toStartOfHour(created_at) AS hour_time, --格式化为小时
sumState(count) AS visit_count_state, -- 统计总和
minState(created_at) AS first_time_state, -- 记录最早访问时间
maxState(created_at) AS last_time_state -- 记录最晚访问时间
FROM visit_log
WHERE count>0
GROUP BY hour_time,url