Wrong number of columns! COPYing 99 columns from a source file containing: FEWER columns.

drno-reg

Hello,

COPY loader return error, BUT I count column in row loaded normally - 99 and column in reject row 99.

Why this error else can occurs ?

marcothesane

Can you paste the CREATE TABLE of the target table into your post, and also exactly the rejected row?

drno-reg

create table ALL_EVENTS
(
id VARCHAR(500) not null,
version NUMBER(10) not null,
title Long Varchar,
description Long Varchar,
solution Long Varchar,
state VARCHAR(500),
severity VARCHAR(500),
priority VARCHAR(500),
category VARCHAR(500),
subcategory VARCHAR(500),
type VARCHAR(500),
related_ci_hint Long Varchar,
om_service_id Long Varchar,
related_ci_id VARCHAR(500),
related_ci_type VARCHAR(500),
node_id VARCHAR(500),
node_type VARCHAR(500),
sequence_number NUMBER(19),
nodehints_hint VARCHAR(8000),
nodehints_dnsname VARCHAR(500),
nodehints_ipaddress VARCHAR(500),
nodehints_coreid VARCHAR(500),
sourcecihints_hint Long Varchar,
sourcecihints_dnsname VARCHAR(500),
sourcecihints_ipaddress VARCHAR(500),
sourcecihints_coreid VARCHAR(500),
originating_dnsname VARCHAR(500),
originating_ipaddress VARCHAR(500),
originating_coreid VARCHAR(500),
sending_dnsname VARCHAR(500),
sending_ipaddress VARCHAR(500),
sending_coreid VARCHAR(500),
om_user VARCHAR(500),
assigned_user NUMBER(10),
assigned_group NUMBER(19),
cause_id VARCHAR(500),
time_created TIMESTAMP(6),
time_changed TIMESTAMP(6),
time_state_changed TIMESTAMP(6),
time_received TIMESTAMP(6) not null,
duplicate_count NUMBER(10),
eti_hint VARCHAR(500),
eti_subcomponent_id VARCHAR(500),
ua_host_dnsname VARCHAR(500),
ua_host_ipaddress VARCHAR(500),
ua_host_coreid VARCHAR(500),
ua_call Long Varchar,
ua_status VARCHAR(500),
ua_add_anno NUMBER(1),
ua_will_resolve NUMBER(1),
aa_host_dnsname VARCHAR(500),
aa_host_ipaddress VARCHAR(500),
aa_host_coreid VARCHAR(500),
aa_call Long Varchar,
aa_status VARCHAR(500),
aa_add_anno NUMBER(1),
aa_will_resolve NUMBER(1),
application VARCHAR(500),
object VARCHAR(500),
event_key Long Varchar,
close_key_pattern Long Varchar,
original_data Long Varchar,
log_only NUMBER(1),
no_dedup NUMBER(1),
received_on_ci_downtime NUMBER(1),
instruction_available NUMBER(1),
source_ci_id VARCHAR(500),
source_ci_type VARCHAR(500),
policy_type VARCHAR(500),
policy_name VARCHAR(500),
condition_id VARCHAR(500),
condition_name VARCHAR(500),
original_id VARCHAR(500),
correlation_type VARCHAR(500),
correlation_rule_id VARCHAR(500),
correlation_weight VARCHAR(500),
eti_indicator_id VARCHAR(500),
eti_value_id VARCHAR(500),
eti_reset_value_id VARCHAR(500),
eti_numeric_value FLOAT,
control_dnsname VARCHAR(500),
control_server_port NUMBER(10),
control_server_id VARCHAR(500),
control_external_id VARCHAR(500),
control_external_url VARCHAR(500),
rule_name VARCHAR(500),
transfer_state VARCHAR(500),
initiated_by_id NUMBER(10),
source_dnsname VARCHAR(500),
source_ia_management_port NUMBER(10),
source_ia_management_protocol VARCHAR(500),
source_server_id VARCHAR(500),
source_external_id VARCHAR(500),
source_external_url VARCHAR(500),
cires_hint_count NUMBER(10),
cires_matched_hint_count NUMBER(10),
cires_quality_metric NUMBER(10),
cires_status Long Varchar,
received_as_notify NUMBER(1)
)
PARTITION BY EXTRACT (MONTH FROM time_received)
;

