Why bulk loading a Vertica database table does not load all lines

vertica

I have a small csv file I would like to bulk load into a Vertica 6.1 database table, this file has 92 lines.

dbadmin=> COPY games FROM '/tmp/games.csv' DELIMITER ',';
 Rows Loaded
-------------
          44

The file is 92 lines, but Vertica only loads 44 lines.
As a test, I tried loading the same file in MySQL, and all 92 lines loaded fine.

I verified the file has 92 lines.

[root@localhost tmp]# cat /tmp/games.csv | wc -l
92

I opened the file and verified it has no unusual characters.

Any ideas?

Best Answer

Try option ABORT ON ERROR - will output a reason for rejection.

COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' ABORT ON ERROR

Check rejected and exception files or define it in COPY: 

COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' REJECTED DATA 'path' EXCEPTIONS 'path'

https://community.vertica.com/vertica/topics/bulk_loading

(Original URL is dead; you can try this one instead)