Clickhouse 中的replace语法

于 2025-01-23 15:37:35 发布
阅读量847 收藏 23
点赞数 8
文章标签： clickhouse java 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_79096508/article/details/145324365
版权

 1. 结果

1.1 版本23.6.1.1524

SQL Dynamic Column Selection with ClickHouse

REPLACE子句在SELECT语句中位于COLUMNS选择之后,但在WHERE子句之前。这意味着REPLACE操作会先于WHERE过滤执行。

此外,文档还指出:

"We can see that those two columns have both been replaced and the other columns are as they were in the previous query."

这进一步说明REPLACE操作是在整个结果集上执行的,而不是在WHERE过滤之后。

因此,我们可以得出结论,在ClickHouse中,查询结果集字段的动态替换(REPLACE)的优先级高于WHERE子句的过滤操作。REPLACE会先执行,然后才是WHERE过滤。

1.2 版本24.3

24.3版本：将where和replace的执行逻辑合并到投影表达式中，所以不会出现这种冲突现象

2. 不同版本测试过程

2.1 23.6版本

ck-test :) 
 CREATE TABLE default.students
(
    `id` UInt32,
    `name` String,
    `age` UInt8
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/students', '{replica}')
ORDER BY id
SETTINGS index_granularity = 8192
;

CREATE TABLE default.students
(
    `id` UInt32,
    `name` String,
    `age` UInt8
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/students', '{replica}')
ORDER BY id
SETTINGS index_granularity = 8192

Query id: 70fece67-c230-47c0-b638-0a1d878d328d

Ok.

0 rows in set. Elapsed: 0.104 sec. 

ck-test :) 

insert into students values(1,'Loe',18);

INSERT INTO students FORMAT Values

Query id: f534c2a2-c919-4ed0-854b-3f1351127589

Ok.

1 row in set. Elapsed: 0.021 sec. 

ck-test :) select * from students;

SELECT *
FROM students

Query id: d45396c8-d2d5-4cc8-bbeb-da3be6b64849

┌─id─┬─name─┬─age─┐
│  1 │ Loe  │  18 │
└────┴──────┴─────┘

1 row in set. Elapsed: 0.003 sec. 

ck-test :) 
SELECT * REPLACE ('LoL' AS name) FROM students where name = 'Leo';


SELECT * REPLACE 'LoL' AS name
FROM students
WHERE name = 'Leo'

Query id: 22ab5c71-8725-4a7c-b99f-85ef070fe723

Ok.

0 rows in set. Elapsed: 0.002 sec. 
ck-test :) explain plan SELECT * REPLACE ('LoL' AS name) FROM students where name = 'Leo';

EXPLAIN
SELECT * REPLACE 'LoL' AS name
FROM students
WHERE name = 'Leo'

Query id: 36fd8fd7-8a15-44bc-998b-8f531d176a4b

┌─explain─────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│   Filter (WHERE)                            │
│     ReadFromMergeTree (default.students)    │
└─────────────────────────────────────────────┘

3 rows in set. Elapsed: 0.002 sec. 

ck-test :) explain syntax SELECT * REPLACE ('LoL' AS name) FROM students where name = 'Leo';

EXPLAIN SYNTAX
SELECT * REPLACE 'LoL' AS name
FROM students
WHERE name = 'Leo'

Query id: 1e76e5f8-6053-4100-a423-4366a486bac7

┌─explain────────────┐
│ SELECT             │
│     id,            │
│     'LoL' AS name, │
│     age            │
│ FROM students      │
│ WHERE 0            │
└────────────────────┘

6 rows in set. Elapsed: 0.002 sec.

2.2 24.3版本

localhost :) SELECT * REPLACE ('LoL' AS name) FROM students where name = 'Leo';

SELECT * REPLACE 'LoL' AS name
FROM students
WHERE name = 'Leo'

Query id: 900bebff-2c32-4186-80cd-00828a74089f

   ┌─id─┬─name─┬─age─┐
1. │ 12 │ LoL  │  31 │
   └────┴──────┴─────┘

