ClickHouse 数据的更新（Mutation）

￡神魔★判官ぃ 2023-06-15 07:23 42阅读 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 的表结构如下：







































































字段名 类型 说明
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 导致最近部分突变失败的异常消息

system.mutations表结构

所有 mutation 操作都会记录在上面这个表中，其中有 create_time ，后续的执行也是按照这个顺序执行，在提交 mutation 之前插入表中的数据将被改动，之后插入的数据将不会被改动，mutation 不会以任何方式阻止期间或后续的 insert 操作。

2 Update 的一次问题排查（UPDATE 成功但数据却没有修改）

在一次 Update ，执行类似的 mutation 语句如下：

  1. ALTER TABLE 表名1
  2. UPDATE `l52677bf3cf0d43baa13db78fc2bdacfa_partyid`=pxxxxid 
  3. WHERE  pxxxxid in (
  4. SELECT pxxxx_id FROM 表名2 WHERE 条件
  5. );

可以发现执行没有报错，成功执行了，但是通过下面的类似语句查询，发下 l52677bf3cf0d43baa13db78fc2bdacfa_partyid 字段的值并没有修改为 pxxxxid 字段的值，既然是异步执行那就稍等下之后再查看结果，于是你等了半个月，再次查询发现数据还是没有修改。

  1. SELECT `l52677bf3cf0d43baa13db78fc2bdacfa_partyid`,pxxxxid 
  2. FROM 表名1 WHERE  pxxxxid in (
  3. SELECT pxxxx_id FROM 表名2 WHERE 条件 LIMIT 10
  4. );

