CREATE EXTERNAL TABLE … ENGINE = SQL|ESQL¶

Map a MatrixOne external table to a foreign SQL database (ENGINE = SQL) or an Elasticsearch ES|QL source (ENGINE = ESQL) whose rows are produced by a query text supplied at scan time. The table is read-only, and the source query is carried by the hidden __mo_query column or the query table option.

Description¶

CREATE EXTERNAL TABLE ... ENGINE = SQL and CREATE EXTERNAL TABLE ... ENGINE = ESQL map a MatrixOne external table to a foreign data source that answers a query at scan time. ENGINE = SQL speaks to a SQL database through a {"driver": "...", "dsn": "..."} config (MySQL and PostgreSQL drivers are supported). ENGINE = ESQL speaks to an Elasticsearch cluster through an ES|QL config.

The table is read-only: SELECT is supported, and writes are rejected. CREATE EXTERNAL TABLE validates the engine options and the config JSON shape but does not dial the source; the connection is opened lazily on the first scan.

Syntax¶

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

Arguments¶

Option	Engine	Required	Description
`config`	SQL, ESQL	No	Connection JSON. For SQL: `{“driver”:”mysql
`query`	SQL, ESQL	No	Default query text used when a `SELECT` has no `__mo_query` predicate.
`pushdown`	SQL	No	`false` (default) sends the query text verbatim and MatrixOne evaluates predicates locally; `true` wraps the text as a derived table so the source applies the predicates MatrixOne can render. ESQL rejects this option.

The `__mo_query` hidden column¶

Like __mo_filepath on file-backed external tables, ENGINE = SQL|ESQL tables expose a hidden trailing column __mo_query of type varchar. It is hidden from SELECT *, DESC, and SHOW COLUMNS, but selectable by name. The query text plays the role of a file path: each distinct query text is one “file” to scan, and each returned row carries the text of the query that produced it.

At scan time MatrixOne derives the query list from the predicate:

__mo_query = '<text>' produces one candidate.
__mo_query IN ('<text1>', '<text2>', ...) produces several candidates.
OR of the above produces the union.
Other conjuncts such as __mo_query LIKE '...' do not generate candidates but may prune the derived list.

If no candidate is derived and the table has a query option, that text is used. Otherwise the scan fails.

The query text is sent to the source verbatim. It must return the table’s declared columns in declared order, and the field count must match.

Usage Notes¶

Read-only: writes into an ENGINE = SQL|ESQL external table are rejected. INSERT ... SELECT FROM such a table is the intended ETL path.
Secret redaction: SHOW CREATE TABLE always renders config as 'config' = '<redacted>', because the JSON carries credentials. Recreate tables with config re-supplied, or use the session variable instead of an inline config.
Connection sharing: an external table and a sql_tvf/esql_tvf with the same config in the same session share one cached connection.
No DDL-time connectivity check: CREATE EXTERNAL TABLE validates only the option set and the config JSON shape. A bad driver or a wrong-typed option is rejected at CREATE time.

Examples¶

The following example creates a read-only SQL external table, inspects its redacted DDL, and demonstrates that unknown options are rejected at CREATE time. It needs no live source connection:

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 next examples read from the table and require a reachable source, so they are syntax templates rather than a paste-and-run script:

-- 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;