Usually, for moving data into a Data Warehouse system like Netezza, there is an extract, transform and loading phase involved commonly referred to as ETL.
Extraction is extracting data from various transactional sources, transformation involves operations like computing new values based on some source value or translating coded values, selecting subset of data and loading is moving the data to the Data Warehouse.There are several tools available in the market to do ETL, however if extraction of data is already complete from source systems and the requirement is to perform transformations and loading to Netezza, Netezza provides a very flexible capability called External Tables.
External Tables provide a SQL interface to the data that exists in a flat file or a pipe. Since SQL is a very rich language with several transformational capabilities, External Table inherits most of the transformational capabilities of SQL. For example using External Tables, users can select only certain columns of data from the file, perform operations like casting, truncation, mathematical operations on the selected data and even join this data with other existing table before loading it into the data warehouse.
Further External Tables in conjunction with User Defined Functions(UDF) can be used to manipulate the data read in from the data file with custom user code. This custom user code can be similar to the rules specified in the ETL tools to transform the data.Because of these strong transformational capabilities, External Table is a very good choice to do the Transformation & Loading of an ETL process. In no way External Tables can solve all the ETL problems , however it may satisfy the needs of several applications which may find the ETL tools an overkill.
Let us consider an example of selecting certain columns, performing decoding transformations on that data, deriving new values from some of the columns and then loading this data into a Netezza Table.
CREATE EXTERNAL TABLE customer_ET(NAME CHAR(20),AGE INT4,GENDER CHAR(1),BALANCE INT4,CITY CHAR(20),ADDRESS CHAR(10))USING (dataobject('/home/mycustomer.dat'));
/* Load the data into Netezza Table customer_DW from External Table customer_ET */
insert into customer_DW select name , generation(age) , rewards(balance), citycode(city) from customer_ET;
Below is a sample of how the evaluate method for citycode() UDF which returns city code based on city name will look like. Similarly UDFs for generation() and rewards() can be defined. Refer to Netezza UDF development guide for more details.
These UDFs can do complex pre-processing on your data before its loaded into Netezza.
/* evaluate method for citycode() UDF. The code under this evaluate method is executed for invocation of citycode UDF. */
virtual nz::udx_ver2::ReturnValue evaluate()
{
StringArg *str;
str = stringArg(0);
int lengths = str->length;
char *datas = str->data;
int32 citycode = 0;
if (memcmp("Chicago",datas,7) == 0)
citycode = 105;
else
if (memcmp("Dallas",datas,6) == 0)
citycode = 207;
else
if (memcmp("Washington",datas,10) == 0)
citycode = 306;
NZ_UDX_RETURN_INT32(citycode);
}
If your requirement is to perform some transformations on the data before loading it into Netezza, then I would recommend you to explore External Tables with UDF to perform these transformations along with loading the data. You would be surprised to see how powerful a simple SQL based interface can be for doing such transformations.
This.
Many a times we come across situations where in we want to accomplish a task for which there is an already built in function or a procedure. Most of the database vendors provide numerous such functions and procedures to achieve such tasks. But there are also occasions that none of the existing functions meet the actual goal that one desires. In such cases a user ends up in writing a function. Herein this article, I try to highlight as to how one can come up with his/her own C++ function which can be invoked using the Netezza SQL.
A user can create custom functions, aggregates, and shared libraries using the user-defined extensions feature. The custom functions can be run on the Netezza Performance Server (NPS) systems and used to perform specific types of analysis for business reporting and data queries. These objects leverage the massively parallel processing (MPP) environment to accelerate analysis of data. User-defined functions enable data processing directly on the system, leading to elimination or reduction of data movement to other systems for analysis, which ensures performance gains.
In other words, User defined Extensions (UDX) enhances Netezza functionality by enabling a user to write a C++ coded modules which can be invoked using the SQL queries.
There different types of UDX’s supported by Netezza are –
A typical UDX development cycle will include –
Let’s take a look on how UDF is created and used. UDF takes 0 or more input parameters and returns one output value. It is called once for every row and can be used in all SQL queries where built in functions are supported.
How to create the C++ file for UDF?
The file must include the header file udxinc.h in addition to any standard header file. The UDX classes and functions for API version 2 are defined in a namespace called nz:udx_ver2.
#include "udxinc.h"
#include "udxinc.h"
#include <string.h>
#include <string.h>
using namespace nz::udx_ver2;
Create a class object derived from the udf base class. The code will look something like this :
using namespace nz::udx_ver2;
class CMyFunc: public nz::udx_ver2::Udf
class CMyFunc: public nz::udx_ver2::Udf
{
{
public:
public:
MyFunc(UdxInit *pInit) : Udf(pInit)
MyFunc(UdxInit *pInit) : Udf(pInit)
}
}
static nz::udx_ver2::Udf* instantiate(UdxInit *pInit);
static nz::udx_ver2::Udf* instantiate(UdxInit *pInit);
virtual nz::udx_ver2::ReturnValue evaluate()
{
// Function Logic
}
};
};
nz::udx_ver2::Udf* MyFunc::instantiate(UdxInit *pInit)
nz::udx_ver2::Udf* MyFunc::instantiate(UdxInit *pInit)
return new MyFunc(pInit);
return new MyFunc(pInit);
The instantiate method creates the object dynamically.
This CPP file will have to be compiled using the nzudxcompile utility.
nzudxcompile myFunc.cpp
The following object files are created
myFunc.o_x86
myFunc.o_spu10
Once the UDF is compiled, it needs to be registered with the NPS system. The same utility can be used
nzudxcompile MyFunc.cpp –o myFunc.o
nzudxcompile MyFunc.cpp –o myFunc.o
–-sig "MyFunc(varchar(64000))" --version 2 –-return INT4
–-sig "MyFunc(varchar(64000))" --version 2 –-return INT4
–-class CMyFunc –-user myuser –-pw password –-db mydb
–-class CMyFunc –-user myuser –-pw password –-db mydb
Alternatively, the UDF can be registered using SQL, For ex –
CREATE FUNCTION MyFunc(varchar(64000))
CREATE FUNCTION MyFunc(varchar(64000))
RETURNS int4 LANGUAGE CPP PARAMETER STYLE NPSGENERIC API VERSION 2
RETURNS int4 LANGUAGE CPP PARAMETER STYLE NPSGENERIC API VERSION 2
EXTERNAL CLASS NAME 'CMyFunc'
EXTERNAL CLASS NAME 'CMyFunc'
EXTERNAL HOST OBJECT '/home/nz/udx_files/myfunc.o_x86'
EXTERNAL HOST OBJECT '/home/nz/udx_files/myfunc.o_x86'
EXTERNAL SPU OBJECT '/home/nz/udx_files/myfunc.o_spu10';
EXTERNAL SPU OBJECT '/home/nz/udx_files/myfunc.o_spu10';
These registered functions can be invoked like any function in a SQL statement.
Too.
With Fluid Query 1.5, Netezza can connect to any system which has Java interface. That said, Fluid query can connect two Netezza database residing in two different Netezza servers. All connection/query/data management is via Netezza JDBC Driver. User can read data from tables in database in Netezza-2 server while connected to database in Netezza-1 server. However cannot write data to in same way. This is only one way data transfer.
As all mechanism is based on Netezza JDBC Driver, can we have write functionality too? Is it already available? I know it will have other drawbacks based on network and JDBC limitations. I feel we can write Netezza udtf to perform write but wish to check if anything already available.
Thanks,
-Ambarish
Performance,.
Do.
Using Netezza ? If answer is yes, you have done the first step in choosing a powerful system for your analytics requirements. Speed, durability, resilience, time to value and most importantly “Simplicity” is built into it.
When you have such an appliance, you would want to take care of it more and use it in right way so as to continue benefitting from the power of Netezza. Although most of the things are automatically taken care of but still here are some tips that you might want to take care of in the following cases:
A) when everything is alright (will you not want it to remain that way):
Although, you might not want to worry about all of these points but my suggestion would be to at least follow points 6,7 and 8 as normally in Netezza type of system you must be having really big data.
B) when something you feel is wrong:
Well, should not happen but if it happens, your next actions really would be driven by the type of problem that you might encounter. More often than not, the best way is to contact IBM support if you think that you are following best practices and problem is directly related to netezza.
But yes, sometimes in my experience, problems that you encounter may be elsewhere and in that case it is always a good idea to review your analytics setup as a whole which mostly comprises of:
Sequencing of jobs,
ETL tool,
Data Model in use,
SQL itself, and
Netezza appliance.
All of these areas, play important role for performance and should be suspected when a problem related to performance occurs.
I will just focus on Netezza in this blog and my worry is that this blog is becoming too big so will try to wrap it up also. Key things in Netezza which should be focused on, are:
Affects auto vacuum which is run at frequent intervals by Nz and also my suggestion would be to do nz_manual_vacuum. In the case of nz_manual_vacuum not only the vacuum of data happens but also reindexing of the entire database happens. A regular vacuum does not and cannot do a reindex. That is why nz_manual_vacuum script is provided. Over a period of time the index files accumulate holes. A reindex cleans this up.
Check in QHD if you have one setup if the queries were running faster earlier or getting to know time window in which something looks wrong. Basically review the data present with you.
Also, at regular intervals, remove old data from the history database based on your need to keep the history
- nzhistcleanupdb -d histdb -t "<date>".
As mentioned earlier for few of the above, these are the main things that if not correctly used, affect query performance.
Review your Workload Management policy based on the users and groups that would want system access in a particular time window.
There might be some system variable and hardware related factors involved but one should not be reviewing/changing those as IBM support person needs to do that.
I will stop here for this topic – you might want to read my other blog for more information related to Nz:
NGINX is an open source webserver and reverse proxy that has grown in popularity in recent years due to its scalability. NGINX was first created to solve the C10K problem – serving 10,000 simultaneous connections on a single webserver. NGINX’s features and performance have made it a staple of high performance sites — now powering 1 in 3 of the world’s busiest web properties.
NGINX Plus is the commercial (subscription-based) version of NGINX open source. It adds a number of features for load-balancing and proxying traffic that can enhance a WebSphere Application Server deployment, including session persistence, health checks, dynamic configuration and live activity data. NGINX Plus is fully supported by NGINX, Inc.
The traditional reverse proxy for WebSphere Application Server is IBM HTTP Server (IHS). IHS and WAS are tightly integrated via the WebSphere WebServer Plug-in and there is a wealth of documentation and expertise in the WebSphere community.
In many ways, NGINX and IHS are similar. They are configured similarly, and behave similarly on the outside but, on the inside, they are quite different. At low load or ideal network conditions, they perform comparably well. However, NGINX is made to scale well on large numbers of long-lived connections and in the face of large traffic variations – exactly where IHS’s one-thread-per-connection struggles.
In addition, NGINX is well-equipped to handle HTTP/2 traffic – it currently supports SPDY, and powers the majority of sites that have adopted this standard. HTTP/2 support is planned for release in late 2015; there are currently no plans to support HTTP/2 within IHS.
On the other hand, there are some WebSphere specific features that are currently only integrated into IHS and Datapower – for example, dynamic clustering of WebSphere Application Servers. Currently, NGINX can only proxy to statically-defined groups of application servers. NGINX Plus does offer an API to configure load-balancing groups, or can be configured via DNS.
NGINX may provide significant value in environments where acceleration via SPDY, HTTP/2 or a high performance centralized cache smaller than an appliance is needed.
This section outlines some basic information on setting up NGINX as a reverse proxy. The configuration here is essentially equivalent to what one would get with a generated plugin-cfg.xml for the WAS plug-in.
plugin-cfg.xml
NGINX’s proxying is based around the concept of an upstream group, which defines a group of servers. Setting up a simple reverse proxy involves defining an upstream group, then using it in one or more proxy_pass directives.
upstream
proxy_pass
Here is an example of an upstream group:
http {
...
upstream websphere {
server 127.0.0.1:9080;
server 127.0.0.1:9081;
}
}
http {
...
upstream websphere {
server 127.0.0.1:9080;
server 127.0.0.1:9081;
}
}
The upstream group above, named websphere, is defined by two servers. Both are on ip 127.0.0.1, with one server on port 9080 and the other on 9081. Note that the upstream group is placed within an http block.
websphere
127.0.0.1
http
We use the proxy_pass directive within a location block to point at an upstream:
location
http {
...
server {
...
location /webapp/ {
proxy_pass;
}
}
}
http {
...
server {
...
location /webapp/ {
proxy_pass;
}
}
}
This tells NGINX to proxy all HTTP requests starting with /webapp/ to one of the servers in the websphereupstream. Note that this configuration only applies to HTTP traffic; additional configuration is needed both for SSL and for WebSockets.
/webapp/
For more information on proxying, refer to the official NGINX documentation on the proxy module and the upstream module.
The NGINX guides: Load Balancing part 1 and Load Balancing part 2 provide a step-by-step walkthrough, and the document On-the-fly reconfiguration of NGINX Plus describes how NGINX Plus’ load-balancing groups can be configured using an API or DNS.
The default strategy for load balancing among servers in a given upstream group is round-robin. In round-robin load balancing, requests are distributed evenly among all servers in turn. For example, in the above proxying configuration, the first request will go to port 9080, the second to 9081, the third to 9080, and so on.
Failover is also automatically configured when using an upstream group. By default, when a request comes in and it is directed to an unreachable server, NGINX marks that server down for some time and automatically redirects the request to another server. After 10 seconds, NGINX starts sending requests to the downed server. If those requests succeed, the server returns to normal operation; otherwise, it remains down for another 10 seconds.
See the documentation for the server directive to configure parameters related to failover.
server
Health checks are out-of-band HTTP requests sent to probe a server on a preset interval. They are used to determine whether or not a server is still up without requiring an actual request from a user. To enable health checks for a server, add the health_check directive to the location block of a given proxy_pass directive:
health_check
http {
upstream websphere {
# Health-monitored upstream groups must be stored in shared memory
zone backend 64k;
server localhost:9080;
server localhost:9081;
}
server {
location /webapp/ {
proxy_pass;
health_check;
}
}
}
http {
upstream websphere {
# Health-monitored upstream groups must be stored in shared memory
zone backend 64k;
server localhost:9080;
server localhost:9081;
}
server {
location /webapp/ {
proxy_pass;
health_check;
}
}
}
This configuration will send an out-of-band request for the URI / to each of the servers in the upstream websphere every 5 seconds. Any server that does not respond with a successful response will be marked down. Note that the health_check directive is placed within the location block, not the upstream block; this means health checks can be enabled per-application.
/
Unlike previous upstream groups in this document, the one in the above example has a zone directive. This directive defines a shared memory zone which stores the group’s configuration and run-time state, and is required when using the health check feature.
zone
See the documentation for the health_check directive for more information and details on how to customize health checks.
NGINX Plus also provides a slow start feature when failed servers recover and are reintroduced into the load-balancing pool.
In response to: 2014 2nd Edition of Netezza Underground Book now on sale at Amazon.com
Imagine!!!