Netezza Data Transformation Functions and Examples

  • Post author:
  • Post last modified:May 19, 2018
  • Post category:Netezza
  • Reading time:4 mins read

You must secure the data in case if you are working on various sensitive and confidential data. Netezza does provides various transformation functions that you can use to change the data representation. In this article, we will check Netezza data transformation functions and some examples.

Netezza Data Transformation Functions and Examples

Netezza Data Transformation Functions

The data transformation functions in Netezza are used to change data into different representation for purpose of security (encryption and decryption), space saving (compression) and save time to transmit data across various medium.

IBM used industry standard algorithms to build these data transformation functions.

Below are the Netezza data transformation functions:

Netezza Compress Function

The compress function compresses a varchar or nvarchar value by using the public source zlib software library. The zlib library uses the DEFLATE compression algorithm (Lempel-Ziv 1977). This function encodes input data to use fewer bits on the storage. You must use the decompress to read the data that is been compressed.

Syntax:

compress(varchar input[, int level]);
compress_nvarchar(varchar input[, int level]);

Input could be either varchar or nvarchar values. Level indicates compression level, it can be 0-9. i.e. 0 indicating the least compression and 9 indicating the most compression

Examples:

TESTTDB.ADMIN(ADMIN)=> select compress('112233445566');
COMPRESS
-----------------
x�344226611553
(1 row)

TESTTDB.ADMIN(ADMIN)=> select compress_nvarchar('112233445566'::nvarchar(12));
COMPRESS_NVARCHAR
-------------------
x�344226611553
(1 row)

Netezza Decompress Function

The decompress function decompresses a compressed varchar or nvarchar value.

Syntax:

decompress(varchar input);
decompress_nvarchar(varchar input);

Examples:

TESTTDB.ADMIN(ADMIN)=> select decompress(compress(‘112233445566’));
DECOMPRESS
————–
112233445566
(1 row)
TESTTDB.ADMIN(ADMIN)=> select decompress_nvarchar(compress_nvarchar(‘112233445566’::nvarchar(12)));
DECOMPRESS_NVARCHAR
———————
112233445566
(1 row)

Netezza Encrypt Function

This function is used to encrypt the data based on the key. This function is basically used to mask or encrypt value to maintain its secrecy. You can read the data only if you know the key that was supplied to encryption function.

You can choose either Advanced Encryption Standard (AES) or RC4 algorithms to encrypt and decrypt.

Syntax:

encrypt(varchar/nvarchar text, varchar/n varchar key [, int algorithm [, varchar IV]]);
decrypt(varchar/nvarchar text, varchar/n varchar key [, int algorithm [, varchar IV]]);

where;

text – value to encrypt or decrypt
key – key to use to encrypt or decrypt
Algorithm – 0: RC4 this is default. 1: AES 128, 2: AES 192, 3: AES 256

fpe_encryp Function

The fpe_encrypt function is used to encrypt an integer value into a ciphertext integer value. This encryption function uses format-preserving encryption (FPE) process of encrypting data, that is, resulting encrypted ciphertext has the same format (data type) as that of the input value. The FPE routines are implemented by using the Botan open source library.

Syntax:

fpe_encrypt(bigint number, varchar(ANY) key, varchar(ANY) iv, bigint mask);

Examples:

TESTTDB.ADMIN(ADMIN)=> select encrypt('dwgeek.com', 'k1', 1);
ENCRYPT
------------
©>Ê��èôÀèÍ
(1 row)

TESTTDB.ADMIN(ADMIN)=> select decrypt(encrypt('dwgeek.com', 'k1', 1),'k1', 1);
DECRYPT
------------
dwgeek.com
(1 row)

TESTTDB.ADMIN(ADMIN)=> select fpe_encrypt (1234,'XXXX','0',10000);
FPE_ENCRYPT
-------------
5089
(1 row)

TESTTDB.ADMIN(ADMIN)=> select fpe_decrypt(5089,'XXXX','0',10000);
FPE_DECRYPT
-------------
1234
(1 row)

Netezza uuencode Function

The uuencode function encodes a binary value as ASCII character code in the range 32 and above by using the UNIX UUencode format.

Syntax:

uuencode(varchar input);
uuencode(nvarchar input);

Examples:

TESTTDB.ADMIN(ADMIN)=> select uuencode ('hello');
UUENCODE
------------------------
begin
%:&5L;&\`
`
end
(1 row)

Netezza uudecode Function

The uudecode function decodes an ASCII value that was previously encoded by using the UNIX UUencode format.

Syntax:

uudecode(varchar input);
uudecode(nvarchar input);

Examples:

TESTTDB.ADMIN(ADMIN)=> select uudecode(uuencode ('hello'));
UUDECODE
----------
hello
(1 row)

Read:

Tags: