把坏行送到拒绝表¶
在 MatrixOne 服务端运行 printf '1,ok\nbad,broken\n2,good\n' > /tmp/mo-cookbook-errors.csv。仅选择 __mo_file_line 不会启用容错,必须选错误消息或原文列。
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;
示例应得到 good_rows=2、bad_rows=1。修正坏行后可重新加载到目标表;源文件和目标表应有独立清理策略。参见 错误模式参考。