External Table Error Mode

File-backed external tables expose three hidden columns that report, per row, the source line number, the error message, and the raw text of a record that failed to load. Requesting these columns turns a load that would fail on the first bad record into one that reports the bad records as rows.

Description

By default, scanning a CSV or JSONLine external table stops with an error on the first record that cannot be parsed or converted. When a query selects either error column, the scan instead reports bad records as rows: good records keep their values, and bad records carry their error status. This lets a single scan route good records into a destination table and bad records into a rejects table.

Syntax

SELECT a, s, __mo_file_line, __mo_error_message, __mo_error_text FROM em_csv;

Select the hidden error columns by name from a CSV or JSONLine external table. Selecting __mo_error_message or __mo_error_text enables error mode for that scan.

Arguments

The hidden columns are selected like ordinary columns:

Column	Type	Meaning
__mo_file_line	bigint	The line number in the source file where the record starts.
__mo_error_message	varchar	The error message for the record, or NULL when the record loaded cleanly.
__mo_error_text	varchar	The raw source text of the failed record.

The columns are hidden from SELECT *, DESC, and SHOW CREATE TABLE, but selectable by name. A record’s failure is a property of the record, not of the projection: selecting __mo_error_message (or __mo_error_text) on a bad record reports it regardless of which declared columns are also selected.

Usage Notes

• Good vs bad: good records have __mo_error_message IS NULL; bad records have __mo_error_message IS NOT NULL. Use these predicates to split rows.

• __mo_file_line alone is not enough: selecting only __mo_file_line is position metadata, not a request to tolerate errors; the scan still fails on the first bad record. Select either error column to enable error mode.

• Parquet is unaffected: Parquet is decoded as typed columnar values, not text, so there is no line number or record text to report; the error columns do not resolve there.

• Reserved names: the three column names are reserved and cannot be declared on a user table.

Examples

The following example creates a CSV external table and shows that the error columns stay hidden from the table shape, then demonstrates that a reserved name is rejected. It performs no scan, so it needs no source file to exist at DDL time:

DROP DATABASE IF EXISTS ext_error_demo;
CREATE DATABASE ext_error_demo;
USE ext_error_demo;

CREATE EXTERNAL TABLE em_csv (a INT, s VARCHAR(20))
INFILE{'filepath'='/tmp/external_table_file/error_mode.csv'}
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

DESC em_csv;
SHOW CREATE TABLE em_csv;

-- Expected-Success: false
CREATE TABLE em_reserved (a INT, __mo_error_message VARCHAR(10));

DROP DATABASE ext_error_demo;

Splitting good and bad records requires reading the file, so it is shown as a syntax template rather than a paste-and-run script:

-- report the bad records instead of failing
SELECT a, s, __mo_file_line, __mo_error_message, __mo_error_text FROM em_csv;

-- route good records into a destination table and bad records into a rejects table
INSERT INTO dest (id, name)
SELECT a, s FROM em_csv WHERE __mo_error_message IS NULL;

INSERT INTO rejects (line, msg, txt)
SELECT __mo_file_line, __mo_error_message, __mo_error_text
FROM em_csv WHERE __mo_error_message IS NOT NULL;

See Also

• CREATE EXTERNAL TABLE