SQL ClickHouse 的 external table 与 PostgreSQL / MySQL 的物化外部表实践

clickhouse 的 postgresql 引擎表是实时查询代理而非物化视图，每次 select 均远程发起全量查询，不缓存、不索引、不压缩；适合低频小结果集维表关联，不适用于报表主表或聚合。

ClickHouse 的 CREATE table ... ENGINE = PostgreSQL 不是物化视图

很多人以为用 PostgreSQL 引擎建的表会自动同步数据，其实它只是个「实时查询代理」：每次 SELECT 都发起一次远程查询，不缓存、不索引、不压缩。你查 10 次，PostgreSQL 就被拉 10 次；加了 WHERE 条件，也未必能下推——取决于 ClickHouse 版本和谓词复杂度。

常见错误现象：SELECT count(*) FROM pg_table 极慢，或者 JOIN 大表时直接超时；日志里反复出现 PQ: server closed the connection unexpectedly。

• 使用场景：适合低频、小结果集的维表关联（比如查用户基本信息），不适合报表主表或聚合入口
• 参数差异：password 必须明文写进 DDL（ClickHouse 23.8+ 支持 secure 参数走密钥管理，但默认不启用）
• 性能影响：没有本地分区/排序键，ORDER BY 和 LIMIT 可能全量拉取再过滤

mysql 外部表必须用 MySQL 引擎 + 显式指定 database

ClickHouse 的 MySQL 引擎不是直连 MySQL 协议，而是通过 libmysqlclient 走 TCP，且要求你在建表时明确写出远端库名——漏写 database 参数会导致建表成功但查询报错 Unknown database，哪怕远端只有一个库。

容易踩的坑：CREATE TABLE t (x int) ENGINE = MySQL('host:3306', 'wrong_db', 'table', 'user', 'pass') 中 wrong_db 若与实际不符，ClickHouse 不校验，直到第一次 SELECT 才失败。

• 使用场景：比 PostgreSQL 引擎更稳定（尤其在高并发简单查询下），但同样不支持写入（INSERT 报错 Unsupported method）
• 兼容性影响：MySQL 8.0 默认禁用 old_passwords，ClickHouse 旧版本（caching_sha2_password
• 建议加 SETTINGS connect_timeout = 5, read_timeout = 30 防止长尾请求拖垮整个查询队列

File 和 URL 引擎才是轻量级「伪物化」方案

如果你真需要一份可查、可索引、可分区的外部数据快照，别硬扛实时引擎——用 File 或 URL 引擎配合定期 INSERT SELECT 更可控。它们把数据落地到 ClickHouse 本地存储，享受完整 OLAP 能力。

典型错误：把 CSV 文件路径写成相对路径（如 'data.csv'），结果服务重启后找不到；或用 URL 引擎指向一个动态生成的接口，却没加 SETTINGS format_http_method = 'GET' 导致 405 错误。

• 格式选择：File 适合离线导入（权限可控、无网络依赖），URL 适合对接 HTTP API 或对象存储（S3 兼容接口需配 url 参数含 ?X-Amz-Algorithm=...）
• 性能关键点：File 引擎读取时不做类型推断，必须显式声明 FORMAT CSVWithNames 等，否则字段全变成 String，后续转换开销大
• 注意 URL 引擎默认不支持重定向（302），遇到跳转会直接报错 HTTP request failed: redirect refused，得手动处理或换用 Remote 引擎

真正物化：用 MATERIALIZED VIEW 套外部表 + ReplacingMergeTree

ClickHouse 没有原生「物化外部表」语法，但可以用 MATERIALIZED VIEW 实现近实时同步：监听源表变更（靠定时 INSERT SELECT 或 Flink/CDC 写入中间 Kafka），再用 ReplacingMergeTree 去重。这是唯一能兼顾查询性能与数据新鲜度的正解。

最容易忽略的一点：物化视图本身不存储数据，它的 TO 目标表必须是可更新引擎（如 ReplacingMergeTree），且要定义 ORDER BY 和 VERSION 字段，否则 OPTIMIZE 合并不生效，重复数据越积越多。

• 示例关键片段：CREATE MATERIALIZED VIEW mv TO target_table AS SELECT *, now() AS _version FROM pg_external_table，其中 target_table 必须是 ReplacingMergeTree(_version)
• 增量逻辑不能只靠时间戳：PostgreSQL 的 updated_at 可能不覆盖所有变更场景，建议加 pg_logical_slot_get_changes 或用 Debezium 抽变更日志
• 如果源表无主键或更新频率极高，ReplacingMergeTree 的合并压力会很大，此时应考虑 VersionedCollapsingMergeTree 或分片策略

外键、事务、自动刷新这些概念在 ClickHouse 外部表里都不存在，所有「物化」效果都得靠你自己设计同步节奏和冲突解决逻辑。最常被绕开的其实是权限链路——PG/MySQL 用户要有对应表的 SELECT 权限，ClickHouse 用户又得有 CREATE TABLE 和 INSERT 权限，两头漏一个，问题就卡在看不见的地方。