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

Moderator: NorbertKrupa

Post Reply
maizer
Newbie
Newbie
Posts: 2
Joined: Thu May 21, 2015 9:06 pm

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

Post by maizer » Thu May 21, 2015 9:11 pm

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!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

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

Post by NorbertKrupa » Mon May 25, 2015 12:43 pm

Yes, Flex should be able to. You won't get great performance and have limited SQL functionality.
Checkout vertica.tips for more Vertica resources.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

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

Post by NorbertKrupa » Tue May 26, 2015 6:28 pm

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=>
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Data Load”