CREATE EXTERNAL TABLE … ENGINE = SQL|ESQL

通过 ENGINE = SQL 或 ENGINE = ESQL 子句将 MatrixOne 外部表映射到外部 SQL 数据库或 Elasticsearch ES|QL 源,其行数据由扫描时提供的查询文本产生。该表只读,源查询由隐藏列 __mo_query 或 query 表选项携带。

描述

CREATE EXTERNAL TABLE ... ENGINE = SQL 与 CREATE EXTERNAL TABLE ... ENGINE = ESQL 将 MatrixOne 外部表映射到在扫描时应答查询的外部数据源。ENGINE = SQL 通过 {"driver": "...", "dsn": "..."} 配置访问 SQL 数据库(支持 MySQL 与 PostgreSQL 驱动)。ENGINE = ESQL 通过 ES|QL 配置访问 Elasticsearch 集群。

该表只读:支持 SELECT,拒绝写入。CREATE EXTERNAL TABLE 会校验引擎选项与配置 JSON 的结构,但不会主动连接数据源;连接在首次扫描时才延迟建立。

语法

CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]table_name (
    column1 type1,
    column2 type2,
    ...
) ENGINE = {SQL | ESQL} [WITH ("option" = 'value' [, "option" = 'value'] ...)];

参数

选项

引擎

是否必填

说明

config

SQL、ESQL

否

连接 JSON。SQL 引擎为 `{“driver”:”mysql

query

SQL、ESQL

否

当 SELECT 没有 __mo_query 谓词时使用的默认查询文本。

pushdown

SQL

否

false(默认)时原样下发查询文本,由 MatrixOne 在本地求值谓词;true 时把文本包装为派生表,让数据源应用 MatrixOne 能翻译的谓词。ESQL 拒绝该选项。

__mo_query 隐藏列

与文件型外部表的 __mo_filepath 类似,ENGINE = SQL|ESQL 表暴露一个隐藏的尾列 __mo_query,类型为 varchar。它在 SELECT *、DESC 与 SHOW COLUMNS 中隐藏,但可按名称查询。查询文本扮演文件路径的角色:每条不同的查询文本都是一个待扫描的“文件”,每行返回数据都会携带产生它的查询文本。

扫描时 MatrixOne 依据谓词推导查询列表:

  • __mo_query = '<text>' 产生一个候选。

  • __mo_query IN ('<text1>', '<text2>', ...) 产生多个候选。

  • 以上形式的 OR 产生并集。

  • 其他合取式(如 __mo_query LIKE '...')不产生候选,但可能对推导出的列表进行裁剪。

若未推导出候选且表带有 query 选项,则使用该文本;否则扫描失败。

查询文本按原样发送给数据源。它必须按声明顺序返回表中声明的列,字段数量必须匹配。

使用说明

  • 只读:向 ENGINE = SQL|ESQL 外部表写入会被拒绝。INSERT ... SELECT FROM 这类表是预期的 ETL 路径。

  • 密钥脱敏:SHOW CREATE TABLE 始终把 config 渲染为 'config' = '<redacted>',因为该 JSON 携带凭据。重建表时需要重新提供 config,或改用会话变量代替内联配置。

  • 连接共享:同一会话中配置相同的外部表与 sql_tvf/esql_tvf 共享同一个缓存连接。

  • DDL 阶段不检查连通性:CREATE EXTERNAL TABLE 只校验选项集合与配置 JSON 结构。错误的驱动或类型错误的选项会在 CREATE 阶段被拒绝。

示例

以下示例创建一个只读 SQL 外部表,查看脱敏后的 DDL,并演示未知选项在 CREATE 阶段被拒绝。该示例无需可用的数据源连接:

DROP DATABASE IF EXISTS foreign_ext_demo;
CREATE DATABASE foreign_ext_demo;
USE foreign_ext_demo;

CREATE EXTERNAL TABLE orders (
    id BIGINT,
    name VARCHAR(64),
    amount DECIMAL(12,2),
    created DATETIME
) ENGINE = SQL WITH ('config' = '{"driver":"mysql","dsn":"dump:111@tcp(127.0.0.1:6001)/foreign_ext_demo"}');

SHOW CREATE TABLE orders;

-- Expected-Success: false
CREATE EXTERNAL TABLE badopt (id INT) ENGINE = SQL WITH ('compress' = 'true');

DROP DATABASE foreign_ext_demo;

以下示例读取该表,需要可访问的数据源,因此仅作语法模板而非可直接粘贴执行的脚本:

-- the query text is supplied on the hidden column
SELECT id, name, amount FROM orders
WHERE __mo_query = 'select id, name, amount, created from src order by id';

-- a default query from the 'query' table option
SELECT id, name FROM with_default;

-- IN produces one scan per query text; the projection shows which query produced each row
SELECT id, __mo_query FROM orders
WHERE __mo_query IN (
  'select id, name, amount, created from src where id = 1',
  'select id, name, amount, created from src where id = 3')
ORDER BY id;

-- pushdown = 'true' lets the source apply a predicate MatrixOne can render
SELECT id FROM pushed WHERE __mo_query = 'select id, name, amount, created from src' AND id > 2;

相关参考