Amazon Redshift Hash Functions and Examples

You can use Amazon Redshift hash functions 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 are designed to minimize 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. Redshift 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.

Amazon Redshift Hash Functions

Redshift Supports following Hash functions:

Redshift FUNC_SHA1 Hash Function

The FUNC_SHA1 function uses the SHA1 cryptographic hash function to convert a variable-length string into a 40-character string that is a text representation of the hexadecimal value of a 160-bit checksum.

Syntax:

FUNC_SHA1(string)

Where string value may be varchar data values.

Redshift FUNC_SHA1 Function Examples

Below is the example that shows usage of FUNC_SHA1 Function:

training=# select func_sha1('Redshift'); 
 func_sha1 
------------------------------------------ 
 61a6d467590bee66e0b1fbd86e96f924ed55e342 
(1 row) 

training=# 
training=# select func_sha1('Amazon Redshift'); 
 func_sha1 
------------------------------------------ 
 eeac5ed1e11cda66032c30ad77b25071b5487f55 
(1 row) 
training=#

Redshift MD5 Hash Function

This function uses the MD5 cryptographic hash function to convert a variable-length string into a 32-character string that is a text representation of the hexadecimal value of a 128-bit checksum.

Syntax:

MD5(string)

Where string value may be varchar data values.

Redshift MD5 Function Examples

Below is the example that shows usage of MD5 Function:

training=# select MD5('Redshift'); 
 md5 
---------------------------------- 
 f52a9d91766886fb3a524dd06d1581cb 
(1 row) 

training=# select MD5('Amazon Redshift'); 
 md5 
---------------------------------- 
 f7415e33f972c03abd4f3fed36748f7a 
(1 row) 

Read:  

• Amazon Redshift Create View Syntax and Examples
• Redshift Type Conversion Functions and Examples
• Commonly used Redshift String Functions and Examples
• Redshift Date Functions and Examples
• Redshift Analytic Functions and Examples 
• How to Alter Redshift Table column Data type? Explanation