1 row in set. Elapsed: 0.004 sec. 
localhost :) explain plan SELECT * REPLACE ('LoL' AS name) FROM students where name = 'Leo';

EXPLAIN
SELECT * REPLACE 'LoL' AS name
FROM students
WHERE name = 'Leo'

Query id: c184e4e2-92a6-46a7-ad46-332b5df62bef

   ┌─explain───────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │   Expression                              │
3. │     ReadFromMergeTree (default.students)  │
   └───────────────────────────────────────────┘

3 rows in set. Elapsed: 0.002 sec. 

localhost :) explain syntax SELECT * REPLACE ('LoL' AS name) FROM students where name = 'Leo';

EXPLAIN SYNTAX
SELECT * REPLACE 'LoL' AS name
FROM students
WHERE name = 'Leo'

Query id: 1908bf5f-1c50-4f67-a39b-07360de52e87

   ┌─explain────────────┐
1. │ SELECT             │
2. │     id,            │
3. │     'LoL' AS name, │
4. │     age            │
5. │ FROM students      │
6. │ WHERE 0            │
   └────────────────────┘

6 rows in set. Elapsed: 0.003 sec.

2.3 核心不同

23.6
┌─explain─────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│   Filter (WHERE)                            │
│     ReadFromMergeTree (default.students)    │
└─────────────────────────────────────────────┘

24.3
   ┌─explain───────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │   Expression                              │
3. │     ReadFromMergeTree (default.students)  │
   └───────────────────────────────────────────┘

上面测试过程使用到了一些clickhouse的基础指令，下面给出相关使用介绍。

Prewhere

PREWHERE 只能在 *MergeTree 家族树里使用；

1. WHERE子句：

  • WHERE子句在查询中是最后执行的，它作用于从表中读取的所有数据。
  • WHERE子句可以包含任意条件，并且可以使用各种函数和操作符进行数据筛选。
  • WHERE子句可以使用索引来加速查询，优化性能。

2. PREWHERE子句：

  • PREWHERE子句在WHERE子句之前执行，它作用于从数据源读取的数据。
  • PREWHERE子句通常用于过滤数据源中不必要的行，以减少读取和处理的数据量，提升性能。
  • PREWHERE子句只能包含简单的条件，不能使用聚合函数、多个列的条件判断等复杂操作。
  • PREWHERE子句不能使用索引来加速查询。

EXPLAIN Types

  • AST — Abstract syntax tree.
  • SYNTAX — Query text after AST-level optimizations.
  • QUERY TREE — Query tree after Query Tree level optimizations.
  • PLAN — Query execution plan.
  • PIPELINE — Query execution pipeline.

可添加参数：

PLAN： 用于查看执行计划，可以指定五个参数；

header：打印计划中各个步骤的 header 说明，默认 0（关闭）；

description：打印计划中各个步骤的描述，默认 1（开启）；

indexes：显示索引使用情况，默认 0（关闭）；

actions：打印计划中各个步骤的详细信息，默认 0（关闭）；

json：以 JSON 格式打印执行计划的详细信息，默认 0（关闭）；

23.6版本/24.3版本

ck-test :) explain plan header=1,actions =1  select * replace ('LOL' as name) from students where name = 'Leo';

EXPLAIN header = 1, actions = 1
SELECT * REPLACE 'LOL' AS name
FROM students
WHERE name = 'Leo'

Query id: d9baf4a5-102e-4c28-b04f-c467fe44c11b

┌─explain──────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))      │
│ Header: id UInt32                                │
│         name String                              │
│         age UInt8                                │
│ Actions: INPUT :: 0 -> id UInt32 : 0             │
│          INPUT :: 1 -> age UInt8 : 1             │
│          COLUMN Const(String) -> name String : 2 │
│ Positions: 0 2 1                                 │
│   Filter (WHERE)                                 │
│   Header: id UInt32                              │
│           age UInt8                              │
│   Filter column: 0 (removed)                     │
│   Actions: INPUT :: 0 -> id UInt32 : 0           │
│            INPUT :: 1 -> age UInt8 : 1           │
│            COLUMN Const(UInt8) -> 0 UInt8 : 2    │
│   Positions: 0 1 2                               │
│     ReadFromMergeTree (default.students)         │
│     Header: id UInt32                            │
│             age UInt8                            │
│     ReadType: Default                            │
└──────────────────────────────────────────────────┘

20 rows in set. Elapsed: 0.002 sec. 

ck-test :) select * from students;

SELECT *
FROM students

Query id: d60b6fbf-fb18-4e9f-b909-862c0adfdce0

┌─id─┬─name─┬─age─┐
│  1 │ Leo  │  18 │
└────┴──────┴─────┘

1 row in set. Elapsed: 0.002 sec.

上面是旧版 的详细计划，我们来看看新版 24.3

localhost :) explain plan  header=1,actions =1  select * replace ('LOL' as name) from students where name = 'Leo';

EXPLAIN header = 1, actions = 1
SELECT * REPLACE 'LOL' AS name
FROM students
WHERE name = 'Leo'

Query id: 3e0e03cc-eb8c-4e5b-9a15-f1560be77bf4

    ┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                                                                              │
 2. │ Header: id UInt32                                                                                                      │
 3. │         name String                                                                                                    │
 4. │         age UInt8                                                                                                      │
 5. │ Actions: INPUT : 0 -> __table1.id UInt32 : 0                                                                           │
 6. │          INPUT : 1 -> __table1.age UInt8 : 1                                                                           │
 7. │          COLUMN Const(String) -> name String : 2                                                                       │
 8. │          ALIAS __table1.id :: 0 -> id UInt32 : 3                                                                       │
 9. │          ALIAS __table1.age :: 1 -> age UInt8 : 0                                                                      │
10. │ Positions: 3 2 0                                                                                                       │
11. │   Expression                                                                                                           │
12. │   Header: __table1.id UInt32                                                                                           │
13. │           __table1.age UInt8                                                                                           │
14. │   Actions: INPUT : 0 -> id UInt32 : 0                                                                                  │
15. │            INPUT : 1 -> age UInt8 : 1                                                                                  │
16. │            INPUT :: 2 -> name String : 2                                                                               │
17. │            ALIAS id :: 0 -> __table1.id UInt32 : 3                                                                     │
18. │            ALIAS age :: 1 -> __table1.age UInt8 : 0                                                                    │
19. │   Positions: 3 0                                                                                                       │
20. │     ReadFromMergeTree (default.students)                                                                               │
21. │     Header: name String                                                                                                │
22. │             id UInt32                                                                                                  │
23. │             age UInt8                                                                                                  │
24. │     ReadType: Default                                                                                                  │
25. │     Parts: 1                                                                                                           │
26. │     Granules: 1                                                                                                        │
27. │     Prewhere info                                                                                                      │
28. │     Need filter: 1                                                                                                     │
29. │       Prewhere filter                                                                                                  │
30. │       Prewhere filter column: equals(__table1.name, 'Leo'_String) (removed)                                            │
31. │       Actions: INPUT : 0 -> name String : 0                                                                            │
32. │                COLUMN Const(String) -> 'Leo'_String String : 1                                                         │
33. │                ALIAS name : 0 -> __table1.name String : 2                                                              │
34. │                FUNCTION equals(__table1.name :: 2, 'Leo'_String :: 1) -> equals(__table1.name, 'Leo'_String) UInt8 : 3 │
35. │       Positions: 0 3                                                                                                   │
    └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

35 rows in set. Elapsed: 0.002 sec.

明显发现 where 在24.3 的版本ReadFromMergeTree (default.students) （读取阶段）就已经执行了，就不会向23.6版本，一样造成歧义。

以上是作者在实际情况中发现的一点，在官方文档中并没有找到相关的修复说明，有人找到的话，望分享～～ 有不足之处，请指正

