Page 1 of 1

Can Vertica Flex handle a 3000 column table with 300 char column names?

Posted: Thu May 21, 2015 9:11 pm
by maizer
We have some 3rd party marketing data that have up to 3000 columns, and column names up to 300 characters. We would love to load this data into Vertica, and join/group with other tables.

Since Vertica itself has a 1600 max number of columns and 100 something byte column name limit, would Flex be able to handle these requirements?

Thank you!

Re: Can Vertica Flex handle a 3000 column table with 300 char column names?

Posted: Mon May 25, 2015 12:43 pm
by NorbertKrupa
Yes, Flex should be able to. You won't get great performance and have limited SQL functionality.

Re: Can Vertica Flex handle a 3000 column table with 300 char column names?

Posted: Tue May 26, 2015 6:28 pm
by NorbertKrupa
After some testing, it seems the upper limit on column names is 256 characters.

Code: Select all

dbadmin=> CREATE FLEX TABLE fjson();
CREATE TABLE
dbadmin=> COPY fjson FROM STDIN PARSER fjsonparser(flatten_maps=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {
    "thisIsTheColumnNameWhichNeverEndsItGoesOnandOnMyFriendsSomePeopleStartedWithSmallColumnsNotKnowingWhatItWasLevel1": {
>> >>         "thisIsTheColumnNameWhichNeverEndsItGoesOnandOnMyFriendsSomePeopleStartedWithSmallColumnsNotKnowingWhatItWasLevel2": {
>>             "thisIsTheColumnNameWhichNeverEndsItGoesOnandOnMyFriendsSomePeopleStartedWithSmallColumnsNotKnowingWhatItWasLevel3": {
>>                 "thisIsTheColumnNameWhichNeverEndsItGoesOnandOnMyFriendsSomePeopleStartedWithSmallColumnsNotKnowingWhatItWasLevel4": 5
>>             }
>>         }
>>     }
>> }
>> \.
ERROR 5861:  Error calling process() in User Function UDParser at [src/JSONParser.cpp:440], error code: 0, message: Unrecoverable parse exception while processing partition: [Overflow error:  Too-long key.  Length would be [341]; max is [256].  [thisIsTheColumnNameWhichNeverEndsItGoesOnandOnMyFriendsSomePeopleStartedWithSmallColumnsNotKnowingWhatItWasLevel3": {
                "thisIsTheColumnNameWhichNeverEndsItGoesOnandOnMyFriendsSomePeopleStartedWithSmallColumnsNotKnowingWhatItWasLevel4": 5
            }
        }
    }
}
]] while parsing JSON data for row [0] with [379] Bytes consumed.
dbadmin=>