如何无需定期执行INSERT将SQL Server分析数据同步至ClickHouse？

SQL Server 2016中有表table1：

CREATE TABLE [dbo].[table1]
(
    [id] [bigint] NULL,
    [name] [nchar](10) NULL
) ON [PRIMARY]

Docker部署的ClickHouse已配置ODBC驱动，可通过ODBC函数查询SQL Server数据：

SELECT *
FROM odbc('DSN=mssql_dsn;UID=sa;PWD=PaSSworD;Database=db1', 'table1')

查询结果：

┌─id─┬─name───────┐
│  1 │ John       │
│  2 │ Jane       │
│  3 │ Bob        │
│  4 │ Alice      │
└────┴────────────┘

ClickHouse中已创建MergeTree表：

CREATE TABLE table1
(
    `id` Int32,
    `name` String
)
ENGINE = MergeTree
ORDER BY (id);

执行INSERT可成功导入数据：

INSERT INTO table1
    SELECT *
    FROM odbc('DSN=mssql_dsn;UID=sa;PWD=PaSSworD;Database=db1', 'table1');

但创建基于ODBC函数的物化视图时报错：

Received exception from server (version 23.10.4):
Code: 397. DB::Exception: Received from localhost:9000. DB::Exception:
StorageMaterializedView cannot be created from table functions
(odbc('DSN=mssql_dsn;UID=sa;PWD=PaSSworD;Database=db1', 'table1')).
(QUERY_IS_NOT_SUPPORTED_IN_MATERIALIZED_VIEW)

需求：无需手动定期执行INSERT语句，在ClickHouse中获取SQL Server的分析数据。

方法1：创建ODBC引擎外部表（实时查询） #

直接创建映射SQL Server表的ODBC引擎外部表，查询时实时拉取SQL Server数据，无需提前导入本地：

CREATE TABLE table1_odbc
(
    `id` Int32,
    `name` String
)
ENGINE = ODBC('DSN=mssql_dsn;UID=sa;PWD=PaSSworD;Database=db1', 'dbo', 'table1');

之后直接查询该表即可获取SQL Server最新数据：

SELECT * FROM table1_odbc;

• 优点：数据始终与SQL Server同步，无需维护本地副本
• 缺点：查询性能依赖SQL Server响应速度与网络带宽，适合小数据量或实时性要求高的场景

方法2：基于ODBC外部表创建物化视图（定时缓存） #

如果需要本地缓存数据提升查询性能，可先创建ODBC外部表，再基于该表创建物化视图，通过定时任务自动刷新：

1. 创建ODBC外部表（同方法1）
2. 创建指向MergeTree表的物化视图：

CREATE MATERIALIZED VIEW mv_table1 TO table1
AS SELECT * FROM table1_odbc;

3. 配置定时刷新：
   • 用外部定时工具（如Linux crontab、Docker容器内定时任务）执行刷新命令：

     clickhouse-client --query "ALTER TABLE mv_table1 REFRESH MATERIALIZED VIEW;"
     

   • 或使用ClickHouse内置事件表实现内部调度：

     CREATE EVENT IF NOT EXISTS refresh_mv_table1
     ON SCHEDULE EVERY 1 HOUR
     DO ALTER TABLE mv_table1 REFRESH MATERIALIZED VIEW;
     

• 优点：本地缓存数据，查询性能优异
• 缺点：数据存在延迟，延迟时长由刷新间隔决定

方法3：增量数据同步（高级场景） #

如果需要增量同步SQL Server数据而非全量刷新，可使用：

• ClickHouse官方SQL Server集成工具（如clickhouse-sql-server）
• 第三方CDC工具（如Debezium）捕获SQL Server数据变更，实时同步到ClickHouse

内容的提问来源于stack exchange，提问作者Konstantin