Josh Peng
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)