联邦查询与外部数据源：MySQL、PostgreSQL 与 URL 表引擎

外部数据源访问方式：
□ 表引擎：MySQL / PostgreSQL / URL / File / S3
□ 表函数：mysql(...) / url(...) / s3(...) / file(...)
□ 字典外部源：外部数据加载为内存字典

适用：读业务库→MySQL/PG；API/文件→URL/File/S3；高频维表→字典

注意：联邦查询不替代数据仓库，大规模分析仍要物化

-- 表函数式即时查询（无需建表）
SELECT * FROM mysql(
    '127.0.0.1:3306', 'app_db', 'orders',
    'reader', 'password'
) LIMIT 10;

-- 查询远程 PostgreSQL
SELECT * FROM postgresql(
    '127.0.0.1:5432', 'app_db', 'customers',
    'reader', 'password'
) LIMIT 10;

MySQL 引擎要点：
□ 建表声明 MySQL 连接信息，列与远端对齐
□ 查询透传到 MySQL，结果回传
□ 支持 SELECT / INSERT（写入远端）
□ WHERE 尽量下推，减少回传

类型映射：INT→Int32、VARCHAR→String、DECIMAL→Decimal、DATETIME→DateTime

限制：无列式/索引能力，连接压力高，大扫描会打垮业务库

-- 建 MySQL 引擎表
CREATE TABLE mysql_orders (
    id UInt64, user_id UInt64,
    amount Decimal(18, 2), created_at DateTime
) ENGINE = MySQL(
    '127.0.0.1:3306', 'app_db', 'orders',
    'reader', 'password'
);

-- 直接查询：等价于查远端表
SELECT user_id, sum(amount) AS total
FROM mysql_orders
WHERE created_at >= now() - INTERVAL 1 DAY
GROUP BY user_id;

PostgreSQL 引擎要点：
□ 建表声明 PG 连接，列类型按 PG 映射
□ 支持 SELECT / INSERT，依赖连接复用

类型映射：serial→UInt64、numeric→Decimal、text→String、json→String

注意：大结果回传慢、PG max_connections 有限

-- 建 PostgreSQL 引擎表
CREATE TABLE pg_customers (
    id UInt64, name String,
    region String, vip_level Int32
) ENGINE = PostgreSQL(
    '127.0.0.1:5432', 'app_db', 'customers',
    'reader', 'password'
);

-- 联邦关联：本地事件 × PG 维表
SELECT e.event_time, c.region, c.vip_level
FROM events AS e
LEFT JOIN pg_customers AS c ON e.customer_id = c.id
WHERE c.region = '华东';

URL 引擎要点：
□ 每次查询发起 HTTP 请求读取远端
□ 支持 GET 请求：GET 参数即查询条件
□ 响应格式：CSV / JSONEachRow / Parquet 等
□ url 表函数临时查询，URL 引擎建表复用

使用场景：第三方 API、内部服务接口、一次性对账

性能特征：
□ 网络延迟主导，不适合高频大查询
□ 接口返回量决定速度
□ 大响应分批读取，别全量拉取

-- 表函数：一次 HTTP GET
SELECT * FROM url(
    'http://api.example.com/metrics?date=2026-09-30',
    'JSONEachRow',
    'ts DateTime, metric String, value Float64'
) LIMIT 100;

-- 建 URL 引擎表（可复用）
CREATE TABLE api_metrics (
    ts DateTime, metric String, value Float64
) ENGINE = URL(
    'http://api.example.com/metrics',
    'JSONEachRow'
);

File 引擎：读本地文件（CSV/Parquet），表函数 file('path','format',...)
S3 引擎：读 S3/OSS/MinIO，支持通配符批量文件（*.parquet）

架构意义：数据湖文件 → 直接 SQL 查询（不搬家）
注意：Parquet 自带列裁剪，配合效果好

-- file 表函数：读本地 CSV
SELECT * FROM file(
    '/tmp/events.csv',
    'CSV',
    'event_time DateTime, user_id UInt64, event_type String'
) LIMIT 10;

-- s3 表函数：读对象存储的 Parquet
SELECT * FROM s3(
    'https://bucket.s3.region.amazonaws.com/logs/2026-09/*.parquet',
    'Parquet'
) LIMIT 10;

字典外部源：CLICKHOUSE / MYSQL / POSTGRESQL / HTTP
□ 加载后 LIFETIME 定期刷新

