Row parsing for external tables

For loads, the sequence of rows from the external data file is parsed row by row and converted into internal records of the external table.

There can be errors during the parsing of each row or each column. For example, there can be errors in identifying the column value itself, as in the case of a missing delimiter. There can also be errors during the conversion from the external format to the internal records of the external table, such as for alphabetic characters that are specified for an integer column in the text-delimited format.

Each error is logged in detail in an nzlog file, and bad rows are logged in an nzbad file. These files help you to identify bad rows in the external data file and correct them for reloading. Depending on the load options of the external table, each bad row causes either the row to be skipped or the entire load to be aborted. Also, each bad column of a bad row can cause the rest of the row to be ignored; if it is possible to recover, the load can continue to parse subsequent columns of the same row.

If there is an error in an expression (arithmetic operation) on the external table columns, the entire load is aborted, and the transaction is rolled back. Due to where processing for the expression takes place, errors of this nature are not logged in nzbad or nzlog files. When the processing reaches the SQL engine, the external table is treated as if it is a normal table.

Unlike an external table that has external rows in an ordered sequence, normal user tables have no implicit row order other than hidden rowid columns. Without rowids, it is not possible to identify the bad row in an SQL engine. In the external table case, the system returns an error that a particular column caused an error, without identifying the bad row. It is as if the query was selecting from a normal table and inserting into another normal table, with some row that caused the error during insertion.