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?

Answers

  • Jim_KnicelyJim_Knicely Administrator

    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?

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.