为什么用字典：一次加载多次探测 → 比 JOIN 快
□ 各节点本地副本 → 分布式可见

注意：内存由数据量决定、刷新窗口内数据可能滞后

-- 从 MySQL 加载维表字典
CREATE DICTIONARY dict_customers (
    customer_id UInt64, name String, region String
)
PRIMARY KEY customer_id
SOURCE(MYSQL(
    host '127.0.0.1' port 3306
    user 'reader' password 'password'
    db 'app_db' table 'customers'
))
LIFETIME(MIN 300 MAX 600);

-- 查询时 dictGet 点查：不经过 JOIN
SELECT event_time, customer_id,
       dictGet('dict_customers', 'region', customer_id) AS region
FROM events;

性能共性：
□ 外部系统每次查询都有网络往返
□ 行式数据库/API 无法利用列裁剪
□ 大结果回传拖慢查询
□ 高并发 → 打满外部连接池

注意清单：
□ WHERE 下推到外部（能过滤就过滤）
□ 只 SELECT 需要的列（少回传）
□ 避免 JOIN 外部大表（字典替代）
□ 外部连接数有限 → 复用而非突增

-- 下推示例：条件在 MySQL 侧过滤，回传少
SELECT user_id, sum(amount)
FROM mysql_orders
WHERE created_at >= '2026-09-01'   -- 下推到远端
  AND amount > 100                  -- 下推到远端
GROUP BY user_id LIMIT 100;

-- 观察联邦查询耗时与读取量
SELECT query, query_duration_ms, read_rows
FROM system.query_log
WHERE query ILIKE '%mysql%' OR query ILIKE '%url%' OR query ILIKE '%s3%'
ORDER BY query_duration_ms DESC LIMIT 20;

同步替代方案：
□ 物化视图：基于外部引擎表持续查询，增量转存
□ 定时拉取：周期性从外部源抽取到本地
□ 外部表作为「暂存层」：先入本地再加工

优点：查询走本地列式、不频繁打外部、有 TTL/分区/索引

注意：物化视图只跟增量、定时拉取要处理去重幂等

-- 物化视图：MySQL 增量转存到本地 MergeTree
CREATE MATERIALIZED VIEW mv_orders TO orders_local AS
SELECT id, user_id, amount, created_at FROM mysql_orders;

-- 定时拉取（替代方案）：周期性执行
INSERT INTO orders_local
SELECT id, user_id, amount, created_at FROM mysql_orders
WHERE created_at > (SELECT max(created_at) FROM orders_local);

-- 本地分析不再触碰外部系统
SELECT toDate(created_at) AS d, sum(amount)
FROM orders_local GROUP BY d ORDER BY d;

选型对比：
□ MySQL/PG 引擎：关系库实时读，小维表/探查
□ URL 引擎：API 即表，小数据、低频
□ File/S3 引擎：文件/对象存储，批式
□ 字典外部源：静态维表高频探测
□ 物化+拉取：持续分析的正确形态

选型决策：
□ 高频关联维表 → 字典
□ 低频探查关系库 → MySQL/PG 引擎
□ 批式文件分析 → S3/File
□ 需要持续分析 → 物化到本地

一句决策：能物化的别联邦，能字典的别 JOIN，能过滤的下推

-- 同一需求的不同实现：关联维表
-- 方案 A：MySQL 引擎 JOIN（低频）
SELECT ... FROM events e LEFT JOIN mysql_customers c ON ...;

-- 方案 B：字典（高频）
SELECT ..., dictGet('dict_customers', 'region', e.customer_id) FROM events e;

-- 方案 C：物化本地维表（持续）
CREATE TABLE dim_customers_local AS SELECT * FROM mysql_customers;

联邦查询速查：
□ MySQL / PG → 关系库实时读（低频）
□ URL → HTTP 接口即表（小数据）
□ File / S3 → 文件与对象存储（批式）
□ 字典外部源 → 静态维表高频探测
□ 物化 + 拉取 → 持续分析的最终形态

注意：WHERE 下推、少列回传、连接复用、数据量大 → 物化

一句记忆：外部数据先物化、再分析

-- 联邦查询体检模板
SELECT query, query_duration_ms, read_rows
FROM system.query_log
WHERE query ILIKE '%_mysql%' OR query ILIKE '%url%' OR query ILIKE '%s3%'
ORDER BY query_duration_ms DESC LIMIT 20;

-- 检查字典加载状态
SELECT name, status, type, bytes_allocated FROM system.dictionaries;