Define an External Table with Single Row Error Isolation
The following example logs errors internally in Greenplum Database and sets an error threshold of 10 errors.
=# CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ('gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-2:8082/*') FORMAT 'TEXT' (DELIMITER '|') LOG ERRORS SEGMENT REJECT LIMIT 10 ROWS;
Use the built-in SQL function gp_read_error_log('external_table') to read the error log data. This example command displays the log errors for ext_expenses:
SELECT gp_read_error_log('ext_expenses');
For information about the format of the error log, see Viewing Bad Rows in the Error Table or Error Log.
The built-in SQL function gp_truncate_error_log('external_table') deletes the error data. This example deletes the error log data created from the previous external table example :
SELECT gp_truncate_error_log('ext_expenses');
The following example creates an external table, ext_expenses, sets an error threshold of 10 errors, and writes error rows to the table err_expenses.
=# CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text ) LOCATION ('gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-2:8082/*') FORMAT 'TEXT' (DELIMITER '|') LOG ERRORS INTO err_expenses SEGMENT REJECT LIMIT 10 ROWS;