关于 ClickHouse 更新数据的一次尝试
程序员文章站
2022-07-13 08:54:35
...
1 需求
假如现在想将表B的数据在满足一定条件时将其某个值更新到表A，如果是MySQL，实现该业务的语法可能如下：
UPDATE A,B set A.field1=B.field1 where filter_expr;
2 一个数据集
这里主要使用的是 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
[[email protected] tools]# head -n 3 /opt/tmp/data/inventory_1_2.dat
2450815,1,1,211
2450815,2,1,235
2450815,4,1,859
# 文件大小
[[email protected] tools]# du -hd1 /opt/tmp/data/inventory_1_2.dat
1.3G /opt/tmp/data/inventory_1_2.dat
# 数据条数
[[email protected] tools]# wc -l /opt/tmp/data/inventory_1_2.dat
66555000 /opt/tmp/data/inventory_1_2.dat
3 表
3.1 登录 client
clickhouse-client -h 127.0.0.1 --port 19000 -u default --password KavrqeN1 --multiline
3.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.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.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.
4 不同点
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);
5 小节
ClickHouse 支持 UPDATE 、 INSERT 也支持 VIEW，但是和传统关系型数据库的语法有很大的不同，在该需求下我们既不能使用 UPDATE，又不能使用 VIEW，尽管我们可以根据主键使用INSERT 将表 B 的数据更新到表 A，但是和 NoSQL 型数据库的 UPSERT 的性能还是有些差距，因此在使用 ClickHouse 时单表查询时的性能非常强悍，单表更新的效率也很快，而多表关联查询或者更新时，如果对速度有要求的情况下是不太适合的。