Redshift equivalent Hash function.

Question

I am looking to convert hash function to Redshift, below is the link for the definition of the function.

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.1.0/com.ibm.nz.sqltk.doc/r_sqlext_hash8.html

This hash8 function produces 64 bit integer(based on Jenkins algorithm) for input string and looks like the equivalent hash algorithm is not present in Redshift.

As a workaround we are re-creating hash values using FNV_HASH but due to large number of impacted tables and size wanted to confirm on the availability of equivalent function in Redshift.

https://docs.aws.amazon.com/redshift/latest/dg/r_FNV_HASH.html

Accepted Answer

Not inbuilt, but i wrote something similar:

create function hash8 (varchar)
  returns bigint
immutable
as $$
  select  strtol(to_hex(strtol(substr(md5($1), 1, 1), 16) & 7) || substr(md5($1), 2, 15), 16)
$$ language sql;

Redshift equivalent Hash function.

Relevant content