Route malformed rows to a rejects table

On the MatrixOne server, run printf '1,ok\nbad,broken\n2,good\n' > /tmp/mo-cookbook-errors.csv. Selecting only __mo_file_line does not enable error tolerance; select an error message or raw text column.

CREATE DATABASE cookbook_rejects;
CREATE EXTERNAL TABLE cookbook_rejects.raw(id INT, label VARCHAR(20))
INFILE{'filepath'='/tmp/mo-cookbook-errors.csv','format'='csv'}
FIELDS TERMINATED BY ',';
CREATE TABLE cookbook_rejects.good(id INT, label VARCHAR(20));
CREATE TABLE cookbook_rejects.bad(line_no BIGINT, message TEXT, source_text TEXT);
INSERT INTO cookbook_rejects.good SELECT id,label FROM cookbook_rejects.raw
WHERE __mo_error_message IS NULL;
INSERT INTO cookbook_rejects.bad
SELECT __mo_file_line,__mo_error_message,__mo_error_text
FROM cookbook_rejects.raw WHERE __mo_error_message IS NOT NULL;
SELECT (SELECT COUNT(*) FROM cookbook_rejects.good) AS good_rows,
       (SELECT COUNT(*) FROM cookbook_rejects.bad) AS bad_rows;
DROP DATABASE cookbook_rejects;

The example should return good_rows=2 and bad_rows=1. After repairing the bad record, reload it into the target table; manage the source file and target data separately. See the error mode reference.