探讨ClickHouse在处理UPDATE操作及多表关联更新时的局限性，对比MySQL的高效处理方式，强调ClickHouse适用于单表查询与更新，但在多表操作时性能受限。
目录
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 的表结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| database | String | 应用 mutation 的数据库名 |
| table | String | 应用 mutation 的表名mutation_idStringmutation的 ID，对于 replicated表，ID 也对应于 ZK 中的 <table_path_in_zookeeper>/mutations 目录下的 znode 名，对于非 replicated 表 ID对应于表数据目录中的文件名。 |
| command | String | mutation 命令的字符串，ALTER TABLE [db.]table 之后的部分 |
| create_time | DateTime | 提交 mutation 命令的时间 |
| block_numbers.partition_id | Array(String) | 对于 replicated 表，数组包含分区 ID（每个分区一个记录），对于非 replicated 表的 mutation ，数组为空。 |
| block_numbers.number | Array(Int64) | 对于 replicated 表，数组包含每一个分区的一条记录以及mutation 获得的 block 数字，只有包含数字小于此数字的 block 部分才会在分区中发生 mutation。在非 replicated 表中，所有分区中的 block 数字形成一个序列，这意味着对于非 replicated表的 mutation，该列将包含一条记录，该记录具有通过 mutation 获得的单个 block 数字。 |
| parts_to_do_names | Array(String) | 完成 mutation 而需要 mutation 的数据部分的array 的名称 |
| parts_to_do | Int64 | 需要 mutation 才能完成 mutation 的数据部分的数量 |
| is_done | UInt8 | mutation 是否完成的标志。 1 表示完成 0 表示正在进行中 【说明】即使 parts_to_do=0，由于长时间运行 insert 查询，replicated 表的 mutation 可能尚未完成，这将创建需要 mutation 的新数据部分。 |
| latest_failed_part | String | 无法改变的最新部分的名称 |
| latest_fail_time | DateTime | 最近一次 mutation 失败部分的日期和时间 |
| latest_fail_reason | String | 导致最近部分突变失败的异常消息 |
所有 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 时单表查询时的性能非常强悍，单表更新的效率也很快，而多表关联查询或者更新时，如果对速度有要求的情况下是不太适合的。
扫一扫
1950
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
