embulk-filter-hashやRuryのハッシュ値と同じ値をBigQueryでがんばる

データ基盤旧環境で古い環境のハッシュ変換値をBigQueyの関数で実行した際の備忘録です。MD5の計算まではどの言語を使っても同じですが変換後の処理はそれぞれのライブラリや関数によって違うのでドキュメントや直接コードの確認が必要です。

BigQueryのMD5関数
> -- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
Hash functions  |  BigQuery  |  Google Cloud

RubのMD5関数
>digest(data) -> String[permalink][rdoc][edit]
>data のダイジェストを SHA256 で計算します。

>[PARAM] data:
>ダイジェストを計算する文字列
>hexdigest(data) -> String[permalink][rdoc][edit]
>data のダイジェストを SHA256 で計算し、16進文字列で返します。
docs.ruby-lang.org

RubyでBigQuey側の関数に合わせると以下のようになります

    element = Digest::SHA256.digest(element)
    element = Base64.encode64(element)

embulk-filter-hash

embulk-filter-hashでMD5を利用してます。

    enum class AlgorithmType {
        MESSAGE_DIGEST {
            override fun validate(config: HashColumn) {}

            override fun generateHash(value: String, config: HashColumn): String {
                val algorithm = config.algorithm.get()
                return MessageDigest.getInstance(algorithm).run {
                    update(value.toByteArray())
                    digest().joinToString("") { "%02x".format(it) }
                }
            }
        },

embulk-filter-hash/HashFilterPlugin.kt at master · kamatama41/embulk-filter-hash · GitHub

一応ローカルでみる

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

public class md5HashTest {
    public static void main(String[] args) {
        String strHash = "";
        System.out.println("target：" + strHash);
        try{
            MessageDigest md5 = MessageDigest.getInstance("MD5");
            byte[] result = md5.digest(strHash.getBytes());
            StringBuffer sb = new StringBuffer();
            for (int j=0; j < result.length; j++){
                sb.append(java.lang.String.format("%02x", result[j]));
                System.out.println(java.lang.String.format("%02x", result[j]));
            }
            System.out.println(sb);
        } catch (NoSuchAlgorithmException x){

        }
    }
}

BigQueryでやる場合こうなります

SELECT
  TO_HEX((md5(CONCAT('email')))) AS EMAIL

なお、embulk-filter-hashのあとRubyで行っていた加工処理は誤差があり、JavascriptのUDFを利用しても誤差がでてしまいBigQueryでは実現できませんでした。悲しみ
Ruby

element.to_i(16)

JavaScript

parseInt(element, 16)