The Netezza user defined functions feature allows you to create custom functions, aggregates, and shared libraries that run on Netezza systems and perform specific types of analysis for your business reporting and data query using those UDFs.
The Netezza user defined functions allows you to make use of the Netezza massively parallel processing (MPP) environment to accelerate or speed up your data analysis, as well as to offer new and unique types of analysis on you tera byte of data.
Read:
- Netezza Advance Analytics Functions
- Netezza String Functions and Examples
- IBM Netezza Date Functions and Examples
- Netezza Advanced Date Functions and Examples
- IBM Netezza Extract Functions and Examples
Netezza User Defined Functions (UDFs)
A user-defined function (UDF) is user code that is executed by the Netezza system in response to SQL invocation syntax. User code is specific set of codes that are written to perform the particular functions that Netezza Built-in functions such as upper(), sqr(), or length() cannot perform. A user-defined function is a scalar function; that is, it returns one value.
Methods to write Netezza User Defined Functions
There are two ways you can write Netezza user defined functions:
Netezza nzlua
LUA is an extension programming language designed specifically support general procedural programming with data description facility. This language also supports for object oriented, functional and data driven programming.
Using Lua API, Netezza created nzlua, which can be used to create Netezza user defined functions, aggregations on Netezza appliances.
Netezza nzlua User Defined Function Example
This is a simple example UDF that adds two numbers together and returns the result.
function evaluate(a,b) return a + b end function getName() return "adder" end function getType() return "udf" end function getArgs() args={} args[1] = { "a", double } args[2] = { "b", double } return args end function getResult() return double end
Netezza User Defined Functions using C++
To begin, use any text editor to create your C++ file. The file name must have a .cpp extension. Your C++ file must include the udxinc.h header file, which contains the required declarations for user-defined functions and processing on the Netezza SPUs.
Netezza User Defined Functions using C++ Example
Office hour verification function:
#include "udxinc.h" using namespace nz::udx; using namespace nz::udx::dthelpers; static const uint8 WORK_START_HOUR=9; static const uint8 WORK_END_HOUR=18; class IsBusinessHours : public Udf { public: static Udf* instantiate(); virtual ReturnValue evaluate() { if(isArgNull(0)) NZ_UDX_RETURN_NULL(); int64 ts = timestampArg(0); if(!isValidTimestamp(ts)) //if this test does not pass, we won't // be able to decode ts throwUdxException("invalid timestamp passed"); struct tm decomp; bool err=false; decodeTimestamp(ts, &decomp, &err); if(err) //if isValidTimestamp(ts) is true, err should be false, //but better safe than sorry throwUdxException("error decoding timestamp"); if(decomp.tm_wday==0||decomp.tm_wday==6||decomp.tm_hour<=WORK_ START_HOUR||decomp.tm_hour>=WORK_END_HOUR) NZ_UDX_RETURN_BOOL(false); NZ_UDX_RETURN_BOOL(true); } private: }; Udf* IsBusinessHours::instantiate() { return new IsBusinessHours; } REGISTRATION: create or replace function isBusinessHours(timestamp) returns bool language cpp parameter style npsgeneric returns null on null input EXTERNAL CLASS NAME 'IsBusinessHours' EXTERNAL HOST OBJECT '[host .o_x86 file]' EXTERNAL SPU OBJECT '[SPU .o_spu10 file]'