如何用 ClickHouse 的 merge() 表函数同时查询多张 schema 不一致的表【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouse当你同一个数据库里有多张结构相近但不完全一致的表——同一列在不同表里类型不同或者部分表多了若干列——用普通 SQL 很难把它们当成一张表来查。ClickHouse 的merge()表函数就是为这个场景设计的它创建一个临时的 Merge 表把匹配到的表当成一个整体来读结果列取各表列名的并集列类型由底层表推导。当某列的类型在各表间不一致时该列会表现为Variant需要配合variantType/variantElement函数按行取出对应类型的值。本文的操作路径基于官方文档中的完整示例按年代拆分的网球比赛数据从建表、比对 schema 到查询与验证。涉及variantTypev24.2.0 引入和variantElementv25.2.0 引入按文中示例执行需要 v25.2.0 或更新的 ClickHouse。merge() 的语法与参数语法见 merge 表函数参考merge([db_name,] tables_regexp)参数说明db_name可选默认currentDatabase()。可以是数据库名、返回数据库名常量的表达式或REGEXP(expression)用来匹配多个库名tables_regexp匹配表名的正则表达式。正则基于 re2支持 PCRE 子集大小写敏感临时 Merge 表不提供写入能力读取自动并行化且底层表已建索引时会被使用。与 Merge 引擎相同查询时可以用到两个虚拟列_table数据实际读自哪张表和_database来自哪个库。对_table加过滤例如WHERE _tablexyz时只会读取满足条件的表。准备示例数据四张 schema 各不相同的表下面的建表命令来自 官方数据建模指南。它从文档给出的 Jeff Sackmann 网球数据集地址下载 1960s–1990s 四个年代的 CSV 并建表执行前需要有外网访问权限。注意每个年代刻意用了不同的 schemawinner_seed类型逐年变化Nullable(String)→Nullable(UInt8)→Nullable(UInt16)1960s 的score是String其余年代用splitByWhitespace拆成Array(String)1990s 把surface设为Enum并额外增加walkover、retirement两列。CREATE OR REPLACE TABLE atp_matches_1960s ORDER BY tourney_id AS SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, score FROM url(https://raw.githubusercontent.com/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{1968..1969}.csv) SETTINGS schema_inference_make_columns_nullable0, schema_inference_hintswinner_seed Nullable(String), loser_seed Nullable(UInt8); CREATE OR REPLACE TABLE atp_matches_1970s ORDER BY tourney_id AS SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, splitByWhitespace(score) AS score FROM url(https://raw.githubusercontent.com/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{1970..1979}.csv) SETTINGS schema_inference_make_columns_nullable0, schema_inference_hintswinner_seed Nullable(UInt8), loser_seed Nullable(UInt8); CREATE OR REPLACE TABLE atp_matches_1980s ORDER BY tourney_id AS SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, splitByWhitespace(score) AS score FROM url(https://raw.githubusercontent.com/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{1980..1989}.csv) SETTINGS schema_inference_make_columns_nullable0, schema_inference_hintswinner_seed Nullable(UInt16), loser_seed Nullable(UInt16); CREATE OR REPLACE TABLE atp_matches_1990s ORDER BY tourney_id AS SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, splitByWhitespace(score) AS score, toBool(arrayExists(x - position(x, W/O) 0, score))::Nullable(bool) AS walkover, toBool(arrayExists(x - position(x, RET) 0, score))::Nullable(bool) AS retirement FROM url(https://raw.githubusercontent.com/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{1990..1999}.csv) SETTINGS schema_inference_make_columns_nullable0, schema_inference_hintswinner_seed Nullable(UInt16), loser_seed Nullable(UInt16), surface Enum(\Hard\, \Grass\, \Clay\, \Carpet\);如果你已有自己的表无需使用这套数据只要保证它们能被同一个正则匹配到本例中是atp_matches*后续查询逻辑通用。先用 system.columns 看清各表的差异查询前把每张表同名列的类型并排列出来便于判断哪些列会形成VariantSELECT * EXCEPT(position) FROM ( SELECT position, name, any(if(table atp_matches_1960s, type, null)) AS 1960s, any(if(table atp_matches_1970s, type, null)) AS 1970s, any(if(table atp_matches_1980s, type, null)) AS 1980s, any(if(table atp_matches_1990s, type, null)) AS 1990s FROM system.columns WHERE database currentDatabase() AND table LIKE atp_matches% GROUP BY ALL ORDER BY position ASC ) SETTINGS output_format_pretty_max_value_width25;文档示例输出┌─name────────┬─1960s────────────┬─1970s───────────┬─1980s────────────┬─1990s─────────────────────┐ │ tourney_id │ String │ String │ String │ String │ │ surface │ String │ String │ String │ Enum8(Hard 1, Grass⋯│ │ winner_name │ String │ String │ String │ String │ │ loser_name │ String │ String │ String │ String │ │ winner_seed │ Nullable(String) │ Nullable(UInt8) │ Nullable(UInt16) │ Nullable(UInt16) │ │ loser_seed │ Nullable(UInt8) │ Nullable(UInt8) │ Nullable(UInt16) │ Nullable(UInt16) │ │ score │ String │ Array(String) │ Array(String) │ Array(String) │ │ walkover │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ Nullable(Bool) │ │ retirement │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ Nullable(Bool) │ └─────────────┴──────────────────┴─────────────────┴───────────────────────────┴───────────────────┘可以确认三处差异winner_seed从Nullable(String)一路变成Nullable(UInt16)score在 1960s 是String、其余是Array(String)1990s 独有的walkover、retirement列在其余表中为 NULL即该表没有这列。用 merge() 直接查询对于类型兼容的列可以直接写普通查询。比如查找 John McEnroe 战胜 1 号种子的比赛SELECT loser_name, score FROM merge(atp_matches*) WHERE winner_name John McEnroe AND loser_seed 1;文档示例输出┌─loser_name────┬─score───────────────────────────┐ │ Bjorn Borg │ [6-3,6-4] │ │ Bjorn Borg │ [7-6,6-1,6-7,5-7,6-4] │ │ Bjorn Borg │ [7-6,6-4] │ │ Bjorn Borg │ [4-6,7-6,7-6,6-4] │ │ Jimmy Connors │ [6-1,6-3] │ │ Ivan Lendl │ [6-2,4-6,6-3,6-7,7-6] │ │ Ivan Lendl │ [6-3,3-6,6-3,7-6] │ │ Ivan Lendl │ [6-1,6-3] │ │ Stefan Edberg │ [6-2,6-3] │ │ Stefan Edberg │ [7-6,6-2] │ │ Stefan Edberg │ [6-2,6-2] │ │ Jakob Hlasek │ [6-3,7-6] │ └───────────────┴─────────────────────────────────┘merge(atp_matches*)没有指定库名默认在当前库中按正则atp_matches*匹配上述四张表一次查询覆盖全部年代。列类型不一致时用 variantType variantElement 分支取值接着在上一步结果上再加条件McEnroe 的winner_seed排在第 3 位或更后。由于winner_seed在各表类型不同合并后是Variant不能直接拿数值比较。文档给出的做法是用 multiIf 按行分派先用variantType判断该行存储的具体类型再用variantElement取出该类型的值String分支需要先转成数值再比较SELECT loser_name, score, winner_seed FROM merge(atp_matches*) WHERE winner_name John McEnroe AND loser_seed 1 AND multiIf( variantType(winner_seed) UInt8, variantElement(winner_seed, UInt8) 3, variantType(winner_seed) UInt16, variantElement(winner_seed, UInt16) 3, variantElement(winner_seed, String)::UInt16 3 );文档示例输出┌─loser_name────┬─score─────────┬─winner_seed─┐ │ Bjorn Borg │ [6-3,6-4] │ 3 │ │ Stefan Edberg │ [6-2,6-3] │ 6 │ │ Stefan Edberg │ [7-6,6-2] │ 4 │ │ Stefan Edberg │ [6-2,6-2] │ 7 │ └───────────────┴───────────────────────────┴───────────┘两个函数的行为见 variantType / variantElement 参考variantType返回每行的变体类型名NULL 行为NonevariantElement(variant, type_name[, default_value])取出指定类型的子列第三个可选参数default_value在变体中不存在该类型时作为默认值。Variant本身的定义与子列读取方式见 Variant 数据类型。用 _table 虚拟列确认行来自哪张表把_table加入 SELECT 即可知道每行读自哪张底表结果与上一查询对应SELECT _table, loser_name, score, winner_seed FROM merge(atp_matches*) WHERE winner_name John McEnroe AND loser_seed 1 AND multiIf( variantType(winner_seed) UInt8, variantElement(winner_seed, UInt8) 3, variantType(winner_seed) UInt16, variantElement(winner_seed, UInt16) 3, variantElement(winner_seed, String)::UInt16 3 );文档示例输出┌─_table────────────┬─loser_name────┬─score─────────┬─winner_seed─┐ │ atp_matches_1970s │ Bjorn Borg │ [6-3,6-4] │ 3 │ │ atp_matches_1980s │ Stefan Edberg │ [6-2,6-3] │ 6 │ │ atp_matches_1980s │ Stefan Edberg │ [7-6,6-2] │ 4 │ │ atp_matches_1980s │ Stefan Edberg │ [6-2,6-2] │ 7 │ └───────────────────┴───────────────┴───────────────────────────┴───────────┘_table还能用来判断“哪些表缺列”。先按表统计walkover的取值分布SELECT _table, walkover, count() FROM merge(atp_matches*) GROUP BY ALL ORDER BY _table;文档示例输出┌─_table────────────┬─walkover─┬─count()─┐ │ atp_matches_1960s │ ᴺᵁᴸᴸ │ 7542 │ │ atp_matches_1970s │ ᴺᵁᴸᴸ │ 39165 │ │ atp_matches_1980s │ ᴺᵁᴸᴸ │ 36233 │ │ atp_matches_1990s │ true │ 128 │ │ atp_matches_1990s │ false │ 37022 │ └───────────────────┴──────────┴─────────┘可以看到只有atp_matches_1990s有walkover的真实值其余表的行全是 NULL——这正是“缺列”表在合并结果中的表现。如果要在统计中把缺列的行也算进来需要回落到score列自行判断W/O而score的类型同样是Variant所以按类型分派Array(String)行遍历数组找W/OString行直接做字符串匹配SELECT _table, multiIf( walkover IS NOT NULL, walkover, variantType(score) Array(String), toBool(arrayExists( x - position(x, W/O) 0, variantElement(score, Array(String)) )), variantElement(score, String) LIKE %W/O% ), count() FROM merge(atp_matches*) GROUP BY ALL ORDER BY _table;文档示例输出┌─_table────────────┬─multiIf(isNo⋯, %W/O%))─┬─count()─┐ │ atp_matches_1960s │ true │ 242 │ │ atp_matches_1960s │ false │ 7300 │ │ atp_matches_1970s │ true │ 422 │ │ atp_matches_1970s │ false │ 38743 │ │ atp_matches_1980s │ true │ 92 │ │ atp_matches_1980s │ false │ 36141 │ │ atp_matches_1990s │ true │ 128 │ │ atp_matches_1990s │ false │ 37022 │ └───────────────────┴───────────────────────────┴─────────┘此时 1960s–1980s 各表也有true行说明缺列的行已通过score补上了判断。验证方式与适用边界验证按上文的顺序执行——先查system.columns确认各表实际类型差异再跑merge()查询对照文档示例输出核对行数与类型列的表现缺列表为 NULL、类型列为Variant。版本variantType自 v24.2.0、variantElement自 v25.2.0 可用本文示例整体需要 v25.2.0。正则匹配tables_regexp大小写敏感且基于 re2Merge 表自身即使匹配到正则也不会被选入读取避免循环。只读merge()生成的临时 Merge 表不支持写入查询过滤放在WHERE中即可对_table的过滤还能把读取范围限定到满足条件的底表。更多上下文可继续查阅仓库中的 merge 表函数参考、Merge 表引擎 和 Merge table function 指南。【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouse创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考