背景
测试Clickhouse的插入特性,了解数仓计算中出现同层依赖场景下的数据处理。本次采用分布式的Clickhouse数据库进行测试。
数据计算流程(同层之间)
A表通过SQL计算完成后写入B表,B表需要经过查询自身以及关联其他表再次计算,计算出B表中的一个或者多个指标数据后写入B表。
测试过程
1.建表语句
CREATE TABLE dw_local.city on cluster XX
(
`id` UInt8,
`country` String,
`province` Nullable(String),
`city` String,
`create_time` DateTime64(6),
`etl_original` String,
`_sign` Int8,
`_version` UInt64
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/table/{shard}/city', '{replica}')
PARTITION BY toYYYYMM(create_time)
ORDER BY id
CREATE TABLE dw_dist.city on cluster XX as dw_local.city ENGINE = Distributed('msun', 'dw_local', 'city', sipHash64(etl_original));
CREATE VIEW dw.city on cluster XX as select * from dw_dist.city final where _sign = 1;
2.插入测试数据
insert into dw_dist.city(id,country,province,city,etl_original,_sign,_version) VALUES(1,'China','Hubei','wuhan','dev',1,0);
3.上面插入数据时一条完整的正常语句,可以通过查询视图DW进行查询
select * from dw.city;
┌─id─┬─country─┬─province─┬─city──┬────────────────create_time─┬─etl_original─┬─_sign─┬─_version─┐
│ 1 │ China │ Hubei │ wuhan │ 1970-01-01 08:00:00.000000 │ dev │ 1 │ 0 │
└────┴─────────┴──────────┴───────┴────────────────────────────┴──────────────┴───────┴──────────┘
4.上述建表语句中,id作为主键不可为空,country设置不可为空,province设置可空,现插入一条数据。
insert into dw_local.city(id,city,etl_original,_sign,_version) VALUES(2,'wuhan','dev',1,0);
查询结果:
┌─id─┬─country─┬─province─┬─city──┬────────────────create_time─┬─etl_original─┬─_sign─┬─_version─┐
│ 1 │ China │ Hubei │ wuhan │ 1970-01-01 08:00:00.000000 │ dev │ 1 │ 0 │
└────┴─────────┴──────────┴───────┴────────────────────────────┴──────────────┴───────┴──────────┘
┌─id─┬─country─┬─province─┬─city──┬────────────────create_time─┬─etl_original─┬─_sign─┬─_version─┐
│ 2 │ │ ᴺᵁᴸᴸ │ wuhan │ 1970-01-01 08:00:00.000000 │ dev │ 1 │ 0 │
└────┴─────────┴──────────┴───────┴────────────────────────────┴──────────────┴───��───┴──────────┘
结果显示:
1.province可为空会赋值为null
2.country字段不作为本次插入的结果集中,也就是指对局部字段进行了更新,只插入指定字段的值。
再次测试不为空的字段
insert into dw_local.city(id,country,city,etl_original,_sign,_version) VALUES(4,'','wuhan','dev',1,0); --成功
insert into dw_local.city(id,country,city,etl_original,_sign,_version) VALUES(5,'null','wuhan','dev',1,0); --成功
但是当使用
insert into dw_local.city(id,country,city,etl_original,_sign,_version) VALUES(5,,'wuhan','dev',1,0);
会出现报错
DB::Exception: Cannot parse expression of type String here: ,'wuhan','dev',1,0);: While executing ValuesBlockInputFormat: data for INSERT was parsed from query
5.同理,非主键不为空是这样,那么主键字段为空是怎么样呢
insert into dw_local.city(city,etl_original,_sign,_version) VALUES('wuhan','dev',1,0);
查询结果
┌─id─┬─country─┬─province─┬─city──┬────────────────create_time─┬─etl_original─┬─_sign─┬─_version─┐
│ 0 │ │ ᴺᵁᴸᴸ │ wuhan │ 1970-01-01 08:00:00.000000 │ dev │ 1 │ 0 │
└────┴─────────┴──────────┴───────┴────────────────────────────┴──────────────┴───────┴──────────┘
可以发现,主键未填写时,默认为 0。
再次插入一条数据
insert into dw_local.city(city,etl_original,_sign,_version) VALUES('wuhan','devs',1,0);
查询结果
┌─id─┬─country─┬─province─┬─city──┬────────────────create_time─┬─etl_original─┬─_sign─┬─_version─┐
│ 0 │ │ ᴺᵁᴸᴸ │ wuhan │ 1970-01-01 08:00:00.000000 │ devs │ 1 │ 0 │
└────┴─────────┴──────────┴───────┴────────────────────────────┴──────────────┴───────┴──────────┘
可以发现是对id = 0 的数据进行了更新。
结论
1.在Clickhouse中,单引号包裹的空以及null都可作为值进行插入操作。
2.可空字段未指定插入数据时,String类型默认为 Null,Int类型默认为 0.
3.Clickhouse可进行局部更新字段,其他字段依然保持原始的值,这样在同层依赖时,可通过主键更新数据。
以上仅代表个人测试过程,不严谨的地方希望大家指正。
注:本章中的测试表结构及测试数据都是采用其他博主的测试数据,如有侵权联系即刻删除。