Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Long Varbinary more than ....
I am writing an aggregation UDF whose return type is Varbinary I get following error message
"User code caused Vertica to throw exception "Length for type Long Varbinary cannot exceed 32000000"
I checked docs and found that this is a system limit.
My requirement is to have almost four times this limit size, is there a way to solve this problem?
There is some external storage concept, how can I make my Aggregate UDF terminate method to write more than this limit?
0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
Use the data type SUPER LONG VARBINARY!
Example:
Ha hah. Just kidding.
Question... Why would you want to store such a large VARBINARY in an analytic database?
What does your "aggregation UDF" actually want to achieve? A string aggregation? Of how many elements?
Vertica allows up t o1600 columns up to now, and the new GA 10.1.0 version allows for an incredible 9800 columns per table...!
We are a relational database, not a file system for "files" with long lines. So it would seem natural to either use arrays of values (which we support) , or also a series of columns of equal data type, maybe named "col0001" through "col1000", if you have one thousand of them, and let your aggregation UDF actually become a horizontal-pivoting aggregate function.
It simply looks like what you intend to do is something that would forbid Vertica to be what it is designed for: a performant SQL relational database, which takes advantage of column types, column histograms, column encoding, and, yes, the fact that it stores its data in columnar format. You seem to be wanting to break that paradigm ...
WOW
Didn't know this
new GA 10.1.0 version allows for an incredible 9800 columns per table...!
Very Nice, especially for de-normalized tables for analytics this is a boon. I thought 1600 was ample, until I started running into that limit at few customers.
Use it with care. I personally think that over 100 columns per table are a flaw in your design - although I admit that they a high allowed number of them comes in handy when working with machine learning models.
Otherwise - a query that deals with 100 columns is nothing for human consumption - and, to me, a query is still a question asked by a human ...
WOW
Didn't know this
new GA 10.1.0 version allows for an incredible 9800 columns per table...!
Very Nice, especially for de-normalized tables for analytics this is a boon. I thought 1600 was ample, until I started running into that limit at few customers.