比较远程 SQL 查询的过滤下推¶
以下示例把同一台 MatrixOne 用作远端 MySQL 协议数据源,使用本地演示账号 root/111。ENGINE = SQL 也支持 PostgreSQL;ESQL 的连接配置见 ESQL_TVF。
CREATE DATABASE cookbook_remote;
CREATE TABLE cookbook_remote.src(id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO cookbook_remote.src VALUES (1,'alice'),(2,'bob'),(3,'carol');
CREATE EXTERNAL TABLE cookbook_remote.plain(id INT, name VARCHAR(50))
ENGINE = SQL WITH ('config' = '{"driver":"mysql","dsn":"root:111@tcp(127.0.0.1:6001)/cookbook_remote"}',
'query' = 'SELECT id,name FROM src');
CREATE EXTERNAL TABLE cookbook_remote.pushed(id INT, name VARCHAR(50))
ENGINE = SQL WITH ('config' = '{"driver":"mysql","dsn":"root:111@tcp(127.0.0.1:6001)/cookbook_remote"}',
'query' = 'SELECT id,name FROM src', 'pushdown' = 'true');
SELECT id,name FROM cookbook_remote.plain WHERE id > 1 ORDER BY id;
SELECT id,name FROM cookbook_remote.pushed WHERE id > 1 ORDER BY id;
两次结果都应是 (2,bob)、(3,carol);pushdown=true 要求远端查询的列名与声明一致。配置中可能有密码,生产环境使用会话配置或受保护的连接方式。清理:DROP DATABASE cookbook_remote;。
ESQL 外表依赖 Elasticsearch。源码提供完整本地链路:从仓库根目录运行 make test-esql-tvf-e2e-local,测试会启动 Elasticsearch 与 MatrixOne、写入 emp 数据、创建 ENGINE = ESQL 外表、查询并核对结果。对应实现见 ESQL E2E。