Netezza Hash Function Usage and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:3 mins read

You can use Netezza hash function to encode the data by transforming the input into a hash code or hash values. If you use the hash function, no two inputs have same hash values. The hash algorithm is designed to minimise the collision (two inputs having same hash value).

netezza hash function

You can use the hash function to speed up the data record retrieval by using simple one way lookup. Hash code is used as an index into a hash table which has a pointer to data records. Netezza hash function also performs the data validation using checksum, i.e. hash code is computed for the data before storage or transmission and then recomputed afterwards to verify data integrity, if hash codes do not match, then data is corrupted. You can secure the data using cryptography.

Read:

IBM Netezza SQL Extensions Toolkit provides the following hash functions:

Hash() function

This function returns a 128, 160 or 256 bit hash of the input data, depending upon the algorithm you select. Hash() function is much slower to calculate than hash4() and Hash8(). If your main goal is speed then use latter functions.

The drawback is column of these type cannot use the zone maps or other performance enhancements.

Syntax:

Hash(varchar or nvarchar data [,int algorithm]);

Where data value may be varchar or nvarchar and algorithm value is integer values. Default is 0. Below are the supported algorithms:

Code Algorithm Result
0 MD5 128 bit
1 SHA-1 160 bit
2 SHA-2 256 bit

Netezza Hash Function Example

The function returns hashed input data.

select hash('Netezza',0);
 HASH
------------------
Ã
®dïO=±NEÂ
ÂÂÃ
(1 row)

Hash4() function

The hash4() function returns the 32 bit checksum hash of the input data.

Syntax:

hash4(varchar or nvarchar data [, int algorithm]);

Where data value may be varchar or nvarchar and algorithm value is integer values. Default is 0- Adler. 1- CRC32

Hash4 function Example

select hash4('Netezza',0);
 HASH4
-----------
 186778338
(1 row)

Hash8() function

The hash8() function returns the 64 bit hash of the input data.

Syntax:

Hash8(varchar or nvarchar data [, int algorithm]);

Where data value may be varchar or nvarchar and algorithm value is integer values. Only one algorithm is supported, that is, Jenkins algorithm.

Hash8 function Example

select hash8('Netezza');
 HASH8
---------------------
 6256861227618928690
(1 row)