By using AWS re:Post, you agree to the Terms of Use

Redshift equivalent Hash function.

0

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

asked 2 years ago73 views
1 Answers
0
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;

profile picture
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions