Long Varbinary more than ....
hoqiyufu
Vertica Customer
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?
Evan made his way into the busy kitchen to grab another beer. His friend Zack had convinced him to come out to one last party before he went away for the summer. Evan wasn’t really sure why, as Zack ditched him within 15 minutes to talk to some girl he had been wanting to bang. It must have gone well because he was on his own for the rest of the night, which he was used to by then. Normally he wouldn’t be feeling as awkward as he was but every year he found himself more ready to leave his hometown and everyone in it behind https://livesex.bar/ .
On the way to the backyard, he saw a semi-familiar face go by him, glancing up and giving a small smile like she always did. He had known of Jane since 1st grade, all the way up through high school. The college he went to was big enough that she could be going there too as far as he knew. He saw her at most of the parties he went to and they had quite a lot of mutual friends. For whatever reason, they had never said more than a sentence to each other; maybe a few paragraphs if he counted the 5th-grade school play.
Jane always seemed to be on the move, heading to some other more important thing where no one else was invited. He had always thought it was a little funny that a girl could be nicknamed ‘hippie’ while appearing to be so stuck up. They had even managed to not speak at the office supply store that they worked at during college. The same one that Evan had just been ‘laid off’ from. He followed Jane, realizing that they finally had something to talk about.
He made his way out the front door, hoping she hadn’t left yet. There were only a handful of partiers out front and soon he found her sitting alone at the far end of the porch, face buried in her phone. He hesitated for a few moments as a strange childhood nervousness gripped him. He had a small, quick crush on her when they were 14, nothing at all serious, but to Evan, Jane had always been unapproachable https://sexchat.bar/ .
She had dirty blonde hair halfway down her back that was usually straight or pulled back into a ponytail; always a bit messy as she was always running her hands through it. The hippie label was probably due to her style of dress as she seemed to have specific tastes that evolved to include some more regular clothes now and then. That night it was tight tan work pants and a yellow shirt with flowers all over it that looked like it had been passed down from the 60’s. All of these details would leave many guys to believe that Jane was more approachable but he didn’t see her that way. In his opinion, the most unattractive quality about her was how aware she was that she looked like a model without having to try.
Jane lit up a cigarette and noticed Evan walking towards her. She resisted an eye roll, as she didn’t have the greatest opinion of him either. He had always seemed to have this arrogance about him that only she noticed. He was wicked involved in school and even made people, like her, look bad at work. Evan was a classic overachiever, which was only made worse by her friends never having a bad thing to say about him.
He had hooked up with a few of her friends over the years, which disgusted her as they would gush about him as if he was god’s gift or something. Evan was definitely hot but everything about his personality came off as fake to her. She felt justified in her assumptions about him as she considered herself to be a great judge of character; except when it came to the guys she dated. He would probably end up being a future serial killer, as too many people liked him, it just wasn’t normal.
Answers
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.