Follow my blog with Bloglovin

Saturday, September 2, 2017

User Defined Function (UDF) : HP Vertica

UDF combines repeated (or complex) SQL or other task into one unit that can be reused. Lets explore this feature in Vertica.
There are five types of UDx (User Defined):

  • UD SQL function: Can use built-in functions, arguments and operators in only one SQL expression and return result o the expression.
  • UD Scalar function: Takes one row as input and produces one row as output. Can be written in C++ and R.
  • UD Aggregate function: Takes one column data as input and produces one column output. Can be written in C++.
  • UD Analytic function: Calculate one output row from multiple input row. Unlike transform function this take input rows one by one, so can read multiple rows and return one output row. Can be written in C++.
  • UD Transform function: Takes multiple rows as input and output multiple rows. Can be written in C++ and R.
Fenced mode: Vertica has a separate process to execute UDx, so that they don't use significant resources of main Vertica process. 
All functions written in C++ can be run in fenced mode or not, but functions in R always run in fenced mode.

To install and setup Vertica see my previous post. It is recommended to have single node installation  of Vertica on developer machine or testing UDx.



Popular Posts