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!
Can Vertica Flex handle a 3000 column table with 300 char column names?
Moderator: NorbertKrupa
-
- 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?
Yes, Flex should be able to. You won't get great performance and have limited SQL functionality.
Checkout vertica.tips for more Vertica resources.
-
- 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?
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.