于是查看 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 文件中。

  1. 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` = '$****$';
  2. 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
  3. 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 字段的值，其中给出了很重要的执行错误的原因。

  1. -- SELECT *
  2. SELECT mutation_id,database,table,is_done,create_time,command
  3. FROM system.mutations
  4. where database='kudu' AND `table`='表名1'
  5. order by create_time

可能会看到如下的错误原因，这次终于大概知道了原因，就是在大量提交 mutation 操作过程中，ClickHouse 异步执行这些 mutation 时，表中的 'le6452913d83f47388dc27ac16141aaca_partyid' 字段却被删除了，导致这个任务执行失败，而之后提交的对这个表的所有 mutation 操作都排在其后执行而无法正常执行（似乎这是 目前 ClickHouse 的 mutation 的一个 Bug）。

  1. 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 参数。

  1. # --mutations_sync 具体可访问下面链接
  2. # https://clickhouse.tech/docs/en/operations/settings/settings/#mutations_sync
  3.  clickhouse-client -h hostname --port 9000 -u 用户 --password 密码 --multiline --mutations_sync=2

第二种方式【推荐】，通过 system.mutations 表排查出错的 mutation 操作，如果这些操作不能删除，则需要备份这些操作，之后表的 mutation 修复之后再根据时间依次执行，如果可以删除，则直接使用下面的命令删除执行失败的 mutation：

  1. -- 详见 https://clickhouse.tech/docs/en/sql-reference/statements/kill/#kill-mutation
  2. -- 想删除单个，可以定位到库和表的 mutation_id 来删除。
  3. -- 删除多个，可以使用 mutation_id IN('') 
  4. -- 删除所有非执行成功的，可以通过 is_done='0'
  5. KILL MUTATION WHERE database = 'kudu' AND table = 'CUS_INFO' 
  6. -- AND mutation_id = 'mutation_126.txt'
  7. -- AND mutation_id IN('mutation_125.txt','mutation_124.txt','mutation_123.txt')
  8. AND is_done='0'

删除旧的 mutation 失败任务后，这个表的 Mutation 操作就可以正常执行，再次提交对表的 Update 操作，可以看到数据已经发生了修改。

3 需求小例子

假如现在想将表B的数据在满足一定条件时将其某个值更新到表A，如果是MySQL，实现该业务的语法可能如下：

  1. UPDATE A,B set A.field1=B.field1 where filter_expr;

3.1 一个数据集

这里主要使用的是 TPC-DS的一个数据集。更多TPC的使用可以查看我GitHue上写的一份文档TPC.md。

  1. wget http://www.tpc.org/tpc_documents_current_versions/temporary_download_files/42d6f585-7c65-469c-b8de-9bfe47b63d81-tpc-ds-tool.zip
  2. mv 42d6f585-7c65-469c-b8de-9bfe47b63d81-tpc-ds-tool.zip TPC-2.11.0.zip
  3. unzip TPC-2.11.0.zip
  4. cd v2.11.0rc2/
  5. cd tools/
  6. # 编译
  7. make
  9. # 生成一份10G的数据集
  10. ./dsdgen -DELIMITER ',' -scale 10 -parallel 2 -TERMINATE N -dir /opt/tmp/data
  12. # 查看 inventory_1_2.dat 
  13. [root@cdh3 tools]# head -n 3 /opt/tmp/data/inventory_1_2.dat
  14. 2450815,1,1,211
  15. 2450815,2,1,235
  16. 2450815,4,1,859
  18. # 文件大小
  19. [root@cdh3 tools]# du -hd1 /opt/tmp/data/inventory_1_2.dat
  20. 1.3G    /opt/tmp/data/inventory_1_2.dat
  22. # 数据条数
  23. [root@cdh3 tools]# wc -l /opt/tmp/data/inventory_1_2.dat
  24. 66555000 /opt/tmp/data/inventory_1_2.dat

3.2 表

3.2.1 登录 client

  1. clickhouse-client -h 127.0.0.1 --port 19000 -u default --password KavrqeN1   --multiline

3.2.2 建表

参考v2.11.0rc2/tools/tpcds.sql脚本的建表语句创建 ClickHouse 表

  1. -- 创建A表
  2. CREATE TABLE inventory(
  3. inv_date_sk               UInt64 ,
  4. inv_item_sk               UInt64 ,
  5. inv_warehouse_sk          UInt64 ,
  6. inv_quantity_on_hand      UInt64 
  7. )ENGINE = MergeTree ORDER BY (inv_date_sk, inv_item_sk, inv_warehouse_sk);
  9. -- 创建B表，
  10. CREATE TABLE inventory2(
  11. inv_date_sk               UInt64 ,
  12. inv_item_sk               UInt64 ,
  13. inv_warehouse_sk          UInt64 ,
  14. inv_quantity_on_hand      UInt64 
  15. )ENGINE = MergeTree ORDER BY (inv_date_sk, inv_item_sk, inv_warehouse_sk);

3.2.3 导入数据

  1. # 导入数据到 cdh2 节点的 clickhouse
  2. 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. -- 1 inventory2 中插入一部分数据
  2. 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);
  3. INSERT INTO inventory2 SELECT
  4.     inv_date_sk,
  5.     inv_item_sk,
  6.     inv_warehouse_sk,
  7.     rand()
  8. FROM inventory
  9. WHERE inv_warehouse_sk IN (1, 2, 3, 4, 5)
  10. → 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.
  11. 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.)
  13. -- 2 数据总数
  14. -- 2.1 inventory
  15. cdh2 :) SELECT COUNT(1) FROM inventory;
  16. ┌─COUNT(1)─┐
  17. │ 66555000 │
  18. └──────────┘
  19. ↓ 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%
  20. 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.)
  21. -- 2.2 inventory2
  22. cdh2 :) SELECT COUNT(1) FROM inventory2;
  23. ┌─COUNT(1)─┐
  24. │ 33405000 │
  25. └──────────┘
  26. ↓ 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%
  27. 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.)
  30. -- 3 统计字段信息。可以看到总共有 10个仓库，68000 类商品
  31. cdh2 :) SELECT COUNT(DISTINCT inv_date_sk),COUNT(DISTINCT inv_item_sk),COUNT(DISTINCT inv_warehouse_sk) FROM inventory;
  32. ┌─uniqExact(inv_date_sk)─┬─uniqExact(inv_item_sk)─┬─uniqExact(inv_warehouse_sk)─┐
  33. │                    131 │                  68000 │                          10 │
  34. └────────────────────────┴────────────────────────┴─────────────────────────────┘
  35. ↖ 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%
  36. 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.)
  39. -- 4 查看每个仓库（inv_warehouse_sk） 的数据库中条数。可以看到(4,3,2,5,1)共33405000，(6,7,9,8,10)共33150000，导入数据总数据条数 66555000
  40. cdh2 :) SELECT inv_warehouse_sk,COUNT(inv_warehouse_sk) FROM inventory GROUP BY inv_warehouse_sk;
  41. ┌─inv_warehouse_sk─┬─COUNT(inv_warehouse_sk)─┐
  42. │                4 │                 6681000 │
  43. │                3 │                 6681000 │
  44. │                2 │                 6681000 │
  45. │                5 │                 6681000 │
  46. │                1 │                 6681000 │
  47. │                6 │                 6630000 │
  48. │                7 │                 6630000 │
  49. │                9 │                 6630000 │
  50. │                8 │                 6630000 │
  51. │               10 │                 6630000 │
  52. └──────────────────┴─────────────────────────┘
  53. ↙ 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%
  54. 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.)
  56. -- 5 查看各个库存量
  57. cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory GROUP BY inv_warehouse_sk;
  58. ┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
  59. │                4 │                3172760518 │
  60. │                3 │                3173305680 │
  61. │                2 │                3173041915 │
  62. │                5 │                3173462792 │
  63. │                1 │                3172739142 │
  64. │                6 │                3148272312 │
  65. │                7 │                3148312176 │
  66. │                9 │                3150290280 │
  67. │                8 │                3149344378 │
  68. │               10 │                3148388511 │
  69. └──────────────────┴───────────────────────────┘
  70. ↖ 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%
  71. 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.)
  74. -- 6 修改仓库为 (4,3,2,5,1)共33405000条的库存量，库存设置为 0。
  75. cdh2 :) ALTER TABLE inventory UPDATE inv_quantity_on_hand = 0 where inv_warehouse_sk in (4,3,2,5,1);
  76. ALTER TABLE inventory
  77.     UPDATE inv_quantity_on_hand = 0 WHERE inv_warehouse_sk IN (4, 3, 2, 5, 1)
  78. Ok.
  79. 0 rows in set. Elapsed: 0.004 sec.
  81. -- 7 查看当前各个库存量。
  82. -- 7.1 inventory。发现仓库(4,3,2,5,1)已经全部清库。
  83. cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory GROUP BY inv_warehouse_sk;
  84. ┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
  85. │                4 │                         0 │
  86. │                3 │                         0 │
  87. │                2 │                         0 │
  88. │                5 │                         0 │
  89. │                1 │                         0 │
  90. │                6 │                3148272312 │
  91. │                7 │                3148312176 │
  92. │                9 │                3150290280 │
  93. │                8 │                3149344378 │
  94. │               10 │                3148388511 │
  95. └──────────────────┴───────────────────────────┘
  96. ↘ 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%
  97. 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.)
  98. -- 7.2 inventory2
  99. cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory2 GROUP BY inv_warehouse_sk;
  100. ┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
  101. │                4 │         14347686397994975 │
  102. │                3 │         14343877924786742 │
  103. │                2 │         14345396281859373 │
  104. │                5 │         14345781573562921 │
  105. │                1 │         14348098422679985 │
  106. └──────────────────┴───────────────────────────┘
  107. ↑ 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%
  108. 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.)
  111. -- 8 将 inventory2 更新到 inventory 表，虽然这次搞的有点大
  112. -- MySQL支持：update inventory A,inventory2 B set A.inv_quantity_on_hand=B.inv_quantity_on_hand where A.id=B.id;
  113. -- 但是ClickHouse不支持更细的字段来自于两个表，但可以使用 INSERT 语句。MySQL使用Insert语句时不能向已存在的主键列插入值。
  114. cdh2 :) INSERT INTO inventory SELECT inv_date_sk, inv_item_sk, inv_warehouse_sk,inv_quantity_on_hand FROM  inventory2
  115. :-]  WHERE inventory2.inv_warehouse_sk in (1,2,3,4,5);
  116. ↑ 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.
  117. 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.)
  118. -- 再次查询。发现 inventory2 中的库存信息已经更新到 inventory 表
  119. cdh2 :) SELECT inv_warehouse_sk,SUM(inv_quantity_on_hand) FROM inventory GROUP BY inv_warehouse_sk;
  120. ┌─inv_warehouse_sk─┬─SUM(inv_quantity_on_hand)─┐
  121. │                4 │         14347686397994975 │
  122. │                3 │         14343877924786742 │
  123. │                2 │         14345396281859373 │
  124. │                5 │         14345781573562921 │
  125. │                1 │         14348098422679985 │
  126. │                6 │                3148272312 │
  127. │                7 │                3148312176 │
  128. │                9 │                3150290280 │
  129. │                8 │                3149344378 │
  130. │               10 │                3148388511 │
  131. └──────────────────┴───────────────────────────┘
  132. ↗ 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%
  133. 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.)
  136. -- 9 视图版
  137. -- 9.1 创建视图。注意子句的 JOIN 不能使用别名（AS）
  138. cdh2 :) CREATE VIEW inventory_view AS SELECT
  139. :-] inventory.inv_date_sk,inventory.inv_item_sk,inventory.inv_warehouse_sk,inventory.inv_quantity_on_hand a,inventory2.inv_quantity_on_hand b
  140. :-] FROM inventory LEFT join inventory2
  141. :-] ON inventory.inv_date_sk = inventory2.inv_date_sk AND
  142. :-]  inventory.inv_item_sk = inventory2.inv_item_sk AND
  143. :-]  inventory.inv_warehouse_sk = inventory2.inv_warehouse_sk
  144. :-] WHERE inventory.inv_warehouse_sk in (1,2,3,4,5)
  145. :-] ;
  146. CREATE VIEW inventory_view AS
  147. SELECT
  148.     inventory.inv_date_sk,
  149.     inventory.inv_item_sk,
  150.     inventory.inv_warehouse_sk,
  151.     inventory.inv_quantity_on_hand AS a,
  152.     inventory2.inv_quantity_on_hand AS b
  153. FROM inventory
  154. 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)
  155. WHERE inventory.inv_warehouse_sk IN (1, 2, 3, 4, 5)
  156. Ok.
  157. 0 rows in set. Elapsed: 0.006 sec.
  159. -- 9.2 查看表和视图
  160. cdh2 :) SHOW TABLES;
  161. SHOW TABLES
  162. ↖ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) ┌─name───────────┐
  163. │ inventory      │
  164. │ inventory2     │
  165. │ inventory_view │
  166. │ ontime_local   │
  167. └────────────────┘
  168. ↑ 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.)
  169. 4 rows in set. Elapsed: 0.002 sec.
  171. -- 9.3 查看视图数据
  172. cdh2 :) SELECT * FROM inventory_view LIMIT 10;
  173. SELECT *
  174. FROM inventory_view
  175. LIMIT 10
  176. → 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%
  177. ┌─inv_date_sk─┬─inv_item_sk─┬─inv_warehouse_sk─┬─a─┬──────────b─┐
  178. │     2451221 │           1 │                1 │ 0 │ 3736098505 │
  179. │     2451221 │           1 │                2 │ 0 │ 2885779993 │
  180. │     2451221 │           1 │                3 │ 0 │  479103458 │
  181. │     2451221 │           1 │                4 │ 0 │ 1752919932 │
  182. │     2451221 │           1 │                5 │ 0 │ 3798676092 │
  183. │     2451221 │           2 │                1 │ 0 │ 1596118095 │
  184. │     2451221 │           2 │                2 │ 0 │ 3044174515 │
  185. │     2451221 │           2 │                3 │ 0 │ 1792720993 │
  186. │     2451221 │           2 │                4 │ 0 │  888870309 │
  187. │     2451221 │           2 │                5 │ 0 │ 1904802464 │
  188. └─────────────┴─────────────┴──────────────────┴───┴────────────┘
  189. → Progress: 33.41 million rows, 1.07 GB (2.38 million rows/s., 76.18 MB/s.) ██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████▊ 98%
  190. 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.)
  192. -- 9.3 UPDATE 数据。VIEW 不支持 Mutations
  193. cdh2 :) ALTER TABLE inventory_view UPDATE a=b
  194. :-] WHERE inv_warehouse_sk in (1,2,3,4,5);
  195. ALTER TABLE inventory_view
  196.     UPDATE a = b WHERE inv_warehouse_sk IN (1, 2, 3, 4, 5)
  197. Received exception from server (version 19.16.3):
  198. Code: 48. DB::Exception: Received from 127.0.0.1:19000. DB::Exception: Mutations are not supported by storage View.
  199. 0 rows in set. Elapsed: 0.004 sec.

3.3 不同点

MySQL更新数据支持如下语法：

  1. -- 可以将 B 表的某字段值 更新到 A表某字段
  2. mysql> UPDATE inventory A,inventory2 B SET A.inv_quantity_on_hand=B.inv_quantity_on_hand
  3.     -> where A.inv_warehouse_sk in (1,2,3,4,5) AND
  4.     -> A.inv_date_sk = B.inv_date_sk AND
  5.     -> A.inv_item_sk = B.inv_item_sk AND
  6.     -> A.inv_warehouse_sk = B.inv_warehouse_sk ;
  7. Query OK, 6 rows affected (0.00 sec)
  8. Rows matched: 6  Changed: 6  Warnings: 0

MySQL不支持使用 INSERT 语句插入一条主键已存在的数据，但是 ClickHouse支持使用 INSERT 插入数据，如果主键已存在就是覆盖那条数据。

ClickHouse的 UPDATE语法如下，从语法上可以看到 TABLE后面只能是一个表名，可以更新一个字段值(根据过滤条件可能更新的是一行，也可能是多行)，也可以更新多个字段值，但不能是主键。

  1. ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

ClickHouse 的创建 视图（VIEW）的语法如下：

  1. CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

MySQL中通过视图更新数据的SQL如下

  1. -- 创建视图
  2. 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
  3.     -> FROM inventory A LEFT join inventory2 B
  4.     -> ON A.inv_date_sk = B.inv_date_sk AND
  5.     -> A.inv_item_sk = B.inv_item_sk AND
  6.     -> A.inv_warehouse_sk = B.inv_warehouse_sk
  7.     -> --WHERE A.inv_warehouse_sk in (1,2,3,4,5)
  8.     -> ;
  9. Query OK, 0 rows affected (0.01 sec)
  11. -- 更新数据
  12. 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 时单表查询时的性能非常强悍，单表更新的效率也很快，而多表关联查询或者更新时，如果对速度有要求的情况下是不太适合的。

发表评论

表情：
评论列表 （有 0 条评论，42人围观）

还没有评论，来说两句吧...

相关阅读