Amazon Redshift: SHA-2(SHA-256)に変換するUDF(ユーザ定義関数)を作成してみた。

はじめに

好物はインフラとフロントエンドのかじわらゆたかです。 案件において、Redshift内に格納してあるで特定のカラムの値のハッシュ値(SHA-256)を求めたいといったニーズがありました。 単純な方法としてはプログラムで値を抽出して、その中でハッシュ値を求めれば良いのですが、 RedshiftはUDF(ユーザー定義関数)が使えることを思い出したので、そちらで求めてみたいと思います。 Amazon Redshift: UDF(User Defined Functions: ユーザー定義の独自関数)が遂にサポートされました！ ｜ Developers.IO

ハッシュ値を求めてみる

RedshiftのUDFはPythonで記載をする必要があります。 Redshift内にUDFとして登録する前に、まずはPythonでハッシュ値を求めてみます。

$ python
Python 2.7.10 (default, Jul 13 2015, 12:18:59)
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.57)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import hashlib
>>> hashlib.sha256("").hexdigest()
'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855'

上記の値がウィキペディアに乗っている空の入力のハッシュ値と同様なことが確認できます。 SHA-2 - Wikipedia

UDFを作成してみる

上記の内容をUDFとして登録していきます。 Amazon Redshift: UDF(User-Defined Functions:ユーザー独自の定義関数)の作成方法 ｜ Developers.IO

create function f_sha256 (mes VARCHAR)
returns VARCHAR
stable
as $$
import hashlib
return hashlib.sha256(mes).hexdigest()
$$ language plpythonu;

上記のブログ記事に基づき、UDFの名前にプリフィックスとしてf_をつけて、作成しております。 作成した際にハマって点としては権限周りにハマりました。これも上記のブログに記載してある通り、 "UDFまたはライブラリを置き換えたり削除したりするために、所有者またはスーパーユーザーである必要があります。"といった点に注意が必要です。

作成したユーザー定義関数を用いてみる

テーブルを作成し、適当な文言に対して作成した関数でハッシュ化してみます。 ハッシュ化された値が同一なことも確認してみます。

CREATE TABLE sha256_test_table (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
test_word VARCHAR(32)
);
INSERT INTO sha256_test_table(test_word) VALUES('');
INSERT INTO sha256_test_table(test_word) VALUES('Test');
INSERT INTO sha256_test_table(test_word) VALUES('TEST');
SELECT id,test_word,f_sha256(test_word) as sha256result FROM sha256_test_table;

id :1 のハッシュ値が一致していることが確認できました。 id:2 id:3のハッシュ値が正しいことも確認します。 先ほどはPythonでハッシュ値を出しましたが、今度はOpenSSLを用いて確認してみます。

$ openssl version
OpenSSL 1.0.2f 28 Jan 2016
$ cat ./id2.txt
Test%
$ openssl dgst -sha256 ./id2.txt
SHA256(./id2.txt)= 532eaabd9574880dbf76b9b8cc00832c20a6ec113d682299550d7a6e0f345e25
$ cat ./id3.txt
TEST%
$ openssl dgst -sha256 ./id3.txt
SHA256(./id3.txt)= 94ee059335e587e501cc4bf90613e0814f00a7b08bc7c648fd865a2af6a22cc2

RedshiftのUDFで求めた値とハッシュ値が一致していることが確認できました。