Long Varbinary more than ....

hoqiyufu

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?

Jim_Knicely

Use the data type SUPER LONG VARBINARY!

Example:

dbadmin=> CREATE TABLE why_do_this(a_very_big_column LONG VARBINARY);
CREATE TABLE

dbadmin=> \d why_do_this;
                                                   List of Fields by Tables
 Schema |    Table    |      Column       |          Type                   |  Size     | Default | Not Null | Primary Key | Foreign Key
--------+-------------+-------------------+---------------------------------+-----------+---------+----------+-------------+-------------
 public | why_do_this | a_very_big_column | super long varbinary(unlimited) | unlimited |         | f        | f           |
(1 row)

Ha hah. Just kidding.

Question... Why would you want to store such a large VARBINARY in an analytic database?

marcothesane

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 ...

Sudhakar_B

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.

marcothesane

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 ...

Sudhakar_B

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.