Vertica: Input record 1 has been rejected (Too few columns found)

Question

I am trying to copy file from Hadoop to a Vertica table and get the an error. The problem is same copy sometimes pass and some times fails,any idea?

The Error:

Caused by: java.sql.SQLException: [Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Too few columns found) at com.vertica.util.ServerErrorData.buildException(Unknown Source) at com.vertica.dataengine.VResultSet.fetchChunk(Unknown Source) at com.vertica.dataengine.VResultSet.initialize(Unknown Source) at com.vertica.dataengine.VQueryExecutor.readExecuteResponse(Unknown Source) at com.vertica.dataengine.VQueryExecutor.handleExecuteResponse(Unknown Source) at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source) at com.vertica.jdbc.SStatement.executeNoParams(Unknown Source) at com.vertica.jdbc.SStatement.execute(Unknown Source) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:420) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395) ... 27 more Caused by: com.vertica.util.ServerException: [Vertica]VJDBC ERROR: COPY: Input record 1 has been rejected (Too few columns found) ... 37 more

The query:

COPY temp__FACT (ACCOUNT_ID,VISIT_ID,CAMPAIGN_SEQ,EVENT_SEQ) 
SOURCE public.Hdfs(url='DATA-r-00000') DELIMITER AS ',' ENCLOSED BY '"' 
REJECTMAX 1 ABORT ON ERROR DIRECT NO COMMIT;

Answer 1

We found the problem:

One node in Hadoop was "shaky" therefore each time Vertica accessed this node the file was empty.

After stopping this node problem solved.

We found 2 issues: 1. Node was "Shaky" but still had ping therefor system "Thinks" it is alive 2. Vertica failed to read file from this node but did not retry on other nodes the files exists in

Answer 2

COPY: Input record 1 has been rejected (Too few columns found)

Check the column counts are correct. I would advise using reject files or reject tables.

Answer 3

So since you checked your column count, make sure your delimiter isn't in the input.

Answer 4

This has been resolved but for reference for other who might run into same error message. I was running python ETL to pull data from somewhere and write to a Vertica table with COPY statement using python. I started seeing following errors.

Error:Severity: ERROR, Message: COPY: Input record 1 has been rejected (Too few columns found), Sqlstate: 22V04, Routine: reject, File: /data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_3_1-x_grader/build/vertica/EE/UDL/RejectRows.cpp, Line: 284, Error Code: 2035,

Based on rejected file, turns \n has slipped into the data and was effective "cutting" the COPY statement where columns are defined.