Josh Peng

Full-Stack Data Scientist

github linkedin email
Redshift SHA-256
Aug 18, 2018
One minute read

Sometimes you won’t find all the functions you need pre-built into Redshift. Fortunately, you can create yourself a User-Defined Function (UDF) extremely quickly. Unfortunately, it might not be the fastest function around, but can hold you over in a pinch.

One scenario we ran into at hush was that we wanted to buy ads on Snapchat. They needed us to provide a postback with mobile identifiers IDFA/GAID hashed with SHA-256. Redshift currently only has something for SHA-1, so UDFs to the rescue.

Just execute the following code on your Redshift cluster and you are good to go.

/*
F_SHA256
Returns a SHA-256 hashed result
Example:
	SELECT f_sha256('hello') --> '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824'
*/
CREATE OR REPLACE FUNCTION f_sha256 (mes VARCHAR)
	returns VARCHAR
STABLE
AS $$
	import hashlib
	return hashlib.sha256(mes).hexdigest()
$$ language plpythonu;

To use is as simple as:

select f_sha256(ios_idfa)


Back to posts


comments powered by Disqus