故障分析 | ClickHouse 物化视图插入时间变为“1970-01-01 08:00:00”问题复盘

作者：大睿

大睿，DBA，爱好减肥，瘦了30多斤，负责公司数据库集群的管理和维护。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

---

物化视图使用to的方式写入到存储表中，即如下：

CREATE MATERIALIZED VIEW
[IF NOT EXISTS]
[db.]table_name
[ON CLUSTER] TO
[db.]nameAS
SELECT ...
1
2
3
4
5
6

指定了存储的表，所以物化视图的创建也不需要指定 engine ，在查询中，查物化视图和查实际的存储表得到一样的数据，因为都是来自于同一份存储数据。

物化视图是计算每次写入原表的数据，经过聚合之后写入到目标表。比如，有按照 1s 一次记录的明细表，同时需要按照分钟级做数据的聚合统计pv（类似的需要），则可以通过创建物化视图的方式将聚合后的数据写到 1min 的表中（这种感觉有点像触发器）

范例

1s记录的明细表

CREATE TABLE dba_test.t_1s
(
    `ctime` DateTime64(0),
    `pv` Int64
)
ENGINE = MergeTree
PARTITION BY toDate(ctime)
ORDER BY ctime
SETTINGS index_granularity = 8192
1
2
3
4
5
6
7
8
9

1min 记录的聚合数据

CREATE TABLE dba_test.t_1m
(
    `ctime` DateTime64(0),
    `pv` Int64
)
ENGINE = SummingMergeTree
PARTITION BY toDate(ctime)
ORDER BY ctime
SETTINGS index_granularity = 8192
1
2
3
4
5
6
7
8
9

物化视图t_1m_mv，查询条件是从1s的表(t_1s)，按照分钟级(toStartOfMinute)聚合查询结果，重新写入到1min的表(t_1m)中

物化视图

CREATE MATERIALIZED VIEW dba_test.t_1m_mv TO dba_test.t_1m
(
    `toStartOfMinute(ctime)` DateTime,
    `pv` Int64
) AS
SELECT
    toStartOfMinute(ctime),
    sum(pv) AS pv
FROM dba_test.t_1s
GROUP BY ctime
1
2
3
4
5
6
7
8
9
10

写入测试

dba-clickhouse-001 :) insert into t_1s values('2022-01-01 00:10:01',1),('2022-01-01 00:10:01',1),('2022-01-01 00:20:01',2),('2022-01-01 00:20:01',2),('2022-01-01 00:30:01',3);

INSERT INTO t_1s VALUES

Query id: 0bf16844-0123-4e25-a3d4-f9b5a5c8db37

Ok.

5 rows in set. Elapsed: 0.003 sec.

dba-clickhouse-001 :) select * from t_1s;

SELECT *
FROM t_1s

Query id: cb442100-37a6-4de7-b6f3-f80f084710dc

┌───────────────ctime─┬─pv─┐
│ 2022-01-01 00:10:01 │  1 │
│ 2022-01-01 00:10:01 │  1 │
│ 2022-01-01 00:20:01 │  2 │
│ 2022-01-01 00:20:01 │  2 │
│ 2022-01-01 00:30:01 │  3 │
└─────────────────────┴────┘

5 rows in set. Elapsed: 0.002 sec.

dba-clickhouse-001 :) select * from t_1m;

SELECT *
FROM t_1m

Query id: f9d2d05d-8ad7-44a4-b66a-ea8c3c758f1f

┌───────────────ctime─┬─pv─┐
│ 1970-01-01 08:00:00 │  9 │
└─────────────────────┴────┘

1 rows in set. Elapsed: 0.002 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

插入的时间竟然是1970-01-01 08:00:00

开始验证是否是查询语句有误

检查物化视图中的查询结果是否符合预期

dba-clickhouse-001 :) SELECT
:-]     toStartOfMinute(ctime),
:-]     sum(pv) AS pv
:-] FROM dba_test.t_1s
:-] GROUP BY ctime;

SELECT
    toStartOfMinute(ctime),
    sum(pv) AS pv
FROM dba_test.t_1s
GROUP BY ctime

Query id: 1ecaf07e-c766-40b7-bfa2-0f87ee54abad

┌─toStartOfMinute(ctime)─┬─pv─┐
│    2022-01-01 00:20:00 │  4 │
│    2022-01-01 00:30:00 │  3 │
│    2022-01-01 00:10:00 │  2 │
└────────────────────────┴────┘

3 rows in set. Elapsed: 0.002 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

查询结果符合预期

直接通过insert ...select...方式确认下插入数据是否符合预期

dba-clickhouse-001 :) insert into t_1m SELECT
:-]     toStartOfMinute(ctime),
:-]     sum(pv) AS pv
:-] FROM dba_test.t_1s
:-] GROUP BY ctime;

