ClickHouse 数据的更新（Mutation）_Yore Yuen的博客-CSDN博客_clickhouse更新字段
ClickHouse 数据的更新（Mutation）
Yore Yuen
于 2019-11-21 14:25:19 发布
16571
收藏
22
分类专栏：
数据库
大数据
文章标签：
ClickHouse
UPDATE
INSERT
Mutation
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/github_39577257/article/details/103181537
版权
数据库
同时被 2 个专栏收录
21 篇文章
3 订阅
订阅专栏
大数据
56 篇文章
22 订阅
订阅专栏
目录
1 关于 Mutation2 Update 的一次问题排查（UPDATE 成功但数据却没有修改）3 需求小例子3.1 一个数据集3.2 表3.2.1 登录 client3.2.2 建表3.2.3 导入数据3.2.4 SQL
3.3 不同点3.4 小节
1 关于 Mutation
常见的 Mutation 操作类似于 ALTER TABLE … DELETE 或者 ALTER TABLE … UPDATE，前者用于删除表数据，后者用于修改表数据，这两种操作都会直接改变当前表的数据，在大数据中修改数据的成本是比较大的，对于这类操作，ClickHouse 虽然是支持的，但改动数据所需时间是不可预期的，ClickHouse 中 Mutation 相关的操作是异步进行的，也就是当我们提交 ALTER TABLE … DELETE 或者 ALTER TABLE … UPDATE 这类操作时，直接返回执行成功的，ClickHouse 会在后台异步执行 mutation 操作，执行的数据保存在 ClickHouse 的系统库下的 mutations 表中，当然这类操作在 ClickHouse 也是没有原子性的，后期的查询出来的数据可能来自于 mutation 之后的，也可能来自于 mutation 中的或者之前的。关于 Mutation 更详细说明可查看官网文档 Mutation。
system.mutations 的表结构如下：
字段名类型说明databaseString应用 mutation 的数据库名tableString应用 mutation 的表名mutation_idStringmutation的 ID，对于 replicated表，ID 也对应于 ZK 中的 <table_path_in_zookeeper>/mutations 目录下的 znode 名，对于非 replicated 表 ID对应于表数据目录中的文件名。commandStringmutation 命令的字符串，ALTER TABLE [db.]table 之后的部分create_timeDateTime提交 mutation 命令的时间block_numbers.partition_idArray(String)对于 replicated 表，数组包含分区 ID（每个分区一个记录），对于非 replicated 表的 mutation ，数组为空。block_numbers.numberArray(Int64)对于 replicated 表，数组包含每一个分区的一条记录以及mutation 获得的 block 数字，只有包含数字小于此数字的 block 部分才会在分区中发生 mutation。在非 replicated 表中，所有分区中的 block 数字形成一个序列，这意味着对于非 replicated表的 mutation，该列将包含一条记录，该记录具有通过 mutation 获得的单个 block 数字。parts_to_do_namesArray(String)完成 mutation 而需要 mutation 的数据部分的array 的名称parts_to_doInt64需要 mutation 才能完成 mutation 的数据部分的数量is_doneUInt8mutation 是否完成的标志。1 表示完成0 表示正在进行中【说明】即使 parts_to_do=0，由于长时间运行 insert 查询，replicated 表的 mutation 可能尚未完成，这将创建需要 mutation 的新数据部分。latest_failed_partString无法改变的最新部分的名称latest_fail_timeDateTime最近一次 mutation 失败部分的日期和时间latest_fail_reasonString导致最近部分突变失败的异常消息
所有 mutation 操作都会记录在上面这个表中，其中有 create_time ，后续的执行也是按照这个顺序执行，在提交 mutation 之前插入表中的数据将被改动，之后插入的数据将不会被改动，mutation 不会以任何方式阻止期间或后续的 insert 操作。
2 Update 的一次问题排查（UPDATE 成功但数据却没有修改）
在一次 Update ，执行类似的 mutation 语句如下：
ALTER TABLE 表名1
UPDATE `l52677bf3cf0d43baa13db78fc2bdacfa_partyid`=pxxxxid
WHERE pxxxxid in (
SELECT pxxxx_id FROM 表名2 WHERE 条件
);
可以发现执行没有报错，成功执行了，但是通过下面的类似语句查询，发下 l52677bf3cf0d43baa13db78fc2bdacfa_partyid 字段的值并没有修改为 pxxxxid 字段的值，既然是异步执行那就稍等下之后再查看结果，于是你等了半个月，再次查询发现数据还是没有修改。
SELECT `l52677bf3cf0d43baa13db78fc2bdacfa_partyid`,pxxxxid
FROM 表名1 WHERE pxxxxid in (
SELECT pxxxx_id FROM 表名2 WHERE 条件 LIMIT 10
);
于是查看 ClickHouse Server 端的日志，根据上次执行 SQL 的 query id Query id: 96bdba0d-4b51-4361-bacc-89cbbcc3c626，查看 clickhouse-server.log（默认由 /etc/clickhouse-server/config.xml 配置文件中的 <log> 指定的路径下），可以看到执行并没有报错，同时我们得知前面提交的 UPDATE 操作写入到了 mutation_124.txt 文件中。
2019.11.21 20:47:17.035273 [ 80740 ] {96bdba0d-4b51-4361-bacc-89cbbcc3c626} <Debug> executeQuery: (from 10.x.xxx.xx:51202, using production parser) ALTER TABLE kudu.表名1 UPDATE `l52677bf3cf0d43baa13db78fc2bdacfa_partyid`=`par tyid` WHERE `pxxxxid` = '$****$';
2019.11.21 20:47:17.035464 [ 80740 ] {96bdba0d-4b51-4361-bacc-89cbbcc3c626} <Trace> ContextAccess (default): Access granted: ALTER UPDATE(l52677bf3cf0d43baa13db78fc2bdacfa_partyid) ON kudu.表名1
2019.11.21 20:47:17.046765 [ 80740 ] {96bdba0d-4b51-4361-bacc-89cbbcc3c626} <Information> kudu.表名1 (719d42c1-e14c-4b95-9235-dafda61eb2a6): Added mutation: mutation_124.txt
然后查询系统表的 mutations，重点查看 is_done=0 的 latest_fail_reason 字段的值，其中给出了很重要的执行错误的原因。
-- SELECT *
SELECT mutation_id,database,table,is_done,create_time,command
FROM system.mutations
where database='kudu' AND `table`='表名1'
order by create_time
可能会看到如下的错误原因，这次终于大概知道了原因，就是在大量提交 mutation 操作过程中，ClickHouse 异步执行这些 mutation 时，表中的 'le6452913d83f47388dc27ac16141aaca_partyid' 字段却被删除了，导致这个任务执行失败，而之后提交的对这个表的所有 mutation 操作都排在其后执行而无法正常执行（似乎这是 目前 ClickHouse 的 mutation 的一个 Bug）。
Code: 47, e.displayText() = DB::Exception: Missing columns: 'le6452913d83f47388dc27ac16141aaca_partyid' while processing query: 'SELECT le6452913d83f47388dc27ac16141aaca_partyid FROM kudu.表名1 WHERE le6452913d83f47388dc27ac16141aaca_partyid != ''', required columns: 'le6452913d83f47388dc27ac16141aaca_partyid' (version 21.2.1.5869 (official build))
解决方法有两种，第一种是在 clickhouse-client 执行添加 --mutations_sync 参数。
# --mutations_sync 具体可访问下面链接
# https://clickhouse.tech/docs/en/operations/settings/settings/#mutations_sync
clickhouse-client -h hostname --port 9000 -u 用户 --password 密码 --multiline --mutations_sync=2
第二种方式【推荐】，通过 system.mutations 表排查出错的 mutation 操作，如果这些操作不能删除，则需要备份这些操作，之后表的 mutation 修复之后再根据时间依次执行，如果可以删除，则直接使用下面的命令删除执行失败的 mutation：
-- 详见 https://clickhouse.tech/docs/en/sql-reference/statements/kill/#kill-mutation
-- 想删除单个，可以定位到库和表的 mutation_id 来删除。
-- 删除多个，可以使用 mutation_id IN('')
-- 删除所有非执行成功的，可以通过 is_done='0'
KILL MUTATION WHERE database = 'kudu' AND table = 'CUS_INFO'
-- AND mutation_id = 'mutation_126.txt'
-- AND mutation_id IN('mutation_125.txt','mutation_124.txt','mutation_123.txt')
AND is_done='0'
删除旧的 mutation 失败任务后，这个表的 Mutation 操作就可以正常执行，再次提交对表的 Update 操作，可以看到数据已经发生了修改。
3 需求小例子
假如现在想将表B的数据在满足一定条件时将其某个值更新到表A，如果是MySQL，实现该业务的语法可能如下：
UPDATE A,B set A.field1=B.field1 where filter_expr;
3.1 一个数据集
这里主要使用的是 TPC-DS的一个数据集。更多TPC的使用可以查看我GitHue上写的一份文档TPC.md。
wget http://www.tpc.org/tpc_documents_current_versions/temporary_download_files/42d6f585-7c65-469c-b8de-9bfe47b63d81-tpc-ds-tool.zip
mv 42d6f585-7c65-469c-b8de-9bfe47b63d81-tpc-ds-tool.zip TPC-2.11.0.zip
unzip TPC-2.11.0.zip
cd v2.11.0rc2/
cd tools/
# 编译
make
# 生成一份10G的数据集
./dsdgen -DELIMITER ',' -scale 10 -parallel 2 -TERMINATE N -dir /opt/tmp/data
# 查看 inventory_1_2.dat
[root@cdh3 tools]# head -n 3 /opt/tmp/data/inventory_1_2.dat
2450815,1,1,211
2450815,2,1,235
2450815,4,1,859
# 文件大小
[root@cdh3 tools]# du -hd1 /opt/tmp/data/inventory_1_2.dat
1.3G /opt/tmp/data/inventory_1_2.dat
# 数据条数
[root@cdh3 tools]# wc -l /opt/tmp/data/inventory_1_2.dat
66555000 /opt/tmp/data/inventory_1_2.dat
3.2 表
3.2.1 登录 client
clickhouse-client -h 127.0.0.1 --port 19000 -u default --password KavrqeN1 --multiline
3.2.2 建表
参考v2.11.0rc2/tools/tpcds.sql脚本的建表语句创建 ClickHouse 表
-- 创建A表
CREATE TABLE inventory(
inv_date_sk UInt64 ,
inv_item_sk UInt64 ,
inv_warehouse_sk UInt64 ,
inv_quantity_on_hand UInt64
)ENGINE = MergeTree ORDER BY (inv_date_sk, inv_item_sk, inv_warehouse_sk);
-- 创建B表，
CREATE TABLE inventory2(
inv_date_sk UInt64 ,
inv_item_sk UInt64 ,
inv_warehouse_sk UInt64 ,
inv_quantity_on_hand UInt64
)ENGINE = MergeTree ORDER BY (inv_date_sk, inv_item_sk, inv_warehouse_sk);
3.2.3 导入数据
# 导入数据到 cdh2 节点的 clickhouse
clickhouse-client -h cdh2 --port 19000 -u default --password KavrqeN1 --query "INSERT INTO inventory FORMAT CSV" < /opt/tmp/data/inventory_1_2.dat
3.2.4 SQL
-- 1 inventory2 中插入一部分数据
cdh2 :) INSERT INTO inventory2 SELECT inv_date_sk, inv_item_sk, inv_warehouse_sk, rand() FROM inventory WHERE inv_warehouse_sk in (1,2,3,4,5);
INSERT INTO inventory2 SELECT
inv_date_sk,
inv_item_sk,
inv_warehouse_sk,
rand()
FROM inventory
WHERE inv_warehouse_sk IN (1, 2, 3, 4, 5)
→ Progress: 2.99 million rows, 45.92 MB (25.22 million rows/s., 387.37 MB/s.) 4%↘ Progress: 5.14 million rows, 80.06 MB (9.91 million rows/s., 154.19 MB/s.) ██████████████▋ %Ok.
0 rows in set. Elapsed: 9.417 sec. Processed 66.56 million rows, 1.07 GB (7.07 million rows/s., 113.30 MB/s.)
-- 2 数据总数
-- 2.1 inventory
cdh2 :) SELECT COUNT(1) FROM inventory;
┌─COUNT(1)─┐
│ 66555000 │
└──────────┘
↓ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↙ Progress: 66.56 million rows, 532.44 MB (1.08 billion rows/s., 8.68 GB/s.) 98%
1 rows in set. Elapsed: 0.052 sec. Processed 66.56 million rows, 532.44 MB (1.08 billion rows/s., 8.67 GB/s.)
-- 2.2 inventory2
cdh2 :) SELECT COUNT(1) FROM inventory2;
┌─COUNT(1)─┐
│ 33405000 │
└──────────┘
↓ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↙ Progress: 33.41 million rows, 267.24 MB (1.34 billion rows/s., 10.72 GB/s.) 98%
1 rows in set. Elapsed: 0.025 sec. Processed 33.41 million rows, 267.24 MB (1.32 billion rows/s., 10.56 GB/s.)
-- 3 统计字段信息。可以看到总共有 10个仓库，68000 类商品
cdh2 :) SELECT COUNT(DISTINCT inv_date_sk),COUNT(DISTINCT inv_item_sk),COUNT(DISTINCT inv_warehouse_sk) FROM inventory;
┌─uniqExact(inv_date_sk)─┬─uniqExact(inv_item_sk)─┬─uniqExact(inv_warehouse_sk)─┐
│ 131 │ 68000 │ 10 │
└────────────────────────┴────────────────────────┴─────────────────────────────┘
↖ Progress: 64.92 million rows, 1.56 GB (190.93 million rows/s., 4.58 GB/s.) 96%↑ Progress: 66.56 million rows, 1.60 GB (195.70 million rows/s., 4.70 GB/s.) 98%
1 rows in set. Elapsed: 0.287 sec. Processed 66.56 million rows, 1.60 GB (195.57 million rows/s., 4.69 GB/s.)
-- 4 查看每个仓库（inv_warehouse_sk） 的数据库中条数。可以看到(4,3,2,5,1)共33405000，(6,7,9,8,10)共33150000，导入数据总数据条数 66555000
cdh2 :) SELECT inv_warehouse_sk,COUNT(inv_warehouse_sk) FROM inventory GROUP BY inv_warehouse_sk;
┌─inv_warehouse_sk─┬─COUNT(inv_warehouse_sk)─┐
│ 4 │ 6681000 │
│ 3 │ 6681000 │
│ 2 │ 6681000 │
│ 5 │ 6681000 │
│ 1 │ 6681000 │
│ 6 │ 6630000 │
│ 7 │ 6630000 │
│ 9 │ 6630000 │
│ 8 │ 6630000 │
│ 10 │ 6630000 │
└──────────────────┴─────────────────────────┘
↙ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ← Progress: 66.56 million rows, 532.44 MB (674.77 million rows/s., 5.40 GB/s.) 98%
10 rows in set. Elapsed: 0.076 sec. Processed 66.56 million rows, 532.44 MB (673.36 million rows/s., 5.39 GB/s.)
-- 5 查看各个库存量
cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory GROUP BY inv_warehouse_sk;
┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
│ 4 │ 3172760518 │
│ 3 │ 3173305680 │
│ 2 │ 3173041915 │
│ 5 │ 3173462792 │
│ 1 │ 3172739142 │
│ 6 │ 3148272312 │
│ 7 │ 3148312176 │
│ 9 │ 3150290280 │
│ 8 │ 3149344378 │
│ 10 │ 3148388511 │
└──────────────────┴───────────────────────────┘
↖ Progress: 39.35 million rows, 629.54 MB (227.31 million rows/s., 3.64 GB/s.) 58%↑ Progress: 66.56 million rows, 1.06 GB (384.34 million rows/s., 6.15 GB/s.) 98%
10 rows in set. Elapsed: 0.173 sec. Processed 66.56 million rows, 1.06 GB (384.14 million rows/s., 6.15 GB/s.)
-- 6 修改仓库为 (4,3,2,5,1)共33405000条的库存量，库存设置为 0。
cdh2 :) ALTER TABLE inventory UPDATE inv_quantity_on_hand = 0 where inv_warehouse_sk in (4,3,2,5,1);
ALTER TABLE inventory
UPDATE inv_quantity_on_hand = 0 WHERE inv_warehouse_sk IN (4, 3, 2, 5, 1)
Ok.
0 rows in set. Elapsed: 0.004 sec.
-- 7 查看当前各个库存量。
-- 7.1 inventory。发现仓库(4,3,2,5,1)已经全部清库。
cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory GROUP BY inv_warehouse_sk;
┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
│ 4 │ 0 │
│ 3 │ 0 │
│ 2 │ 0 │
│ 5 │ 0 │
│ 1 │ 0 │
│ 6 │ 3148272312 │
│ 7 │ 3148312176 │
│ 9 │ 3150290280 │
│ 8 │ 3149344378 │
│ 10 │ 3148388511 │
└──────────────────┴───────────────────────────┘
↘ Progress: 55.33 million rows, 885.26 MB (427.34 million rows/s., 6.84 GB/s.) 82%↓ Progress: 66.56 million rows, 1.06 GB (513.79 million rows/s., 8.22 GB/s.) 98%
10 rows in set. Elapsed: 0.130 sec. Processed 66.56 million rows, 1.06 GB (513.45 million rows/s., 8.22 GB/s.)
-- 7.2 inventory2
cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory2 GROUP BY inv_warehouse_sk;
┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
│ 4 │ 14347686397994975 │
│ 3 │ 14343877924786742 │
│ 2 │ 14345396281859373 │
│ 5 │ 14345781573562921 │
│ 1 │ 14348098422679985 │
└──────────────────┴───────────────────────────┘
↑ Progress: 30.48 million rows, 487.68 MB (269.58 million rows/s., 4.31 GB/s.) 90%↗ Progress: 33.41 million rows, 534.48 MB (295.30 million rows/s., 4.72 GB/s.) 98%
5 rows in set. Elapsed: 0.113 sec. Processed 33.41 million rows, 534.48 MB (295.11 million rows/s., 4.72 GB/s.)
-- 8 将 inventory2 更新到 inventory 表，虽然这次搞的有点大
-- MySQL支持：update inventory A,inventory2 B set A.inv_quantity_on_hand=B.inv_quantity_on_hand where A.id=B.id;
-- 但是ClickHouse不支持更细的字段来自于两个表，但可以使用 INSERT 语句。MySQL使用Insert语句时不能向已存在的主键列插入值。
cdh2 :) INSERT INTO inventory SELECT inv_date_sk, inv_item_sk, inv_warehouse_sk,inv_quantity_on_hand FROM inventory2
:-] WHERE inventory2.inv_warehouse_sk in (1,2,3,4,5);
↑ Progress: 1.45 million rows, 46.40 MB (14.01 million rows/s., 448.37 MB/s.) 4%↗ Progress: 1.99 million rows, 63.70 MB (4.93 million rows/s., 157.64 MB/s.) 5%→ Progress: 2.51 million rows, 80.22 MB (4.97 million rows/s., 159.07 MB/s.) ██████████████▎ %Ok.
0 rows in set. Elapsed: 8.993 sec. Processed 33.41 million rows, 1.07 GB (3.71 million rows/s., 118.87 MB/s.)
-- 再次查询。发现 inventory2 中的库存信息已经更新到 inventory 表
cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory GROUP BY inv_warehouse_sk;
┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
│ 4 │ 14347686397994975 │
│ 3 │ 14343877924786742 │
│ 2 │ 14345396281859373 │
│ 5 │ 14345781573562921 │
│ 1 │ 14348098422679985 │
│ 6 │ 3148272312 │
│ 7 │ 3148312176 │
│ 9 │ 3150290280 │
│ 8 │ 3149344378 │
│ 10 │ 3148388511 │
└──────────────────┴───────────────────────────┘
↗ Progress: 90.37 million rows, 1.45 GB (372.91 million rows/s., 5.97 GB/s.) 89%→ Progress: 99.96 million rows, 1.60 GB (412.39 million rows/s., 6.60 GB/s.) 98%
10 rows in set. Elapsed: 0.242 sec. Processed 99.96 million rows, 1.60 GB (412.26 million rows/s., 6.60 GB/s.)
-- 9 视图版
-- 9.1 创建视图。注意子句的 JOIN 不能使用别名（AS）
cdh2 :) CREATE VIEW inventory_view AS SELECT
:-] inventory.inv_date_sk,inventory.inv_item_sk,inventory.inv_warehouse_sk,inventory.inv_quantity_on_hand a,inventory2.inv_quantity_on_hand b
:-] FROM inventory LEFT join inventory2
:-] ON inventory.inv_date_sk = inventory2.inv_date_sk AND
:-] inventory.inv_item_sk = inventory2.inv_item_sk AND
:-] inventory.inv_warehouse_sk = inventory2.inv_warehouse_sk
:-] WHERE inventory.inv_warehouse_sk in (1,2,3,4,5)
:-] ;
CREATE VIEW inventory_view AS
SELECT
inventory.inv_date_sk,
inventory.inv_item_sk,
inventory.inv_warehouse_sk,
inventory.inv_quantity_on_hand AS a,
inventory2.inv_quantity_on_hand AS b
FROM inventory
LEFT JOIN inventory2 ON (inventory.inv_date_sk = inventory2.inv_date_sk) AND (inventory.inv_item_sk = inventory2.inv_item_sk) AND (inventory.inv_warehouse_sk = inventory2.inv_warehouse_sk)
WHERE inventory.inv_warehouse_sk IN (1, 2, 3, 4, 5)
Ok.
0 rows in set. Elapsed: 0.006 sec.
-- 9.2 查看表和视图
cdh2 :) SHOW TABLES;
SHOW TABLES
↖ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ┌─name───────────┐
│ inventory │
│ inventory2 │
│ inventory_view │
│ ontime_local │
└────────────────┘
↑ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↗ Progress: 4.00 rows, 145.00 B (1.86 thousand rows/s., 67.36 KB/s.)
4 rows in set. Elapsed: 0.002 sec.
-- 9.3 查看视图数据
cdh2 :) SELECT * FROM inventory_view LIMIT 10;
SELECT *
FROM inventory_view
LIMIT 10
→ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ↘ Progress: 540.67 thousand rows, 17.30 MB (4.38 million rows/s., 140.31 MB/s.) 1%↓ Progress: 933.89 thousand rows, 29.88 MB (4.18 million rows/s., 133.69 MB/s.) 2%↙ Progress: 1.45 million rows, 46.40 MB (3.42 mi%
┌─inv_date_sk─┬─inv_item_sk─┬─inv_warehouse_sk─┬─a─┬──────────b─┐
│ 2451221 │ 1 │ 1 │ 0 │ 3736098505 │
│ 2451221 │ 1 │ 2 │ 0 │ 2885779993 │
│ 2451221 │ 1 │ 3 │ 0 │ 479103458 │
│ 2451221 │ 1 │ 4 │ 0 │ 1752919932 │
│ 2451221 │ 1 │ 5 │ 0 │ 3798676092 │
│ 2451221 │ 2 │ 1 │ 0 │ 1596118095 │
│ 2451221 │ 2 │ 2 │ 0 │ 3044174515 │
│ 2451221 │ 2 │ 3 │ 0 │ 1792720993 │
│ 2451221 │ 2 │ 4 │ 0 │ 888870309 │
│ 2451221 │ 2 │ 5 │ 0 │ 1904802464 │
└─────────────┴─────────────┴──────────────────┴───┴────────────┘
→ Progress: 33.41 million rows, 1.07 GB (2.38 million rows/s., 76.18 MB/s.) ██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████▊ 98%
10 rows in set. Elapsed: 14.042 sec. Processed 34.02 million rows, 1.08 GB (2.42 million rows/s., 76.96 MB/s.)
-- 9.3 UPDATE 数据。VIEW 不支持 Mutations
cdh2 :) ALTER TABLE inventory_view UPDATE a=b
:-] WHERE inv_warehouse_sk in (1,2,3,4,5);
ALTER TABLE inventory_view
UPDATE a = b WHERE inv_warehouse_sk IN (1, 2, 3, 4, 5)
Received exception from server (version 19.16.3):
Code: 48. DB::Exception: Received from 127.0.0.1:19000. DB::Exception: Mutations are not supported by storage View.
0 rows in set. Elapsed: 0.004 sec.
3.3 不同点
MySQL更新数据支持如下语法：
-- 可以将 B 表的某字段值 更新到 A表某字段
mysql> UPDATE inventory A,inventory2 B SET A.inv_quantity_on_hand=B.inv_quantity_on_hand
-> where A.inv_warehouse_sk in (1,2,3,4,5) AND
-> A.inv_date_sk = B.inv_date_sk AND
-> A.inv_item_sk = B.inv_item_sk AND
-> A.inv_warehouse_sk = B.inv_warehouse_sk ;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
MySQL不支持使用 INSERT 语句插入一条主键已存在的数据，但是 ClickHouse支持使用 INSERT 插入数据，如果主键已存在就是覆盖那条数据。
ClickHouse的 UPDATE语法如下，从语法上可以看到 TABLE后面只能是一个表名，可以更新一个字段值(根据过滤条件可能更新的是一行，也可能是多行)，也可以更新多个字段值，但不能是主键。
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr
ClickHouse 的创建 视图（VIEW）的语法如下：
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
MySQL中通过视图更新数据的SQL如下
-- 创建视图
mysql> CREATE VIEW inventory_view AS SELECT A.inv_date_sk,A.inv_item_sk,A.inv_warehouse_sk,A.inv_quantity_on_hand a,B.inv_quantity_on_hand b
-> FROM inventory A LEFT join inventory2 B
-> ON A.inv_date_sk = B.inv_date_sk AND
-> A.inv_item_sk = B.inv_item_sk AND
-> A.inv_warehouse_sk = B.inv_warehouse_sk
-> --WHERE A.inv_warehouse_sk in (1,2,3,4,5)
-> ;
Query OK, 0 rows affected (0.01 sec)
-- 更新数据
mysql> UPDATE inventory_view SET a=b WHERE inv_warehouse_sk in (1,2,3,4,5);
3.4 小节
ClickHouse 支持 UPDATE 、 INSERT 也支持 VIEW，但是和传统关系型数据库的语法有很大的不同，在该需求下我们既不能使用 UPDATE，又不能使用 VIEW，尽管我们可以根据主键使用INSERT 将表 B 的数据更新到表 A，但是和 NoSQL 型数据库的 UPSERT 的性能还是有些差距，因此在使用 ClickHouse 时单表查询时的性能非常强悍，单表更新的效率也很快，而多表关联查询或者更新时，如果对速度有要求的情况下是不太适合的。
Yore Yuen
关注
关注
点赞
22
收藏
打赏
评论
ClickHouse 数据的更新（Mutation）
本文着重讲解了ClickHouse关于UPDATE数据时的一些问题，由一个需求引出，通过TPC-DS的数据集演示这种场景，接着通过 UPDATE、INSERT、VIEW方式尝试实现，期间会和MySQL对比，最终通过 INSERT实现这个需求。最后对比了和MySQL实现上的区别，并做了一个小节。
复制链接
扫一扫
专栏目录
JDBC批量插入 更新 删除等操作
03-23
一、JDBC的批量插入
一、JDBC的批量更新
一、JDBC的批量删除
一、JDBC的批量修改
clickhouse批量写入数据
robinhunan的博客
10-24
736
clickhouse 批量插入数据的示例
评论 1
您还未登录，请先
登录
后发表或查看评论
Clickhouse UPDATE 和 DELETE操作
热门推荐
vkingnew 的技术博客
06-23
4万+
历史：
概述：
Clickhouse提供了delete和update操作，这类操作被称之为Mutation查询，是ALTER语句的变种。虽然Mutation能最终实现修改和删除，但是不能完全以通常意义上的update和delete操作来理解。
1.Mutation操作适用于批量数据的修改和删除
2.不支持事务 一旦语句被提交执行就会立刻对现有的数据产生影响，无法回滚。
3.Mutation操作执行是一个异步的过程，语句提交会立即返回，但是不代表具体逻辑已经执行完毕，具体的执行记录需要在sys.
clickhouse官方文档_记一次 ClickHouse 数据迁移
weixin_39805087的博客
11-28
848
背景大约在 2018 年 8 月份开始正式接触 ClickHouse，当时机房没有合适的服务器，就在 Azure 开了一台虚拟机来部署。平稳运行了两年，支撑了 YiDrone 和 YiSonar 两个重要的产品的底层数据存储和查询。前段时间采购服务器的时候预留了一些资源，加上 Azure 的免费订阅即将到期，于是准备把 ClickHouse 迁回到机房。数据量不大，只有一个节点，硬盘上的数据加起来...
基于ClickHouse的近实时数据更新方案
最新发布
TomAndersen的博客
11-07
1203
众所周知，主攻OLAP场景的数据库引擎一般都会采用某种列式存储格式，以支撑其强大的数据处理性能，当无法同时兼顾行级事务，以及频繁的数据实时更新操作。如ROLAP中的Hive、Impala、Presto、ClickHouse，以及MOLAP中的Druid、Kylin，等等。
ClickHouse
weixin_41924879的博客
11-19
761
ClickHouse
ClickHouse是俄罗斯第一大搜索引擎Yandex开发的列式储存数据库，Yandex在2016年6月15日开源了ClickHouse。
ClickHouse是一个用于联机分析处理（OLAP）的列式数据库管理系统（columnar DBMS）。
传统数据库在数据大小比较小，索引大小适合内存，数据缓存命中率足够高的情形下能正常提供服务。但残酷的是，这种理想情形最终会随着业务的增长走到尽头，查询会变得越来越慢。你可能通过增加更多的内存，订购更快的磁盘等等来解决问题（纵向扩展），但这只是拖
解决Clickhouse数据重复问题
东城庞太师
06-03
7089
目录
问题描述：
原因：
解决：
问题描述：
使用MergeTree引擎建表，进行插入数据时，发现数据重复。
原因：
MergeTree的主键（PRIMARYKEY）只是用来生成一级索引（primary.idx）的，并没有唯一性约束这样的语义。
ReplacingMergeTree通过ORDERBY，表示判断唯一约束的条件。当分区合并之时，根据ORDERBY排序后，相邻重复的数据会被排除。
解决：
换用ReplacingMergeTree引擎建表
ReplacingM...
【ClickHouse】Clickhouse中update/delete的使用
技术能量站
05-20
6884
从使用场景来说，Clickhouse是个分析型数据库。这种场景下，数据一般是不变的，因此Clickhouse对update、delete的支持是比较弱的，实际上并不支持标准的update、delete操作。
下面介绍一下Clickhouse中update、delete的使用。
1. 更新和删除的语法
Clickhouse通过alter方式实现更新、删除，它把update、delete操作叫做mutation(突变)。语法为：
ALTER TABLE [db.]table DELETE WHERE
Clickhouse 数据删除更新
基咯咯
06-20
3万+
数据删除
按分区删除
ALTER TABLE db_name.table_name DROP PARTITION '20200601'
按条件删除
ALTER TABLE db_name.table_name DELETE WHERE day = '20200618'
数据更新
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>
举例：
:) select event_status_
Positioned Update not supported异常解决方法
lichuhui
11-15
2514
今天在SSH项目中遇到运用JSON插件，产生如下异常：
[code="java"]
2008-11-15 12:58:21 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet default threw exception
java.sql.SQLException: ...
ClickHouse增加删除更新操作
haveanybody的博客
05-22
1936
前面我们已经介绍过 ClickHouse 是列式存储数据库，并且是按照有序存储、且按照索引粒度建立稀疏索引，所以 ClickHouse 是不擅长做 update/delete 操作的，对于需要经常变化的数据，也不建议使用clickhouse。但是并不是说clickhouse就不能更新数据，clickhouse提供了一种基于alter语句的“突变”（mutations）操作来实现更新/删除操作。在使用mutations操作之前需要注意：
mutations操作需要重置分区，是一种“很重”的操作，更适用于操作
Clickhouse如何实现数据更新
suchy
01-28
4784
【摘要】 Clickhouse作为一个OLAP数据库，它对事务的支持非常有限。本文主要介绍通过ReplacingMergeTree来实现Clickhouse数据的更新、删除。
Clickhouse作为一个OLAP数据库，它对事务的支持非常有限。Clickhouse提供了MUTATION操作（通过ALTER TABLE语句）来实现数据的更新、删除，但这是一种“较重”的操作，它与标准SQL语法中的UPDATE、DELETE不同，是异步执行的，对于批量数据不频繁的更新或删除比较有用，可参考https://alt
Amazon Kinesis：实时收集、处理和分析视频和数据流。
徐子云的博客
10-13
923
KinesisKinesis Data Streams1.Kinesis Data FirehoseKinesis Data Analytics适用于 Microsoft Windows 的 Amazon Kinesis Agent
Kinesis Data Streams
1.
Kinesis Data Firehose
Kinesis Data Analytics
适用于 Microsoft Windows 的 Amazon Kinesis Agent
Clickhouse数据库引擎Mysql
chenzuoli的博客
03-12
1580
今天介绍一个数据库引擎mysql，它能够做到访问和操作mysql的数据。
ClickHouse Join篇
Yuhang.Pei 's blog
05-06
3805
join：
1.Alter table mt drop partition '' 可删除本地分区 集群表的分区需要依次删本地表
alter table dwd_dms_msg_detial_di drop partition ('201912','20191226')
2.join 减少列，临时表不能起别名，只能更改字段名 避免冲突模糊不清
3.普通join数据发到远程服务器形成右表 gl...
clickhouse
weixin_45596022的博客
03-31
1万+
一，clickhouse特点
1.列式存储
行存储
好处是想查某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的。
id
姓名
年龄
张三
18
李四
19
王五
20
列存储
列存储的好处
1 对于列的聚合，计数，求和等统计操作要优于行式存储。
2 由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
ReplacingMergeTree：实现Clickhouse数据更新
华为云官方博客
11-04
1097
摘要：Clickhouse作为一个OLAP数据库，它对事务的支持非常有限。本文主要介绍通过ReplacingMergeTree来实现Clickhouse数据的更新、删除。
Clickhouse实战--数据更新和删除概述
zg_hover的专栏
05-03
3955
本文对Clickhouse的数据更新操作进行了一个概述，对更新数据的几种方式进行了说明。这几种方式的详细步骤介绍，会在新的文章中进行说明。
[ClickHouse] 将 mutation 操作由异步非阻塞改为阻塞
weixin_42902669的博客
03-06
1464
ClickHouse 中的 mutation 操作(update/delete) 默认是异步执行的, 这会导致一种情况的出现: 删除的数据在一段时间内还能查询到. 在非事务性的使用场景中这个设置可以加快处理速度, 并且不会影响后来数据的添加, 但在要求事务性的使用场景中(比如新增数据依赖历史数据), 这个设置会导致后加的数据出现错误.
为了解决这个问题, 尝试了下面两种方法
通过查询 system.mutations 表的 is_done 字段来判断一个操作是否已完成, 在 is_done = 1 之前人
关于ClickHouse的update和delete
onway_goahead的博客
02-06
5021
删除数据
ALTER TABLE [db.]table DELETE WHERE filter_expr
更新数据
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr
注意：
1. 这两条命令必须在版本号大于1.1.54388才可以使用，适用于 mergeTree...
clickhouse停止和避免mutations操作
邢为栋
10-20
461
在使用clickhouse时，使用了alter查询的delete和update操作进行数据的删除和更新，当大量使用这类操作时，造成clickhouse服务无法连接，观察日志发现，出现了大量mutation的操作，严重占用clickhouse资源。经过研究，发现ALTER DELETE和UPDATE表数据的实现机制是mutations。对于MergeTree表，mutations机制的操作会重写整个数据块（rewriting whole data parts），所以是一个重操作，会严重占用服务资源。
“相关推荐”对你有帮助么？
非常没帮助
没帮助
一般
有帮助
非常有帮助
提交
©️2022 CSDN
皮肤主题：点我我会动
设计师：我叫白小胖
返回首页
Yore Yuen
CSDN认证博客专家
CSDN认证企业博客
码龄5年
暂无认证
54
原创
9798
周排名
14万+
总排名
46万+
访问
等级
3205
积分
5778
粉丝
327
获赞
338
评论
1070
收藏
私信
关注
热门文章
Spring cloud多模块开发下Feign的使用，以及@FeignClient注入bean找不到异常解决
131486
打扮一下咱们的开发工具--更换IDEA主题
76611
工作流任务调度系统：Apache DolphinScheduler
66373
ClickHouse 数据的更新（Mutation）
16567
CDH 6.2.0 或 6.3.0 安装实战及官方文档资料链接
15085
分类专栏
系统
1篇
Python
1篇
数据库
21篇
工具
20篇
大数据
56篇
java
5篇
Spring
3篇
Hive
4篇
最新评论
Spring cloud多模块开发下Feign的使用，以及@FeignClient注入bean找不到异常解决
Mckay88:
然后确定这两个服务引用的Spring Cloud和Spring Boot版本是否一致，有时因为不一致，在 第一个服务中注解可能引用的是org.springframework.cloud.netflix.feign.FeignClient这个包下的，另一个服务中引用的是org.springframework.cloud.openfeign.FeignClient包下的，这时也会包这个错误
...........
不升级的话可不可以解决这个问题呢?? 有没有其他的办法????
Centos7环境下离线安装mysql 5.7 / mysql 8.0
FrankL666:
这篇文章真的优秀，别的方法可能会各种问题，但这篇文章让我安装好了mysql8.0.31
HDP 之 Timeline Service 2.0
谁动了我的bug~:
大佬，我timeline切换了hbase，报kerberos权限问题，怎么整啊
HDP 之 Timeline Service 2.0
谁动了我的bug~:
大佬有没有研究ambari metrics不使用内置hbase的需求
Spring cloud多模块开发下Feign的使用，以及@FeignClient注入bean找不到异常解决
休霸哥:
解决了我的问题
您愿意向朋友推荐“博客详情页”吗？
强烈不推荐
不推荐
一般般
推荐
强烈推荐
提交
最新文章
Apache Kudu 从源码开始构建并部署 Kudu 集群
HBase集群升级, 通过HBase rpm 或 tar包形式(以HDP 为例，包含 升级及HBCK2 介绍和使用)
手动安装Hive 3(以 hdp 为例，适用于 CentOS 8)
2022年2篇
2021年5篇
2020年14篇
2019年37篇
2018年9篇
2017年1篇
目录
目录
分类专栏
系统
1篇
Python
1篇
数据库
21篇
工具
20篇
大数据
56篇
java
5篇
Spring
3篇
Hive
4篇
目录
评论 1
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
打赏作者
Yore Yuen
你的支持认可是我创作的最大动力
¥2
¥4
¥6
¥10
¥20
输入1-500的整数
余额支付
(余额：-- )
扫码支付
扫码支付：¥2
获取中
扫码支付
您的余额不足，请更换扫码支付或充值
打赏作者
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额
抵扣说明：
1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。 2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。
余额充值