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).
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:
- Netezza FPGA
- nzsql Command and its Usage
- Working with Materialized views in Netezza
- Importance of right distribution Key
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)