INSERT INTO t_1m SELECT
    toStartOfMinute(ctime),
    sum(pv) AS pv
FROM dba_test.t_1s
GROUP BY ctime

Query id: 5db8279a-ffb1-4174-843c-80cee48b448c

Ok.

0 rows in set. Elapsed: 0.002 sec.

dba-clickhouse-001 :) select * from t_1m;

SELECT *
FROM t_1m

Query id: acd79ea7-dc82-49f1-bb71-430a05895f19

┌───────────────ctime─┬─pv─┐
│ 1970-01-01 08:00:00 │  9 │
└─────────────────────┴────┘
┌───────────────ctime─┬─pv─┐
│ 2022-01-01 00:10:00 │  2 │
│ 2022-01-01 00:20:00 │  4 │
│ 2022-01-01 00:30:00 │  3 │
└─────────────────────┴────┘

4 rows in set. Elapsed: 0.002 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

直接插入，数据正确，时间没有被转化。

可以确认物化视图的查询部分是没有问题，那只能是在写入的时候出现了问题，换个思路去想一下，时间戳的开始时间是1970-01-01 00:00:00，而这里插入的时间是1970-01-01 08:00:00多了8小时，也就是说因为时区的原因导致时间推迟了。那会不会是因为插入的数据不规范，或者是“空”被转化了呢。

验证

dba-clickhouse-001 :) insert into t_1m values('',100);

INSERT INTO t_1m VALUES

Query id: af1785ef-dca1-467b-84c6-27f9da6547f6

Ok.

1 rows in set. Elapsed: 0.002 sec.

dba-clickhouse-001 :) select * from t_1m;

SELECT *
FROM t_1m

Query id: 34db2057-7274-4859-898e-6132f8df4465

┌───────────────ctime─┬─pv─┐
│ 1970-01-01 08:00:00 │  9 │
└─────────────────────┴────┘
┌───────────────ctime─┬─pv─┐
│ 2022-01-01 00:10:00 │  2 │
│ 2022-01-01 00:20:00 │  4 │
│ 2022-01-01 00:30:00 │  3 │
└─────────────────────┴────┘
┌───────────────ctime─┬──pv─┐
│ 1970-01-01 08:00:00 │ 100 │
└─────────────────────┴─────┘

5 rows in set. Elapsed: 0.002 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

果然，当插入的数据为空的时候，时间被重置了。

对比下物化视图和目标的聚合表的结构

dba-clickhouse-001 :) desc t_1m;

DESCRIBE TABLE  t_1m

Query id: 96c6a5ca-e42a-47e1-8212-cbcfefa6ffa4

┌─name──┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ ctime │ DateTime64(0) │              │                    │         │                  │                │
│ pv    │ Int64         │              │                    │         │                  │                │
└───────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

2 rows in set. Elapsed: 0.001 sec.

dba-clickhouse-001 :) desc t_1m_mv;

DESCRIBE TABLE  t_1m_mv

Query id: a258f6b5-f195-4386-a9bb-4ec86e7e9bd1

┌─name───────────────────┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ toStartOfMinute(ctime) │ DateTime │              │                    │         │                  │                │
│ pv                     │ Int64    │              │                    │         │                  │                │
└────────────────────────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

2 rows in set. Elapsed: 0.001 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

聚合表时间字段名叫ctime，物化视图的则是toStartOfMinute(ctime)

重新调整物化视图的写法，并清理t_1m表中的数据

dba-clickhouse-001 :) show create table t_1m_mv\G
statement: CREATE MATERIALIZED VIEW dba_test.t_1m_mv TO dba_test.t_1m
(
    `ctime` DateTime,
    `pv` Int64
) AS
SELECT
    toStartOfTenMinutes(ctime) AS ctime,
    sum(pv) AS pv
FROM dba_test.t_1s
GROUP BY ctime

dba-clickhouse-001 :) insert into t_1s values('2022-01-01 00:10:01',1),('2022-01-01 00:10:01',1),('2022-01-01 00:20:01',2),('2022-01-01 00:20:01',2),('2022-01-01 00:30:01',3);

INSERT INTO t_1s VALUES

Query id: 812d1bbd-55f3-4a8f-b9f7-bbbe93e694af

Ok.

5 rows in set. Elapsed: 0.003 sec.

dba-clickhouse-001 :) select * from t_1m;

SELECT *
FROM t_1m

Query id: 2d1a045a-4e53-4f94-bb6a-fe5e5d58f5c7

┌───────────────ctime─┬─pv─┐
│ 2022-01-01 00:10:00 │  2 │
│ 2022-01-01 00:20:00 │  4 │
│ 2022-01-01 00:30:00 │  3 │
└─────────────────────┴────┘

3 rows in set. Elapsed: 0.002 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

结论

物化视图的字段(t_1m_mv)要与目标表(t_1m)的字段名对齐

（表达不是很严谨，大概是上面的意思）