Can Vertica Flex support long(500 char) column names and up to 3000 rows?

maizermaizer Registered User

Since Vertica has a 1600 column limit and also a column name length limit, I'm trying to use Vertica Flex.

 

Is it possible to load into Vertica Flex column names that are up to 300~500 characters long? And with the total column cound around 3000?

Comments

  • DerrickRDerrickR Registered User

    I believe that Flex supports long key names and there is no limit on the number of keys. However, the provided fjsonparser has a limit of 250 bytes (ascii characters) for key names.

     

    - Derrick

  • maizermaizer Registered User

    Thank you Derrick,

     

    Unfortunately for TSV files, it seems there is some limit for the column name. Some of my files with merely a few hundred columns are injested properly, but the large one with 200~500 char column names fail silently. No error message at all.

     

    When I exclude the headers, the data seems to be loaded correctly, so it looks like its not a number of columns problem, but the column names are too large.

     

    I'm trying to see if increasing the size of the _raw_ column might help, but would you have any suggestions by any chance?

  • DerrickRDerrickR Registered User

    If the data is being successfully loaded, take a look at what public.MapToString(__raw__) looks like. Does it have the full column names, or are they truncated / omitted? If they are there, then you know that it's not the load process that is failing.

     

    Be sure to look at rejections from your load. Documentation for the COPY statement explains the rejections mechanism.

     

    The next thing to try is to see if you can successfully use public.MapLookup(__raw__, 'columnName') for long names.

     

    Finally, ensure your column names have no special characters or - if they do - use identifier quoting. That's different from string literal quoting (it's double quotes, not singles)

  • maizermaizer Registered User

    Thanks Derrick, unfortunately no data is being loaded at all. I get Rows 0;. I even reduced my tsv such that all it has is the headers, and 3 rows. Still failing. And the column names are very similar to the other files I'm loading no problem.

     

    I did read in the doc:

     

    Note: Specifying rejected data and exceptions files is not currently supported while loading flex tables.

     

    So not sure if I can get the rejected data, but I'm currently trying!

  • maizermaizer Registered User

    Success! After increasing the _raw_ size, everything seems to be being loaded just fine.

     

    alter table ad.food alter column __raw__ set data type long varbinary(1200000);

Leave a Comment

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