drno-reg

content reject_row

"2ba84514-400d-71e7-1e5f-0a4805420000";"1";"Many files in catalog «C:\oris\UPS!Unrecognized:.» = 1, upper threshold 1";"Gadukino host01 Count files in catalogies «C:\oris\SUPS!Unrecognized:.» = 1, upper threshold 1 ORIS OS";"";"CLOSED";"MINOR";"MEDIUM";"BASIC_SITESCOPE_EVENT (Directory)";"Catalogies - count files include catalogies:DIR_FilesCount:C:\oris\UPS!Unrecognized:.@host01";"SiteScopeMonitor:1227687353:1227687355@@host01";"7f8021ea247d965c4092f41d63ac9577";"fileshare";"912bba5b7fd438e480385a54bbbda39f";"nt";"158963838";"host01|192.168.0.1";"host01";"192.168.0.1";"";"SiteScope@@sitescope01";"sitescope01";"192.168.0.1";"f955cb42-6ba0-7586-1f18-942c652caf25";"HPBSMAPPHOST";"192.168.0.1";"";"";"";"";"";"29";"602";"";"2017-05-23 23:11:36";"2017-06-16 06:23:58.725000";"2017-06-16 06:23:58.378000";"2017-05-23 23:11:37.109000";"0";"System_Performance:MINOR";"Catalogies - count files include all folders:DIR_FilesCount:C:\oris!Unrecognized:.";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"ORIS OS";"SiteScope:Sitescope01:Gadukino: ORIS OSS: host01: Catalogies: [C:~oris~UPS~NSP~CDR~!Unrecognized~:.]:DIR_FilesCount";"sitescope01:b8ef0f50-403f-46c6-84d4-bfe0bc498b9c:Alert:";"sitescope01:b8ef0f50-403f-46c6-84d4-bfe0bc498b9c:Alert<>";"Gadukino

24.05.2017 02:11:36
Host: host01
Title: Count files in folder «C:\oris\UPS!Unrecognized:.*» = 1, upper threshold 1
System: ORIS OS
ID TM: 10091000
URL by TM: http://superorders/monpoint/OpenMonPoint.jsf?id=10091000
Scheduler time: 00:00-23:59XSan,Mon
Emergensy team: Capitan America,Iron Man,АМ
URL Emergency: http://duty/DutyList/web/DutyList.jsf?groupId=399991500
Event ID: 2ba84514-400d-71e7-1e5f-0a4805420000
URL by event: https://hpbsm/opr-web/opr-evt-details?eventId=2ba84514-400d-71e7-1e5f-0a4805420000
List operations for duty team: If you got Healph event ""Heartbeat"" create incident on emegensy team Microsoft
URL by IMS: https://ims/view.asp?BLOCK=MAIN&ID=696000@host01"" resolved.";"0"

drno-reg

information from previous post

sKwa

Hi!

File example_reject_row.txt contains non UTF-8 char sequences. Are you sure original file encoding is URF-8 and not ISO-8859-14?

drno-reg

original file has UTF-8 char

drno-reg

for loading I used python script

with open(file_name, "rt", encoding='UTF-8') as fs:
my_file = fs.read()
cursor.copy("COPY %s.%s from stdin PARSER fcsvparser(type='traditional', delimiter=';', record_terminator='\n')" %(SCHEMA_NAME, TABLE_NAME), my_file)

marcothesane

The row that got rejected has two properties - in one string - that cause problems with a normal COPY command:
1. You have the double quote as the string encloser, and the string contains double quotes, which are doubled in your case: [...]Healph event ""Heartbeat"" create incident [...].
2. The same string, the one beginning with "Gadukino at position 1242 in the file, has a big number of newlines inside. The normal COPY command reads the input until the next newline and tries to load it. The fact that the newline is in a string, does not count.

I would suggest that you look at the Vertica documentation on the CSV parser, and try : COPY <table> FROM '<file>' PARSER fcsvparser( ....) . Check the documentation for format and contents of the parameters for fcsvparser() .
Good luck -
Marco

marcothesane

I just saw that you use the fcsvparser() already. I would try to remove type=traditional.

marcothesane

I tried it myself. Does not work. The problem persists because of the newlines in the string.