Netezza User Defined Functions and Sample Examples

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:4 mins read

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 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]'
Tags: , ,