UDFs vs ORACLE Procedures

RajatGupta

Hi,

I am new to Vertica. So far I understand that Stored/external procedures and functions in Vertica do not offer the equivalent complexity/functionality as offered by ORACLE stored procedures and functions.

However, Vertica do offer to create UDFs, which can be written in JAVA/Python/R. I am not sure to which extent UDFs support the sql operations.

I have two questions regarding UDFs =>

Do the UDFs offer the same complexity such as - Table joins, Returning Multi-row cursors in the same way as ORACLE procedures do ?

I am trying to explore options to develop functions in JAVA is there any step by step instruction to create UDFs using an IDE like Eclipse ?

Thanks

RajatGupta

Can someone please help me on this!!!!

TomM

Hi RajatGupta,

The Forums are informal, and subject to the ability of people to respond to them. If you have an important issue and you're on support you can always reach out to us via formal channels.

In the meantime I've contacted someone who may be able to help. I'm not clear on their availability to respond to your inquiry, and let's see where it goes.

saltzm

Hi,

There a few different questions in here that I'll try to answer. First, the Vertica docs have a lot of information about developing UDxs [1]. Information specific to developing Java UDxs can be found in the link in [2]. I also found an older blog post about developing Java UDxs from within Eclipse, but I don't know whether or not the information is up to date [3].

Now, to give a high level overview of our UDx framework:

There are a few different types of UDxs. UD scalar functions return a single value per row, e.g. if user_defined_sum is a scalar function that computes the sum of its inputs,

SELECT a, b, user_defined_sum(a, b)
FROM foo;

would return columns a, b, and the sum of a and b for every row in foo.

UD transform functions can return an arbitrary number of columns and rows per row, and use an OVER() clause to partition the data however you want into its input.

UD aggregate functions let you define an aggregate function like the built-in aggregates, e.g. sum, avg, etc.

There's also UD analytic functions, which are similar to UDTFs.

In summary, UDFs in Vertica allow you to write functions that can then be called in the same manner as Vertica built-in functions within the SQL syntax.

External procedures are effectively scripts that you can have Vertica execute for you, external to the database process. If these scripts happen to connect back to Vertica with a client and execute queries, then that's fine - but other than that there's nothing special about them as far as accessing data in Vertica.

I can't comment on the comparison to Oracle as I'm not familiar with their functionality, but hopefully this overview provided some clarity.

[1] https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/ExtendingVertica/ExtendingVertica.htm?TocPath=Extending%20Vertica|_____0
[2] https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/ExtendingVertica/Java/DevelopingInJava.htm?TocPath=Extending%20Vertica|Developing%20User-Defined%20Extensions%20(UDxs)|Developing%20with%20the%20Java%20SDK|_____0
[3] https://my.vertica.com/blog/updating-udx-projects-syncing-the-vertica-plug-in-for-eclipse-with-new-vertica-versions/

TomM

Nice job, saltzm. Thanks!

RajatGupta

Thanks a lot Tom and saltzm for help. I will go through these links.

Cheers!!