ClickHouse 字符串替换函数
Whoisbug的博客
01-21 1414
函数，它根据参数中列出的值（字符串、整数、浮点数等）格式化给定的字符串。不在大括号中的内容被视为普通文本，并原样复制到输出中。假设字符串包含有效的 UTF-8 编码文本。按照 Python 中类似的格式化方式，使用参数列表中的值（字符串、整数等）格式化。和后续格式说明符之间的内容都被视为普通文本，并原样复制到输出中。一般字符串操作函数和用于字符串搜索的函数，可参考：。同时请注意，字符串字面量需要额外的转义。中要被替换的片段的长度。中要被替换的片段的长度。：用于替换的字符串。：用于替换的字符串。
详解ClickHouse的ReplaceMergeTree
qq_38608642的博客
11-27 1849
数据去重发生在后台合并数据时，后台合并数据是随机的，所以有时会有一些没处理的数据，可以通过OPTIMIZI来手动合并，官方建议不要指望它，因为OPTIMIZE会读写大量的数据（可能是会从头再合并一的原因吧）所以，ReplacingMergeTre适用于后台去重数据来节省空间的场景，但不保证没有一个重复的（官方说的，不是我说的）ver设置后才能设置is_deleted，用来标记这行数据是否删除，1代表删除（deleted），0代表存在（state）选最大的那个，ver设置时，选择设置值中最大的那一行。
clickhouse插入代替更新
bluekrystal的博客
10-31 658
使用ORDER BY排序键，作为判断数据是否重复的唯一键。这个表以id_card作为判断是否重复的唯一键。ReplacingMergeTree通过ORDER BY，表示判断唯一约束的条件。当分区合并之时，根据ORDER BY排序后，相邻重复的数据会被排除。clickhouse-client -h ip -u 用户名 --port=端口 --password=密码 --query='select * from xxx final FORMAT TabSeparated' > xxx(导出到哪个文件）
clickhouse: 使用ReplaceMergeTree以增代删同步mysql binlog日志
lixia0417mul2的博客
05-03 3322
一般同步mysql的数据到clickhouse中有两种常见的方式 方式1: 每天定时拉取mysql的全量数据同步到clickhouse中，对于相同的主键，只保留update_time最新的一条记录，这种方式要求mysql的数据记录必须包含update_time字段，并且不能物理删除数据，删除时只能软删除比如设置is_deleted=1这种方式.我目前所在的公司就是采用的这种方式同步mysql的数据. 方式2： mysql数据变更的binlog通过canal + kafka的形式以kafka主题的对外提供，k
【Clickhouse】ReplaceingMergeTree引擎final实现合并去重探索 | 京东云技术团队
JDDTechTalk的博客
06-08 2135
为了保证统计数据的准确性，比如订单金额，一个常用的方法是在查询时增加final关键字。那final关键字是如何合并数据的，以及合并的数据范围是怎样的，本文就对此做一个简单的探索
ClickHouse 数据基础处理 SQL（部分）
一名数据分析师
11-14 440
列赋值：方案一：用于某一类型的赋值方案二：表关联更新--没有成功，提示left的那种表要支持storejoin列增加和删除，以及类型修改。
clickHouse导入MySQL数据表的数据
人生一张西的博客
09-22 2847
MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行insert和select查询，以方便您在ClickHouse与MySQL之间进行数据交换。 MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如show tables或show create table之类的操作。 ClickHouse使用mysql引擎可以与mysql数据库中的数据表建⽴映射，并通过SQL向其发起远程查询或插入数据，这是一个异步的过程，相当于ck
Clickhouse外部储存表引擎(HDFS、MySQL、JDBC、Kafka、File)
Bulut0907
07-25 5307
目录1. HDFS表引擎1.1 准备工作 外部储存表引擎只负责元数据管理，和数据查询 1. HDFS表引擎 1.1 准备工作 HDFS表引擎不支持Kerberos认证，如果开启，请关闭HDFS的Kerberos认证 HDFS上新建clickhouse目录 [root@clickhouse1 ~]# [root@clickhouse1 ~]# hadoop fs -mkdir /clickhouse [root@clickhouse1 ~]# [root@clickhouse1 ~]# hadoop fs
clickhouse 函数大全
sky924102334的博客
11-07 7034
clickhouse函数大全
Clickhouse 基础知识 - 函数
weixin_30670965的博客
07-31 2486
部分基本函数SQL脚本示例： -------------------------------------------------- -------------------------------------------------- ---------------Clickhouse基础知识：函数学习------------- --官址学习文档：https://clickho...
Clickhouse节点替换
huawuqing的博客
12-08 1282
Clickhouse节点替换 副本节点故障
替代 ClickHouse！这个数据分析平台太强了！
最新发布
芋艿V
04-09 1173
ByConity 是字节跳动开源的云原生数据仓库，在满足数仓用户对资源弹性扩缩容，读写分离，资源隔离，数据强一致性等多种需求的同时，提供优异的查询，写入性能。GitHub ｜https://github.com/ByConity/ByConity作者｜程伟，MetaAPP 大数据研发工程师MetaApp 是国内领先的游戏开发与运营商，专注移动端信息高效分发，致力于构建面向全年龄段的虚拟世界。截至 ...
ClickHouse常用函数速查大全
haveanybody的博客
07-31 9486
ClickHouse主要有两种函数：常规函数和聚合函数，除此之外，还有 ‘arrayJoin’ 等特殊函数，我们将分别介绍。需要注意的是ClickHouse具有强类型限制，换句话说，它不进行类型之间的隐式转换，每个函数都适用于特定的类型参数。这意味着有时需要使用类型转换函数。 如果下面函数执行报错，则可能是clickhouse版本不支持。 toTypeName(0) 1. 算术函数 对于所有算术函数，计算结果类型向上兼容。 SELECT toTypeName(0), toTypeName(0 + 0), t
ClickHouse函数整理（详细）
Fresh_man888的博客
11-18 4808
1、日期类函数 1.1 时间或日期截取函数（to）—— 返回非日期 函数用途举例结果toYear()取日期或时间日期的年份toYear(toDateTime(‘2018-12-11 11:12:13’)) toYear(toDate(‘2018-12-11’))返回 2018 返...
clickhouse的ReplacingMergeTree引擎实战
zhangsir的博客
12-16 1万+
学习ReplacingMergeTree引擎，首先你得了解clickhouse的MergeTree引擎，因为MergeTree引擎是MergeTree引擎的一个扩展版引擎，他拥有和MergeTree一样的功能，同时新增了一个删除相同主键数据的功能。 我们知道，clickhouse的MergeTree引擎，是clickhouse众多引擎中，号称性能最好的一个引擎，但他只能按照分区删除数据，所以有些场...
clickhouse语句_ClickHouse之ReplacingMergeTree引擎
weixin_34553907的博客
12-30 1205
简介该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。在数据合并的时候会对主键进行去重，合并会在后台执行，执行时间未知，因此你无法预先做出计划，当然你也可以调用OPTIMIZE语句来发起合并计划，但是这种方式是不推荐的，因为OPTIMIZE语句会引发大量的读写请求。建表语句CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUS...
clickhouse几种create table的情况
热门推荐
Sojer的读书笔记
03-06 2万+
普通建表： CREATE TABLE dis_j.D_F1_shard on cluster cluster_demo ( `product_code` String, `package_name` String ) ENGINE = MergeTree ORDER BY package_name SETTINGS index_granularity = 8192 分布表： CREATE ...
clickhouse 修改数据 语法错误
10-18
好的，关于您的问题，可以提供更具体的错误信息和修改语句吗？这样我才能更好地帮助您解决问题。 同时，我可以先给您提供一些常见的 ClickHouse 修改数据的语法，供您参考： 1. 修改单个字段： ``` ALTER TABLE table_name UPDATE column_name = new_value WHERE condition; ``` 2. 修改多个字段： ``` ALTER TABLE table_name UPDATE column1 = new_value1, column2 = new_value2 WHERE condition; ``` 3. 替换某个字段中的字符串： ``` ALTER TABLE table_name UPDATE column_name = REPLACE(column_name, 'old_string', 'new_string') WHERE condition; ``` 希望这些能对您有所帮助。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值