We are importing bulk data i.e millions of records. We observed that due to a null value for a column , the import is getting failed. Is there a way we could skip the import error when a record is missing and keep the job running?
Add a comment
|
1 Answer
The PostgreSQL server \COPY command is very simple and just aborts on a single failure. You might think that it could do far better (I know I do), but there's a reason that the PostgreSQL codebase is so compact with respect to MySQL's (by a factor of ~ 10/1).
However, there is the (very) nice pgloader programme which compensates for this at the price of having to run a separate utility.
Of course, if you're good at the PL/pgSQL language (internal to the the server), then maybe you could explore that route - but why reinvent the wheel? Python and Perl also have internal PostgreSQL options. Then of course, there's all the languages under the sun external to the server.
From the manual:
PgLoader Reference Manual
pgloader loads data from various sources into PostgreSQL. It can transform the data it reads on the fly and submit raw SQL before and after the loading. It uses the COPY PostgreSQL protocol to stream the data into the server, and manages errors by filling a pair of reject.dat and reject.log files.
which appears to be right up your alley?
The way it works is: (sorry for the long quote)
TL;DR - pgloader loads a batch (configurable) at a time. On failure, it "marks the spot", uses \COPY again up until that point, stops, then puts the bad record into a file and continues from bad-record + 1.
Batches And Retry Behaviour
To load data to PostgreSQL, pgloader uses the COPY streaming protocol. While this is the faster way to load data, COPY has an important drawback: as soon as PostgreSQL emits an error with any bit of data sent to it, whatever the problem is, the whole data set is rejected by PostgreSQL.
To work around that, pgloader cuts the data into batches of 25000 rows each, so that when a problem occurs it's only impacting that many rows of data. Each batch is kept in memory while the COPY streaming happens, in order to be able to handle errors should some happen.
When PostgreSQL rejects the whole batch, pgloader logs the error message then isolates the bad row(s) from the accepted ones by retrying the batched rows in smaller batches. To do that, pgloader parses the CONTEXT error message from the failed COPY, as the message contains the line number where the error was found in the batch, as in the following example:
CONTEXT: COPY errors, line 3, column b: "2006-13-11"
Using that information, pgloader will reload all rows in the batch before the erroneous one, log the erroneous one as rejected, then try loading the remaining of the batch in a single attempt, which may or may not contain other erroneous data.
At the end of a load containing rejected rows, you will find two files in the root-dir location, under a directory named the same as the target database of your setup. The filenames are the target table, and their extensions are .dat for the rejected data and .log for the file containing the full PostgreSQL client side logs about the rejected data.