ClickHouse 数据的更新（Mutation）

￡神魔★判官ぃ 2023-06-15 07:23 17阅读 0赞

### 目录 ###

*  1 关于 Mutation
 *  2 Update 的一次问题排查（UPDATE 成功但数据却没有修改）
 *  3 需求小例子
 *   *  3.1 一个数据集
     *  3.2 表
     *   *  3.2.1 登录 client
         *  3.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` 的表结构如下：

<table> 
 <thead> 
  <tr> 
   <th align="left">字段名</th> 
   <th>类型</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">database</td> 
   <td>String</td> 
   <td align="left">应用 mutation 的数据库名</td> 
  </tr> 
  <tr> 
   <td align="left">table</td> 
   <td>String</td> 
   <td align="left">应用 mutation 的表名mutation_idStringmutation的 ID，对于 replicated表，ID 也对应于 ZK 中的 <code>&lt;table_path_in_zookeeper&gt;/mutations</code> 目录下的 znode 名，对于非 replicated 表 ID对应于表数据目录中的文件名。</td> 
  </tr> 
  <tr> 
   <td align="left">command</td> 
   <td>String</td> 
   <td align="left">mutation 命令的字符串，<code>ALTER TABLE [db.]table</code> 之后的部分</td> 
  </tr> 
  <tr> 
   <td align="left">create_time</td> 
   <td>DateTime</td> 
   <td align="left">提交 mutation 命令的时间</td> 
  </tr> 
  <tr> 
   <td align="left">block_numbers.partition_id</td> 
   <td>Array(String)</td> 
   <td align="left">对于 replicated 表，数组包含分区 ID（每个分区一个记录），对于非 replicated 表的 mutation ，数组为空。</td> 
  </tr> 
  <tr> 
   <td align="left">block_numbers.number</td> 
   <td>Array(Int64)</td> 
   <td align="left">对于 replicated 表，数组包含每一个分区的一条记录以及mutation 获得的 block 数字，只有包含数字小于此数字的 block 部分才会在分区中发生 mutation。在非 replicated 表中，所有分区中的 block 数字形成一个序列，这意味着对于非 replicated表的 mutation，该列将包含一条记录，该记录具有通过 mutation 获得的单个 block 数字。</td> 
  </tr> 
  <tr> 
   <td align="left">parts_to_do_names</td> 
   <td>Array(String)</td> 
   <td align="left">完成 mutation 而需要 mutation 的数据部分的array 的名称</td> 
  </tr> 
  <tr> 
   <td align="left">parts_to_do</td> 
   <td>Int64</td> 
   <td align="left">需要 mutation 才能完成 mutation 的数据部分的数量</td> 
  </tr> 
  <tr> 
   <td align="left">is_done</td> 
   <td>UInt8</td> 
   <td align="left">mutation 是否完成的标志。<br>1 表示完成<br>0 表示正在进行中<br>【说明】即使 parts_to_do=0，由于长时间运行 insert 查询，replicated 表的 mutation 可能尚未完成，这将创建需要 mutation 的新数据部分。</td> 
  </tr> 
  <tr> 
   <td align="left">latest_failed_part</td> 
   <td>String</td> 
   <td align="left">无法改变的最新部分的名称</td> 
  </tr> 
  <tr> 
   <td align="left">latest_fail_time</td> 
   <td>DateTime</td> 
   <td align="left">最近一次 mutation 失败部分的日期和时间</td> 
  </tr> 
  <tr> 
   <td align="left">latest_fail_reason</td> 
   <td>String</td> 
   <td align="left">导致最近部分突变失败的异常消息</td> 
  </tr> 
 </tbody> 
</table>

![system.mutations表结构][system.mutations]

所有 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 时单表查询时的性能非常强悍，单表更新的效率也很快，而多表关联查询或者更新时，如果对速度有要求的情况下是不太适合的。

[Mutation]: https://clickhouse.tech/docs/en/sql-reference/statements/alter/#mutations
[system.mutations]: https://img-blog.csdnimg.cn/2021060414383068.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2dpdGh1Yl8zOTU3NzI1Nw==,size_16,color_FFFFFF,t_70
[TPC-DS]: http://www.tpc.org/tpcds/default.asp
[TPC.md]: https://github.com/yoreyuan/My_hadoop/blob/master/doc/TPC.md