AthenaとBigQueryで同じハッシュ値を使いたい

o3君を使っても意外と10分くらいかかってしまったので、結果を残しておく。

結論としては、SHA256でハッシュ化するが、前半半分を取って、32 bit 整数ハッシュにする。

実装

以下でPython, Athena, BigQueryで同じ結果が得られる。

Python

import hashlib


def hash32(value: str) -> int:
    # 先頭 4 byte (=32bit) を取り出して整数化
    digest = hashlib.sha256(value.encode()).digest()
    n = int.from_bytes(digest[:4], byteorder="big", signed=False)
    return n

# 動作確認
print(hash32("chatgpt"))  # 1620463976 (0x60965168)
print(hash32("bar"))  # 4242418478 (0xFCDE2B2E)  ← unsigned

Athena

SELECT
    -- ⑤16進 → 10進 (32bit 整数, 正数として取得)
    from_base(
        -- ①UTF-8 → ②SHA-256 → ③16進文字列 → ④先頭8byte
        substr(to_hex(sha256(to_utf8('bar'))), 1, 8),
        16
    )

BigQuery

SELECT
    -- 16進→INT64 は '0x' プレフィクス付き文字列を CAST するだけ
    CAST(
        CONCAT('0x', SUBSTR(TO_HEX(SHA256('bar')), 1, 8))
        AS INT64
    )

用途

2で割った余りでグループ分けすれば、A/Bテストの割り振りに使える。

余りの計算は、PythonとAthenaではval % 2、BigQueryではMOD(val, 2)