CREATE EXTERNAL TABLE¶

External table access data in external sources as if it were in a table in the database.

Description¶

External table access data in external sources as if it were in a table in the database.

You can connect to the database and create metadata for the external table using DDL.

The DDL for an external table consists of two parts: one part that describes the MatrixOne column types, and another part (the access parameters) that describes the mapping of the external data to the MatrixOne data columns.

This document describe how to create a new tables outside of the MatrixOne databases.

Syntax¶

Common syntax¶

> CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]table_name;
(
    name1 type1,
    name2 type2,
    ...
)

Syntax¶

## Create a external table for a local file (specify the compression format)
create external table t(...) localfile{"filepath"='<string>', "compression"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';

## Create a external table for a local file (if no compression format is specified, the format is auto, and the file format is automatically checked)
create external table t(...) localfile{"filepath"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';


## Create a external table for an S3 file (specify the compression format)
create external table t(...) URL s3option{"endpoint"='<string>', "access_key_id"='<string>', "secret_access_key"='<string>', "bucket"='<string>', "filepath"='<string>', "region"='<string>', "compression"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';

## Create a external table for an S3 file (if no compression format is specified, the format is auto, and the file format is automatically checked)
create external table t(...) URL s3option{"endpoint"='<string>', "access_key_id"='<string>', "secret_access_key"='<string>', "bucket"='<string>', "filepath"='<string>', "region"='<string>'} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';

Explanations¶

Parameter Description¶

Parameter	Description
endpoint	A endpoint is a URL that can conncect to AWS Web service. For example: s3.us-west-2.amazonaws.com
access_key_id	S3 Access key ID
secret_access_key	S3 Secret access key
bucket	S3 Bucket to access
filepath	relative file path
region	AWS S3 Area
compression	Compressed format of S3 files. If empty, it indicates uncompressed files. Supported fields or Compressed format are “auto”, “none”, “gzip”, “bzip2”, “flate”, “zlib”, and “lz4”.
auto	Compressed format: indicates that the file name extension automatically checks the compressed format of a file
none	Compressed format: indicates the uncompressed format, and the rest indicates the compressed format of the file

Example¶

create external table ex_table_cpk(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/cpk_table_1.csv'};

For more information on creating an external table with an s3option mapping to an S3 file, see Import the data from S3 Compatible object storage.

INFILE Parquet syntax¶

MatrixOne also supports creating external tables on Parquet files using the INFILE clause:

CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]table_name (
    column1 type1,
    column2 type2,
    ...
) infile{"filepath"='<path>', "format"='parquet'};

Parameter	Description
filepath	Path to the local file or directory containing Parquet files.
format	Set to `'parquet'` for Parquet-format external tables.

Hive-Style Partitioned Parquet External Tables¶

For Parquet files organized in Hive-style directory partitions (e.g., year=2024/month=01/data.parquet), use the following INFILE options:

CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]table_name (
    column1 type1,
    partition_col1 type_p1,
    partition_col2 type_p2,
    ...
) infile{
    "filepath"='<path>',
    "format"='parquet',
    "hive_partitioning"='true',
    "hive_partition_columns"='partition_col1,partition_col2'
};

INFILE option	Required	Description
`hive_partitioning`	Yes	Set to `'true'` to enable Hive-style partition discovery.
`hive_partition_columns`	Yes	Comma-separated list of partition column names. Must match columns declared in the table schema (case-insensitive).

Partition column behavior:

Partition columns are declared as regular columns in the table schema and are filled from directory names.
Column name matching is case-insensitive.
= and IN predicates on partition columns are pruned at the file-listing stage for reduced I/O.
Directory named __HIVE_DEFAULT_PARTITION__ maps to SQL NULL for the partition column.
The virtual column __mo_filepath returns the source file path for each row and is available on all external tables.

Example — Single-level partition:

DROP DATABASE IF EXISTS hive_single_demo;
CREATE DATABASE hive_single_demo;
USE hive_single_demo;

CREATE EXTERNAL TABLE hive_single (
    id INT,
    name VARCHAR(50),
    year INT
) infile{
    "filepath"='$resources/hive_partition/single_level/',
    "format"='parquet',
    "hive_partitioning"='true',
    "hive_partition_columns"='year'
};

SELECT * FROM hive_single WHERE year = 2024;
SELECT COUNT(DISTINCT __mo_filepath) FROM hive_single;

DROP DATABASE hive_single_demo;

Example — Multi-level partition:

DROP DATABASE IF EXISTS hive_multi_demo;
CREATE DATABASE hive_multi_demo;
USE hive_multi_demo;

CREATE EXTERNAL TABLE hive_multi (
    id INT,
    value DOUBLE,
    year INT,
    month VARCHAR(2)
) infile{
    "filepath"='$resources/hive_partition/multi_level/',
    "format"='parquet',
    "hive_partitioning"='true',
    "hive_partition_columns"='year,month'
};

SELECT * FROM hive_multi WHERE year = 2024 AND month = '01';

DROP DATABASE hive_multi_demo;

Constraints and notes for Hive external tables:

The 'format' must be 'parquet'. CSV and other formats are not supported with hive partitioning.
Partition columns cannot be of VECTOR type.
NOT NULL partition columns reject directories named __HIVE_DEFAULT_PARTITION__.
URL-encoded directory names (containing %) are not currently supported.
When a Parquet file contains a physical column that overlaps with a partition column name, the partition value from the directory path overrides the physical column value.
LOAD DATA INFILE into a hive-partitioned external table is rejected.

Writable External Tables¶

Writable external tables allow INSERT ... SELECT and LOAD DATA to write rows into stage-backed external files via the WRITE_FILE_PATTERN option, supporting CSV and JSONLine output formats with full column-type fidelity and round-trip guarantees.

Description¶

Starting from v4.1.0, MatrixOne supports writing to external tables backed by a stage. When an external table is created with the WRITE_FILE_PATTERN option in the INFILE clause, INSERT ... SELECT and LOAD DATA statements can write rows into the external table. Each writer pipeline produces a distinct file under the stage directory; reading back through the table’s read glob (FILEPATH) sees all written files from all pipelines.

The writer respects the table’s FIELDS and LINES options — field terminator, enclosure character, escape character, line terminator, and LINES STARTING BY — so that written files can be read back with perfect fidelity. Supported output formats are CSV (default) and JSONLine ('format'='jsonline').

Syntax¶

Creating a Writable External Table¶

> CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]table_name (
    column1 type1,
    column2 type2,
    ...
) INFILE{
    "filepath"='<stage_glob>',
    "format"='csv|jsonline',
    "write_file_pattern"='<stage_write_pattern>'
    [, "jsondata"='object']
} [FIELDS TERMINATED BY '<char>' [ENCLOSED BY '<char>' [ESCAPED BY '<char>']]]
  [LINES TERMINATED BY '<string>' [STARTING BY '<string>']];

The WRITE_FILE_PATTERN is a stage path template (stage://<stage_name>/<prefix>_%U.<ext>) that controls how output files are named.

Writing to an External Table¶

> INSERT INTO ext_table SELECT ... FROM source_table;
> INSERT INTO ext_table VALUES (...);
> LOAD DATA INFILE '<file>' INTO TABLE ext_table [FIELDS ...] [LINES ...];

Options and Parameters¶

Option	Description
`WRITE_FILE_PATTERN`	Stage path template for output files. Each writer pipeline expands the pattern into a distinct path. Must be a `stage://...` path.
`'format'`	Must be `'csv'` or `'jsonline'`. Parquet and other formats are not writable.
`'jsondata'`	Required for JSONLine writable tables. Must be `'object'`. The `'array'` mode is not writable.

Pattern Directives¶

Directive	Description
`%U`	Expands to a unique identifier (32 hex characters). The recommended choice.
`%<n>N`	Expands to a zero-padded decimal counter of width `n`. `n` must be at least 6 to keep parallel writers apart.
`%Y`, `%m`, `%d`	strftime-style date directives. Produce the same value across writers and cannot be the only directive in the pattern.

The pattern must contain at least one %U or %<n>N so that parallel pipelines write to distinct files. Patterns with only date directives (e.g., out-%Y%m%d.csv) are rejected.

CSV Output Format¶

CSV is the default writable format. The writer supports the full set of CSV options:

FIELDS TERMINATED BY: Any single character or multi-character string. The first byte must not be a quote character (", '), CR, LF, or NUL.
ENCLOSED BY: A single character applied with OPTIONALLY ENCLOSED semantics — values are enclosed only when they contain the field terminator, the enclosure character itself, the escape character, a line terminator byte, or LINES STARTING BY prefix bytes. An explicit empty string '' falls back to the default ".
ESCAPED BY: A single byte used to escape the enclosure character, escape character itself, and special bytes within values. Must not be a control character. Must not equal the enclosure byte. Setting it to '' disables escaping; enclosure doubling still protects special characters.
LINES TERMINATED BY: The record separator. Defaults to '\n'; also supports '\r\n' and custom multi-character terminators.
LINES STARTING BY: A prefix emitted before every record. The reader strips it on readback. Mutually exclusive with COMMENT.

Auto-Enclosure Rules¶

The writer automatically encloses a value when its string representation contains any byte that would confuse the reader’s tokenizer:

The field terminator (or any of its bytes for multi-char terminators)
The enclosure character
The escape character (when escaping is enabled)
Any byte of the line terminator
Any byte of the LINES STARTING BY prefix
A leading # when a COMMENT marker is configured

This ensures typed values like DATE (2026-06-12) and DECIMAL (123.45) round-trip even when the field terminator byte appears inside them.

NULL Handling¶

SQL NULL is written as the escaped NULL sentinel \N. An empty string '' and a true NULL are distinct and round-trip correctly.

JSONLine Output Format¶

JSONLine writable tables ('format'='jsonline') write each row as a single JSON object per line.

Requirements:

'jsondata' must be set to 'object' (the writer emits objects, not arrays).
BIT(N) and BLOB columns are not supported for JSONLine — raw bytes cannot round-trip through JSON strings.
COMMENT is not supported (JSON objects always begin with {, which would match as a comment prefix on every line).
Only LINES TERMINATED BY '\n' or '\r\n' is supported; custom line terminators are rejected.
FIELDS ENCLOSED BY has no effect on JSONLine output; JSON quoting follows the JSON specification.

SHOW CREATE TABLE Round-Trip¶

SHOW CREATE TABLE on a writable external table emits the full DDL including WRITE_FILE_PATTERN. The table can be dropped and recreated from this output — the new table reads existing stage files and accepts new writes. Empty optional keys are omitted from the output.

Column Type Support¶

Supported column types for writable external tables in both CSV and JSONLine formats:

Integer: TINYINT, SMALLINT, INT, BIGINT (signed and unsigned)
Floating-point: FLOAT, DOUBLE
Fixed-point: DECIMAL
String: CHAR, VARCHAR, TEXT
Date/time: DATE, DATETIME, TIMESTAMP, TIME
Boolean: BOOL
JSON: JSON
Enumeration: ENUM
Bit (CSV only): BIT(N) — raw bytes are enclosed and escaped. Not supported in JSONLine.

Writable external tables do not support:

VECTOR columns
BLOB columns in JSONLine format
BIT columns in JSONLine format
Generated columns (col INT AS (expr) STORED)
AUTO_INCREMENT columns

NOT NULL Enforcement¶

NOT NULL constraints are enforced during writes. Attempting to insert a NULL value into a NOT NULL column raises a constraint violation error.

LINES STARTING BY Round-Trip¶

When LINES STARTING BY is specified, the writer emits the prefix before every record, and the reader strips it on readback. Values containing the prefix string are auto-enclosed to prevent false record-boundary detection.

Custom ESCAPED BY¶

The FIELDS ESCAPED BY option controls how special characters are escaped in CSV output. Custom escape characters (e.g., '!') are supported. The writer doubles the escape character in every field; the reader unescapes both quoted and unquoted fields. Setting ESCAPED BY '' disables escaping entirely (enclosure doubling still protects quotes and separators).

Multi-Pipeline and Multi-CN Write Dispatch¶

On multi-CN clusters, large INSERT ... SELECT statements compile to multi-CN plans. Source scan scopes with the external-write insert are dispatched to remote CNs through the pipeline protocol. Each CN writes to a distinct file via the %U directive in WRITE_FILE_PATTERN. On a single CN, the insert runs with parallel pipelines, each owning one writer and file. Results are identical across deployment topologies.

Example¶

The following example demonstrates CSV and JSONLine writable external tables, multi-insert accumulation, SHOW CREATE TABLE round-trip, and NOT NULL enforcement.

DROP DATABASE IF EXISTS dbextwrite;
CREATE DATABASE dbextwrite;
USE dbextwrite;

CREATE STAGE wstage URL = 'file:///$resources/into_outfile/stage';

-- Source data for INSERT ... SELECT
CREATE TABLE src(a INT, b VARCHAR(20), c DOUBLE);
INSERT INTO src VALUES (1, 'alice', 1.5), (2, 'bob', 2.5), (3, 'carol', 3.5);

-- Create a CSV writable external table
CREATE EXTERNAL TABLE ext_csv(a INT, b VARCHAR(20), c DOUBLE)
INFILE{'FILEPATH'='stage://wstage/dbextwrite_csv_*.csv', 'FORMAT'='csv', 'WRITE_FILE_PATTERN'='stage://wstage/dbextwrite_csv_%U.csv'}
FIELDS TERMINATED BY ',';

-- Write rows via INSERT ... SELECT and read them back
INSERT INTO ext_csv SELECT * FROM src;
SELECT * FROM ext_csv ORDER BY a;

-- A second insert writes a new file; the read glob sees all files
INSERT INTO ext_csv SELECT a+10, b, c FROM src;
SELECT COUNT(*) FROM ext_csv;

-- SHOW CREATE TABLE preserves WRITE_FILE_PATTERN for re-creation
SHOW CREATE TABLE ext_csv;

-- Create a JSONLine writable external table
CREATE EXTERNAL TABLE ext_jl(a INT, b VARCHAR(20), c DOUBLE)
INFILE{'FILEPATH'='stage://wstage/dbextwrite_jl_*.jl', 'FORMAT'='jsonline', 'JSONDATA'='object', 'WRITE_FILE_PATTERN'='stage://wstage/dbextwrite_jl_%U.jl'}
FIELDS TERMINATED BY ',';

INSERT INTO ext_jl SELECT * FROM src;
SELECT * FROM ext_jl ORDER BY a;

-- NOT NULL is enforced on writable external tables
CREATE EXTERNAL TABLE ext_nn(a INT NOT NULL, b VARCHAR(10))
INFILE{'FILEPATH'='stage://wstage/dbextwrite_nn_*.csv', 'FORMAT'='csv', 'WRITE_FILE_PATTERN'='stage://wstage/dbextwrite_nn_%U.csv'}
FIELDS TERMINATED BY ',';

INSERT INTO ext_nn VALUES (1, 'ok');
-- Expected-Success: false
INSERT INTO ext_nn VALUES (NULL, 'boom');

DROP TABLE IF EXISTS ext_csv;
DROP TABLE IF EXISTS ext_jl;
DROP TABLE IF EXISTS ext_nn;
DROP TABLE IF EXISTS src;
DROP STAGE IF EXISTS wstage;
DROP DATABASE dbextwrite;

LOAD DATA into a Writable External Table¶

LOAD DATA INFILE writes into a writable external table. The input file’s format is described by the LOAD DATA statement’s own FIELDS/LINES clauses (describing the source), while the output format is governed by the external table’s stored options.

DROP DATABASE IF EXISTS dbextwrite;
CREATE DATABASE dbextwrite;
USE dbextwrite;

CREATE STAGE wstage URL = 'file:///$resources/into_outfile/stage';

CREATE EXTERNAL TABLE ext_load(col1 DATE NOT NULL, col2 DATETIME, col3 TIMESTAMP, col4 BOOL)
INFILE{'FILEPATH'='stage://wstage/dbextwrite_load_*.csv', 'FORMAT'='csv', 'WRITE_FILE_PATTERN'='stage://wstage/dbextwrite_load_%U.csv'}
FIELDS TERMINATED BY ',';

LOAD DATA INFILE '$resources/load_data/time_date_1.csv' INTO TABLE ext_load FIELDS TERMINATED BY ',';
SELECT * FROM ext_load ORDER BY col1;

DROP TABLE IF EXISTS ext_load;
DROP STAGE IF EXISTS wstage;
DROP DATABASE dbextwrite;

Limitations¶

UPDATE, DELETE, and TRUNCATE are not supported on writable external tables. TRUNCATE is explicitly rejected because stage files are not managed by the table.
REPLACE INTO on external tables is rejected.
AUTO_INCREMENT columns are not supported.
Generated columns (col INT AS (expr) STORED) are not supported.
IGNORE ... LINES is not supported; the writer emits no header lines.
Compression (explicit 'compression' option or file-suffix-inferred compression) is not supported. The writer always emits uncompressed files.

Error Cases¶

The following configurations are rejected at CREATE EXTERNAL TABLE time:

WRITE_FILE_PATTERN not starting with stage:// (must be a stage path).
WRITE_FILE_PATTERN missing %U or %<n>N directive (parallel writers would overwrite each other).
'format' other than 'csv' or 'jsonline' (only these formats are writable).
'format'='jsonline' with 'jsondata'='array' (writer emits objects, not arrays).
'format'='jsonline' with BIT or BLOB columns.
'format'='jsonline' with custom LINES TERMINATED BY (only '\n' or '\r\n').
'format'='jsonline' with COMMENT option.
'compression' set to a non-empty value.
FILEPATH suffix implying compression (e.g., .csv.gz).
Duplicate keys in the INFILE option list.
FIELDS ESCAPED BY set to a control-character byte (reader maps escape sequences to control chars).
FIELDS ESCAPED BY equal to ENCLOSED BY.
ENCLOSED BY byte occurring in a field/line terminator or LINES STARTING BY.
FIELDS TERMINATED BY starting with a quote, CR, LF, or NUL byte.
COMMENT and LINES STARTING BY together.
COMMENT prefix colliding with enclosure byte, escape byte, NULL sentinel \N, or field terminator byte.

Constraints¶

SELECT is supported on all external tables.
INSERT ... SELECT and INSERT ... VALUES are supported only on writable external tables (those with WRITE_FILE_PATTERN).
LOAD DATA INFILE is supported on writable external tables.
UPDATE, DELETE, TRUNCATE, and REPLACE INTO are not supported on any external table.
AUTO_INCREMENT, generated columns, and IGNORE ... LINES are not supported on writable external tables.
Only CSV and JSONLine formats can be written to. Parquet external tables are read-only.