Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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 ?

Comments

  • marcothesanemarcothesane Administrator

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

  • 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)
    ;

  • edited August 2017

    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:[email protected]@host01";"7f8021ea247d965c4092f41d63ac9577";"fileshare";"912bba5b7fd438e480385a54bbbda39f";"nt";"158963838";"host01|192.168.0.1";"host01";"192.168.0.1";"";"[email protected]@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"
  • information from previous post

  • edited August 2017

    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?

  • original file has UTF-8 char

  • 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)

  • marcothesanemarcothesane Administrator

    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

  • marcothesanemarcothesane Administrator

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

  • marcothesanemarcothesane Administrator

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.