We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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 - Select Field - 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: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"
  • 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 - Select Field - 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 - Select Field - Administrator

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

  • marcothesanemarcothesane - Select Field - 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